To process SQL statements using iBATIS, SqlMapConfig XML file and SqlMap XML file must be created. These files allow programmers to easily map JavaBeans to PreparedStatement parameters and ResultsSets.
This section describes how to create SqlMapConfig XML file, SqlMap XML file, and how to actually process SQL using this file in application. For more information on writing the sample program, please refer to the appendix.
Creating SqlMap File
SqlMap XML file is a file that specifies the SQL statements to be transferred to DB, mapping of parameters to be bound to PreparedStatement, and mappings of ResultSet.
The following is an example of writing SqlMap XML file that processes CRUD in person table (Person.xml).
<?xml version= "1.0" encoding= "UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" <sqlMap namespace= "Person" > <resultMap id= "PersonResult" class = "examples.domain.Person" > <result property= "id" column= "per_id" /> <result property= "name" column= "per_name" /> <result property= "birthDate" column= "per_birth_date" /> <result property= "weightInKilograms" column= "per_weight_kg" /> <result property= "heightInMeters" column= "per_height_m" /> </resultMap> <select id= "getPerson" parameterClass= "int" resultClass= "examples.domain.Person" > <![CDATA[ SELECT PER_ID as id, PER_NAME as name, PER_BIRTH_DATE as birthDate, PER_WEIGHT_KG as weightInKilograms, PER_HEIGHT_M as heightInMeters FROM PERSON WHERE PER_ID = #value# ]]> </select> <insert id= "insertPerson" parameterClass= "examples.domain.Person" > <![CDATA[ INSERT INTO PERSON (PER_ID, PER_NAME, PER_BIRTH_DATE, PER_WEIGHT_KG, PER_HEIGHT_M) VALUES (#id#, #name#, #birthDate#, #weightInKilograms#, #heightInMeters#) ]]> </insert> <update id= "updatePerson" parameterClass= "examples.domain.Person" > <![CDATA[ UPDATE PERSON SET PER_NAME = #name#, PER_BIRTH_DATE = #birthDate#, PER_WEIGHT_KG = #weightInKilograms#, PER_HEIGHT_M = #heightInMeters# WHERE PER_ID = #id# ]]> </update> <delete id= "deletePerson" parameterClass= "int" > <![CDATA[ DELETE PERSON WHERE PER_ID = #id# ]]> </delete> <select id= "getAllPersons" resultMap= "PersonResult" > <![CDATA[ SELECT * FROM person ]]> </select> </sqlMap> |
After executing the SELECT statement in the <resultMap> tag, define the Map object of the data to be stored in the ResultSet, and in the <insert>, <update>, <delete>, and <select> tags, define each SQL statement for CRUD operation. do.
For more information on each tag, refer to http://ibatis.apache.org or refer to the attached document iBATIS-SqlMaps-2-en.pdf.
Creating SqlMapConfig File
SqlMapConfig file is a SQL Maps configuration file that writes dataSource for DB connection, the path of SqlMap file, and other properties to control SqlMapClient.
The following is an example of the SqlMapConfig file (SqlMapConfigExample.xml).
<?xml version= "1.0" encoding= "UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" <sqlMapConfig> <properties resource= "db.properties" /> <settings cacheModelsEnabled= "true" enhancementEnabled= "true" lazyLoadingEnabled= "true" maxRequests= "32" maxSessions= "10" maxTransactions= "5" useStatementNamespaces= "false" /> <transactionManager type= "JDBC" > <dataSource type= "SIMPLE" > <property name= "JDBC.Driver" value= "${driver}" /> <property name= "JDBC.ConnectionURL" value= "${url}" /> <property name= "JDBC.Username" value= "${username}" /> <property name= "JDBC.Password" value= "${password}" /> <property name= "Pool.MaximumActiveConnections" value= "10" /> <property name= "Pool.MaximumIdleConnections" value= "5" /> <property name= "Pool.MaximumCheckoutTime" value= "120000" /> <property name= "Pool.TimeToWait" value= "500" /> <property name= "Pool.PingQuery" value= "select 1 from dual" /> <property name= "Pool.PingEnabled" value= "false" /> <property name= "Pool.PingConnectionsOlderThan" value= "1" /> <property name= "Pool.PingConnectionsNotUsedFor" value= "1" /> </dataSource> </transactionManager> <sqlMap resource= "Person.xml" /> </sqlMapConfig> |
In the <properties> tag, specify the path and name of the properties file in which properties defined in the form are written, in the <settings> tag, write the properties to control the SqlMapClient, and in the <transactionManager> and <dataSource>, write the DB information to connect. In addition, the <SqlMap> tag writes the path and name of the previously created SqlMap files.
For more detailed information on each tag, refer to http://ibatis.apache.org or refer to the attached document iBATIS-SqlMaps-2-en.pdf.
Creating SqlMapConfig file - iBatis.Net Integration
How to set up the Altibase connection of SqlMap.config when connecting through ODBC is explained briefly.
First, the user needs to install the Altibase ODBC Driver and add the User DSN in the ODBC Data Source Administrator.
For how to install and configure ODBC, refer to the technical document 'Altibase Windows ODBC'.
<!-- Database connection information --> <database> <provider name= "Odbc2.0" /> <dataSource name= "Altibase" connectionString= "DSN=Altibase5;USER ID=sys;PASSWORD=manager" /> </database> |
Among the various DBMS provides defined in providers.config, the provider to use when connecting to Altibase is Odbc2.0. Write Odbc2.0 in the <provider> tag.
In the <dataSource> tag, enter the DSN added by the ODBC Data Source Administrator in connectionString.
Creating Application
CRUD operations can be processed by integrating with objects mapped to DB tables using SqlMapClient instance in the application.
In order to integrate with DB using iBATIS, the user must first obtain the SqlMapClient object through the SqlMapConfig file. Then, the user can connect to the DB by calling method corresponding to CRUD through the SqlMapClient object.
The following is an application that INSERT, UPDATE, DELETE, and SELECT data in the person table of the DB.
Ex) SimpleConnection's PersonApp.java
… String resource = "SqlMapConfigExample.xml" ; Reader reader = Resources.getResourceAsReader(resource); SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader); //insert Person Person newPerson1 = new Person(); … sqlMap.insert ( "insertPerson" , newPerson1); … //select all Persons List<Person> list = (List<Person>)sqlMap.queryForList( "getAllPersons" ); … //update Person sqlMap.update( "updatePerson" , newPerson1); … //get Person Person person = (Person) sqlMap.queryForObject ( "getPerson" , personPk); … //delete Person sqlMap.delete ( "deletePerson" , new Integer( 1 )); … |
First, read the SqlMapConfig file to get the SqlMapClient object. (
String resource ="SqlMapConfigExample.xml";
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
)
Then, call each method of SqlMapClient class corresponding to CRUD. (
sqlMap.insert(), sqlMap.queryForList(), sqlMap.queryForObject(),sqlMap.update(), sqlMap.delete()
)
For detailed description of each method, refer to http://ibatis.apache.org or refer to the attached document iBATIS-SqlMaps-2-en.pdf file.