Mybatis3 快速入门

目前常见的持久层java框架有Hibernate，Mybatis，SpringData。笔者比较喜欢用SpringData。Hibernate 和 Mybatis 也经常用。今天通过 Mybatis 的简介，数据的增删改查，表的级联查询，动态SQL语句来快速入门 Mybatis 。

1 Mybatis 简介

摘录百度百科的内容：MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录

如果 Hibernate 是自动化持久层框架，那么 Mybatis 就是半自动化持久层框架。半自动？？？听起来好像 lower 了。其实不然，Mybatis 将 sql 和 java 分离开。让专业的db工程师负责 sql 的优化，提高其性能，在高并发的场景，系统依然稳如dog 。程序员可以把更多的精力放在业务逻辑上。

Mybatis：https://github.com/mybatis/my...

2 Mybatis 快速入门

需求：使用 mybatis 框架完成数据的增删改查操作，和级联查询，模糊查询，调用存储过程，使用mybatis的一二级缓存

技术：mybatis，maven

源码：见文章底部

说明：本文内容属于快速入门，通过手写 xml 映射文件了解 mybatis 的工作原理。实际开发中，一般采用官方提供的逆向工程自动生成需要的 java 文件和 xml 文件

结构：

准备：

Mysql数据库表结构

创建四张表，其中 person 独立存在。classroom 和 student，teacher 存在主外键关系。

① classroom 的 student_id 和 student 的 class_id 存在主外键关系，并且是一对多的关系

② classroom 的 teacher_id 和 teacher 的 id 存在主外键关系，并且是一对一的关系

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for classroom
-- ----------------------------
DROP TABLE IF EXISTS `classroom`;
CREATE TABLE `classroom` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room` varchar(255) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_t_id` (`teacher_id`),
  KEY `c_s_id` (`student_id`),
  CONSTRAINT `c_t_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of classroom
-- ----------------------------
INSERT INTO `classroom` VALUES ('1', 'JavaEE', '1', '1');
INSERT INTO `classroom` VALUES ('2', 'Linux', '2', '2');

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1', 'lxl@qq.com', 'lxl');
INSERT INTO `person` VALUES ('2', 'cyy@qq.com', 'cyy');
INSERT INTO `person` VALUES ('3', 'itdrgon@qq.com', 'itdragon');
INSERT INTO `person` VALUES ('4', 'java@qq.com', 'java');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `s_c_id` (`class_id`),
  CONSTRAINT `s_c_id` FOREIGN KEY (`class_id`) REFERENCES `classroom` (`student_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'ITDragon', '1');
INSERT INTO `student` VALUES ('2', 'Marry', '1');
INSERT INTO `student` VALUES ('3', 'XiaoMing', '2');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', 'Java');
INSERT INTO `teacher` VALUES ('2', 'Docker');

Maven 项目的核心文件 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.0</modelVersion>
  <groupId>com.itdragon.mybatis</groupId>
  <artifactId>mybatis-basic</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <properties>
          <commons-lang3.version>3.3.2</commons-lang3.version>
        <commons-io.version>1.3.2</commons-io.version>
        <commons-net.version>3.3</commons-net.version>
        <junit.version>4.12</junit.version>
        <slf4j.version>1.6.4</slf4j.version>
        <mybatis.version>3.2.8</mybatis.version>
        <mybatis.spring.version>1.2.2</mybatis.spring.version>
        <mybatis.paginator.version>1.2.15</mybatis.paginator.version>
        <mysql.version>5.1.6</mysql.version>
        <druid.version>1.0.9</druid.version>
    </properties>

    <dependencies>
        <!-- Apache工具组件 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons-lang3.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-io</artifactId>
            <version>${commons-io.version}</version>
        </dependency>
        <dependency>
            <groupId>commons-net</groupId>
            <artifactId>commons-net</artifactId>
            <version>${commons-net.version}</version>
        </dependency>
        <!-- 单元测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <!-- 日志处理 -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <!-- Mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>${mybatis.spring.version}</version>
        </dependency>
        <dependency>
            <groupId>com.github.miemiedev</groupId>
            <artifactId>mybatis-paginator</artifactId>
            <version>${mybatis.paginator.version}</version>
        </dependency>
        <!-- MySql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!-- 连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
    </dependencies>

