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