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');

然后是iBatis的配置文件 SqlMapConfig.xml
这里进行数据源的配置以及一些参数的设置和优化
Java代码

<?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>

User.java就是domain了，是映射的对象。
Java代码

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;
	}
}

下面是比较重要的SQL Map XML映射文件，所有方法都在这里。
User.xml
Java代码

<?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" class="UserObject">
		<result property="id" column="id" jdbcType="NUMBER" />
		<result property="name" column="name" jdbcType="VARCHAR2" />
		<result property="date" column="date" jdbcType="DATE" />
	</resultMap>

	<select id="getByPK" resultMap="userResult" parameterClass="UserObject">
		<![CDATA[ 
		select
		  id,
		  name, 
		  date 
		from t_user 
		where id = #id# 
		]]>
	</select>
	
	<select id="getById" resultMap="userResult" parameterClass="java.lang.String">
		<![CDATA[ 
		select
		  id,
		  name, 
		  date 
		from t_user 
		where id = $String$
		]]>
	</select>

	<sql id="Dy_SC">
		<dynamic prepend="WHERE">
			<isNotNull prepend="AND" property="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" parameterClass="UserObject">
		INSERT INTO t_user (name,date) VALUES (#name#,#date#)
	</insert>
	
	<insert id="insertUserTest" parameterClass="UserObject">
		INSERT INTO t_user (id,name,date) VALUES (#id#,#name#,#date#)
	</insert>

	<update id="updateUser" parameterClass="UserObject">
		<![CDATA[ 
		UPDATE t_user 
		SET  
		  name=#name#, 
		  date=#date# 
		WHERE id = #id#
		]]>
	</update>

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

	<statement id="getMaxId" resultClass="java.lang.Integer">
		select Max(id) from t_user
	</statement>

	<statement id="getMax" resultClass="java.util.HashMap">
		select Max(id) as id,Max(name) as name,Max(date) as date from t_user
	</statement>

</sqlMap>

这样就可以来测试了，测试也使用了两种方法，先使用一个普通应用程序来测试一下程序的运行好了
Java代码

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

方便测试，首先为SqlMap的单元测试编写一个抽象的测试基类

Java代码

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;
    }
}

然后为每个SqlMap映射文件编写一个测试用例，extends上面的抽象类
Java代码

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为auto_increment，可能需要对测试数据中<dataset/>进行设置，网络中并未查询到相关的方法，所以在映射文件中加入了一个
Java代码

<insert id="insertUserTest" parameterClass="UserObject">
		INSERT INTO t_user (id,name,date) VALUES (#id#,#name#,#date#)
	</insert>

用例测试中测试的是insertUserTest，并非insertUser，请注意。

到这里就结束了，demo比较简单，希望能给大家带来帮助。

参考：
http://ibatis.apache.org/
http://hsqldb.org/
http://www.dbunit.org/