IBatis SQL 映射
IBatis SQL 映射
--------------------
1. 特殊字符
<select id="getPersonsByAge" parameterClass=”int” resultresultresultparameter>
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" parameter>
SELECT @@IDENTITY AS ID
</selectKey>
</insert>
4. 存储过程
<parameterMap id="swapParameters" >
<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 configurationlog4j.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.ConsoleAppenderlog4j.appender.stdout.layout=org.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
<?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>
<?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" parameterparameterparameterparameterparametername="code">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;}}
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;}@Overridepublic String toString() {return "id=" + id + " name=" + name + " sex=" + sex;}}