ibatas增删改查
相对Hibernate和Apache OJB 等“一站式”ORM解决方案而言，ibatis 是一种“半
自动化”的ORM实现。
所谓“半自动”，可能理解上有点生涩。纵观目前主流的ORM，无论Hibernate 还是
Apache OJB，都对数据库结构提供了较为完整的封装，提供了从POJO 到数据库表的全
套映射机制。程序员往往只需定义好了POJO 到数据库表的映射关系，即可通过Hibernate
或者OJB 提供的方法完成持久层操作。程序员甚至不需要对SQL 的熟练掌握，
Hibernate/OJB 会根据制定的存储逻辑，自动生成对应的SQL 并调用JDBC 接口加以执
行。 ----摘自官方资料的一段话
iBatis是一种很好的解决方案，使用起来很灵活，参考一些网络中的资料我也想把自己的使用过程写下来，如有错误希望指正。
环境：JDK1.5+Eclipse3.2 使用时仅需要在Eclipse中导入项目。
首先是表结构, 提供了两种数据库的支持分别为MySQL与hsqldb，可以根据实际情况选择使用。以MySQL为例：
Java代码
create database if not exists `ibatis_schema`;USE `ibatis_schema`;drop table if exists `t_user`;CREATE TABLE `t_user` ( `id` int(12) NOT NULL auto_increment, `name` varchar(50) default NULL, `date` date default NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=GBK;Insertinto`t_user`(name,date) values('liulu','2007-03-15'),('liulu2','2007-03-15'),('liulu3','2007-03-15');<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"><sqlMapConfig><settings cacheModelsEnabled="true" useStatementNamespaces="true" /><transactionManager type="JDBC"><dataSource type="SIMPLE"><property name="JDBC.Driver" value="com.mysql.jdbc.Driver" /><property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/ibatis_schema" /><property name="JDBC.Username" value="root" /><property name="JDBC.Password" value="1234" /></dataSource></transactionManager><sqlMap resource="com/javaeye/liulu/maps/User.xml" /></sqlMapConfig>
package com.javaeye.liulu.domain;import java.io.Serializable;import java.util.Date;public class User implements Serializable{private int id;private String name;private Date date;public Date getDate() {return date;}public void setDate(Date date) {this.date = date;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}}<?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="User"><typeAlias alias="UserObject" type="com.javaeye.liulu.domain.User" /><resultMap id="userResult" column="id" jdbcType="NUMBER" /><result property="name" column="name" jdbcType="VARCHAR2" /><result property="date" column="date" jdbcType="DATE" /></resultMap><select id="getByPK" resultMap="userResult" parameterresultMap="userResult" parameterproperty="id">id like #id#</isNotNull><isNotNull prepend="AND" property="name">name like #name#</isNotNull></dynamic></sql><select id="getUser" resultMap="userResult"><![CDATA[ select id, name, date from t_user ]]><include refid="Dy_SC" /></select><insert id="insertUser" parameterparameterparameterparameterresultresultname="code">package com.javaeye.liulu;import java.io.Reader;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import com.ibatis.common.jdbc.ScriptRunner;import com.ibatis.common.resources.Resources;import com.ibatis.sqlmap.client.SqlMapClient;import com.ibatis.sqlmap.client.SqlMapClientBuilder;import com.javaeye.liulu.domain.User;public class Main {//hsql初始化,对MySQL没有影响static {try {Properties props = Resources.getResourceAsProperties("properties/database.properties");String url = props.getProperty("url");String driver = props.getProperty("driver");String username = props.getProperty("username");String password = props.getProperty("password");if (url.equals("jdbc:hsqldb:mem:ibatisDemo")) {Class.forName(driver).newInstance();Connection conn = DriverManager.getConnection(url, username, password);try {ScriptRunner runner = new ScriptRunner(conn, false, false);runner.setErrorLogWriter(null);runner.setLogWriter(null);runner.runScript(Resources.getResourceAsReader("ddl/hsql/ibatisdemo-hsqldb-schema.sql"));runner.runScript(Resources.getResourceAsReader("ddl/hsql/ibatisdemo-hsqldb-dataload.sql"));} finally {conn.close();}}} catch (Exception e) {throw new RuntimeException("Description. Cause: " + e, e);}}/** * 初始化iBatis获得一个SqlMapClient对象 * * @param * @return SqlMapClient */public static SqlMapClient getSqlMapClient() {String resource = "com/javaeye/liulu/maps/SqlMapConfig.xml";SqlMapClient sqlMap = null;try {Reader reader = Resources.getResourceAsReader(resource);sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);} catch (Exception e) {e.printStackTrace();}return sqlMap;}/** * 插入一条记录 * * @param * @return */public static void insert() {SqlMapClient sqlMap = getSqlMapClient();try {sqlMap.startTransaction();User user = new User();user.setName("insert1");user.setDate(new Date());sqlMap.insert("User.insertUser", user);sqlMap.commitTransaction();} catch (SQLException e) {e.printStackTrace();}}/** * 将第一条记录的信息更新 * * @param * @return */public static void update() {SqlMapClient sqlMap = getSqlMapClient();try {sqlMap.startTransaction();User user = (User)sqlMap.queryForObject("User.getById", "1");user.setName("update1");sqlMap.update("User.updateUser", user);sqlMap.commitTransaction();} catch (SQLException e) {e.printStackTrace();} finally {try {sqlMap.endTransaction();} catch (SQLException e) {e.printStackTrace();}}}/** * 删除id最大的记录 * * @param * @return */public static void delete() {SqlMapClient sqlMap = getSqlMapClient();try {sqlMap.startTransaction();String maxId = sqlMap.queryForObject("User.getMaxId", null).toString();sqlMap.delete("User.deleteUser", maxId);sqlMap.commitTransaction();} catch (SQLException e) {e.printStackTrace();}}/** * 根据name查询User为Map的List * * @param * @return List */public static List getUser() {SqlMapClient sqlMap = getSqlMapClient();List<User> user = null;try {sqlMap.startTransaction();HashMap params = new HashMap();params.put("name", "%liulu%");user = sqlMap.queryForList("User.getUser", params);sqlMap.commitTransaction();} catch (SQLException e) {e.printStackTrace();} finally {try {sqlMap.endTransaction();} catch (SQLException e) {e.printStackTrace();}}return user;}/** * 查询各个字段的最大值(一般用于统计，此处演示使用方法) * * @param * @return */public static void getMax() {SqlMapClient sqlMap = getSqlMapClient();try {sqlMap.startTransaction();Map search = (HashMap) sqlMap.queryForObject("User.getMax", null);System.out.println(search.get("id").toString() + "\n"+ search.get("name").toString() + "\n"+ search.get("date").toString());sqlMap.commitTransaction();} catch (SQLException e) {e.printStackTrace();}}/** * 通过主键查找,返回user * * @param * @return */public static void getByPK() {SqlMapClient sqlMap = getSqlMapClient();User user = new User();try {sqlMap.startTransaction();user.setId(1);user = (User) sqlMap.queryForObject("User.getByPK", user);System.out.println(user.getId() + "\n" + user.getName() + "\n"+ user.getDate());sqlMap.commitTransaction();} catch (SQLException e) {e.printStackTrace();}}public static void main(String[] args) {//insert();//update();//delete();List<User> user = getUser();for (User o : user) {System.out.println("id:" + o.getId() + "\nname:" + o.getName()+ "\nDate:" + o.getDate() + "\n------------");}//getMax();//getByPK();}}package com.javaeye.liulu.test;import java.io.Reader;import java.sql.Connection;import java.sql.DriverManager;import java.util.Properties;import javax.sql.DataSource;import org.dbunit.DatabaseTestCase;import org.dbunit.database.DatabaseConnection;import org.dbunit.database.IDatabaseConnection;import com.ibatis.common.resources.Resources;import com.ibatis.db.util.ScriptRunner;import com.ibatis.sqlmap.client.SqlMapClient;import com.ibatis.sqlmap.client.SqlMapClientBuilder;public abstract class BaseSqlMapTest extends DatabaseTestCase { protected static SqlMapClient sqlMap; protected IDatabaseConnection getConnection() throws Exception { return new DatabaseConnection(getJdbcConnection()); } protected void setUp() throws Exception { super.setUp(); init(); } protected void tearDown() throws Exception { super.tearDown(); getConnection().close(); if (sqlMap != null) { DataSource ds = sqlMap.getDataSource(); Connection conn = ds.getConnection(); conn.close(); } } protected void init() throws Exception { initSqlMap("com/javaeye/liulu/maps/SqlMapConfig.xml", null); } protected SqlMapClient getSqlMapClient() { return sqlMap; } protected void initSqlMap(String configFile, Properties props) throws Exception { Reader reader = Resources.getResourceAsReader(configFile); sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader, props); reader.close(); } protected void initScript(String script) throws Exception { DataSource ds = sqlMap.getDataSource(); Connection conn = ds.getConnection(); Reader reader = Resources.getResourceAsReader(script); ScriptRunner runner = new ScriptRunner(); runner.setStopOnError(false); runner.setLogWriter(null); runner.setErrorLogWriter(null); runner.runScript(conn, reader); conn.commit(); conn.close(); reader.close(); } private Connection getJdbcConnection() throws Exception { /* Properties props = new Properties(); props.load(Resources.getResourceAsStream("properties/database.properties")); Class driver = Class.forName(props.getProperty("driver")); Connection conn = DriverManager.getConnection(props.getProperty("url"), props.getProperty("username"), props.getProperty("password")); */ Class driver = Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/ibatis_schema","root","1234"); return conn; }}package com.javaeye.liulu.test;import java.io.Reader;import java.util.Date;import java.util.HashMap;import java.util.List;import org.dbunit.dataset.IDataSet;import org.dbunit.dataset.xml.FlatXmlDataSet;import org.dbunit.operation.DatabaseOperation;import com.ibatis.common.resources.Resources;import com.javaeye.liulu.domain.User;public class UserTest extends BaseSqlMapTest {protected IDataSet getDataSet() throws Exception {Reader reader = Resources.getResourceAsReader("com/javaeye/liulu/test/user_seed.xml");return new FlatXmlDataSet(reader);}public void testGetByPK() throws Exception {User user = new User();user.setId(1);user = (User) sqlMap.queryForObject("User.getByPK", user);assertNotNull(user);assertEquals(user.getId(), 1);assertEquals(user.getName(), "liulu");assertEquals(user.getDate().getDay(), 1);}public void testGetUser() throws Exception {List users = null;HashMap params = new HashMap();params.put("name", "%liulu%");users = (List) sqlMap.queryForList("User.getUser", params);assertEquals(users.size(),3);}public void testInsertUser() throws Exception {User user = new User();user.setId(4);user.setName("insert1");user.setDate(new Date());sqlMap.insert("User.insertUserTest", user);User user2 = new User();user2.setId(4);user2 = (User) sqlMap.queryForObject("User.getById", "4");assertEquals(user.getId(),user2.getId());assertEquals(user.getName(),user2.getName());}public void testUpdateUser() throws Exception {User user = (User)sqlMap.queryForObject("User.getById", "1");user.setName("liulu7");sqlMap.update("User.updateUser", user);User user2 = (User)sqlMap.queryForObject("User.getById", "1");assertEquals(user2.getName(),"liulu7");}public void testDeleteUser() throws Exception {int num = sqlMap.delete("User.deleteUser", "1");assertEquals(num,1);}public void testGetMaxId() throws Exception {int i = (Integer)sqlMap.queryForObject("User.getMaxId", null);assertEquals(3,i);}}<insert id="insertUserTest" parameterClass="UserObject">INSERT INTO t_user (id,name,date) VALUES (#id#,#name#,#date#)</insert>