ibatis SQL Map demo

在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();
		}