介绍
iBATIS是以SQL为中心的持久化层框架。能支持懒加载、关联查询、继承等特性。
iBATIS不同于一般的OR映射框架。OR映射框架,将数据库表、字段等映射到类、属性,那是一种元数据(meta-data)映射。iBATIS则是将SQL查询的参数和结果集映射到类。
所以,iBATIS做的是SQL Mapping的工作。
它把SQL语句看成输入以及输出,结果集就是输出,而where后面的条件参数则是输入。iBATIS能将输入的普通POJO对象、Map、XML等映射到SQL的条件参数上,同时也可以将查询结果映射到普通POJO对象(集合)、Map、XML等上面。
iBATIS使用xml文件来映射这些输入以及输出。
简单示例
基于ibatis-2.3.0.677版本。
1、 创建新的项目,并引入jar包
a) ibatis-2.3.0.677.jar
b) mysql驱动
2、 在类路径中提供ibatis的配置文件:sqlMapConfig.xml
<?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>
<transactionManager type="JDBC" commitRequired="false"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/> <property name="JDBC.ConnectionURL" value="jdbc:mysql://127.0.0.1/crm"/> <property name="JDBC.Username" value="root"/> <property name="JDBC.Password" value="mysql"/> </dataSource> </transactionManager> <sqlMap resource="com/bjsxt/crm/model/User.xml"/>
</sqlMapConfig> |
3、创建实体类:User.java
package com.bjsxt.crm.model;
public class User { private int id; private String username; private String password;
public int getId() { return id; } public void setId(int id) { this.id = id; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } }
|
4、创建针对User对象的CRUD的xml映射配置:User.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
<!-- Use type aliases to avoid typing the full classname every time. --> <typeAlias alias="User" type="com.bjsxt.crm.model.User"/>
<!-- Select with no parameters using the result map for Account class. --> <select id="selectAllUsers" resultClass="User"> select * from t_user </select>
<select id="selectUser" resultClass="User" parameterClass="int"> select * from t_user where id=#id# </select>
<insert id="insertUser" parameterClass="User"> insert into t_user values ( null,#username#,#password# ) </insert>
<update id="updateUser" parameterClass="User"> update t_user set username = #username#,password=#password# where id=#id# </update>
<delete id="deleteUser" parameterClass="int"> delete from t_user where id=#id# </delete> </sqlMap> |
5、创建测试程序测试:
package com.bjsxt.crm.model;
import java.io.Reader; import java.util.Iterator; import java.util.List;
import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder;
/** * 最简单的形式! * @author Lee * */ public class UserTest {
/** * @param args */ public static void main(String[] args) throws Exception{
//从配置文件中得到SqlMapClient对象 Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml"); SqlMapClient sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader); reader.close();
//创建用户数据 for(int i=0; i<10; i++){ User user = new User(); user.setUsername("用户"+i); user.setPassword("密码"+i); sqlMapper.insert("insertUser", user); }
//查询用户数据 List users = sqlMapper.queryForList("selectAllUsers"); for (Iterator iter = users.iterator(); iter.hasNext();) { User user = (User) iter.next(); System.out.println(user.getUsername()); }
//查询特定用户的数据 User user = (User)sqlMapper.queryForObject("selectUser", 1); System.out.println("用户【id="+1+"】的名称是:"+user.getUsername());
//更新用户的信息 user = new User(); user.setId(3); user.setUsername("更改之后的用户名称"); user.setPassword("密码被更改"); sqlMapper.update("updateUser", user);
//删除用户的信息 sqlMapper.delete("deleteUser", 6); }
}
|
SqlMapClient对象
这个对象是iBatis操作数据库的接口(执行CRUD等操作),它也可以执行事务管理等操作。这个类是我们使用iBATIS的最主要的类。它是线程安全的。通常,将它定义为单例。(与hibernate中sessionFactory的定义类似)。如:
import java.io.Reader; import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class IbatisSQLMapConfig {
private static final SqlMapClient sqlMap;
//在静态区块中初试化返回 static { try { //声明配置文件的名称(映射文件被定义在其中) String resource = "sql_map_config.xml";
//利用工具类Resources来读取到配置文件 Reader reader = Resources.getResourceAsReader(resource);
//创建SqlMapClient接口的变量实例 sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
} catch (Exception e) { e.printStackTrace(); throw new RuntimeException( "Error initializing MyAppSqlConfig class. Cause: " + e); } } public static SqlMapClient getSqlMapInstance() { //提供静态方法返回静态区块中得到的SqlMapClient return sqlMap; } } |
How To
如何获得刚插入记录的自增长ID值?
<insert id="insertUser" parameterClass="User"> insert into t_user values ( null,#username#,#password# ) <selectKey resultClass="int" keyProperty="id"> SELECT @@IDENTITY AS ID </selectKey> </insert> |
User user = new User(); user.setUsername("张三"); user.setPassword("张三密码");
//如果主键是自动生成的,则其返回值可以通过<selectKey>标签来设置 //如果不通过<selectKey>标签来设置,则返回值为空! //<selectKey >中的keyProperty,是指定User中的id属性,当调用结束之后, //user对象的id值和insert方法的返回值都是这条记录的ID值! Object obj = sqlMapper.insert("insertUser", user); |
parameterClass的使用
<insert id="insertUser" parameterClass="User"> insert into t_user values ( null,#username#,#password# ) <selectKey resultClass="int" keyProperty="id"> SELECT @@IDENTITY AS ID </selectKey> </insert>
<insert id="insertUser2"> insert into t_user values ( null,#username#,#password# ) <selectKey resultClass="int" keyProperty="id"> SELECT @@IDENTITY AS ID </selectKey> </insert> |
insertUser使用了parameterClass,所以必需传入User类型的对象
User user = new User(); user.setUsername("张三"); user.setPassword("张三密码");
//传递进去的对象,必须是User类型 Object obj = sqlMapper.insert("insertUser", user); |
insertUser2没有使用parameterClass,所以可以传入任意具有相应属性值的对象
JustAnObject anobj = new JustAnObject(); anobj.setUsername("用户名"); anobj.setPassword("用户密码");
//如果没有指定parameterClass属性,则任何一个具有相应属性值 //的对象都可以被传递进去 Object obj = sqlMapper.insert("insertUser2", anobj); |
parameterMap的使用
<parameterMap class="User" id="insertUser-param"> <parameter property="username"/> <parameter property="password"/> </parameterMap>
<insert id="insertUser" parameterMap="insertUser-param"> insert into t_user values ( null,?,? ) <selectKey resultClass="int" keyProperty="id"> SELECT @@IDENTITY AS ID </selectKey> </insert> |
parameterMap用于传入参数,以便匹配SQL语句中的?号
User user = new User(); user.setUsername("张三dd"); user.setPassword("张三密码dd");
Object obj = sqlMapper.insert("insertUser", user); |
利用parameterMap,可以定义参数对象的属性如何映射到SQL查询语句的动态参数上,注意parameterMap中<parameter/>标签的先后顺序不能颠倒!
如何将查询结果映射到不同的对象?(resultClass的使用)
<select id="selectUserForOtherObject" resultClass="com.bjsxt.ibatis.OtherObject" parameterClass="int"> select username as prop1, password as prop2 from t_user where id=#value# </select> |
//查找t_user表,将其结果映射到一个属性名不同的对象中! OtherObject obj = (OtherObject)sqlMapper.queryForObject("selectUserForOtherObject", 1); System.out.println(obj.getProp1()+","+obj.getProp2()); |
如何将查询结果集映射到不同的对象?(resultMap的基本使用)
<resultMap class="com.bjsxt.ibatis.OtherObject" id="ooResult"> <result property="prop1" column="username"/> <result property="prop2" column="password"/> </resultMap> <!-- 如果使用resultMap来定义如何映射,则如下语句不可写成: select username as prop1,password as prop2 .... --> <select id="selectUserForOtherObject2" parameterClass="int" resultMap="ooResult"> select username, password from t_user where id=#value# </select> |
//查找t_user表,将其结果映射到一个属性名不同的对象中! OtherObject obj = (OtherObject)sqlMapper.queryForObject("selectUserForOtherObject2", 17); System.out.println(obj.getProp1()+","+obj.getProp2()); |
如何将查询结果集映射为xml格式的数据?
<select id="selectXmlData" resultClass="xml" xmlResultName="User" parameterClass="int"> select * from t_user where id=#value# </select> <select id="selectXmlDatas" resultClass="xml" xmlResultName="User"> select * from t_user </select> |
//查找t_user表,将其结果映射到xml! //返回值是xml形式的字符串 Object obj = (Object)sqlMapper.queryForObject("selectXmlData", 1); System.out.println(obj);
//查找t_user表,将其结果映射到xml! List list = (List)sqlMapper.queryForList("selectXmlDatas"); System.out.println(list); |
如何用Map类型的对象作为传入参数?
<!-- 这里,可以使用全路径类名,如: java.util.Map java.util.HashMap java.util.TreeMap 或 map --> <insert id="insertUser" parameterClass="map"> insert into t_user values ( null,#username#,#password# ) </insert> |
Map user = new TreeMap(); user.put("username", "Map用户"); user.put("password", "Map用户密码"); sqlMapper.insert("insertUser",user); |
如何将查询结果集的元素转换为Map类型的对象?
<!-- resultClass可以定义为java.util.HashMap类型, 将能自动转换 --> <select id="selectMapUsers" resultClass="java.util.HashMap"> select * from t_user </select> |
List list = (List)sqlMapper.queryForList("selectMapUsers"); System.out.println(list); for (Iterator iter = list.iterator(); iter.hasNext();) { Map map = (Map) iter.next(); //可在此输出map的数据 } |
关联数据的存取(一对多,从多查询一)
学生和班级
Student类
public class Student { private int id; private String name; private String sex; private Classes classes;
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; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Classes getClasses() { return classes; } public void setClasses(Classes classes) { this.classes = classes; } } |
Classes类
public class Classes { private int id; private String cname;
public int getId() { return id; } public void setId(int id) { this.id = id; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } } |
第一种方法,通过命别名
Student的xml文件
<sqlMap> <typeAlias alias="Student" type="com.bjsxt.ibatis.Student"/> <insert id="saveStudent" parameterClass="Student"> insert into t_student values (null,#name#,#sex#,#classes.id#) </insert>
<select id="findStudent" parameterClass="int" resultClass="Student"> select s.name, s.sex, s.cid as "classes.id", c.cname as "classes.cname" from t_student s,t_classes c where s.cid = c.id and s.id = #id# </select> </sqlMap> |
Classes的xml文件
<sqlMap> <typeAlias alias="Classes" type="com.bjsxt.ibatis.Classes"/> <insert id="saveClasses" parameterClass="Classes"> insert into t_classes values (null,#cname#) <selectKey resultClass="int" keyProperty="id"> select @@identity as id </selectKey> </insert> </sqlMap> |
测试
public class ClassesAnStudentTest extends TestCase { public void testSaveClassesAndStudent() throws Exception{ //从配置文件中得到SqlMapClient对象 Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml"); SqlMapClient sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader); reader.close();
Classes c1 = new Classes(); c1.setCname("测试班级"); sqlMapper.insert("saveClasses", c1);
Student s1 = new Student(); s1.setName("学生"+new Random().nextInt(9999)); s1.setSex("男"); s1.setClasses(c1); sqlMapper.insert("saveStudent", s1); }
public void testFindStudent() throws Exception{ //从配置文件中得到SqlMapClient对象 Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml"); SqlMapClient sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader); reader.close();
Student s = (Student)sqlMapper.queryForObject("findStudent", 1); System.out.println(s.getName()+"属于班级:"+s.getClasses().getCname()); } } |
第二种方法,通过第二条查询语句
Classes的xml文件
<sqlMap> <typeAlias alias="Classes" type="com.bjsxt.ibatis.Classes"/> <insert id="saveClasses" parameterClass="Classes"> insert into t_classes values (null,#cname#) <selectKey resultClass="int" keyProperty="id"> select @@identity as id </selectKey> </insert>
<select id="findClassesById" parameterClass="int" resultClass="Classes"> select * from t_classes where id = #cid# </select> </sqlMap> |
Student的xml文件
<sqlMap> <typeAlias alias="Student" type="com.bjsxt.ibatis.Student"/> <insert id="saveStudent" parameterClass="Student"> insert into t_student values (null,#name#,#sex#,#classes.id#) </insert> <resultMap class="Student" id="findStudent-result"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <result property="classes" column="cid" select="findClassesById"/> </resultMap> <select id="findStudent" parameterClass="int" resultMap="findStudent-result"> select * from t_student where id = #id# </select> </sqlMap> |
关联数据的存取(一对多,从一查询多)
Student类
public class Student { private int id; private String name; private String sex; private Classes classes; } |
Classes类
public class Classes { private int id; private String cname; private Set students; } |
Student的xml文件
<sqlMap> <typeAlias alias="Student" type="com.bjsxt.ibatis.Student"/> <insert id="saveStudent" parameterClass="Student"> insert into t_student values (null,#name#,#sex#,#classes.id#) </insert> <resultMap class="Student" id="findStudent-result"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <result property="classes" column="cid" select="findClassesById"/> </resultMap> <select id="findStudent" parameterClass="int" resultMap="findStudent-result"> select * from t_student where id = #id# </select>
<select id="findStudentsByCid" resultClass="Student" parameterClass="int"> select * from t_student where cid = #cid# </select> </sqlMap> |
Classes的xml文件
<sqlMap> <typeAlias alias="Classes" type="com.bjsxt.ibatis.Classes"/> <insert id="saveClasses" parameterClass="Classes"> insert into t_classes values (null,#cname#) <selectKey resultClass="int" keyProperty="id"> select @@identity as id </selectKey> </insert>
<select id="findClassesById" parameterClass="int" resultClass="Classes"> select * from t_classes where id = #cid# </select>
<resultMap class="Classes" id="findClasses-result"> <result property="id" column="id"/> <result property="cname" column="cname"/> <result property="students" column="id" select="findStudentsByCid"/> </resultMap> <select id="findClasses" parameterClass="int" resultMap="findClasses-result"> select * from t_classes where id = #id# </select> </sqlMap> |
关联数据的存取(双向)
启用懒加载,双向中用List,不用Set,否则会有异常
Student类
public class Student { private int id; private String name; private String sex; private Classes classes; } |
Classes类
public class Classes { private int id; private String cname; private List students; } |
iBATIS主配置文件,启用懒加载
<sqlMapConfig> <settings lazyLoadingEnabled="true"/> <transactionManager type="JDBC" commitRequired="false"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/> <property name="JDBC.ConnectionURL" value="jdbc:mysql://127.0.0.1/ibatis"/> <property name="JDBC.Username" value="root"/> <property name="JDBC.Password" value="bjsxt"/> </dataSource> </transactionManager> <sqlMap resource="com/bjsxt/ibatis/Classes.xml"/> <sqlMap resource="com/bjsxt/ibatis/Student.xml"/> </sqlMapConfig> |
Student的xml文件
<sqlMap> <typeAlias alias="Student" type="com.bjsxt.ibatis.Student"/> <insert id="saveStudent" parameterClass="Student"> insert into t_student values (null,#name#,#sex#,#classes.id#) </insert> <resultMap class="Student" id="findStudent-result"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <result property="classes" column="cid" select="findClasses"/> </resultMap> <select id="findStudent" parameterClass="int" resultMap="findStudent-result"> select * from t_student where id = #id# </select>
<select id="findStudentsByCid" resultMap="findStudent-result" parameterClass="int"> select * from t_student where cid = #cid# </select> </sqlMap> |
Classes的xml文件
<sqlMap> <typeAlias alias="Classes" type="com.bjsxt.ibatis.Classes"/> <insert id="saveClasses" parameterClass="Classes"> insert into t_classes values (null,#cname#) <selectKey resultClass="int" keyProperty="id"> select @@identity as id </selectKey> </insert>
<!-- <select id="findClassesById" parameterClass="int" resultClass="Classes"> select * from t_classes where id = #cid# </select> -->
<resultMap class="Classes" id="findClasses-result"> <result property="id" column="id"/> <result property="cname" column="cname"/> <result property="students" column="id" select="findStudentsByCid"/> </resultMap> <select id="findClasses" parameterClass="int" resultMap="findClasses-result"> select * from t_classes where id = #id# </select> </sqlMap> |
iBATIS多态查询
多态查询的映射策略
- 在设计数据库表的时候,需要提供一个辨别字段,来记录对象的类型
- 在插入数据的时候,我们要自己定义这个辨别字段的值
- 在查询数据的时候可以利用iBATIS提供的discriminator元素来映射多态的数据
Animal类
public class Animal { private int id; private String name; private String sex; } |
Bird类
public class Bird extends Animal{ private int height;
public int getHeight() { return height; }
public void setHeight(int height) { this.height = height; } } |
Pig类
public class Pig extends Animal{ private int weight;
public int getWeight() { return weight; }
public void setWeight(int weight) { this.weight = weight; } } |
建表语句
create table t_animal( ID integer not null auto_increment, NAME varchar(255), SEX varchar(255), HEIGHT integer, WEIGHT integer, ANIMAL_TYPE varchar(20), primary key(ID) ); |
Animal的xml文件
<sqlMap> <typeAlias alias="Animal" type="com.bjsxt.ibatis.Animal"/> <typeAlias alias="Bird" type="com.bjsxt.ibatis.Bird"/> <typeAlias alias="Pig" type="com.bjsxt.ibatis.Pig"/>
<insert id="insertAnimal" parameterClass="Animal"> insert into t_animal (name,sex,animal_type) values (#name#,#sex#,'Animal') </insert> <insert id="insertBird" parameterClass="Bird"> insert into t_animal (name,sex,height,animal_type) values (#name#,#sex#,#height#,'Bird') </insert> <insert id="insertPig" parameterClass="Pig"> insert into t_animal (name,sex,weight,animal_type) values (#name#,#sex#,#weight#,'Pig') </insert>
<resultMap class="Animal" id="animal"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <discriminator column="animal_type" javaType="string"> <subMap value="Bird" resultMap="bird"/> <subMap value="Pig" resultMap="pig"/> </discriminator> </resultMap> <resultMap class="Bird" id="bird" extends="animal"> <result property="height" column="height"/> </resultMap> <resultMap class="Pig" id="pig" extends="animal"> <result property="weight" column="weight"/> </resultMap> <select id="findAllAnimals" resultMap="animal"> select * from t_animal </select> </sqlMap> |
事务处理
编程式事务处理:
可以使用sqlMapClient的startTransaction/commitTransaction/endTransaction等方法来控制事务的边界。
声明式事务处理:
如果与spring整合(这是iBatis推荐的方式),则我们需要在spring配置文件中指定其事务特性。
与spring集成
Spring通过DAO模式,提供了对iBATIS的良好支持。SqlMapClient对象是iBATIS中的主要对象,我们可以通过配置让spring来管理SqlMapClient对象的创建。
与hibernate类似,Spring 提供了SqlMapClientDaoSupport对象,我们的DAO可以继承这个类,通过它所提供的SqlMapClientTemplate对象来操纵数据库。看起来这些概念都与hibernate类似。
通过SqlMapClientTemplate来操纵数据库的CRUD是没有问题的,这里面关键的问题是事务处理。Spring提供了强大的声明式事务处理的功能,我们已经清楚hibernate中如何配置声明式的事务,那么在iBATIS中如何获得声明式事务的能力呢?
第一,我们需要了解的是spring通过AOP来拦截方法的调用,从而在这些方法上面添加声明式事务处理的能力。典型配置如下:
<!-- 配置事务特性 --> <tx:advice id="txAdvice" transaction-manager="事务管理器名称"> <tx:attributes> <tx:method name="add*" propagation="REQUIRED"/> <tx:method name="del*" propagation="REQUIRED"/> <tx:method name="update*" propagation="REQUIRED"/> <tx:method name="*" read-only="true"/> </tx:attributes> </tx:advice>
<!-- 配置哪些类的方法需要进行事务管理 --> <aop:config> <aop:pointcut id="allManagerMethod" expression="execution(* com.bjsxt.crm.manager.*.*(..))"/> <aop:advisor advice-ref="txAdvice" pointcut-ref="allManagerMethod"/> </aop:config> |
这些事务都是声明在业务逻辑层的对象上的。
第二,我们需要一个事务管理器,对事务进行管理。
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1/crm"/> <property name="username" value="root"/> <property name="password" value="mysql"/> </bean> |
此后,我们需要让spring来管理SqlMapClient对象:
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> <property name="configLocation"> <value>classpath:sqlMapConfig.xml</value> </property> <property name="dataSource" ref="dataSource"></property> </bean> |
|
我们的DAO的编写:
package com.bjsxt.crm.dao.impl.ibatis;
import java.util.List;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import com.bjsxt.crm.dao.UserDAO; import com.bjsxt.crm.model.User;
public class UserDAOImpl extends SqlMapClientDaoSupport implements UserDAO {
public void delete(User user) { getSqlMapClientTemplate().delete("delete",user.getId()); }
public List findAll() { return getSqlMapClientTemplate().queryForList("findAll"); }
public User findById(int id) { return (User)getSqlMapClientTemplate().queryForObject("findById", id); }
public void save(User user) { getSqlMapClientTemplate().insert("save",user); }
public void update(User user) { getSqlMapClientTemplate().update("update",user); }
} |
继承SqlMapClientDaoSupport,要求我们注入SqlMapClient对象,因此,需要有如下的DAO配置:
<bean id="userDAO" class="com.bjsxt.crm.dao.impl.ibatis.UserDAOImpl"> <property name=”sqlMapClient” ref=”sqlMapClient”/> </bean> |
这就是所有需要注意的问题了,此后就可以在业务逻辑层调用DAO对象了!