</project>

Mybatis 的配置文件 SqlMapConfig.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>

    <!-- 标签必须按顺序写,否则会提示错误：The content of element type "configuration" must match "(properties?,settings?,...)". -->  
    
    <!-- 引入配置文件 -->
    <properties resource="db.properties" />
    
    <!-- 配置实体类的别名 -->
    <typeAliases>
        <!-- 给指定包取别名，别名为实体类对应的简单类名，如 com.itdragon.pojo.Person 的别名就是 Person -->
        <package name="com.itdragon.pojo" />
    </typeAliases>
    
    <!-- 配置数据库链接 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <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="com/itdragon/mapper/PersonMapper.xml" />
        <mapper resource="com/itdragon/mapper/ClassroomMapper.xml" />
    </mappers>
    
</configuration>

数据库的配置文件 db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jpa?characterEncoding=utf-8
jdbc.username=root
jdbc.password=root

到这里准备工作就做完了。

3 数据的增删改查

Person.java 实体类

package com.itdragon.pojo;

// 学习 mybatis crud 实体类
public class Person {
    private Integer id;
    private String email;
    private String lastName; // 这里lastName 在数据库中对应的是 last_name, 这会出现：字段名与实体类属性名不相同的冲突问题
    
    public Person() {
    }
    
    public Person(Integer id, String email, String lastName) {
        this.id = id;
        this.email = email;
        this.lastName = lastName;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName == null ? null : lastName.trim();
    }

    @Override
    public String toString() {
        return "Person [id=" + id + ", email=" + email + ", lastName=" + lastName + "]";
    }
    
}

