3_MyBatis

一. 引言

1.1 什么是框架?

• 软件的半成品, 解决了软件开发过程中的普适性问题, 从而简化了开发步骤, 提升了开发效率

1.2 什么是ORM框架?

• ORM(Object Relational Mapping) 对象关系映射, 将程序中的 一个对象与表中的一行数据一一对应
• ORM框架提供了持久化类与表的映射关系, 在运行时参照映射文件的信息, *把对象持久化到数据库中

1.3 使用JDBC完成ORM操作的缺点?

• 存在大量的冗余代码
• 手工创建Connection、Statement等
• 手工将结果集封装成实体对象
• 查询效率低, 没有对数据访问进行过优化 (Not Cache)

二. MyBatis框架

2.1 概念

• MyBatis本是Apache软件基金会的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了 Google, 并且改名MyBatis, 2013年11月迁移到Github
• MyBatis是一个 优秀的基于Java的持久层框架 支持自定义SQL, 存储过程和高级映射
• MyBatis 对原有JDBC操作进行了封装, 几乎消除了所有JDBC代码, 使开发者只需关注SQL本身
• MYBatis可以使用简单的XML或Annotation来配置执行SQL, 并 自动完成ORM操作 ,将执行结果返回

2.2 访问与下载

• 官网:https://mybatis.org/mybatis-3/
• 中文官网:https://mybatis.org/mybatis-3/zh/index.html
• 下载地址:https://github.com/mybatis/mybatis-3/releases/tag/mybatis-3.5.1

三. 构建Maven项目

3.1 新建项目

• Create New Project

3.2 选择Maven目录

• Maven > Next

3.3 GAV坐标

1. 输入项目名称
2. 确认项目保存位置
3. 输入组织编号(域名倒置), 例com.baidu
4. 项目唯一标识(与项目名一致)
5. 规定项目版本编号

四. MyBatis环境搭建[重点]

4.1 pom.xml中引入MyBatis核心依赖

