花了几个小时整理了一下MyBaits的使用,在这里总结和分享一下。
MyBatis的官网是这么介绍的:
MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的持久层框架。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以对配置和原生Map使用简单的 XML 或注解,将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
简单明了,这介绍很不错。但是这样,往往容易会把它复杂化。
下面先来一个简单易懂的demo,演示往mybatis数据库中的userinfo表插入一条数据。项目的文件架构图如下:
MyBatis框架所要用到的jar包有:asm-3.3.1 cglib-2.2.2 javassist-3.17.1-GA log4j-1.2.17 mybatis-3.2.2 mysql-connector-java-5.1.7-bin slf4j-api-1.7.5 slf4j-log4j12-1.7.5
创建entity实体类UserInfo:
package yzr.entity;
import java.io.Serializable;
public class UserInfo implements Serializable {
private int userId;
private String userName;
private String eMail;
public UserInfo(){}
public int getUserId() {
return userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String geteMail() {
return eMail;
}
public void seteMail(String eMail) {
this.eMail = eMail;
}
@Override
public String toString() {
return "UserInfo [userId=" + userId + ", userName=" + userName
+ ", eMail=" + eMail + "]";
}
public UserInfo(String UserName,String EMail){
this.userName=UserName;
this.eMail=EMail;
}
}
为UserInfo实体类创建一个映射文件:UserInfoMapper.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 命名空间可以任选命名,但最好要定义一定规则,便于后继的使用 -->
<mapper namespace="yzr.entity.UserInfo">
<cache eviction="FIFO" flushInterval="60000" readOnly="false" size="512"></cache>
<!-- 定义插入的sql语句,通过命名空间+id方式被定位 -->
<insert id="insert" parameterType="yzr.entity.UserInfo">
<![CDATA[
insert into
userinfo(username,email) values(#{userName},#{eMail});
]]>
</insert>
<!-- 定义update的sQL语句 -->
<update id="update" parameterType="yzr.entity.UserInfo">
<![CDATA[update UserInfo set
username=#{userName},email=#{eMail} where userid=#{userId}
]]>
</update>
<!-- 定义删除的SQL -->
<delete id="delete" parameterType="Integer">
delete from userinfo where
userid=#{userId}
</delete>
<!-- 一般在查询时使用 -->
<resultMap type="yzr.entity.UserInfo" id="userInfoResultMap">
<id property="userId" column="UserId" />
<result property="userName" column="UserName" />
<result property="eMail" column="EMail" />
</resultMap>
<!-- 省略其它的配置信息 -->
<!-- 返回单条记录,表字段和对应实体属性命名一致时可以不使用resultMap属性配置,直接使用resultType="返回的全类名或别名",建议使用前者;查询结果为所有字段时,也可以用*表示 -->
<select id="selectOne" parameterType="int" resultMap="userInfoResultMap" useCache="true">
select
userid, username,email from userinfo where userid=#{userId}
</select>
<!-- 返回多条记录,返回结果是集合元素的类型;参数也可以通过Map等方式封装 -->
<select id="selectList" parameterType="Map" resultMap="userInfoResultMap">
select *
from userinfo where username like #{userName}
</select>
<!-- 动态IF条件 -->
<select id="selectListUseIf" parameterType="yzr.entity.UserInfo"
resultMap="userInfoResultMap">
select * from userinfo where 1=1
<if test="userId!=null">
and userid=#{userId}
</if>
<if test="userName!=null">
and username=#{userName}
</if>
<if test="eMail!=null">
and email=#{eMail}
</if>
</select>
<!-- 动态Where条件 ,一般也需要与if结合使用,与纯if比较,省略了where 1=1 -->
<select id="selectListUseWhere" parameterType="yzr.entity.UserInfo"
resultMap="userInfoResultMap">
select * from userinfo
<where>
<if test="userId!=null">
and userid=#{userId}
</if>
<if test="userName!=null">
and username=#{userName}
</if>
<if test="eMail!=null">
and email=#{eMail}
</if>
</where>
</select>
<!-- 动态choose条件 ,如下配置,可以完成没有选择条件时,查找不出任何数据 -->
<select id="selectListUseChoose" parameterType="yzr.entity.UserInfo"
resultMap="userInfoResultMap">
select * from userinfo where 1=1
<choose>
<when test="userId!=null">and userid=#{userId}</when>
<when test="userName!=null">and username=#{userName}</when>
<when test="eMail!=null">and email=#{eMail}</when>
<otherwise>and !1 = 1</otherwise>
</choose>
</select>
<!--动态set语句可以用来更新数据 -->
<update id="updateUseSet" parameterType="yzr.entity.UserInfo">
update userinfo
<set>
<if test="userName!=null">username=#{userName},</if>
<if test="eMail!=null">email=#{eMail},</if>
</set>
where dept_id=#{deptId}
</update>
<!-- 动态in写法,resultMap的值是指集合里元素的类型,parameterType不用指定 -->
<select id="selectListUseForeach" parameterType="Integer[]"
resultMap="userInfoResultMap">
select * from userinfo where userid in
<!-- collection="array或list",array用来对应参数为数组,list对应参数为 集合 -->
<foreach collection="array" item="deptId" open="(" separator=","
close=")">
#{userId}
</foreach>
</select>
<!-- 使用include语句动态插入表的字段及对应的值 -->
<sql id="key">
<!--suffixOverrides="," 可以忽略最后“,”号 -->
<trim suffixOverrides=",">
<if test="userName!=null">
username,
</if>
<if test="eMail!=null">
email,
</if>
</trim>
</sql>
<sql id="value">
<trim suffixOverrides=",">
<if test="userName!=null">
#{userName},
</if>
<if test="eMail!=null">
#{eMail},
</if>
</trim>
</sql>
<insert id="insertUseInclude" parameterType="yzr.entity.UserInfo">
insert into userinfo(
<include refid="key" />
) values(
<include refid="value" />
)
</insert>
<sql id="Insertkey">
<!--suffixOverrides="," 可以忽略最后“,”号 -->
<trim suffixOverrides=",">
username,
email,
</trim>
</sql>
<insert id="insertUserInfoList">
insert into userinfo(
<include refid="Insertkey" />
) values
<foreach collection="list" item="item" separator=",">
(#{item.userName},#{item.eMail})
</foreach>
</insert>
<delete id="deleteUserInfoList">
delete from userinfo where userid in
<foreach collection="list" item="item" open="(" close=")"
separator=",">
#{item}
</foreach>
</delete>
<update id="updateUserInfoList">
<foreach collection="list" item="user" separator=";">
update userinfo
<set>
<if test="user.userName!=null">username=#{user.userName},</if>
<if test="user.eMail!=null">email=#{user.EMail},</if>
</set>
where userId=#{user.userId}
</foreach>
</update>
</mapper>
在Dao中创建个userInfoDao:
package yzr.dao;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import yzr.entity.UserInfo;
public class UserInfoDao {
public int insert(UserInfo user) {
/*
* 1.读取配置信息 2.构建session工厂 3.创建session 4.启动事务(可选) 5.数据处理 6.提交事务、回滚事务(可选)
* 7.关闭session
*/
int i = 0;
SqlSession session = null;
String config = "myBatis-config.xml";
Reader reader = null;
try {
reader = Resources.getResourceAsReader(config);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(reader);
session = sqlSessionFactory.openSession();
// 事务默认自动启动
// SQL映射文件定义的命名空间+SQL语句的ID定位SQL语句,例如下的:cn.itcast.entity.DeptMapper.insert
i = session.insert("yzr.entity.UserInfo.insert", user);
session.commit();
} catch (IOException e) {
e.printStackTrace();
session.rollback();
} finally {
// 关闭reader对象,这里略
session.close();
}
return i;
}
}
最后一步,也是关键一步,配置myBatis-config.xml文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="cacheEnabled" value="true"/>
</settings>
<!--可以设置多个运行环境,满足不同需要,例如 开发、测试、生产环境上有不同一配置 -->
<environments default="development">
<environment id="development">
<!-- 事务管理类型主要有jdbc和managed,前者依赖于数据源获得的连接,后者依赖于容器 -->
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<!-- 如果数据库设置为UTF-8,则URL参数连接需要添加?useUnicode=true&characterEncoding=UTF-8,如下 -->
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true" />
<property name="username" value="root" />
<property name="password" value="677714" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="yzr/entity/UserInfoMapper.xml" />
</mappers>
</configuration>
那现在就可以测试一下了:
@Test
public void testInsert() {
UserInfo user=new UserInfo();
user.setUserName("YZR");
user.seteMail("2437676796@qq.com");
int i=userInfoDao.insert(user);
System.out.println("受影响行数:"+i);
}
在UserInfoDao中编写了获取资源配置文件以及创建myBatis的sqlSession,下面优化一下,编写一个myBatisUtil帮助类:
package yzr.util;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisUtil {
private static final ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
private static SqlSessionFactory sessionFactory;
private static String CONFIG_FILE_LOCATION = "myBatis-config.xml";
static {
try {
buildSessionFactory();
} catch (Exception e) {
System.err.println("%%%% Error Creating SessionFactory %%%%");
e.printStackTrace();
}
}
private MyBatisUtil() {
}
/**
* Returns the ThreadLocal Session instance. Lazy initialize the
* <code>SessionFactory</code> if needed.
*
* @return Session
* @throws Exception
*/
public static SqlSession getSession() throws Exception {
SqlSession session =threadLocal.get();
if (session == null) {
if (sessionFactory == null) {
buildSessionFactory();
}
session = (sessionFactory != null) ? sessionFactory.openSession()
: null;
threadLocal.set(session);
}
return session;
}
/**
* build session factory
*
*/
public static void buildSessionFactory() {
Reader reader = null;
try {
reader = Resources.getResourceAsReader(CONFIG_FILE_LOCATION);
sessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
System.err.println("%%%% Error Creating SessionFactory %%%%");
e.printStackTrace();
} finally {
try {
if (reader != null) {
reader.close();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* Close the single session instance.
*
* @throws Exception
*/
public static void closeSession() {
SqlSession session = (SqlSession) threadLocal.get();
threadLocal.set(null);
if (session != null) {
session.close();
}
}
/**
* return session factory
*
*/
public static SqlSessionFactory getSessionFactory() {
return sessionFactory;
}
}
在myBatis中对象关联关系中用两个节点来描述:association和collection。
collection用于表示一对多,或者多对多。association用于表示多对一,或者一对一。
举个列子,部门和员工的关系使用association来关联:
Deptmapper.xml映射文件为:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 命名空间可以任选命名,但最好要定义一定规则,便于后继的使用 -->
<mapper namespace="yzr.entity.DeptMapper">
<!-- 定义插入的sql语句,通过命名空间+id方式被定位 -->
<insert id="insert" parameterType="yzr.entity.Dept">
insert into
dept(deptname) values(#{deptName});
</insert>
<!-- 一般在查询时使用 -->
<resultMap type="yzr.entity.Dept" id="deptResultMap">
<id property="deptId" column="deptId" />
<result property="deptName" column="DeptName" />
</resultMap>
<!-- 返回多条记录,返回结果是集合元素的类型;参数也可以通过Map等方式封装 -->
<select id="selectList" parameterType="Map" resultMap="deptResultMap">
select *
from dept where deptname like #{deptName}
</select>
</mapper>
EmployeesMapper.xml配置文件为:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 命名空间可以任选命名,但最好要定义一定规则,便于后继的使用 -->
<mapper namespace="yzr.entity.EmployeesMapper">
<!-- 定义插入的sql语句,通过命名空间+id方式被定位 -->
<insert id="insert" parameterType="yzr.entity.Employees">
insert into
employees(empname,deptid) values(#{empName},#{dept.deptId});
</insert>
<!-- 一般在查询时使用 -->
<resultMap type="yzr.entity.Employees" id="employeesResultMap">
<id property="empId" column="EmpId" />
<result property="empName" column="EmpName" />
<association property="dept" column="DeptId" javaType="yzr.entity.Dept" resultMap="yzr.entity.DeptMapper.deptResultMap"></association>
</resultMap>
<!-- 返回多条记录,返回结果是集合元素的类型;参数也可以通过Map等方式封装 -->
<select id="selectList" parameterType="Map" resultMap="employeesResultMap">
select emp.*,dp.*
from employees emp inner join dept dp on emp.deptid=dp.deptId where empname like #{empName}
</select>
</mapper>
测试一下:
@SuppressWarnings({ "rawtypes", "unchecked" })
@Test
public void testAssoication(){
SqlSession session=null;
try {
session=MyBatisUtil.getSession();
Map map =new HashMap();
map.put("empName", "YZR");
List<Employees> list=session.selectList("yzr.entity.EmployeesMapper.selectList",map);
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}
}
结果:[Employees [empId=2, empName=YZR, dept=Dept [deptId=3, deptName=研发部]], Employees [empId=3, empName=YZR, dept=Dept [deptId=3, deptName=研发部]]]
同理,使用collection可以双向获取对象集合,比如学生和教师的关系。某一教师下的所有学生,学生的老师。
StudentMapper.xml配置文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 命名空间可以任选命名,但最好要定义一定规则,便于后继的使用 -->
<mapper namespace="yzr.entity.StudentMapper">
<!-- 一般在查询时使用 -->
<resultMap type="yzr.entity.Student" id="StudentResultMap">
<id property="sId" column="sId" />
<result property="sName" column="sName" />
<association property="teacher" column="tId" javaType="yzr.entity.Teacher" resultMap="yzr.entity.TeacherMapper.TeacherResultMap"></association>
</resultMap>
<!-- 返回多条记录,返回结果是集合元素的类型;参数也可以通过Map等方式封装 -->
<select id="selectList" parameterType="Map" resultMap="StudentResultMap">
select s.*,t.*
from student s inner join teacher t on s.tid=t.tid where sName like #{sName}
</select>
</mapper>
Teachermapper.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 命名空间可以任选命名,但最好要定义一定规则,便于后继的使用 -->
<mapper namespace="yzr.entity.TeacherMapper">
<!-- 一般在查询时使用 -->
<resultMap type="yzr.entity.Teacher" id="TeacherResultMap">
<id property="tId" column="tId" />
<result property="tName" column="tName" />
</resultMap>
<resultMap type="yzr.entity.Teacher" id="TeacherExtResultMap" extends="TeacherResultMap">
<collection property="students" ofType="yzr.entity.Student" resultMap="yzr.entity.StudentMapper.StudentResultMap"></collection>
</resultMap>
<!-- 返回多条记录,返回结果是集合元素的类型;参数也可以通过Map等方式封装 -->
<select id="selectList" parameterType="Map" resultMap="TeacherExtResultMap">
select * from Teacher t inner join student s on s.tid=t.tid where t.tname like #{tName}
</select>
</mapper>
测试:
@Test
public void testCollection(){
SqlSession session=null;
try {
session=MyBatisUtil.getSession();
Map map =new HashMap();
map.put("sName", "YZR");
List<Student> list=session.selectList("yzr.entity.StudentMapper.selectList",map);
System.out.println(list);
Map map2 =new HashMap();
map2.put("tName", "LYF");
List<Teacher> list2=session.selectList("yzr.entity.TeacherMapper.selectList",map2);
System.out.println(list2);
} catch (Exception e) {
e.printStackTrace();
}
}
结果:
[Student [sId=1, sName=YZR, teacher=Teacher [tId=1, tName=LYF, students=null]]]
[Teacher [tId=1, tName=LYF, students=[Student [sId=1, sName=YZR, teacher=Teacher [tId=1, tName=LYF, students=null]]]]]
如果在你运行过程中出现如下错误:
<mappers>
<mapper resource="yzr/entity/UserInfoMapper.xml" />
<mapper resource="yzr/entity/EmployeesMapper.xml" />
<mapper resource="yzr/entity/DeptMapper.xml" />
<mapper resource="yzr/entity/StudentMapper.xml" />
<mapper resource="yzr/entity/TeacherMapper.xml" />
</mappers>
在进行UserInfo的批量更新时需要注意,在datasource中的url需要加上:
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true" />
sping和myBatis的整合:
<?xml version="1.0" encoding="UTF-8"?>
<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<!-- 配置数据源,记得去掉myBatis-config.xml的数据源相关配置 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8" />
<property name="user" value="root" />
<property name="password" value="root" />
</bean>
<!-- 配置session工厂 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:myBatis-config.xml" />
<!-- 配置扫描式加载SQL映射文件 -->
<property name="mapperLocations" value="classpath:cn/itcast/entity/*.xml"/>
</bean>
<!-- 配置事务管理器,管理数据源事务处理-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 配置SessionTemplate,已封装了繁琐的数据操作-->
<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>
</beans>
myBatis-config.xml文件编写就简单了很多很多:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias type="" alias="" />
</typeAliases>
</configuration>