IBATIS learning Summary

IBATIS learning Summary

====================================== SqlMapConfig ================== ====================

1. classpath dependent Project

Download the latest ibatis-x.x.x.xxx.zip file for the latest ibatis-2.3.4.726.jar,
Download the jar package that iBATIS depends on according to the description of the jar-dependencies file.
Add the jar package to the project classpath.

2. SQL Map configuration file

2.1. the SQL Map configuration file has a unique <properties> element

<Properties resource = "com/newbee/SqlMapConfig. properties"/>

2.2./sqlMapConfig/settings global Parameters

CacheModelsEnabled = "true" # enable or disable all cache models of SqlMapClient globally
EnhancementEnabled = "true" # enable or disable runtime bytecode enhancement globally to optimize the performance of accessing Java Bean attributes
LazyLoadingEnabled = "true" # enable or disable all delayed loading of SqlMapClient globally
MaxRequests = "32" # maximum number of threads that execute SQL statements simultaneously
MaxSessions = "10" # maximum number of sessions active at the same time
MaxTransactions = "5" # maximum number of threads simultaneously entering SqlMapClient. startTransaction ()
UseStatementNamespaces = "false" # use a fully qualified name to reference mapped statement

2.3 dataSource type

2.3.1. SIMPLE Type

<TransactionManager type = "JDBC" commitRequired = "false"> # Whether the select statement needs to be submitted by the commitRequired query statement
<DataSource type = "SIMPLE">
<Property name = "JDBC. Driver" value = "org. postgresql. Driver"/>
<Property name = "JDBC. ConnectionURL" value = "jdbc: postgresql: // server: 5432/dbname"/>
<Property name = "JDBC. Username" value = "user"/>
<Property name = "JDBC. Password" value = "password"/>
<! -- Optional properties below -->
<Property name = "Pool. MaximumActiveConnections" value = "10"/>
<Property name = "Pool. MaximumIdleConnections" value = "5"/>
<Property name = "Pool. MaximumCheckoutTime" value = "120000"/>
<Property name = "Pool. TimeToWait" value = "10000"/>
<Property name = "Pool. PingQuery" value = "select * from dual"/>
<Property name = "Pool. PingEnabled" value = "false"/>
<Property name = "Pool. PingConnectionsOlderThan" value = "0"/>
<Property name = "Pool. PingConnectionsNotUsedFor" value = "0"/>
</DataSource>
</TransactionManager>

2.3.2 DBCP type (CS application Recommendation)

<TransactionManager type = "JDBC">
<DataSource type = "DBCP">
<Property name = "JDBC. Driver" value = "$ {driver}"/>
<Property name = "JDBC. ConnectionURL" value = "$ {url}"/>
<Property name = "JDBC. Username" value = "$ {username}"/>
<Property name = "JDBC. Password" value = "$ {password}"/>
<! -- Optional properties below -->
<Property name = "Pool. MaximumActiveConnections" value = "10"/>
<Property name = "Pool. MaximumIdleConnections" value = "5"/>
<Property name = "Pool. MaximumWait" value = "60000"/>
<! -- Use of the validation query can be problematic.
If you have difficulty, try without it. -->
<Property name = "Pool. ValidationQuery" value = "select * from ACCOUNT"/>
<Property name = "Pool. LogAbandoned" value = "false"/>
<Property name = "Pool. RemoveAbandoned" value = "false"/>
<Property name = "Pool. RemoveAbandonedTimeout" value = "50000"/>
</Datasource>
</TransactionManager>

2.3.3. JNDI type (recommended for servers)

<TransactionManager type = "JDBC">
<DataSource type = "JNDI">
<Property name = "DataSource" value = "java: comp/env/jdbc/newbee"/>
</DataSource>
</TransactionManager>

3./sqlMapConfig/sqlMap file reference parameters

<SqlMap resource = "com/newbee/domain/Account. xml"/>

====================================== SqlMap ================== ====================

1. namespace to avoid attribute name conflicts

<SqlMap namespace = "Account">

2. The <typeAlias> element allows you to specify a short alias for a normally long, fully qualified class name.

<TypeAlias alias = "Account" type = "com. newbee. domain. Account"/> # defined here

<ResultMap id = "AccountResult" class = "Account"> # Use
<Result property = "id" column = "ACC_ID"/>
<Result property = "firstName" column = "ACC_FIRST_NAME"/>
<Result property = "lastName" column = "ACC_LAST_NAME"/>
<Result property = "emailAddress" column = "ACC_EMAIL"/>
</ResultMap>

3. The <statement> element is a general declaration and can be used for any type of SQL statements, or even contains multiple statements (separated ). Generally, it is a good idea to use the following specific statement type.

<Insert>
<Update>
<Delete>
<Select>
<Procedure>

4. Special SQL characters: Because SQL statements are embedded in XML documents, some special characters cannot be used directly, such as greater than or less than signs (<> ).
You only need to place SQL statements containing special characters in the CDATA area of XML.