• 在pom.xml中引入相关依赖

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0modelVersion>

    <groupId>com.dzgroupId>
    <artifactId>mybatis_testartifactId>
    <version>1.0-SNAPSHOTversion>
    <dependencies>

        <dependency>
            <groupId>org.mybatisgroupId>
            <artifactId>mybatisartifactId>
            <version>3.4.6version>
        dependency>

        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <version>5.1.25version>
        dependency>

        <dependency>
            <groupId>log4jgroupId>
            <artifactId>log4jartifactId>
            <version>1.2.17version>
        dependency>

        <dependency>
            <groupId>junitgroupId>
            <artifactId>junitartifactId>
            <version>4.12version>
            <scope>testscope>
        dependency>

    dependencies>

    <build>

        <resources>
            <resource>

                <directory>src/main/javadirectory>
                <includes>
                    <include>*.xmlinclude>
                    <include>**/*.xmlinclude>
                includes>
                <filtering>falsefiltering>
            resource>
        resources>
    build>

project>

4.2 创建MyBatis配置文件

• src > main > resources 下创建并配置mybatis-config.xml

<configuration>

    <properties resource="jdbc.properties"/>

    <typeAliases>

        <package name="com.dz.entity"/>
    typeAliases>

    <environments default="mysql">

        <environment id="mysql">

            <transactionManager type="jdbc"/>

            <dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory">
                <property name="driver" value="${jdbc.driver}"/>

                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            dataSource>
        environment>
    environments>

    <mappers>

        <mapper resource="UserDaoMapper.xml"/>
        <mapper resource="StudentDaoMapper.xml"/>
        <mapper resource="PassengerDaoMapper.xml"/>
        <mapper resource="PassportDaoMapper.xml"/>
        <mapper resource="DepartmentDaoMapper.xml"/>
        <mapper resource="EmployeeDaoMapper.xml"/>
        <mapper resource="SubjectsDaoMapper.xml"/>
        <mapper resource="StudentsDaoMapper.xml"/>
    mappers>
configuration>

• 注意: mapper.xml文件默认存放在resources目录中,路径不能以 / 开头
• 如果放在其他目录下
• 在pom.xml中导入build标签, 更改maven的编译规则
• 在mybatis-config.xml中注册mapper, 有两种方式

<mapper resource="com/dz/dao/UserDaoMapper.xml"/>

<mapper class="com.dz.dao.UserDaoMapper"/>

• 放在其他目录下可能出现错误: 1 字节的 UTF-8 序列的字节 1 无效
• 解决方法一: 把xxxmapper.xml文件首行的UTF-8改为UTF8
• 解决方法二:[推荐]在pom.xml配置编码格式

    <project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
properties>

4.3 jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis_db?useUnicode=true&useSSL=false&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=8031

4.4 log4j.properties

log4j.rootLogger=DEBUG, stdout

log4j.logger.org.mybatis.example.BlogMapper=TRACE

log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} - %5p [%t] - %m%n

五. MyBatis开发步骤[重点]

5.1 建表

create table t_user
(
    id            int auto_increment
        primary key,
    username      varchar(50) null,
    password      varchar(50) null,
    gender        tinyint     null,
    register_time datetime    null
);

5.2 定义实体类

• 定义所需CURD操作的实体类

package com.dz.entity;

import java.util.Date;

public class User {
    private Integer id;
    private String username;
    private String password;
    private Boolean gender;
    private Date registTime;

    public User() {
    }

    public User(Integer id, String username, String password, Boolean gender, Date registTime) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.gender = gender;
        this.registTime = registTime;
    }

5.3 定义DAO接口

• 根据所需DAO定义接口以及方法

5.3.1 UserDao.java

package com.dz.dao;
import com.dz.entity.User;

public interface UserDao {

    User queryUserById(Integer id);
}

5.4 编写Mapper.xml

5.4.1 UserDaoMapper.xml

• 在resources目录下创建UserDaoMapper.xml文件

<mapper namespace="com.dz.dao.UserDao">
    <select id="queryUserById" resultType="User">
        select id,username,password,gender,register_time as registTime
        from t_user
        where id=#{arg0}
    select>
mapper>

5.5 注册Mapper

• 将UserDaoMapper.xml注册到mybatis-config.xml中(4.2小节中已经配置)

<mappers>

        <mapper resource="UserDaoMapper.xml"/>
mappers>

5.6 测试一

• MyBatis的API操作方式

package com.dz.test;

import com.dz.dao.UserDao;
import com.dz.entity.User;
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 java.io.IOException;
import java.io.InputStream;

    public static void test() throws IOException {

        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserDao mapper = sqlSession.getMapper(UserDao.class);

        System.out.println(mapper.queryUserById(1));

    }

六. 细节补充

6.1 解决mapper.xml存放在resources以外路径中的读取问题

• 在pom.xml文件最后追加< build>标签, 以便可以将xml文件复制到classes中, 并在程序运行时正确读取

    <build>

        <resources>
            <resource>

                <directory>src/main/javadirectory>
                <includes>
                    <include>*.xmlinclude>
                    <include>**/*.xmlinclude>
                includes>
                <filtering>falsefiltering>
            resource>
        resources>
    build>

6.2 properties配置文件

• 对于mybatis-config.xml的核心配置中, 如果存在需要频繁改动的数据内容, 可以提取到properties中 (4.2小节中已经配置)

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis_db?useUnicode=true&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=8031

6.3 类型别名

• 为实体类定义别名, 提高书写效率(4.2小节中已经配置)

<configuration>

    <properties resource="jdbc.properties"/>

    <typeAliases>

        <package name="com.dz.entity"/>
    typeAliases>
configuration>

6.4 创建log4j配置文件

• pom.xml中添加log4j依赖 (4.1小节已配置)

<dependency>
    <groupId>log4jgroupId>
    <artifactId>log4jartifactId>
    <version>1.2.17version>
dependency>

• 创建并配置log4j.properties

log4j.rootLogger=DEBUG, stdout

log4j.logger.org.mybatis.example.BlogMapper=TRACE

log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} - %5p [%t] - %m%n

