Named SQL Datasets in Primeton EOS

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

Property

<statement>

ID, Parameterclass, ResultClass, Parametermap, Resultmap

<insert>

ID, Parameterclass, parametermap

<update>

ID, Parameterclass, parametermap

<delete>

ID, Parameterclass, parametermap

<select>

ID, Parameterclass, ResultClass, Parametermap, Resultmap

<procedure>

ID, Parameterclass, ResultClass, Parametermap, Resultmap

In the actual use of the process is usually ignored <statement>, and directly to the <insert>,<update>,<delete>,<select>,< Procedure>

"One" for the use of various statement examples:

Ps:

(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" parameterclass= "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_email_address (?,?)}

</procedure>

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

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 = "PropertyName"

The [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. So

When the property value equals the specified value, the corresponding field is assigned a value of NULL. This feature allows for data types that do not support NULL in the application

(i.e., int,double,float, etc.) is assigned a null value.

/>

<parameter .../>

<parameter .../>

</parameterMap>

brackets [] are optional properties. 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 Resultmap.

Original: http://hi.baidu.com/alimyself/item/b4ff4cc64259de76cfd4f807