ibatis增删节查实例

2012-06-29

ibatis增删改查实例相对Hibernate和Apache OJB 等“一站式”ORM解决方案而言，ibatis 是一种“半自动化”的ORM

ibatis增删改查实例
相对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

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
这里进行数据源的配置以及一些参数的设置和优化

1.<?xml version="1.0" encoding="UTF-8" ?>   2.  3.<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"  4.    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">   5.  6.<sqlMapConfig>   7.  8.    <settings cacheModelsEnabled="true" useStatementNamespaces="true" />   9.    <transactionManager type="JDBC">   10.        <dataSource type="SIMPLE">   11.            <property name="JDBC.Driver" value="com.mysql.jdbc.Driver" />   12.            <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/ibatis_schema" />   13.            <property name="JDBC.Username" value="root" />   14.            <property name="JDBC.Password" value="1234" />   15.        </dataSource>   16.    </transactionManager>   17.       18.    <sqlMap resource="com/javaeye/liulu/maps/User.xml" />   19.</sqlMapConfig>

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

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

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

所有的方法都是静态的，可以分别对映射方法进行测试。

下面使用的是单元测试对iBatis来测试，使用了dbunit这个开源项目。
首先，要为单元测试准备数据。使用DbUnit，我们可以用XML文件来准备测试数据集。
t_user对应表名
id，name，date分别对应列名

user_seed.xml
代码

<?xml version="1.0" encoding="GBK"?><dataset>    <t_user id="1"    name="liulu"        date="2007-01-01"/>        <t_user id="2"    name="liulu2"        date="2007-03-15"/>        <t_user id="3"    name="liulu3"        date="2007-03-18"/></dataset>

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

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

    发表时间：2007-03-27   收藏 < > 猎头职位: 北京: ITeye网站诚聘产品交互设计相关文章:
■关于jdk5范型写的dao的疑问
■IBatis简单实现（附主键自动生成）
■iabtis 测试通过的例子