• 级别
• ALL LEVEL: 打开所有日志记录开关; 是最低等级的, 用于打开所有日志记录
• DEBUG: 输出调试信息; 指出细粒度信息事件对调试应用程序是非常有帮助的
• INFO: 输出提示信息; 消息在粗粒度级别上突出强调应用程序的运行过程
• WARN: 输出警告信息; 表明会出现潜在错误的情形
• ERROR: 输出错误信息; 指出虽然发生错误事件, 但仍然不影响系统的继续运行
• FATAL: 输出致命错误; 指出每个严重的错误事件将会导致应用程序的退出
• OFF LEVEL: 关闭所有日志记录开关; 是最高等级的, 用于关闭所有日志记录

七. MyBatis的CRUD操作[重点]

7.1 查询

• 标签: < select id=”” resultType=””>

7.1.1 序号参数绑定

public interface UserDao {

    User queryUserByIdAndUsername(Integer id, String username);
        User queryUserByIdAndUsername1(Integer id, String username);
}

"queryUserByIdAndUsername" resultType="User">
    select id,username,password,gender,register_time registTime
    where id=#{arg0} and username=#{arg1}

"queryUserByIdAndUsername1" resultType="User">
    select id,username,password,gender,register_time registTime
    where id=#{param1} and username=#{param2}

7.1.2 注解参数绑定[推荐]

import org.apache.ibatis.annotations.Param;
public interface UserDao {

    User queryUserByIdAndPassword( Integer id,  String password);
}

"queryUserByIdAndPassword" resultType="User">
    select id,username,password,gender,register_time registTime
    from t_user
    where id=#{id} and password=#{password}

7.1.3 Map参数绑定

import java.util.Map;

public interface UserDao {

    User queryUserByIdAndUsername2(Map map);
}

"queryUserByIdAndUsername2" resultType="User">
    select id,username,password,gender,register_time registTime
    from t_user
    where id=#{id} and username=#{username}

Map map = new HashMap();
map.put("id", 1);
map.put("username", "dz1");
User user4 = mapper.queryUserByIdAndUsername2(map);
System.out.println(user4);

7.1.4 对象参数绑定

public interface UserDao {

    User queryUserByIdAndPassword2(User user);
}

"queryUserByIdAndPassword2" resultType="User">
    select id,username,password,gender,register_time registTime
    from t_user
    where id=#{id} and password=#{password}

7.1.5 模糊查询

public interface UserDao {

    List queryUserByUsername( String username);
}

