Naming SQL datasets

The so-called named SQL is actually the SQL statement in the database, Primeton Eos made a certain encapsulation, in order to facilitate the use of the program.

The basic elements of named SQL include:

1. <parameterMap> Parametermap is responsible for mapping object properties to statement parameters.

2. <resultMap> Resultmap is responsible for mapping the column values of the result set to the object's property values

3. The <statement> (Mapped statement) element is a generic declaration that can be used with any type of SQL statement,

But specific statement types (i.e. <insert>, <update>, <delete>, <select>, <procedure>)

Provides a more intuitive XML DTD, so it is highly recommended. The following table summarizes the statement types and their properties: statement type properties

"One" for the use of various statement examples:

(1) If you use special characters (such as < or &) or entity references (such as < or &) in a script or style sheet, you need to mark the contents of the script or style sheet as CDATA (the number of characters

If you do not use CDATA, you must use the corresponding entity instead, for example < must replace it with &lt;

(2) named SQL parameters with # #包起来, generated is PreparedStatement, you can also use $$ to wrap the parameters, which is generated statement, that is, when executing SQL

The parameters are put into SQL first, and are generally used in the like or in operation. When the in operation writes a named SQL, the arguments cannot be written in the In (#field #) manner, but should be written as

In ($filed $), because the in operation does not support PreparedStatement.

<updateid= "Saveexamineinfo" parameterclass= "Java.util.HashMap" > <!                      [cdata[UPDATE $mainTableName $ T SET t.main_check1 = ' $mainCheck 1$ ', T.main_check2 = ' $mainCheck 2$ ' WHERE t.main_id = $mainId $]]></update> <deleteid= "deletetemplate" parameter class= "Java.util.HashMap" > <! [cdata[Delete from iptsm_templateinfo where no = $no $]]></delete> <selectid= " Gettemplateinfo "parameterclass=" Java.util.Map "resultclass=" Commonj.sdo.DataObject "" > <!              [cdata[SELECT No,identify,title, Filename,to_char (modifydate, ' yyyy-mm-dd HH24:mi:ss ') modifydate      From iptsm_templateinfo where identify = ' $identify $ ' and the TITLE like '% $title $% ' ORDER by Modifydate desc]]> </select> <procedure id= "swapemailaddresses" parametermap= "Swapparameters" > {call Swap_emai    L_address (?,?)} </procedUre> 

Use the usual or very complex work, pre-written with SQL statements and stored with a specified name, then to call the database to provide the same functionality as a defined stored procedure, just call execute to automatically complete the command

Advantages of stored procedures:

1. Stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so the stored procedure is used

Can improve database execution speed.

2. When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated in a stored procedure with the database

Transactional processing is used in conjunction with.

3. Stored procedures can be reused to reduce the workload of database developers

4. High security, can be set only a certain user has the right to use the specified stored procedure

"II" Parametermap

The PARAMETERMAP is responsible for mapping object properties into statement parameters.     <parametermap  id= "Parametermapname" class= "Commonj.sdo.DataObject" >           <parameter property = " The PropertyName "                    [jdbctype=" VARCHAR] property Javatype is used to explicitly specify the type of the parameter being assigned. If no type is provided, it is assumed to be of type object. The                      [javatype= string] property Javatype is used to explicitly specify the type of the parameter being assigned. If no type is provided, it is assumed to be of type object.                       the value of the [nullvalue= ' NUMERIC] Property nullvalue can be any legal value that is arbitrary for the properties type, specifying the replacement value for NULL. That is, when the property value equals the specified value, the corresponding field is assigned a value of NULL. This feature allows you to assign null to data types that do not support nulls (that is, int,double,float, and so on) in your app. />             <parameter .../>            <parameter .../>           </parameterMap> parentheses [] is an optional property of. A parametermap can contain any number of parameter elements. The various properties of parameter are discussed later

"three" Resultmap

In a named SQL Framework, Result Map is an extremely important component. When executing a query mapped statement, RESULTMAP is responsible for mapping the column values of the result set to the object's property values. The structure of the Resultmap is as follows:     <resultmap id= "Resultmapname" class= "Commonj.sdo.DataObject" >         <result property= " PropertyName "column=" column_name "[columnindex=" 1 "]                [javatype=" int "] [jdbctype=" NUMERIC "] [nullvalue="-999999 "]/>         <result .../>         <result .../>     </resultMap> brackets [] are optional properties. The id attribute of the RESULTMAP is a unique identity. The class property of Resultmap is used to specify the type name of the DataObject (including the URI). Resultmap can include any number of attribute mappings, mapping the column values of the query result set to DataObject properties. Properties are mapped in the order in which they are defined in the Resultmap

Below we illustrate:

<?xml version= "1.0" encoding= "UTF-8"?><!--author:asus--><sqlmap> <parametermap class= " Commonj.sdo.DataObject "id=" Parametermap "> <parameter javatype=" date "jdbctype=" date "property=" Datetype "/&gt    ;  </parameterMap> <resultmap class= "Commonj.sdo.DataObject" id= "Resultmap" > <result column= "TYPEID" Javatype= "string" property= "TypeId"/> </resultMap> <select id= "select_id" parameterclass= "Java.util.Has Hmap "resultclass=" Commonj.sdo.DataObject "><!  [cdata[Select NVL (s.tdhisrownum, 0) as Tdhisrownum, Decode (NVL (S.customer_name, ' 0 '), ' 0 ', ' total ', s.customer_name) As Customer_name, s.pack_sell_id, S.pack_sell_code, S.pack_sell_name, sum (s.sell_num) as Sell        _num, S.mscell_name, S.gcmaxdate, sum (s.gctic) as gctic, sum (s.realticsum) as Realticsum, SUM (s.dktic) as dktic, sum (s.gcticincome) as Gcticincome, sum (s.realticincome) as REalticincome, SUM (s.dkticincome) as Dkticincome, S.empname, s.hxdate, Case S.hasproblem WH En ' p1 ' then ' if ' P2 ' then ' none ' else null end as Hasproblem, Case s.pronature when ' 1 ' then '        Funding tense ' when ' 2 ' then ' Project Issues ' when ' 3 ' then ' customer problem ' when ' 4 ' then ' other issues ' else null end as ProNature, S.prodesc, S.solutions, S.orgid, s.orgname from Dp_report_packtictdhis S]]><dyna Mic prepend= "WHERE" ><isnotnull prepend= "and" property= "ORW1" > S.dktic > 0 </isnotnull>&l           T;isnotnull prepend= "and" property= "CustomerName" > Customer_name like ' $customerName $ ' </isNotNull> <isnotnull prepend= "and" property= "Packsellcode" > Pack_sell_code like '% $packSellCode $% ' </isnotn Ull> <isnotnull prepend= "and" property= "Packsellname" > Pack_sell_name like '% $packSellName $% ' &lt ;/isnotnull> <iSnotnull prepend= "and" property= "EmpName" > EmpName like '% $empname $% ' </isNotNull> <isnotnull Prepend= "and" property= "OrgName" > OrgName like '% $orgname $% ' </isNotNull> <isnotnull prepend= ' and "property=" Hasproblem "> Hasproblem like '% $hasproblem $% ' </isNotNull> <isnotnull prepend=" and "property=" ProNature "> pronature like '% $pronature $% ' </isNotNull> <isnotnull prepend=" and "property=" Hxdate "> Hxdate >= to_date (' $hxdate $ ', ' yyyy-mm-dd hh24:mi:ss ') </isNotNull> <i Snotnull prepend= "and" property= "Tohxdate" > Hxdate <= to_date (' $tohxdate $ ', ' yyyy-mm-dd hh24:mi:ss ') &lt ;/isnotnull> <isnotnull prepend= "and" property= "Gcmaxdate" > Gcmaxdate >= to_date (' $gcmaxdate $ ', ' Y Yyy-mm-dd hh24:mi:ss ') </isNotNull> <isnotnull prepend= "and" property= "Togcmaxdate" > Gcmaxdat E <= to_date (' $TOGCMAxdate$ ', ' yyyy-mm-dd hh24:mi:ss ') </isNotNull> </dynamic>group by Rollup ((S.tdhisrownum,s.customer_nam                  E, s.pack_sell_id, S.pack_sell_code, S.pack_sell_name, S.mscell_name, S.gcmaxdate, S.empname, S.hxdate, S.hasproblem, S.pronature, S.prodesc, S.solutions, S.orgid,s.orgname)) Order by Tdhisrownu M Asc,s.hxdate asc</select></sqlmap>

Naming SQL datasets