MyBatis
1.Mybatis 简介
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis 。2013年11月迁移到Github。
iBATIS一词来源于“internet”和“abatis”的组合,是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects(DAO)
MyBatis 是支持普通 SQL查询,存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML或注解用于配置和原始映射,将接口和 Java 的POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录
JDBC—>dbutils(或手动封装jdbc工具类)—>MyBatis—>Hibernate
MyBatis 一个半自动的持久层框架(ORM框架)
SSM:
spring spring MVC Mybatis(Ibatis)
2.MyBatis入门
2.1 导入MyBatis 的 jar 包
MyBatis.jar
数据库驱动包
2.2 创建数据库
2.3 Mybatis的配置文件
跟hibernate相同 也是两类配置文件
总体配置文件
通常情况 src/configuration.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>
<!-- 引入资源文件 -->
<properties resource="db.properties"></properties>
<!--
development 开发模式
work 工作模式
-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- MANAGED -->
<dataSource type="POOLED">
<property name="driver" value="${driver_class}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="domain/UserMapper.xml" />
</mappers>
</configuration>
映射文件
<?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="domain.UserMapper">
<select id="selectUser" parameterType="int" resultType="domain.User">
select * from user where id = #{id}
</select>
</mapper>
关于在总体文件中起别名
<typeAliases>
<!--
<typeAlias alias="User" type="com.zrgk.domain.User" />
-->
<package name="com.zrgk.domain"></package>
</typeAliases>
2.4测试类的编写
Reader reader = Resources.getResourceAsReader("configuration.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
User user = (User)session.selectOne("selectUser",1);
System.out.println(user.getId()+"\t"+user.getName()+"\t"+user.getAge()+"\t"+user.getBirthday());
session.close();
3对User的CRUD操作
<!-- 添加 -->
<insert id="insertUser" parameterType="User">
insert into user values(null,#{name},#{age},#{birthday})
</insert>
parameterType 参数类型
#{name} #{age} #{birthday}
name,age,birthday 是 User的属性
<!-- 更新 -->
<update id="updateUser" parameterType="User">
update user set name=#{name},age=#{age},birthday=#{birthday} where id=#{id}
</update>
<delete id="deleteUser" parameterType="User">
delete from user where id=#{id}
</delete>
<!-- 根据主键查询 -->
<select id="selectUser" parameterType="int" resultType="User">
select * from user where id = #{id}
</select>
#{id} ---- 这个括号中不是一定写 id,因为参数是 int类型
resultType 查询结果的类型
关于参数类型:
对于普通的 Java 类型,有许多内建的类型别名。它们都是大小写不敏感的,由于重载
的名字,要注意原生类型的特殊处理。
4.使用like 查询
<!-- 根据名字查询 使用like -->
<select id="selectUserByName" parameterType="string" resultType="User">
select * from user where name like "%"#{name}"%"
<!--
select * from user where name like '%'||#{name}||'%'
-->
</select>
5.动态SQL
<!-- 条件查询 -->
<select id="selectUserByCondition" parameterType="User" resultType="User">
<!--
select * from user where 1=1
<if test='name !=null and name!=""'>
and name like "%"#{name}"%"
</if>
<if test="age !=0">
and age = #{age}
</if>
<if test="birthday !=null">
and birthday = #{birthday}
</if>
-->
select * from user
<where>
<if test='name !=null and name!=""'>
name like "%"#{name}"%"
</if>
<if test="age !=0">
and age = #{age}
</if>
<if test="birthday !=null">
and birthday = #{birthday}
</if>
</where>
</select>
也可以使用
<choose> <when> <otherwise>
<set>:动态更新
<update id="dynamicUpdate" parameterType="User">
update user
<set>
<if test="name != null">
name = #{name},
</if>
<if test="age != 0">
age = #{age},
</if>
<if test="birthday != birthday">
birthday = #{ birthday }
</if>
</set>
where id = #{id}
</update>
<forEach>:
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item,index,collection,open,separator,close。item表示集合中每一个元素进行迭代时的别名,index指定一个名字,用于表示在迭代过程中,每次迭代到的位置,open表示该语句以什么开始,separator表示在每次进行迭代之间以什么符号作为分隔符,close表示以什么结束,在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:
1. 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
2. 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
3. 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map,实际上如果你在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,map的key就是参数名,所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
1 参数是list
<select id="dynamicForeachTest" resultType="User" parameterType="java.util.List">
select * from user where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
2 参数是数组
<select id="dynamicForeachTest2" resultType="User">
select * from user where id in
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
3 参数Map
<select id="dynamicForeachTest3" resultType="User">
select * from user where name like "%"#{name}"%" and id in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
6.字段名和实体属性名不同
解决方式一:起别名
<!-- 根据主键查询 字段名和属性名不同 -->
<select id="selectPerson" parameterType="int" resultType="Person">
select id personId,name personName,age personAge from person where id = #{id}
</select>
解决方式二: 使用resultMap
<select id="selectPerson2" parameterType="int" resultMap="personMap">
select * from person where id = #{id}
</select>
<resultMap type="Person" id="personMap">
<id property="personId" column="id"/>
<result property="personName" column="name"/>
<result property="personAge" column="age"/>
</resultMap>
7 关联关系映射
一对多 多对一
<!-- one2many 方式一:使用连接查询 -->
<select id="selectDeptWithEmp1" resultMap="deptMapWithEmp1" parameterType="int">
select d.id as dept_id,d.name as dept_name,e.id emp_id,e.name emp_name,dept_id from dept d,emp e where d.id = e.dept_id and d.id=#{id}
</select>
<resultMap type="Dept" id="deptMapWithEmp1">
<id property="id" column="dept_id" />
<result property="name" column="dept_name" />
<collection property="empList" ofType="Emp">
<id property="id" column="emp_id" />
<result property="name" column="emp_name" />
</collection>
</resultMap>
<!-- one2many 方式一:使用连接查询 -->
<!-- one2many 方式二:使用两次查询 -->
<select id="selectDeptWithEmp2" resultMap="deptMapWithEmp2" parameterType="int">
select id,name from dept where id=#{id}
</select>
<resultMap type="Dept" id="deptMapWithEmp2">
<id property="id" column="dept_id" />
<result property="name" column="dept_name" />
<collection property="empList" column="id" select="selectEmpForDept"></collection>
</resultMap>
<select id="selectEmpForDept" resultType="Emp" parameterType="int">
select * from emp where dept_id=#{id}
</select>
<!-- one2many 方式二:使用两次查询 -->
<!-- many2one 方式一:使用连接查询-->
<select id="selectEmpWithDept1" parameterType="int" resultMap="empMapWithDept1">
select e.id emp_id,e.name emp_name,d.id dept_id,d.name dept_name,dept_id from emp e,dept d where e.dept_id=d.id and e.id=#{id}
</select>
<resultMap type="Emp" id="empMapWithDept1">
<id property="id" column="emp_id" />
<result property="name" column="emp_name" />
<association property="dept" javaType="Dept">
<id property="id" column="dept_id" />
<result property="name" column="dept_name" />
</association>
</resultMap>
<!-- many2one 方式一:使用连接查询-->
<!-- many2one 方式二:使用两次查询-->
<select id="selectEmpWithDept2" parameterType="int" resultMap="empMapWithDept2">
select e.id emp_id,e.name emp_name,dept_id from emp e where e.id=#{id}
</select>
<resultMap type="Emp" id="empMapWithDept2">
<id property="id" column="emp_id" />
<result property="name" column="emp_name" />
<association property="dept" column="dept_id" select="selectDeptForEmp"></association>
</resultMap>
<select id="selectDeptForEmp" parameterType="int" resultType="Dept">
select * from dept where id=#{id}
</select>
<!-- many2one 方式二:使用两次查询-->
一对一:
<!-- 1:1 连接查询-->
<select id="selectHusbandWithWife" parameterType="int" resultMap="HusbandMapWithWife">
select h.id husband_id,h.name husband_name,w.id wife_id, w.name wife_name from husband h,wife w
where h.id =w.id and h.id=#{id}
</select>
<resultMap id="HusbandMapWithWife" type="Husband">
<id property="id" column="husband_id"></id>
<result property="name" column="husband_name"></result>
<association property="wife" javaType="Wife">
<id property="id" column="wife_id"></id>
<result property="name" column="wife_name"></result>
</association>
</resultMap>
<!-- 1:1 连接查询-->
<!-- 1:1 两次查询 -->
<select id="selectHusbandWithWife2" parameterType="int" resultMap="HusbandMapWithWife2">
select * from husband where id=#{id}
</select>
<resultMap id="HusbandMapWithWife2" type="Husband">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<association property="wife" column="id" select="selectWifeForHusband">
</association>
</resultMap>
<select id="selectWifeForHusband" parameterType="int" resultType="Wife">
select * from wife where id=#{id}
</select>
<!-- 1:1 两次查询 -->
多对多:
<!-- N:N 连接查询 -->
<select id="selectStudentByIdWithCourse" parameterType="int" resultMap="StudentMapWithCourse">
select s.id stu_id,s.name stu_name,c.id course_id,c.name course_name from student s ,course c,student_course sc
where s.id = sc.student_id and sc.course_id=c.id and s.id = #{id}
</select>
<resultMap type="Student" id="StudentMapWithCourse">
<id property="id" column="stu_id" />
<result property="name" column="stu_name" />
<collection property="courseList" ofType="Course">
<id property="id" column="course_id" />
<result property="name" column="course_name" />
</collection>
</resultMap>
<!-- N:N 连接查询 -->
s
<!-- N:N 两次查询 -->
<select id="selectStudentByIdWithCourse2" parameterType="int" resultMap="StudentMapWithCourse2">
select * from student where id=#{id}
</select>
<resultMap type="Student" id="StudentMapWithCourse2">
<id property="id" column="id" />
<result property="name" column="name" />
<collection property="courseList" select="selectCourseForStudent" column="id"></collection>
</resultMap>
<select id="selectCourseForStudent" resultType="Course" parameterType="int">
select c.id id,c.name name from course c,student_course sc
where c.id = sc.course_id and sc.student_id = #{id}
</select>
<!-- N:N 两次查询 -->
8 Mybatis 缓存
Mybatis 一级缓存 SqlSession的缓存 自动开启
二级缓存 SqlSessionFactory的缓存 手动开启
Log4J :apache
commons-logging.jar 通用日志记录包
log4j.jar ------------log4j的核心包
src下需要一个配置文件: log4j.xml log4j.properties
日志记录有5个级别:
debug
info
warn
error
fatal
文档38页