在spring的jpestore中,数据持久层用到了iBATIS SQL Maps,这里写一个ibatis的简单demo,作为ibatis的学习笔记。
ibatis的SQL Map配置文件是xml形式,一般叫SqlMapConfig.xml,如下:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0/" "http://www.ibatis.com/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <!--settings配置是可选的,用来控制sqlMapping的详细配置,主要是事务处理。参数具体含义请参考ibatis开发指南--> <settings cacheModelsEnabled="true" enhancementEnabled="true" lazyLoadingEnabled="true" errorTracingEnabled="true" maxRequests="32" maxSessions="10" maxTransactions="5" useStatementNamespaces="false" /> <!--使用SimpleDataSource配置数据源,DataSource的iBATIS实现:SimpleDataSource类,Jakarta DBCP(Commons),和可通过JNDI上下文查找的DataSource(即应用服务器中的DataSource)--> <transactionManager type="JDBC"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="com.mysql.jdbc.Driver" /> <property name="JDBC.ConnectionURL" value="jdbc:mysql://127.0.0.1:3306/jpetstore?" /> <property name="JDBC.Username" value="root" /> <property name="JDBC.Password" value="" /> <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 ACCOUNT" /> <property name="Pool.PingEnabled" value="false" /> <property name="Pool.PingConnectionsOlderThan" value="1" /> <property name="Pool.PingConnectionsNotUsedFor" value="1" /> </dataSource> </transactionManager> <!--引入所有的SQL Map配置文件,这里的路径是classpath的相对路径,例如下面的配置说明引入Account.xml,位置在com/quqworld/test/ibatis包内--> <sqlMap resource="com/quqworld/test/ibatis/Account.xml" /> </sqlMapConfig>
接下来是SQL Map映射文件Account.xml:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="Account"> <!--在SQL Map框架中,Result Map是极其重要的组件。在执行查询Mapped Statement时,resultMap负责将结果集的列值映射成Java Bean的属性值。--> <resultMap id="result" class="org.springframework.samples.jpetstore.domain.Account"> <result property="username" column="userid" columnIndex="1" /> <result property="email" column="email" columnIndex="2" /> <result property="firstName" column="firstname" columnIndex="3" /> <result property="lastName" column="lastname" columnIndex="4" /> <result property="status" column="status" columnIndex="5" /> <result property="address1" column="addr1" columnIndex="6" /> <result property="address2" column="addr2" columnIndex="7" /> <result property="city" column="city" columnIndex="8" /> <result property="state" column="state" columnIndex="9" /> <result property="zip" column="zip" columnIndex="10" /> <result property="country" column="country" columnIndex="11" /> <result property="phone" column="phone" columnIndex="12" /></resultMap> <select id="getAllAccountByUserid" resultMap="result"> select account.userid, account.email, account.firstname, account.lastname, account.status, account.addr1, account.addr2, account.city, account.state, account.zip, account.country, account.phone from account where account.userid=#value# </select> </sqlMap>
下面是Java代码:
try {
String resource = "com/quqworld/test/ibatis/SqlMapConfig.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
Object object = sqlMap.queryForObject(
"getAllAccountByUserid", "ACID",new Account());
System.out.println("select result: " +object);
} catch (Exception e) {
e.printStackTrace();
}