IBatis SQL 映射

IBatis SQL 映射
--------------------
1. 特殊字符
<select id="getPersonsByAge" parameterClass=”int” resultClass="examples.domain.Person">
SELECT *
FROM PERSON
WHERE AGE [u]<![CDATA[ [b]>[/b] ]]>[/u] #value#
</select>

2. SQL片段
<sql id="selectItem_fragment">
FROM items
WHERE parentid = 6
</sql>

<select id="selectItemCount" resultClass="int">
SELECT COUNT(*) AS total
<include refid="selectItem_fragment"/>
</select>

<select id="selectItems" resultClass="Item">
SELECT id, name
<include refid="selectItem_fragment"/>
</select>

3. 序列
<!—Oracle SEQUENCE Example -->
<insert id="insertProduct-ORACLE" parameterClass="com.domain.Product">
<selectKey resultClass="int" >
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" >
SELECT @@IDENTITY AS ID
</selectKey>
</insert>

4. 存储过程
<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. 模糊查询
SELECT * FROM PRODUCT WHERE PRD_DESCRIPTION LIKE '%$dog$%'

6. 配置Log4j日志
log4j.properties

# Global logging configuration
log4j.rootLogger=ERROR, stdout

# SqlMap logging configuration...
#log4j.logger.com.ibatis=DEBUG
#log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
#log4j.logger.com.ibatis.sqlmap.engine.cache.CacheModel=DEBUG
#log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientImpl=DEBUG

#log4j.logger.com.ibatis.sqlmap.engine.builder.xml.SqlMapParser=DEBUG
#log4j.logger.com.ibatis.common.util.StopWatch=DEBUG
#log4j.logger.java.sql.Connection=DEBUG
#log4j.logger.java.sql.Statement=DEBUG
#log4j.logger.java.sql.PreparedStatement=DEBUG
#log4j.logger.java.sql.ResultSet=DEBUG

# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

7. 动态SQL映射

<select id="dynamicGetAccountList"
cacheModel="account-cache"
resultMap="account-result" >
select * from ACCOUNT
<isGreaterThan prepend="and" property="id" compareValue="0">
where ACC_ID = #id#
</isGreaterThan>
order by ACC_LAST_NAME
</select>

上面的例子中，根据参数 bean“id”属性的不同情况，可创建两个可能的语句。如果参
数“id”大于 0，将创建下面的语句：
select * from ACCOUNT where ACC_ID = ?
或者，如果“id”参数小于等于 0，将创建下面的语句：
select * from ACCOUNT

8. 示例

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
	<settings 
		cacheModelsEnabled="true"
		lazyLoadingEnabled="true" 
		enhancementEnabled="true"
		errorTracingEnabled="true"
		maxRequests="32" 
		maxSessions="10"
		maxTransactions="5"
		useStatementNamespaces="true"
	/>
	<transactionManager type="JDBC">   <!-- 定义了ibatis的事务管理器有3中（JDBC,JTA,EXTERNAL） -->
		<dataSource type="SIMPLE">   <!-- type属性指定了数据源的链接类型，也有3种类型(SIMPLE,DBCP,JNDI) -->
			<property name="JDBC.Driver" value="org.hsqldb.jdbcDriver" />
			<property name="JDBC.ConnectionURL" value="jdbc:hsqldb:hsql://localhost/botoa" />
			<property name="JDBC.Username" value="sa" />
			<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="TimeToWait" value="500" />   <!-- 线程允许等待的最大时间 -->
		</dataSource>
	</transactionManager>
	<sqlMap resource="cn/bisoft/java/webapp/pojo/userMap.xml" />
</sqlMapConfig>

userMap.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>

	<typeAlias alias="User" type="cn.bisoft.java.webapp.pojo.User" />

	<select id="getUsers" parameterClass="java.lang.Integer"
		resultClass="User">
	    select id, name, sex 
	    from user
	    <dynamic prepend="WHERE"> 
			<isParameterPresent prepend="AND"> 
				id = #value# 
			</isParameterPresent>
		</dynamic> 

	</select>

	<select id="queryUserByCondition" parameterClass="java.lang.String"
		resultClass="User">
	    <![CDATA[
	    select id,name,sex 
	    from user 
	    where name like '%$name$%'
	    ]]>
	</select>

	<update id="updateUser" parameterClass="User">
        <![CDATA[
            update user set name=#name#,sex=#sex# where id=#id#
        ]]>
	</update>

	<insert id="insertUser" parameterClass="User">
		insert into
		user(name,sex) values(#name#,#sex#)
    </insert>

	<delete id="deleteUser" parameterClass="java.lang.Integer">
		delete from user where
		id=#value#
    </delete>

</sqlMap>

UserDaoImpl.java

package cn.bisoft.java.webapp.dao.impl;

import java.sql.SQLException;
import java.util.List;

import cn.bisoft.java.webapp.dao.UserDAO;
import cn.bisoft.java.webapp.pojo.User;
import cn.bisoft.java.webapp.util.IbatisUtil;

public class UserDAOImpl implements UserDAO {

	public void add(User user) {
		try {
			IbatisUtil.getClient().update("insertUser", user);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void delete(Integer id) {
		try {
			IbatisUtil.getClient().delete("deleteUser", id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void update(User user) {
		try {
			IbatisUtil.getClient().update("updateUser", user);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unchecked")
	public List<User> queryAll() {
		List<User> userList = null;
		try {
			userList = IbatisUtil.getClient().queryForList("getUsers");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		for(User user : userList)
		{
			System.out.println(user);
		}

		return userList;
	}

	@SuppressWarnings("unchecked")
	public List<User> queryByName(String name) {
		List<User> userList = null;
		try {
			userList = IbatisUtil.getClient().queryForList("queryUserByCondition", name);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		for(User user : userList)
		{
			System.out.println(user);
		}

		return userList;
	}

	public User query(Integer id) {
		User user = null;
		try {
			user = (User) IbatisUtil.getClient().queryForObject("getUsers", id);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println(user);

		return user;
	}

}

User.java

package cn.bisoft.java.webapp.pojo;

public class User {
	private Integer id;

	private String name;

	private Integer sex;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getSex() {
		return sex;
	}

	public void setSex(Integer sex) {
		this.sex = sex;
	}

	@Override
	public String toString() {
		return "id=" + id + " name=" + name + " sex=" + sex;
	}

}