<Statement id = "getPersonsByAge" parameterClass = "int" resultClass = "examples. domain. Person">
<! [CDATA [SELECT *
FROM PERSON
Where age> # value #
]>
</Statement>

3. Automatic Generation of primary keys: many databases support Automatic Generation of primary key data types. However, this is usually (not always) a private feature.
SQL Map supports automatically generated key values through the <insert> sub-element <selectKey>. It also supports pre-generation (such as Oracle)
And generate two types (such as MS-SQL Server ). This value is the return value for SQL Map to execute the insert () method.

<! -Oracle SEQUENCE Example -->
<Insert id = "insertProduct-ORACLE" parameterClass = "com. domain. Product">
<SelectKey resultClass = "int" keyProperty = "id">
Select stockidsequence. NEXTVAL AS ID FROM DUAL
</SelectKey>
Insert into PRODUCT (PRD_ID, PRD_DESCRIPTION)
Values (# id #, # description #)
</Insert>

<! -Microsoft SQL Server IDENTITY Column Example -->
<Insert id = "insertProduct-MS-SQL" parameterClass = "com. domain. Product">
Insert into PRODUCT (PRD_DESCRIPTION)
Values (# description #)
<SelectKey resultClass = "int" keyProperty = "id">
SELECT @ IDENTITY AS ID
</SelectKey>
</Insert>

4. SQL Map supports stored procedures through the <procedure> element. If the mode attribute of the parameter is set to INOUT or OUT, the value of the parameter object is modified. The IN parameter object
Is not modified.

<ParameterMap id = "swapParameters" class = "map">
<Parameter property = "email1" jdbcType = "VARCHAR" javaType = "java. lang. String" mode = "INOUT"/>
<Parameter property = "email2" jdbcType = "VARCHAR" javaType = "java. lang. String" mode = "INOUT"/>
</ParameterMap>
<Procedure id = "swapEmailAddresses" parameterMap = "swapParameters">
{Call swap_email_address (?, ?)}
</Procedure>

5. SQL input parameters (a description of a single parameter)

5.1 define Java Bean as input parameters (mixed use of parameterClass, parameterMap, and Inline Parameter)

Simplified Version:

<Statement id = "insertProduct" parameterClass = "com. domain. Product">
Insert into PRODUCT (PRD_ID, PRD_DESCRIPTION)
Values (# id #, # description #);
</Statement>

Exact version: (the field corresponding to the date type must specify the field type. You can specify the field type and null value for null fields. Blob field types are not supported)

<Statement id = "insertProduct" parameterClass = "com. domain. Product">
Insert into PRODUCT (PRD_ID, PRD_DESCRIPTION)
Values (# id: NUMERIC:-999999 #, # description: VARCHAR: NO_ENTRY #);
</Statement>

5.2 Basic type input parameters

If you do not need to write a Java Bean as a parameter, you can directly use the basic type of packaging class (that is, String, Integer, Date, etc.) as a parameter.

<Statement id = "insertProduct" parameter = "java. lang. Integer">
Select * from PRODUCT where PRD_ID = # value #
</Statement>

5.3 Map type input parameters

If you do not need to write a Java Bean as a parameter, and you need to input more than one parameter, you can use the Map class (such as HashMap and TreeMap) as the parameter object.

<Statement id = "insertProduct" parameterClass = "java. util. Map">
Select * from PRODUCT
Where prd_cat_id = # catid #
And prd_code = # code #
</Statement>

6. SQL return value (a description of a single return value)

6.1 define Java Bean as return value

Simplified Version:

<Statement id = "getproduct" resultclass = "com. ibatis. example. Product">
Select
PRD_ID as id, # id field attribute
PRD_DESCRIPTION as description # description field attribute
From PRODUCT
Where PRD_ID = # value #
</Statement>

Exact version: (the field corresponding to the date type must specify the field type. You can specify the field type and null value for null fields. The Blob field type is supported)

<ResultMap id = "get-product-result" class = "com. ibatis. example. Product">
<Result property = "ID" column = "prd_id"/>
<Result property = "Description" column = "prd_description" jdbctype = "varchar" nullvalue = "no_entry"/>
</Resultmap>

<Statement id = "getproduct" resultmap = "get-product-result">
Select
Prd_id as ID, # Id field attribute
Prd_description as description # Description field attribute
From Product
Where prd_id = # value #
</Statement>

6.2 basic type return value

<Statement id = "getproductcount" resultclass = "Java. Lang. Integer">
Select count (*) as Value
From Product
</Statement>

6.3 Map type return value

<Statement id = "getProductCount" resultClass = "java. util. HashMap">
Select * from PRODUCT
</Statement>

7. cache the Mapped Statement result set (only used in select)

7.1 define the cache Mode

<CacheModel id = "product-cache" type = "LRU" readOnly = "true" serialize = "false">
<FlushInterval hours = "24"/> # automatically refresh once every 24 hours.
<FlushOnExecute statement = "insertProduct"/> # specify the statement to be refreshed once.
<FlushOnExecute statement = "updateProduct"/>
<FlushOnExecute statement = "deleteProduct"/>
<Property name = "cache-size" value = "1000"/> # cache size
</CacheModel>

7.2 use the cache Mode

<Statement id = "getProductList" cacheModel = "product-cache">
Select * from product where prd_cat_id = # value #
</Statement>

8. Dynamic mapped statement

8.1 binary dynamic conditions

<Isequal> compare whether the property value is equal to the static value or another property value.
<Isnotequal> compare whether the property value is not equal to the static value or another property value.
<Isgreaterthan> compare whether the property value is greater than the static value or another property value.
<Isgreaterequal> compares whether the property value is greater than or equal to the static value or another property value.
<Islessthan> compares whether the property value is smaller than the static value or another property value.
<Islessequal> compares whether the property value is less than or equal to the static value or another property value.

Available attributes:

Prepend-an SQL statement that can be overwritten. (optional)
Property-the property to be compared (required)
CompareProperty-another property used for comparison with the former (required or selected compareValue)
CompareValue-value used for comparison (required or compareProperty)

Example:

<Statement id = "someName" resultMap = "account-result">
Select * from ACCOUNT
<Dynamic prepend = "where">
<IsGreaterThan prepend = "and" property = "id" compareValue = "0"> # binary condition, and will be overwritten by where
ACC_ID = # id #
</IsGreaterThan>
<IsNotNull prepend = "and" property = "lastName">
ACC_LAST_NAME = # lastName #
</IsNotNull>
</Dynamic>
Order by ACC_LAST_NAME
</Statement>

8.2 one-dollar dynamic conditions

<IsPropertyAvailable>
Check whether this property exists (the property of parameter bean exists ).
<IsNotPropertyAvailable>
Check whether this attribute does not exist (the parameter bean attribute does not exist ).
<IsNull>
Check whether the property is null.
<IsNotNull>
Check whether the property is not null.
<IsEmpty>
Check the value of Collection. size (), String or String. valueOf () of the property, whether it is null or empty ("" or size () <1 ).
<IsNotEmpty>
Check the value of Collection. size (), String or String. valueOf () of the property, whether it is not null or not empty ("" or size ()> 0 ).

Available attributes:

Prepend-an SQL statement that can be overwritten. (optional)
Property-the property to be compared (required)

8.3 other dynamic conditions

8.3.1. Parameter Present: these elements check whether the Parameter object exists.

<Isparameterpresent>
Check whether a parameter object exists (not null ).
<Isnotparameterpresent>
Check whether the parameter object does not exist (the parameter object is null ).

Available attributes:

Prepend-an SQL statement that can be overwritten. (optional)

Example:

<IsNotParameterPresent prepend = "AND">
EMPLOYEE_TYPE = 'default'
</IsNotParameterPresent>

8.3.2 and Iterate: This property traverses the entire List set and repeats content for each element in the set.

<Iterate> elements whose traversal type is java. util. List.

Available attributes:

Prepend-an SQL statement that can be overwritten. (optional)
Property-elements used for traversal whose type is Java. util. List (required)
Open-string starting from the whole traversal content body, used to define parentheses (optional)
Close-the end string of the entire traversal body, used to define parentheses (optional)
Conjunction-a string between each traversal content, used to define and or (optional)

Example:
<Iterate prepend = "and" property = "usernamelist"
Open = "(" close = ")" conjunction = "or">
Username = # userNameList [] # -- [] Do not lose
</Iterate>

8.3.3 dynamic SQL Elements

It helps implement dynamic order by clauses, dynamic query fields, or other dynamic parts of SQL statements.
It fundamentally changes the SQL statement itself, much more serious than simply changing the parameter value.
It uses $ instead of # To include dynamic variables.

<Statement id = "getProduct" resultMap = "get-product-result">
Select * from PRODUCT order by $ preferredOrder $
</Statement>

9. Batch Processing SQL

SqlMap. startBatch ();
// Execute statements in
SqlMap.exe cuteBatch ();

10. transaction-related

10.1 transactions are automatically submitted and managed each time an SQL statement is executed by default.

10.2 If the following code is used, you need to manage the transaction by yourself.

Try {
Sqlmap. starttransaction ();
// Insert, update, delete statements
Sqlmap. committransaction ();
} Finally {
Sqlmap. endtransaction ();
}

11. Configure log4j to display the executed SQL

11.1 copy log4j. jar to the project class path
11.2 configure all the content of the log4j. properties file as follows (put in the root directory of the source code folder)

#--------------------------------
# Level = fatal error warn info debug

Log4j. rootlogger = debug, stdout, fileout # the SQL statement is no longer displayed when the bebug is changed to another level.

Log4j. appender. stdout = org. Apache. log4j. leleappender

Log4j. appender. fileout = org. Apache. log4j. rollingfileappender
Log4j. appender. fileout. File =/home/Guo/ibatis. Log
Log4j. appender. fileout. MaxFileSize = 0000kb

Log4j. appender. stdout. layout = org. apache. log4j. PatternLayout
Log4j. appender. stdout. layout. ConversionPattern = [%-5 p] % d {yyyy-MM-dd HH: mm: ss }:% m % n
Log4j. appender. fileout. layout = org. apache. log4j. PatternLayout
Log4j. appender. fileout. layout. ConversionPattern = [%-5 p] _ % d {yyyy-MM-dd HH: mm: ss }:% m % n