推荐群组: DI
更多相关推荐相对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代码
1.create database if not exists `ibatis_schema`;
2.
3.USE `ibatis_schema`;
4.
5.drop table if exists `t_user`;
6.
7.CREATE TABLE `t_user` (
8. `id` int(12) NOT NULL auto_increment,
9. `name` varchar(50) default NULL,
10. `date` date default NULL,
11. PRIMARY KEY (`id`)
12.) ENGINE=InnoDB DEFAULT CHARSET=GBK;
13.
14.Insertinto `t_user`(name,date) values('liulu','2007-03-15'),('liulu2','2007-03-15'),('liulu3','2007-03-15');
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代码
1.<?xml version="1.0" encoding="UTF-8" ?>
2.
3.<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
4.    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
5.
6.<sqlMapConfig>
7.
8.    <settings cacheModelsEnabled="true" useStatementNamespaces="true" />
9.    <transactionManager type="JDBC">
10.        <dataSource type="SIMPLE">
11.            <property name="JDBC.Driver" value="com.mysql.jdbc.Driver" />
12.            <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/ibatis_schema" />
13.            <property name="JDBC.Username" value="root" />
14.            <property name="JDBC.Password" value="1234" />
15.        </dataSource>
16.    </transactionManager>
17.
18.    <sqlMap resource="com/javaeye/liulu/maps/User.xml" />
19.</sqlMapConfig>
<?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代码
1.package com.javaeye.liulu.domain;
2.
3.import java.io.Serializable;
4.import java.util.Date;
5.
6.public class User implements Serializable{
7.    private int id;
8.    private String name;
9.    private Date date;
10.    public Date getDate() {
11.        return date;
12.    }
13.    public void setDate(Date date) {
14.        this.date = date;
15.    }
16.    public int getId() {
17.        return id;
18.    }
19.    public void setId(int id) {
20.        this.id = id;
21.    }
22.    public String getName() {
23.        return name;
24.    }
25.    public void setName(String name) {
26.        this.name = name;
27.    }
28.}
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代码
1.<?xml version="1.0" encoding="UTF-8"?>
2.<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
3."http://www.ibatis.com/dtd/sql-map-2.dtd">
4.
5.<sqlMap namespace="User">
6.    <typeAlias alias="UserObject" type="com.javaeye.liulu.domain.User" />
7.    <resultMap id="userResult" column="id" jdbcType="NUMBER" />
9.        <result property="name" column="name" jdbcType="VARCHAR2" />
10.        <result property="date" column="date" jdbcType="DATE" />
11.    </resultMap>
12.
13.    <select id="getByPK" resultMap="userResult" parameterresultMap="userResult" parameterproperty="id">id like #id#</isNotNull>
38.            <isNotNull prepend="AND" property="name">name like #name#</isNotNull>
39.        </dynamic>
40.    </sql>
41.
42.    <select id="getUser" resultMap="userResult">
43.        <![CDATA[
44.        select
45.          id,
46.          name,
47.          date
48.        from t_user
49.        ]]>
50.        <include refid="Dy_SC" />
51.    </select>
52.
53.    <insert id="insertUser" parameterparameterparameterparameterresultresultencoding="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" parameterparameterparameterparameterresultresult+ e, e);
44.        }
45.    }
46.
47.    /**
48.     * 初始化iBatis获得一个SqlMapClient对象
49.     *
50.     * @param
51.     * @return SqlMapClient
52.     */
53.    public static SqlMapClient getSqlMapClient() {
54.        String resource = "com/javaeye/liulu/maps/SqlMapConfig.xml";
55.        SqlMapClient sqlMap = null;
56.        try {
57.            Reader reader = Resources.getResourceAsReader(resource);
58.            sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
59.        } catch (Exception e) {
60.            e.printStackTrace();
61.        }
62.        return sqlMap;
63.    }
64.
65.    /**
66.     * 插入一条记录
67.     *
68.     * @param
69.     * @return
70.     */
71.    public static void insert() {
72.        SqlMapClient sqlMap = getSqlMapClient();
73.        try {
74.            sqlMap.startTransaction();
75.            User user = new User();
76.            user.setName("insert1");
77.            user.setDate(new Date());
78.            sqlMap.insert("User.insertUser", user);
79.            sqlMap.commitTransaction();
80.        } catch (SQLException e) {
81.            e.printStackTrace();
82.        }
83.    }
84.
85.    /**
86.     * 将第一条记录的信息更新
87.     *
88.     * @param
89.     * @return
90.     */
91.    public static void update() {
92.        SqlMapClient sqlMap = getSqlMapClient();
93.        try {
94.            sqlMap.startTransaction();
95.            User user = (User)sqlMap.queryForObject("User.getById", "1");
96.            user.setName("update1");
97.            sqlMap.update("User.updateUser", user);
98.            sqlMap.commitTransaction();
99.        } catch (SQLException e) {
100.            e.printStackTrace();
101.        } finally {
102.            try {
103.                sqlMap.endTransaction();
104.            } catch (SQLException e) {
105.                e.printStackTrace();
106.            }
107.        }
108.    }
109.
110.    /**
111.     * 删除id最大的记录
112.     *
113.     * @param
114.     * @return
115.     */
116.    public static void delete() {
117.        SqlMapClient sqlMap = getSqlMapClient();
118.        try {
119.            sqlMap.startTransaction();
120.            String maxId = sqlMap.queryForObject("User.getMaxId", null).toString();
121.            sqlMap.delete("User.deleteUser", maxId);
122.            sqlMap.commitTransaction();
123.        } catch (SQLException e) {
124.            e.printStackTrace();
125.        }
126.    }
127.
128.    /**
129.     * 根据name查询User为Map的List
130.     *
131.     * @param
132.     * @return List
133.     */
134.    public static List getUser() {
135.        SqlMapClient sqlMap = getSqlMapClient();
136.        List<User> user = null;
137.        try {
138.            sqlMap.startTransaction();
139.            HashMap params = new HashMap();
140.            params.put("name", "%liulu%");
141.            user = sqlMap.queryForList("User.getUser", params);
142.            sqlMap.commitTransaction();
143.        } catch (SQLException e) {
144.            e.printStackTrace();
145.        } finally {
146.            try {
147.                sqlMap.endTransaction();
148.            } catch (SQLException e) {
149.                e.printStackTrace();
150.            }
151.        }
152.        return user;
153.    }
154.
155.    /**
156.     * 查询各个字段的最大值(一般用于统计，此处演示使用方法)
157.     *
158.     * @param
159.     * @return
160.     */
161.    public static void getMax() {
162.        SqlMapClient sqlMap = getSqlMapClient();
163.        try {
164.            sqlMap.startTransaction();
165.            Map search = (HashMap) sqlMap.queryForObject("User.getMax", null);
166.            System.out.println(search.get("id").toString() + "\n"
167.                    + search.get("name").toString() + "\n"
168.                    + search.get("date").toString());
169.            sqlMap.commitTransaction();
170.        } catch (SQLException e) {
171.            e.printStackTrace();
172.        }
173.    }
174.
175.    /**
176.     * 通过主键查找,返回user
177.     *
178.     * @param
179.     * @return
180.     */
181.    public static void getByPK() {
182.        SqlMapClient sqlMap = getSqlMapClient();
183.        User user = new User();
184.        try {
185.            sqlMap.startTransaction();
186.            user.setId(1);
187.            user = (User) sqlMap.queryForObject("User.getByPK", user);
188.            System.out.println(user.getId() + "\n" + user.getName() + "\n"
189.                    + user.getDate());
190.            sqlMap.commitTransaction();
191.        } catch (SQLException e) {
192.            e.printStackTrace();
193.        }
194.    }
195.
196.    public static void main(String[] args) {
197.        //insert();
198.        //update();
199.        //delete();
200.
201.        List<User> user = getUser();
202.        for (User o : user) {
203.            System.out.println("id:" + o.getId() + "\nname:" + o.getName()
204.                    + "\nDate:" + o.getDate() + "\n------------");
205.        }
206.        //getMax();
207.        //getByPK();
208.    }
209.}
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();
}
}

所有的方法都是静态的，可以分别对映射方法进行测试。

下面使用的是单元测试对iBatis来测试，使用了dbunit这个开源项目。
首先，要为单元测试准备数据。使用DbUnit，我们可以用XML文件来准备测试数据集。
t_user对应表名
id，name，date分别对应列名

user_seed.xml

Java代码
1.<?xml version="1.0" encoding="GBK"?>
2.<dataset>
3.    <t_user id="1"
4.        name="liulu"
5.        date="2007-01-01"/>
6.
7.    <t_user id="2"
8.        name="liulu2"
9.        date="2007-03-15"/>
10.
11.    <t_user id="3"
12.        name="liulu3"
13.        date="2007-03-18"/>
14.</dataset>
<?xml version="1.0" encoding="GBK"?>
<dataset>
    <t_user id="1"
    name="liulu"
        date="2007-01-01"/>

    <t_user id="2"
    name="liulu2"
        date="2007-03-15"/>

<t_user id="3"
    name="liulu3"
        date="2007-03-18"/>
</dataset>

方便测试，首先为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;
    }
}

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上面的抽象类
代码

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/

热点排行

软件开发

ibatis增删节查实例