PersonMapper.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="com.itdragon.mapper.PersonMapper">
    <!-- CRUD 操作 -->
    <!-- 
        基础知识： 
        select 查询数据
        insert 插入数据
        delete 删除数据
        update 更新数据
        namespace 命名空间
        id 方法名, 命名空间  + 方法名 = 唯一方法
        parameterType 传入参数类型
        resultType 返回值类型  
        resultMap 以键值对的类型返回结果
        
        参数传值：#{xxx}
        parameterType 如果不是实体类，对应的参数名可以自定义。如 #{id} 也可以是 #{xxxx}
        如果是实体类，对应的参数名必须是实体类属性名。为了避免错误，尽量全部都用属性名。
        
        扩展知识：
        resultType 的值是 com.itdragon.pojo.Person 全类名，但为了方便，可以考虑使用别名 
        resultMap 为了避免类似 lastName 和 last_name 冲突，导致查询的 last_name 会是 null 问题，可以设置键值关系
     -->
    <select id="getPersonById" parameterType="int" resultType="com.itdragon.pojo.Person">
        select * from person where id=#{id}
    </select>

    <!-- 解决字段名与实体类属性名不相同的冲突问题第一种办法（不推荐） -->
    <select id="getPersonByIdOne" parameterType="int" resultType="com.itdragon.pojo.Person">
        select id, email, last_name lastName from person where id=#{id}
    </select>

    <select id="getPersonByIdTwo" parameterType="int" resultMap="getPersonMap">
        select * from person where id=#{id}
    </select>
    <!-- 使用 resultMap 设置冲突字段名和实体类属性名对应关系，（推荐） -->
    <resultMap type="Person" id="getPersonMap">
        <result property="lastName" column="last_name" />
    </resultMap>

    <!-- parameterType 中直接使用了 Person 是因为在 SqlMapConfig.xml 文件中设置了别名 -->
    <insert id="createPerson" parameterType="Person">
        insert into person(email, last_name) values(#{email}, #{lastName})
    </insert>

    <delete id="deletePersonById" parameterType="int">
        delete from person where id=#{id}
    </delete>

    <update id="updatePersonById" parameterType="Person">
        update person set email=#{email}, last_name=#{lastName} where id=#{id}
    </update>

    <select id="getAllperson" resultType="Person">
        select * from person
    </select>
    
</mapper>

测试方法：

package com.itdragon.test;

import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.itdragon.pojo.Classroom;
import com.itdragon.pojo.Person;

public class MyBatisTest {
    
    public SqlSession getSqlSession() {
        String resource = "SqlMapConfig.xml"; 
        InputStream is = MyBatisTest.class.getClassLoader().getResourceAsStream(resource);
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(true); // false 默认手动提交, true 自动提交
        return session;
    }
    
    // crud 操作
    @Test
    public void getPersonById() {
        String statement = "com.itdragon.mapper.PersonMapper.getPersonById";
        Person person = getSqlSession().selectOne(statement, 2);
        System.out.println(person);
        
        statement = "com.itdragon.mapper.PersonMapper.getPersonByIdOne";
        person = getSqlSession().selectOne(statement, 2);
        System.out.println(person);
        
        statement = "com.itdragon.mapper.PersonMapper.getPersonByIdTwo";
        person = getSqlSession().selectOne(statement, 2);
        System.out.println(person);
    }
    @Test
    public void getAllperson() {
        String statement = "com.itdragon.mapper.PersonMapper.getAllperson";
        List<Person> persons = getSqlSession().selectList(statement);
        System.out.println(persons);
    }
    @Test
    public void createPerson() {
        String statement = "com.itdragon.mapper.PersonMapper.createPerson";
        int result = getSqlSession().insert(statement, new Person(3, "itdragon@qq.com", "ITDragon"));
        System.out.println(result);
    }
    @Test
    public void updatePersonById() {
        String statement = "com.itdragon.mapper.PersonMapper.updatePersonById";
        int result = getSqlSession().update(statement, new Person(4, "itdragon@qq.com", "ITDragon博客"));
        System.out.println(result);
    }
    @Test
    public void deletePersonById() {
        String statement = "com.itdragon.mapper.PersonMapper.deletePersonById";
        int result = getSqlSession().delete(statement, 4);
        System.out.println(result);
    }
    
}

4 级联查询

为了满足一对一和一对多的级联操作，新增三个实体类，分别是 Classroom（教室），Teacher（老师），Student（学生）

Classroom 和 Teacher 是一对一的关系，Classroom 和 Student 是一对多的关系

package com.itdragon.pojo;

import java.io.Serializable;
import java.util.List;

// 学习 表的关联关系所用字段，一个教室关联一个老师（一对一），一个教室关联一群学生（一对多）
public class Classroom implements Serializable {

    private Integer id;
    private String room;
    private Teacher teacher;
    private List<Student> students;

    public Classroom() {
    }

    public Classroom(Integer id, String room, Teacher teacher, List<Student> students) {
        this.id = id;
        this.room = room;
        this.teacher = teacher;
        this.students = students;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getRoom() {
        return room;
    }

    public void setRoom(String room) {
        this.room = room;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Classroom [id=" + id + ", room=" + room + ", teacher=" + teacher + ", students=" + students + "]";
    }

}

package com.itdragon.pojo;

import java.io.Serializable;

public class Teacher implements Serializable{
    
    private Integer id;
    private String subject;
    
    public Teacher() {
    }

    public Teacher(Integer id, String subject) {
        this.id = id;
        this.subject = subject;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getSubject() {
        return subject;
    }

    public void setSubject(String subject) {
        this.subject = subject;
    }

    @Override
    public String toString() {
        return "Teacher [id=" + id + ", subject=" + subject + "]";
    }

}

package com.itdragon.pojo;

import java.io.Serializable;

public class Student implements Serializable {

    private Integer id;
    private String name;

    public Student() {
    }

    public Student(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + "]";
    }

}

ClassroomMapper.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="com.itdragon.mapper.ClassroomMapper">
    <!-- 关联表查询 -->
    <!-- 
        基础知识：
        association：用于一对一的关联查询
            property：对象的属性名
            javaType：对象的类型
            column：对应数据表中外键
            select：使用另外一个查询的封装结果
        collection：用于一对多的关联查询
            ofType：指定集合对象的类型
    -->
    
    <!-- 
        需求：通过 id 查询 classroom, 并打印 teacher 信息
        嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
        第一步：先查询 classroom SELECT * FROM classroom WHERE id=?;
        第二步：再查询 teacher SELECT * FROM teacher WHERE id=classroom.id //classroom 是第一步的查询结果
        说明：嵌套查询的方法，虽然好理解，当时不建议
    -->
    <select id="getClassroomById" parameterType="int" resultMap="getClassroomMap">
        SELECT * FROM classroom WHERE id=#{id}
    </select>
    
    <select id="getTeacherById" parameterType="int" resultType="Teacher">
        SELECT * FROM teacher WHERE id=#{id}
    </select>
    
    <resultMap type="Classroom" id="getClassroomMap">
        <association property="teacher" column="teacher_id" select="getTeacherById">
        <!-- 如果 teacher 存在属性字段和字段冲突，需要在这里设置 -->
        </association>
    </resultMap>
    
    <!-- 
        需求：通过 id 查询 classroom, 并打印 teacher 信息
        嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集,封装联表查询的数据(去除重复的数据)
        select * from classroom, teacher where classroom.teacher_id=teacher.id and classroom.id=?
    -->
    <select id="getClassroom2ById" parameterType="int" resultMap="getClassroom2Map">
        SELECT * FROM classroom c, teacher WHERE c.teacher_id = teacher.id AND c.id = #{id}
    </select>
    <resultMap type="Classroom" id="getClassroom2Map">
        <id property="id" column="id"/>
        <result property="room" column="room"/>
        <association property="teacher" javaType="Teacher">
            <id property="id" column="id"/>
            <result property="subject" column="subject"/>
        </association>
    </resultMap>
    
    <!-- 
        需求：通过 id 查询 classroom, 并打印 teacher 和 student 信息
        嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
        第一步：先查询 classroom SELECT * FROM classroom WHERE id=?;
        第二步：再查询 teacher SELECT * FROM teacher WHERE id=classroom.id //classroom 是第一步的查询结果
        第三步：再查询 student SELECT * FROM student WHERE id=classroom.id //classroom 是第一步的查询结果
    -->
    <select id="getClassroom3ById" resultMap="getClassroom3Map">
        SELECT * FROM classroom WHERE id=#{id}
    </select>
    <!-- getTeacherById 上面有了，就不重复写了 -->
    <select id="getStudentById" parameterType="int" resultType="Student">
        SELECT * FROM student WHERE class_id=#{id}
    </select>
    
    <resultMap type="Classroom" id="getClassroom3Map">
        <association property="teacher" column="teacher_id" select="getTeacherById"></association>
        <collection property="students" column="student_id" select="getStudentById"></collection>
    </resultMap>
    
    <!-- 
        需求：通过 id 查询 classroom, 并打印 teacher 和 student 信息
         嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集,封装联表查询的数据(去除重复的数据)
        SELECT * FROM classroom c, teacher t,student s WHERE c.teacher_id=t.id AND c.id=s.class_id AND c.id=?
     -->
    <select id="getClassroom4ById" parameterType="int" resultMap="getClassroom4Map">
        SELECT * FROM classroom c, teacher t, student s WHERE c.teacher_id=t.id AND c.student_id=s.class_id AND c.id=#{id}
    </select>
    
    <resultMap type="Classroom" id="getClassroom4Map">
        <id property="id" column="id"/>
        <result property="room" column="room"/>
        <association property="teacher" javaType="Teacher">
            <id property="id" column="id"/>
            <result property="subject" column="subject"/>
        </association>
        <collection property="students" ofType="Student">
            <!-- 
                存在问题
                如果两表联查，主表和明细表的主键都是id的话，明细表的多条只能查询出来第一条。
                <id property="id" column="s_id"/>
                解决方法：https://www.cnblogs.com/junge/p/5145881.html
            -->
            <result property="name" column="name"/>
        </collection>
    </resultMap>
    
</mapper>

测试方法：

// 关联表的查询
    @Test
    public void getClassroomById() {
        String statement = "com.itdragon.mapper.ClassroomMapper.getClassroomById";
        Classroom classroom = getSqlSession().selectOne(statement, 1);
        System.out.println(classroom);
        
        statement = "com.itdragon.mapper.ClassroomMapper.getClassroom2ById";
        classroom = getSqlSession().selectOne(statement, 1);
        System.out.println(classroom);
        
        statement = "com.itdragon.mapper.ClassroomMapper.getClassroom3ById";
        classroom = getSqlSession().selectOne(statement, 1);
        System.out.println(classroom);
        
        statement = "com.itdragon.mapper.ClassroomMapper.getClassroom4ById";
        classroom = getSqlSession().selectOne(statement, 1);
        System.out.println(classroom);
        
    }

5 动态SQL语句

这里通过模糊查询 Email 来了解动态SQL语句，在 PersonMapper.xml 中添加如下代码

<!-- 动态SQL与模糊查询 -->
    <!-- 
        需求：通过模糊查询邮箱和指定id范围查询数据
        动态SQL：
            if：判断语句  <if test=''></if> 
            where：去掉多余的 and 和 or 
                <where><if test=''>AND xxx</if></where>
            set：去掉多余的 ","
                <set><if test=''>xxx , </if></set>
            trim： 代替 where , set
                if + where == <trim prefix="WHERE" prefixOverrides="AND |OR "></trim>
                if + set == <trim prefix="SET" suffixOverrides=","></trim>
            choose： (when, otherwise) 类似java的switch case default
                <choose><when test="">xxx</when><otherwise>xxx</otherwise></choose>
            foreach：类似java的加强for循环
                <foreach collection="array" item="xxx"  open="(" separator="," close=")"></foreach>
        说明：mybatis 提供了自动生成的逆向工程的工具，这里只需要了解即可，虽然是很重要的知识点
        学习博客：http://limingnihao.iteye.com/blog/782190
    -->
     
    <select id="getPersonLikeKey" parameterType="Person" resultMap="getPersonMap">
        select * from person where 
        <if test='email != "%null%"'>
             email like #{email} and 
        </if>
        id > #{id}
    </select>

测试方法：

// 调用存储过程
    @Test
    public void getPersonCountGtId(){
        String statement = "com.itdragon.mapper.PersonMapper.getPersonCountGtId";
        Map<String, Integer> parameterMap = new HashMap<String, Integer>();
        parameterMap.put("personId", 1);
        parameterMap.put("personCount", -1);
        getSqlSession().selectOne(statement, parameterMap);
        Integer result = parameterMap.get("personCount");
        System.out.println(result);
    }

6 存储过程

引用百度百科：存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

这里通过获取大于Person id 数量的逻辑来了解Mybatis 是如何调用存储过程的。首先在Mysql 命令行中执行一下代码

#创建存储过程 传入Id的值，返回id大于该值的数量
DELIMITER $
#在 jpa 数据库中，创建一个名为get_person_count的方法，传入参数是person_id,返回参数是person_count
CREATE PROCEDURE jpa.get_person_count(IN person_id INT, OUT person_count INT) 
BEGIN  
SELECT COUNT(*) FROM jpa.person WHERE person.id > person_id INTO person_count;
END 
$
#调用存储过程
DELIMITER ;
SET @person_count = 0;
CALL jpa.get_person_count(1, @person_count);
SELECT @person_count;

打印结果如下，则说明创建成功了

还是在 PersonMapper.xml 文件中添加如下代码

<!-- 调用存储过程 -->
    <!-- 
        通过id，获取大于该id的数量
        CALL jpa.get_person_count(1, @person_count);
        注意：需关闭二级缓存
        Caching stored procedures with OUT params is not supported.  Please configure useCache=false in ...
     -->
    <select id="getPersonCountGtId" parameterMap="getPersonCountMap" statementType="CALLABLE">
        CALL jpa.get_person_count(?,?)
    </select>
    
    <parameterMap type="java.util.Map" id="getPersonCountMap">
        <parameter property="personId" mode="IN" jdbcType="INTEGER"/>
        <parameter property="personCount" mode="OUT" jdbcType="INTEGER"/>
    </parameterMap>

测试方法：

// 调用存储过程
    @Test
    public void getPersonCountGtId(){
        String statement = "com.itdragon.mapper.PersonMapper.getPersonCountGtId";
        Map<String, Integer> parameterMap = new HashMap<String, Integer>();
        parameterMap.put("personId", 1);
        parameterMap.put("personCount", -1);
        getSqlSession().selectOne(statement, parameterMap);
        Integer result = parameterMap.get("personCount");
        System.out.println(result);
    }

7 一二级缓存

一级缓存：基于PerpetualCache 的 HashMap本地缓存，其存储作用域为 Session，当 Session flush 或 close 之后，该Session中的所有 Cache 就将清空。

　　① 若Session 被关闭了，缓存清空

　　② 若数据执行了创建，更新，删除操作，缓存清空

　　③ 如果不是同一个Session，缓存失效

二级缓存：与一级缓存其机制相同，不同在于其存储作用域为 Mapper(Namespace)，并且可自定义存储源，如 Ehcache。

　　① 默认是关闭的

　　② 是一个映射文件级的缓存，

　　③ 开启二级缓存 <cache/>

还是在 PersonMapper.xml 文件中添加如下代码

<!-- 开启二级缓存 -->
    <!-- 
        eviction="FIFO"          回收策略为先进先出
        flushInterval="60000"     自动刷新时间60s
        size="512"                最多缓存512个引用对象
        readOnly="true"            只读
    -->
    <cache 
        eviction="FIFO" 
        flushInterval="60000"
        size="1024"    
        readOnly="true"/>

打印结果如下

log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
[Person [id=2, email=cyy@qq.com, lastName=cyy]]
1
0
Classroom [id=1, room=JavaEE, teacher=Teacher [id=1, subject=Java], students=null]
Classroom [id=1, room=JavaEE, teacher=Teacher [id=1, subject=Java], students=null]
Classroom [id=1, room=JavaEE, teacher=Teacher [id=1, subject=Java], students=[Student [id=1, name=ITDragon], Student [id=2, name=Marry]]]
Classroom [id=1, room=JavaEE, teacher=Teacher [id=1, subject=Java], students=[Student [id=null, name=ITDragon], Student [id=null, name=Marry]]]
[Person [id=1, email=lxl@qq.com, lastName=null], Person [id=2, email=cyy@qq.com, lastName=null], Person [id=3, email=itdrgon@qq.com, lastName=null]]
Person [id=2, email=cyy@qq.com, lastName=null]
Person [id=2, email=cyy@qq.com, lastName=cyy]
Person [id=2, email=cyy@qq.com, lastName=cyy]
2
1

源码地址：https://github.com/ITDragonBl...

到这里，Mybatis 的入门知识就讲完了。如果大家觉得不错，可以关注我！后续还有很多不错的内容提供。

Mybatis3 快速入门