"queryUserByUsername" resultType="User">
    select id,username,password,gender,register_time registTime
    from t_user
    where username like concat('%', #{username}, '%')

7.2 删除

• 标签: < delete id=”” parameterType=””>

<delete id="deleteUserById" parameterType="int">
    delete from t_user
    where id=#{id}
delete>

7.3 修改

• 标签: < update id=”” parameterType=””>

<update id="updateUser" parameterType="User">
    update t_user
    set username=#{username},password=#{password},gender=#{gender},register_time=#{registTime}
    where id=#{id}
update>

7.4 增加

• 标签: < insert id=”” parameterType=””>

<insert id="insertUser" parameterType="User">
    insert into t_user
    values(#{id},#{username},#{password},#{gender},#{registTime})
insert>

<insert id="insertUser" parameterType="User">

    insert into t_user
    values(null,#{username},#{password},#{gender},#{registTime})
insert>

7.5 主键回填

• 标签: < selectKey id=”” parameterType=”” order=”AFTER|BRFORE”>

7.5.1 通过last_insert_id()查询主键

• 适用于整数类型自增主键

<insert id="insertUser" parameterType="User">

    <selectKey order="AFTER" resultType="int" keyProperty="id">

        select last_insert_id()
    selectKey>
    insert into t_user values(#{id},#{username},#{password},#{gender},#{registTime})
insert>

7.5.2 通过UUID()查询主键

• 适用于字符串类型自增主键

create table t_student
(
    id     varchar(32) not null
        primary key,
    name   varchar(50) null,
    gender tinyint     null
);

package com.dz.entity;

public class Student {
    private String id;
    private String name;
    private Boolean gender;
    //Getter and Setter...

}

<insert id="insertStudent" parameterType="Student">
    <selectKey order="BEFORE" keyProperty="id" resultType="String">
        select replace(UUID(),'-','')
    </selectKey>
    insert into t_student values(#{id},#{name},#{gender})
</insert>

八. MyBatis工具类[重点]

8.1 封装工具类

• Resources: 用于获得读取配置文件的IO对象, 耗费资源, 建议通过IO一次性读取所有所需要的数据
• SqlSessionFactory: SqlSession工厂类, 内存占用多, 耗费资源, 建议每个应用只创建一个对象
• SqlSession: 相对于Connection, 可控制事务, 应为线程私有, 不被多线程共享
• 将获得连接, 关闭连接, 提交事务, 回滚事务, 获得接口实现类等方法进行封装

package com.dz.utils;

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 java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {

    private static SqlSessionFactory sqlSessionFactory;

    private static final ThreadLocal THREAD_LOCAL = new ThreadLocal<>();
    static {

        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession openSession() {
        SqlSession sqlSession = THREAD_LOCAL.get();
        if (sqlSession == null) {
            sqlSession = sqlSessionFactory.openSession();
            THREAD_LOCAL.set(sqlSession);
        }
        return sqlSession;
    }

    public static void closeSession() {
        SqlSession sqlSession = THREAD_LOCAL.get();
        sqlSession.close();
        THREAD_LOCAL.remove();
    }

    public static void commit() {
        SqlSession sqlSession = openSession();
        sqlSession.commit();
        closeSession();
    }

    public static void rollback() {
        SqlSession sqlSession = openSession();
        sqlSession.rollback();
        closeSession();
    }

    public static  T getMapper(Class mapper) {
        SqlSession sqlSession = openSession();
        return sqlSession.getMapper(mapper);
    }
}

8.2 测试工具类

• 调用MyBatisUtil中的封装方法

package com.dz.test;

import com.dz.dao.UserDao;
import com.dz.entity.User;
import com.dz.utils.MyBatisUtil;
import org.junit.Test;

import java.util.Date;
import java.util.List;

public class MyBatisTest {

    public void test() {
        UserDao mapper = MyBatisUtil.getMapper(UserDao.class);
        User user = mapper.queryUserByIdAndUsername(3, "bbb");
        List users = mapper.queryUserByUsername("bbb");
        System.out.println(user);
        for (User user1 : users) {
            System.out.println(user1);
        }
        System.out.println(mapper.queryUserById(3));
    }

    public void test1() {
        UserDao mapper = MyBatisUtil.getMapper(UserDao.class);
        User user = new User(null,"jack","123",true,new Date());
        mapper.insertUser(user);
        System.out.println(user.getId());
        MyBatisUtil.commit();
    }
}

九. ORM映射[重点]

9.1 MyBatis自动ORM失效

• MyBatis只能自动维护库表”列名”与”属性名”相同时的一一对应关系, 二者不同时, 无法自动识别ORM

9.2 方案一: 列的别名

• 在SQL中使用 as 为查询字段添加列的别名, 以匹配属性名
• 或者把as省略, 直接在想要添加别名的列后面 写上别名

<select id="queryUserById" resultType="User">
    select id,username,password,gender,register_time as registTime
    from t_user
    where id=#{arg0}
select>

<select id="queryUserById" resultType="User">
    select id,username,password,gender,register_time registTime
    from t_user
    where id=#{arg0}
select>

9.3 方案二: 结果映射

• 结果映射(ResultMap 查询结果的封装规则)
• 通过< resultMap id=”” type=””>映射, 匹配列名与属性名

<mapper namespace="com.dz.dao.UserDao">

    <resultMap id="user_resultMap" type="User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="gender" property="gender"/>
        <result column="register_time" property="registTime"/>
    resultMap>
    <select id="queryUserById" resultMap="user_resultMap">
        select id,username,password,gender,register_time
        from t_user
        where id=#{arg0}
    select>
mapper>

十. MyBatis处理关联关系-多表连接[重点]

• 实体间的关系: 关联关系(拥有 has, 属于 belong)
• OneToOne: 一对一关系(passenger—Passport)
• OneToMany: 一对多关系(Department—Employee)
• ManyToMany: 多对多关系(Student—Subject)

create table t_passenger
(
    id       int auto_increment
        primary key,
    name     varchar(50) null,
    sex      varchar(1)  null,
    birthday date        null
);

create table t_passport
(
    id           int auto_increment
        primary key,
    nationality  varchar(50) null,
    expire       date        null,
    passenger_id int         null,
    constraint passenger_id
        unique (passenger_id),
    constraint t_passport_ibfk_1
        foreign key (passenger_id) references t_passenger (id)
);

package com.dz.entity;

import java.util.Date;

public class Passenger {
    private Integer id;
    private String name;
    private Boolean sex;
    private Date birthday;

    //存储旅客的护照信息: 关系属性
    private Passport passport;
    //Getter and Setter...

}

package com.dz.entity;

import java.util.Date;

public class Passport {
    private Integer id;
    private String nationality;
    private Date expire;

    //存储旅客信息: 关系属性
    private Passenger passenger;
    //Getter and Setter
}

• 关系属性: 将关系的另一方, 作为本方属性进行保存
• 关系方向:
• 单向关系: 只能从关系的一方, 查到关系的另一方
• 双向关系: 在关系的任何一方, 都可查到关系的另一方
• 级联查询
• 当访问其中的一方关系时, 如果需要查看与之关联的另一方数据, 则必须使用表连接查询, 将查询到的另一方数据, 保存在本方的属性中
  • 一对一关联查询
  • 一对多关联查询
  • 多对多关联查询

10.1 OneToOne

• 一个乘客对应一本护照, 反之亦然

package com.dz.dao;

import com.dz.entity.Passenger;
import org.apache.ibatis.annotations.Param;

public interface PassengerDao {

    Passenger queryPassengerById( Integer id);
}

"1.0" encoding="UTF-8"?>
"-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

"com.dz.dao.PassengerDao">

    "passenger_passport" type="Passenger">
        "id" property="id"/>
        "name" property="name"/>
        "sex" property="sex"/>
        "birthday" property="birthday"/>

        "passport" javaType="Passport">
            "passId" property="id"/>
            "nationality" property="nationality"/>
            "expire" property="expire"/>

    "queryPassengerById" resultMap="passenger_passport">
        select t_passenger.id,name,sex,birthday,t_passport.id passId,nationality,expire
        from t_passenger join t_passport
        on t_passenger.id = t_passport.passenger_id
        where t_passenger.id = #{id}

• *注意: 指定”一方”关系时 (对象), 使用< association javaType=””>

10.2 OneToMany

• 一个部门对应多个员工, 一个员工对应一个部门

package com.dz.entity;

import java.util.List;

public class Department {
    private Integer id;
    private String name;
    private String location;

    private List employees;

}

package com.dz.entity;

public class Employee {
    private Integer id;
    private String name;
    private Double salary;

    private Department department;

}

"1.0" encoding="UTF-8"?>
"-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

"com.dz.dao.DepartmentDao">

    "dept_emp" type="Department">
        "id" property="id"/>
        "name" property="name"/>
        "location" property="location"/>

        "employees" ofType="Employee">
            "emp_id" property="id"/>
            "emp_name" property="name"/>
            "salary" property="salary"/>

    "queryDepartmentById" resultMap="dept_emp">
        select t_department.id,t_department.name,location,t_employee.id emp_id,t_employee.name emp_name,salary
        from t_department join t_employee
        on t_department.id = t_employee.dept_id
        where t_department.id=#{id}

• *注意: 指定”多方”关系时 (集合), 使用< collection ofType=””>

10.3 ManyToMany

• 一个学生对应很多科目, 一个科目也对应很多学生
• 建立三张关系表,学生表, 科目表, 学生科目id表

create table t_students
(
    id   int auto_increment
        primary key,
    name varchar(50) null,
    sex  varchar(1)  null
);

create table t_subjects
(
    id    int auto_increment
        primary key,
    name  varchar(50) null,
    grade int         null
);

create table t_stu_sub
(
    student_id int not null,
    subject_id int not null,
    primary key (student_id, subject_id),
    constraint t_stu_sub_ibfk_1
        foreign key (student_id) references t_students (id),
    constraint t_stu_sub_ibfk_2
        foreign key (subject_id) references t_subjects (id)
);

<?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="com.dz.dao.StudentsDao">
    <resultMap id="students_subjects" type="Students">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="sex" property="sex"/>

        <collection property="subjects" ofType="Subjects">
            <id column="sub_id" property="id"/>
            <result column="sub_name" property="name"/>
            <result column="grade" property="grade"/>
        </collection>
    </resultMap>

    <select id="queryStudentsById" resultMap="students_subjects">
        select t_students.id,t_students.name,sex,t_subjects.id sub_id,t_subjects.name sub_name,grade
        from t_students join t_stu_sub
        on t_students.id = t_stu_sub.student_id
        join t_subjects
        on t_stu_sub.subject_id = t_subjects.id
        where t_students.id = #{id}
    </select>

</mapper>

• *注意: 指定”多方”关系时 (集合), 使用< collection ofType=””>

10.4 关系总结

• 双方均可建立关系属性, 建立关系属性后, 对应的Mapper文件中需使用< ResultMap>完成多表映射
• 一对一: < association javaType=””>
• 一对多:
• 一的那一方持有集合关系属性,使用< collection ofType=””>
• 多的那一方持有对象关系属性, 使用< association javaType=””>
• 多对多: < collection ofType=””>

十一. 动态SQL[重点]

• MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作, 并动态拼接成完整的SQL之后再执行, 以达到SQL复用、 简化编程的效果

11.1 < sql >

<mapper namespace="com.dz.dao.UserDao">

    <sql id="user_sql">
        select id,username,password,gender,register_time
        from t_user
    sql>
    <select id="queryUserById" resultType="User">
        <include refid="user_sql"/>
        where id=#{arg0}
    select>
mapper>

11.2 < where >

<select id="queryUser2" resultMap="user_resultMap">
        <include refid="user_sql"/>
        <where>
            <if test="username!=null">
                username=#{username}
            if>
            <if test="gender!=null">
                or gender=#{gender}
            if>
        where>
select>

11.3 < set >

<update id="updateUser" parameterType="User">
        update t_user

        <set>
            <if test="username!=null">
                username=#{username},
            if>
            <if test="password!=null">
                password=#{password},
            if>
            <if test="gender!=null">
                gender=#{gender},
            if>
            <if test="registTime!=null">
                register_time=#{registTime}
            if>
        set>
update>

11.4 < trim >

• *< trim prefix=”” suffix=”” prefixOverrides=”” suffixOverrides=””>代替< where >, < set >

11.4.1 trim替换where

<select id="queryUser2" resultMap="user_resultMap">
        <include refid="user_sql"/>

        <trim prefix="where" prefixOverrides="or|and">
            <if test="username!=null">
                username=#{username}
            if>
            <if test="gender!=null">
                or gender=#{gender}
            if>
        trim>
select>

11.4.2 trim替换set

<update id="updateUser" parameterType="User">
        update t_user

        <trim prefix="set" suffixOverrides=",">
            <if test="username!=null">
                username=#{username},
            if>
            <if test="password!=null">
                password=#{password},
            if>
            <if test="gender!=null">
                gender=#{gender},
            if>
            <if test="registTime!=null">
                register_time=#{registTime}
            if>
        trim>
        where id=#{id}
    update>

11.5 < foreach >

参数

• collection: 容器类型
• list, array, map
• open: 起始符
• (
• close: 结束符
• )
• separator: 分隔符
• ,
• index: 下标号
• 从0开始, 依次递增
• item: 当前项
• 任意名称(循环中通过#{任意名称} 表达式访问)

11.5.1 批量删除

package com.dz.dao;

import com.dz.entity.User;
import java.util.List;

public interface UserDao {
    Integer insertManyUser(List users);
}

"deleteManyUser" parameterType="java.util.List">

    delete from t_user where id in
    "list" open="(" close=")" item="id9" separator=",">
        #{id9}

public class MyBatisTest {
    private UserDao mapper;

    public void init() {
        mapper = MyBatisUtil.getMapper(UserDao.class);
    }

    public void test4() {

        List ids = Arrays.asList(1000,1001);
        mapper.deleteManyUser(ids);
        MyBatisUtil.commit();
    }
}

11.5.2 批量增加

package com.dz.dao;

import com.dz.entity.User;
import java.util.List;

public interface UserDao {
    Integer insertManyUser(List users);
}

"insertManyUser" parameterType="java.util.List">

    insert into t_user values
    "list" open="" close="" item="user9" separator=",">
        (null,#{user9.username},#{user9.password},#{user9.gender},#{user9.registTime})

public class MyBatisTest {
    private UserDao mapper;

    public void init() {
        mapper = MyBatisUtil.getMapper(UserDao.class);
    }

    public void test5() {
        List users = Arrays.asList(new User(null, "dz1", "123", true, new Date()),
                                         new User(null, "dz2", "123", false, new Date()),
                                         new User(null, "dz3", "123", true, new Date()));
        mapper.insertManyUser(users);
        MyBatisUtil.commit();
    }
}

十二. 缓存(Cache) [重点]

• 内存中的一块存储空间, 服务于某个应用程序, 旨在将频繁读取的数据临时保存在内存中, 便于二次快速访问
• 无缓存:
• 用户在访问相同数据时, 需要发起多次对数据库的直接访问, 导致产生大量IO, 读写硬盘的操作, 效率低下
• 有缓存:
• 首次访问时, 查询数据库, 将数据存储到缓存中, 再次访问时, 直接访问缓存, 减少IO, 硬盘读写次数, 提高效率

12.1 一级缓存

• SqlSession级别的缓存, 同一个SqlSession发起多次同构查询, 会将数据保存在一级缓存中
• 注意: *无需任何配置, 默认开启一级缓存

12.2 二级缓存

• SqlSessionFactory级别的缓存, 同一个SqlSessionFactory构建的SqlSession发起的多次同构查询, 会将数据保存在二级缓存中
• 注意: *在sqlSession.commit()或者sqlSession.close()之后生效

12.2.1 开启全局缓存

• < setting > 是MyBatis中极为重要的调整设置, 他们会改变MyBatis的运行行为, 其他详细配置可参考官方文档

<properties resource="jdbc.properties"/>

<settings>
    <setting name="cacheEnabled" value="true"/>
settings>

<typeAliases>
    <package name="com.dz.entity"/>
typeAliases>

12.2.2 指定Mapper缓存

<mapper namespace="com.dz.dao.UserDao">

    <cache/>
    <select id="queryUserById" resultType="User">
        select id,username,password,gender,register_time as registTime
        from t_user
        where id=#{arg0}
    select>
mapper>

@Test
public void test7() {
    //通过形同的SqlSessionFactory获取多个SqlSession
    SqlSession session1 = MyBatisUtil.getSession();
    SqlSession session2 = MyBatisUtil.getSession();
    SqlSession session3 = MyBatisUtil.getSession();
    UserDao mapper1 = session1.getMapper(UserDao.class);
    UserDao mapper2 = session2.getMapper(UserDao.class);
    UserDao mapper3 = session3.getMapper(UserDao.class);
    mapper1.queryUsers();
    session1.close();//必须关闭sqlSession才可缓存数据
    System.out.println("=============");
    mapper2.queryUsers();
    session2.close();//必须关闭sqlSession才可缓存数据
    System.out.println("=============");
    mapper3.queryUsers();
    session3.close();//必须关闭sqlSession才可缓存数据
}

12.2.3 缓存清空并重新缓存

public void test7() {

    SqlSession session1 = MyBatisUtil.getSession();
    SqlSession session2 = MyBatisUtil.getSession();
    SqlSession session3 = MyBatisUtil.getSession();
    UserDao mapper1 = session1.getMapper(UserDao.class);
    UserDao mapper2 = session2.getMapper(UserDao.class);
    UserDao mapper3 = session3.getMapper(UserDao.class);
    mapper1.queryUsers();
    session1.close();
    System.out.println("=============");
    mapper2.deleteUserById(1);
    session2.commit();
    session2.close();
    System.out.println("=============");
    mapper3.queryUsers();
    session3.close();
}

十三. Druid连接池

13.1 概念

• Druid 是阿里巴巴开源平台上的一个项目, 整个项目由数据库连接池, 插件框架和SQL解析器组成. 该项目主要是为了扩展 JDBC 的一些限制. 可以让程序员实现一些特殊的需求, 比如向密钥服务请求凭证, 统计SQL信息, SQL性能收集, SQL注入检查, SQL翻译等, 程序员可以通过定制来实现自己需要的功能

13.2 不同连接池对比

• 测试执行申请归还连接1,000,000(一百万)次总耗时性能对比

13.2.1 测试结论

• Druid是性能最好的数据库连接池, tomcat-jdbc 和 Druid 性能接近
• Proxool 在激烈并发时会抛异常, 不适用
• C3P0 和 Proxool 都相当慢, 影响 sql 执行效率
• BoneCP 性能并不优越, 采用linkedTransferQueue 并没有获得性能提升
• 除了 bonecp, 其他的在 JDK7 上跑的比 JDK6 上快
• jboss-datasource 虽然稳定, 但性能很糟糕

13.3 配置pom.xml

• 引入Druid依赖

<dependency>
    <groupId>com.alibabagroupId>
    <artifactId>druidartifactId>
    <version>1.1.16version>
dependency>

13.4 创建DruidDataSourceFactory

• 创建MyDruidDataSourceFactory类, 并继承PooledDataSourceFactory, 并替换数据源

package com.dz.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory;

public class MyDruidDataSourceFactory extends PooledDataSourceFactory {
    public MyDruidDataSourceFactory() {
        this.dataSource = new DruidDataSource();
    }
}

13.5 修改mybatis-config.xml

• mybatis-config.xml中连接池相关配置

<dataSource type="com.dz.datasource.MyDruidDataSourceFactory">
    <property name="driverClass" value="${jdbc.driver}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
dataSource>

• 注意: < property name=”属性名” />属性名必须和com.alibaba.druid.pool.DruidAbstractDataSource中一致

十四. PageHelper

14.1 概念

• PageHelper是适用于MyBatis框架的一个分页插件, 使用方式极为便捷, 支持任何复杂的单表, 多表分页查询操作

14.2 访问与下载

• 官方网站:https://pagehelper.github.io/
• 下载地址:https://github.com/pagehelper/Mybatis-PageHelper

14.3 开发步骤

• PageHelper中提供了多个分页操作的静态方法入口

14.3.1 引入依赖

• pom.xml中引入PageHelper依赖

<dependency>
    <groupId>com.github.pagehelpergroupId>
    <artifactId>pagehelperartifactId>
    <version>5.1.10version>
dependency>

14.3.2 配置mybatis-config.xml

• 在mybatis-config.xml中添加 < plugins >

<configuration>
    <typeAliases>typeAliases>

    <plugins>

        <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
    plugins>

    <environments>environments>
configuration>

14.3.3 PageHelper应用方式

• 使用PageHelper提供的静态方法设置分页查询条件

public void testPage() {

    PageHelper.startPage(1,2);
    List users = mapper.queryUsers();
    for (User user : users) {
        System.out.println(user);
    }
}

14.4 PageInfo对象

• PageInfo对象中包含了分页操作中的所有相关数据

14.4.1 PageInfo应用方式

• 使用PageInfo保存分页查询结果

public void testPage() {

    PageHelper.startPage(1,2);
    List users = mapper.queryUsers();
    for (User user : users) {
        System.out.println(user);
    }

    PageInfo pageInfo = new PageInfo<>(users);
    System.out.println("================");
}

14.4.2 注意事项

• 只有在PageHelper.startPage()方法之后的 第一个查询会有执行分页
• 分页插件 不支持带有”for update”的查询语句
• 分页插件不支持 嵌套查询 , 由于嵌套结果方式会导致结果集被折叠, 所以无法保证分页结果数量正确

14.4.3 分页练习

• 使用Servlet + JSP + MyBatis + PageHelper, 完成分页查询功能

Original: https://www.cnblogs.com/qimu666/p/16565695.html
Author: 柒木木木
Title: 3_MyBatis