IBatis SQL 映射

sm702

IBatis SQL 映射
--------------------
1. 特殊字符
<select id="getPersonsByAge" parameterClass=”int” resultClass="examples.domain.Person">
SELECT *
FROM PERSON
WHERE AGE <![CDATA[  >  ]]>  #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;
}
}