mybatis

发布日期: 2020-04-10

更新日期: 2020-04-18

文章字数: 14.2k

阅读时长: 70 分

阅读次数:

Mybatis的xml映射文件

本地所有code地址: https://gitee.com/caimengzhi/code_java/tree/master/mybatis/mybatis_sqlmapping

测试标题折叠

``` 4.0.0

<groupId>com.cmz</groupId>
<artifactId>mybatis_generator</artifactId>
<version>1.0-SNAPSHOT</version>


<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.19</version>
    </dependency>

    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.mybatis.caches</groupId>
        <artifactId>mybatis-ehcache</artifactId>
        <version>1.2.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>2.0.0-alpha1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-log4j12 -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>2.0.0-alpha1</version>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-core</artifactId>
        <version>1.4.0</version>
    </dependency>

</dependencies>

```

0. xml 映射文件

https://mybatis.org/mybatis-3/zh/getting-started.html

<?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.cmz.dao.EmpDao">
    <select id="selectEmpByEmpno" resultType="com.cmz.bean.Emp" databaseId="mysql"> /* 没有别名之前*/
    <!--<select id="selectEmpByEmpno" resultType="Emp">   /*添加别名之后，可以简写*/-->
        select * from emp where empno = #{empno}
    </select>

    <select id="selectEmpByEmpno" resultType="com.cmz.bean.Emp" databaseId="oracle">
        select * from emp where empno = #{empno}
    </select>


    <!--insert delete update分别用来标识不同类型的sql语句
    id: 用来标识跟dao接口中匹配的方法，必须与方法的名字一一对应。
    -->
    <insert id="save">
        insert into emp(empno,ename) values(#{empno},#{ename})
    </insert>

    <update id="update">
        update emp set sal=#{sal} where empno=#{empno}
    </update>

    <delete id="delete">
        delete from emp where empno=#{empno}
    </delete>
</mapper>

其中 id,flushCache,useGeneratedKeys,KeyProperty相对需要掌握

1. Insert, Update, Delete 元素的属性

属性	描述
`id`	在命名空间中唯一的标识符，可以被用来引用这条语句。
`parameterType`	将会传入这条语句的参数的类全限定名或别名。这个属性是可选的，因为 MyBatis 可以通过类型处理器（TypeHandler）推断出具体传入语句的参数，默认值为未设置（unset）。
`parameterMap`	用于引用外部 parameterMap 的属性，目前已被废弃。请使用行内参数映射和 parameterType 属性。
`flushCache`	将其设置为 true 后，只要语句被调用，都会导致本地缓存和二级缓存被清空，默认值：（对 insert、update 和 delete 语句）true。
`timeout`	这个设置是在抛出异常之前，驱动程序等待数据库返回请求结果的秒数。默认值为未设置（unset）（依赖数据库驱动）。
`statementType`	可选 STATEMENT，PREPARED 或 CALLABLE。这会让 MyBatis 分别使用 Statement，PreparedStatement 或 CallableStatement，默认值：PREPARED。
`useGeneratedKeys`	（仅适用于 insert 和 update）这会令 MyBatis 使用 JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的主键（比如：像 MySQL 和 SQL Server 这样的关系型数据库管理系统的自动递增字段），默认值：false。
`keyProperty`	（仅适用于 insert 和 update）指定能够唯一识别对象的属性，MyBatis 会使用 getGeneratedKeys 的返回值或 insert 语句的 selectKey 子元素设置它的值，默认值：未设置（`unset`）。如果生成列不止一个，可以用逗号分隔多个属性名称。
`keyColumn`	（仅适用于 insert 和 update）设置生成键值在表中的列名，在某些数据库（像 PostgreSQL）中，当主键列不是表中的第一列的时候，是必须设置的。如果生成列不止一个，可以用逗号分隔多个属性名称。
`databaseId`	如果配置了数据库厂商标识（databaseIdProvider），MyBatis 会加载所有不带 databaseId 或匹配当前 databaseId 的语句；如果带和不带的语句都有，则不带的会被忽略。

1.1 id

用来标识跟dao接口中匹配的方法，必须与方法的名字一一对应。

EmpDao.class

package com.cmz.dao;

import com.cmz.bean.Emp;

/**
 * @author summer
 * @create 2020-04-08 16:35
 */
public interface EmpDao {

    public Integer save(Emp emp);
    public Integer update(Emp emp);
    public Integer delete(Integer empno);
    public Emp selectEmpByEmpno(Integer empno);
}

xml中的id要和对应此接口的方法名字一样。

1.2 statementType

用来选择执行sql语句的方式。

statement: 最基本的jdbc操作，用来表示一个sql语句，不能防止sql注入
PREPARED: preareStatement: 采用预编译的方式，它能够防止sql注入,设计参数时候需要改对象来进行设置
CALLABLE: 调用存储过程

1.3 useGeneratedKeys

官方的说法是该参数的作用是：“允许JDBC支持自动生成主键，需要驱动兼容”，如何理解这句话的意思？

其本意是说：对于支持自动生成记录主键的数据库，如：MySQL，SQL Server，此时设置useGeneratedKeys参数值为true，在执行添加记录之后可以获取到数据库自动生成的主键ID。

当主键是自增的情况下，添加一条记录的同时，其主键是不能使用的，但是有时我们需要该主键，这时我们该如何处理呢？这时我们只需要在其对应xml中加入以下属性即可：

useGeneratedKeys=“true” keyProperty=“id”
useGeneratedKeys设置为 true 时
1. 表示如果插入的表id以自增列为主键，则允许 JDBC 支持自动生成主键
2. 并可将自动生成的主键id返回。
useGeneratedKeys参数只针对 insert 语句生效，默认为 false；

keyProperty：指定对应的主键属性，也就是mybatis获取到主键后，讲这个值封装给JavaBean的哪个属性。

1.3.1 未配置

我们先看一个例子。

<?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.cmz.dao.UserDao">
    <select id="selectUserById" resultType="com.cmz.bean.User">
        select * from user where id = #{id}
    </select>
    <!--<insert id="saveUser" useGeneratedKeys="true" keyProperty="id">-->
    <insert id="saveUser">
        insert into user(user_name) values(#{userName})
    </insert>
</mapper>

测试代码

    @Test
    public void test07(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setUserName("lisi");
        Integer count = mapper.saveUser(user);
        System.out.println(user);
        System.out.println("插入成功条数: "+ count);
        sqlSession.commit();
        sqlSession.close();
    }

运行结果

DEBUG [main] - ==>  Preparing: insert into user(user_name) values(?) 
DEBUG [main] - ==> Parameters: lisi(String)
DEBUG [main] - <==    Updates: 1
User{id=null, userName='lisi'}
插入成功条数: 1

但是发现id是null，也就是说id[主键]不能使用，此时我们就的需要配置那个参数了

1.3.2 配置

<?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.cmz.dao.UserDao">
    <select id="selectUserById" resultType="com.cmz.bean.User">
        select * from user where id = #{id}
    </select>

    <insert id="saveUser" useGeneratedKeys="true" keyProperty="id">
        insert into user(user_name) values(#{userName})
    </insert>
</mapper>

再次运行测试代码,查看输出

DEBUG [main] - ==>  Preparing: insert into user(user_name) values(?) 
DEBUG [main] - ==> Parameters: lisi(String)
DEBUG [main] - <==    Updates: 1
User{id=8, userName='lisi'}
插入成功条数: 1

你看id就能拿到了吧

当完成插入操作的时候，可以将自增生成的主键值返回到具体的对象。

keyProperty: 指定返回的主键要赋值到哪个属性中。

在编写sql语句的过程中，无论你是否配置了驼峰标识的识别settings，都需要在sql语句总写具体的属性名，不能写对象的名称

User.class

package com.cmz.bean;

/**
 * @author summer
 * @create 2020-04-08 17:42
 */
public class User {
    private Integer id;
    private String userName;

    public User() {
    }

    public User(Integer id, String userName) {
        this.id = id;
        this.userName = userName;
    }

    public Integer getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                '}';
    }
}

UserDao.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.cmz.dao.UserDao">
    <select id="selectUserById" resultType="com.cmz.bean.User">
        select * from user where id = #{id}
    </select>

    <insert id="saveUser" useGeneratedKeys="true" keyProperty="id">
        insert into user(user_name) values(#{userName})
    </insert>
</mapper>

user(user_name) 后面的要写数据中表的字段。

mysql> desc user;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

2. select元素属性

属性	描述
`id`	在命名空间中唯一的标识符，可以被用来引用这条语句。
`parameterType`	将会传入这条语句的参数的类全限定名或别名。这个属性是可选的，因为 MyBatis 可以通过类型处理器（TypeHandler）推断出具体传入语句的参数，默认值为未设置（unset）。
parameterMap	用于引用外部 parameterMap 的属性，目前已被废弃。请使用行内参数映射和 parameterType 属性。
`resultType`	期望从这条语句中返回结果的类全限定名或别名。注意，如果返回的是集合，那应该设置为集合包含的类型，而不是集合本身的类型。 resultType 和 resultMap 之间只能同时使用一个。
`resultMap`	对外部 resultMap 的命名引用。结果映射是 MyBatis 最强大的特性，如果你对其理解透彻，许多复杂的映射问题都能迎刃而解。 resultType 和 resultMap 之间只能同时使用一个。
`flushCache`	将其设置为 true 后，只要语句被调用，都会导致本地缓存和二级缓存被清空，默认值：false。
`useCache`	将其设置为 true 后，将会导致本条语句的结果被二级缓存缓存起来，默认值：对 select 元素为 true。
`timeout`	这个设置是在抛出异常之前，驱动程序等待数据库返回请求结果的秒数。默认值为未设置（unset）（依赖数据库驱动）。
`fetchSize`	这是一个给驱动的建议值，尝试让驱动程序每次批量返回的结果行数等于这个设置值。默认值为未设置（unset）（依赖驱动）。
`statementType`	可选 STATEMENT，PREPARED 或 CALLABLE。这会让 MyBatis 分别使用 Statement，PreparedStatement 或 CallableStatement，默认值：PREPARED。
`resultSetType`	FORWARD_ONLY，SCROLL_SENSITIVE, SCROLL_INSENSITIVE 或 DEFAULT（等价于 unset）中的一个，默认值为 unset （依赖数据库驱动）。
`databaseId`	如果配置了数据库厂商标识（databaseIdProvider），MyBatis 会加载所有不带 databaseId 或匹配当前 databaseId 的语句；如果带和不带的语句都有，则不带的会被忽略。
`resultOrdered`	这个设置仅针对嵌套结果 select 语句：如果为 true，将会假设包含了嵌套结果集或是分组，当返回一个主结果行时，就不会产生对前面结果集的引用。这就使得在获取嵌套结果集的时候不至于内存不够用。默认值：`false`。
`resultSets`	这个设置仅适用于多结果集的情况。它将列出语句执行后返回的结果集并赋予每个结果集一个名称，多个名称之间以逗号分隔。

查询语句是 MyBatis 中最常用的元素之一——光能把数据存到数据库中价值并不大，还要能重新取出来才有用，多数应用也都是查询比修改要频繁。 MyBatis 的基本原则之一是：在每个插入、更新或删除操作之间，通常会执行多个查询操作。因此，MyBatis 在查询和结果映射做了相当多的改进。

resultType和resultMap用的非常的多。需要重点掌握。

2.1 resultType/resultMap

resultType:表示返回的结果的类型,一般使用的并不多，此类型只能返回单一的对象，而我们在查询的时候特别是关联查询的时候，需要自定义结果集合，当返回的结果是一个集合的时候，并不需要resultmap，只需要使用resulttype指定集合中的元素类型即可
resultMap:当进行关联查询的时候，在返回结果的对象中还包含另一个对象的引用时，此时需要自定义结果集合，使用resultmap
二者只能选取其中一个

2.1.1 resultType

新加UserDao.class中加入如下

public List<Emp> selectAll();

package com.cmz.dao;

import com.cmz.bean.Emp;

import java.util.List;

/**
 * @author summer
 * @create 2020-04-08 16:35
 */
public interface EmpDao {

    public Integer save(Emp emp);
    public Integer update(Emp emp);
    public Integer delete(Integer empno);
    public Emp selectEmpByEmpno(Integer empno);
    public List<Emp> selectAll();java
}

xml修改

<select id="selectAll" resultType="Emp">
  select * from emp
</select>

测试

@Test
public void test08(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    List<Emp> list = mapper.selectAll();
    for (Emp emp : list) {
        System.out.println(emp);
    }
    sqlSession.commit();
    sqlSession.close();
}

运行结果

DEBUG [main] - ==>  Preparing: select * from emp 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 3333, 张三, null, null, null, null, null, null
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
TRACE [main] - <==        Row: 7499, ALLEN, SALESMAN, 7698.0, 1981-02-20, 1600.0, 300.0, 30
TRACE [main] - <==        Row: 7521, WARD, SALESMAN, 7698.0, 1981-02-22, 1250.0, 500.0, 30
TRACE [main] - <==        Row: 7566, JONES, MANAGER, 7839.0, 1981-04-02, 2975.0, null, 20
TRACE [main] - <==        Row: 7654, MARTIN, SALESMAN, 7698.0, 1981-09-28, 1250.0, 1400.0, 30
TRACE [main] - <==        Row: 7698, BLAKE, MANAGER, 7839.0, 1981-05-01, 2850.0, null, 30
TRACE [main] - <==        Row: 7782, CLARK, MANAGER, 7839.0, 1981-06-09, 2450.0, null, 10
TRACE [main] - <==        Row: 7788, SCOTT, ANALYST, 7566.0, 1987-07-13, 3000.0, null, 20
TRACE [main] - <==        Row: 7839, KING, PRESIDENT, null, 1981-11-17, 5000.0, null, 10
TRACE [main] - <==        Row: 7844, TURNER, SALESMAN, 7698.0, 1981-09-08, 1500.0, 0.0, 30
TRACE [main] - <==        Row: 7876, ADAMS, CLERK, 7788.0, 1987-07-13, 1100.0, null, 20
TRACE [main] - <==        Row: 7900, JAMES, CLERK, 7698.0, 1981-12-03, 950.0, null, 30
TRACE [main] - <==        Row: 7902, FORD, ANALYST, 7566.0, 1981-12-03, 3000.0, null, 20
TRACE [main] - <==        Row: 7934, MILLER, CLERK, 7782.0, 1982-01-23, 1300.0, null, 10
DEBUG [main] - <==      Total: 15
Emp{empno=3333, ename='张三', job='null', mgr=null, hiredate=null, sal=null, common=null, deptno=null}
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}
Emp{empno=7499, ename='ALLEN', job='SALESMAN', mgr=7698, hiredate=Fri Feb 20 08:00:00 CST 1981, sal=1600.0, common=null, deptno=30}
Emp{empno=7521, ename='WARD', job='SALESMAN', mgr=7698, hiredate=Sun Feb 22 08:00:00 CST 1981, sal=1250.0, common=null, deptno=30}
Emp{empno=7566, ename='JONES', job='MANAGER', mgr=7839, hiredate=Thu Apr 02 08:00:00 CST 1981, sal=2975.0, common=null, deptno=20}
Emp{empno=7654, ename='MARTIN', job='SALESMAN', mgr=7698, hiredate=Mon Sep 28 08:00:00 CST 1981, sal=1250.0, common=null, deptno=30}
Emp{empno=7698, ename='BLAKE', job='MANAGER', mgr=7839, hiredate=Fri May 01 08:00:00 CST 1981, sal=2850.0, common=null, deptno=30}
Emp{empno=7782, ename='CLARK', job='MANAGER', mgr=7839, hiredate=Tue Jun 09 08:00:00 CST 1981, sal=2450.0, common=null, deptno=10}
Emp{empno=7788, ename='SCOTT', job='ANALYST', mgr=7566, hiredate=Mon Jul 13 09:00:00 CDT 1987, sal=3000.0, common=null, deptno=20}
Emp{empno=7839, ename='KING', job='PRESIDENT', mgr=null, hiredate=Tue Nov 17 08:00:00 CST 1981, sal=5000.0, common=null, deptno=10}
Emp{empno=7844, ename='TURNER', job='SALESMAN', mgr=7698, hiredate=Tue Sep 08 08:00:00 CST 1981, sal=1500.0, common=null, deptno=30}
Emp{empno=7876, ename='ADAMS', job='CLERK', mgr=7788, hiredate=Mon Jul 13 09:00:00 CDT 1987, sal=1100.0, common=null, deptno=20}
Emp{empno=7900, ename='JAMES', job='CLERK', mgr=7698, hiredate=Thu Dec 03 08:00:00 CST 1981, sal=950.0, common=null, deptno=30}
Emp{empno=7902, ename='FORD', job='ANALYST', mgr=7566, hiredate=Thu Dec 03 08:00:00 CST 1981, sal=3000.0, common=null, deptno=20}
Emp{empno=7934, ename='MILLER', job='CLERK', mgr=7782, hiredate=Sat Jan 23 08:00:00 CST 1982, sal=1300.0, common=null, deptno=10}

可以看到查出了很多数据，放到了list中了，然后打印。

2.2 结果返回list集合

有时候我们查询的数据需要返回list。

EmpDao.class

public List<Emp> selectEmpByEnameLike(String ename);

EmpDao.xml

<!--resultType 如果返回的是一个集合，要写集合中的元素的类型-->
<select id="selectEmpByEnameLike" resultType="com.cmz.bean.Emp">
    select * from emp where ename like #{ename}
</select>

resultType 如果返回的是一个集合，要写集合中的元素的类型

MyBatis会吧返回的类型转换resultType配置的类型装入List集合

MyTest.class

/*通过名字模糊查询返回List*/
@Test
public void test14(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    List<Emp> emps = mapper.selectEmpByEnameLike("%M%");
    for (Emp emp1 : emps) {
        System.out.println(emp1);
    }
    sqlSession.commit();
    sqlSession.close();
}

代码是查询名字中带有M的员工信息。运行后的结果

DEBUG [main] - ==>  Preparing: select * from emp where ename like ? 
DEBUG [main] - ==> Parameters: %M%(String)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
TRACE [main] - <==        Row: 7654, MARTIN, SALESMAN, 7698.0, 1981-09-28, 1250.0, 1400.0, 30
TRACE [main] - <==        Row: 7876, ADAMS, CLERK, 7788.0, 1987-07-13, 1100.0, null, 20
TRACE [main] - <==        Row: 7900, JAMES, CLERK, 7698.0, 1981-12-03, 950.0, null, 30
TRACE [main] - <==        Row: 7934, MILLER, CLERK, 7782.0, 1982-01-23, 1300.0, null, 10
DEBUG [main] - <==      Total: 5
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}
Emp{empno=7654, ename='MARTIN', job='SALESMAN', mgr=7698, hiredate=Mon Sep 28 08:00:00 CST 1981, sal=1250.0, common=null, deptno=30}
Emp{empno=7876, ename='ADAMS', job='CLERK', mgr=7788, hiredate=Mon Jul 13 09:00:00 CDT 1987, sal=1100.0, common=null, deptno=20}
Emp{empno=7900, ename='JAMES', job='CLERK', mgr=7698, hiredate=Thu Dec 03 08:00:00 CST 1981, sal=950.0, common=null, deptno=30}
Emp{empno=7934, ename='MILLER', job='CLERK', mgr=7782, hiredate=Sat Jan 23 08:00:00 CST 1982, sal=1300.0, common=null, deptno=10}

结果返回List集合

2.3 结果返回Map集合

2.3.1 返回单条数据

返回一条记录的map，key就是列名字，值就是对应的值,类似
{ENAME=SMITH, HIREDATE=1980-12-17, EMPNO=7369, MGR=7902.0, JOB=CLERK, DEPTNO=20, SAL=800.0}

EmpDao.java

/*返回一条记录的map，key就是列名字，值就是对应的值*/
public Map<Object,Object> selectEmpByEmpnoReturnMap(Integer empno);

EmpDao.xml

<!--当返回值是map结构的时候，会把查询结构的字段作为key，结果作为value-->
<select id="selectEmpByEmpnoReturnMap" resultType="map">
  select * from emp where empno=#{empno}
</select>

MyTest.class

@Test
public void test12(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    Map<Object, Object> objectObjectMap = mapper.selectEmpByEmpnoReturnMap(7369);
    System.out.println(objectObjectMap);
    System.out.println("-----循环输出------");
    for(Map.Entry<Object,Object> entry:objectObjectMap.entrySet()){
        Object mapkey = entry.getKey();
        Object mapvalue = entry.getValue();
        System.out.println(mapkey+":"+mapvalue);
    }
    sqlSession.commit();
    sqlSession.close();
}

结果输出

DEBUG [main] - ==>  Preparing: select * from emp where empno=? 
DEBUG [main] - ==> Parameters: 7369(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - <==      Total: 1
{ENAME=SMITH, HIREDATE=1980-12-17, EMPNO=7369, MGR=7902.0, JOB=CLERK, DEPTNO=20, SAL=800.0}
-----循环输出------
ENAME:SMITH
HIREDATE:1980-12-17
EMPNO:7369
MGR:7902.0
JOB:CLERK
DEPTNO:20
SAL:800.0

Process finished with exit code 0

2.3.2 返回多条数据

当返回的结果是一个map的集合时候，同时map中包含多个对象，那么此时必须要在dao方法上添加MapKey注解，来标识哪一个属性值作为key

EmpDao.java

@MapKey("empno")
public Map<String,Emp> selectAll2();

必须通过注解@MapKey(“empno”)来表明哪个是key。empno是表示map的key，否则报错

org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 15

EmpDao.xml

<!--当返回的结果是一个map的集合时候，同时map中包含多个对象，那么此时必须要在dao方法上添加MapKey注解，来标识哪一个属性值作为key-->
<select id="selectAll2" resultType="Emp">
  select * from emp
</select>

MyTest.class

/*自定义map,返回多个数据，模糊查询*/
@Test
public void test15(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    Map<String, Emp> stringEmpMap = mapper.selectEmpByEnameLikeReturnMap("%M%");
    System.out.println(stringEmpMap);
    System.out.println("-------------");
    Iterator<Map.Entry<String, Emp>> entries = stringEmpMap.entrySet().iterator();
    while (entries.hasNext()){
        Map.Entry<String, Emp> entry = entries.next();
        String key = entry.getKey();
        Emp value = entry.getValue();
        System.out.println(key+":"+value);
    }
    sqlSession.commit();
    sqlSession.close();
}

输出结果

DEBUG [main] - ==>  Preparing: select * from emp where ename like ? 
DEBUG [main] - ==> Parameters: %M%(String)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
TRACE [main] - <==        Row: 7654, MARTIN, SALESMAN, 7698.0, 1981-09-28, 1250.0, 1400.0, 30
TRACE [main] - <==        Row: 7876, ADAMS, CLERK, 7788.0, 1987-07-13, 1100.0, null, 20
TRACE [main] - <==        Row: 7900, JAMES, CLERK, 7698.0, 1981-12-03, 950.0, null, 30
TRACE [main] - <==        Row: 7934, MILLER, CLERK, 7782.0, 1982-01-23, 1300.0, null, 10
DEBUG [main] - <==      Total: 5
{MARTIN=Emp{empno=7654, ename='MARTIN', job='SALESMAN', mgr=7698, hiredate=Mon Sep 28 08:00:00 CST 1981, sal=1250.0, common=null, deptno=30}, MILLER=Emp{empno=7934, ename='MILLER', job='CLERK', mgr=7782, hiredate=Sat Jan 23 08:00:00 CST 1982, sal=1300.0, common=null, deptno=10}, JAMES=Emp{empno=7900, ename='JAMES', job='CLERK', mgr=7698, hiredate=Thu Dec 03 08:00:00 CST 1981, sal=950.0, common=null, deptno=30}, SMITH=Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}, ADAMS=Emp{empno=7876, ename='ADAMS', job='CLERK', mgr=7788, hiredate=Mon Jul 13 09:00:00 CDT 1987, sal=1100.0, common=null, deptno=20}}
-------------
MARTIN:Emp{empno=7654, ename='MARTIN', job='SALESMAN', mgr=7698, hiredate=Mon Sep 28 08:00:00 CST 1981, sal=1250.0, common=null, deptno=30}
MILLER:Emp{empno=7934, ename='MILLER', job='CLERK', mgr=7782, hiredate=Sat Jan 23 08:00:00 CST 1982, sal=1300.0, common=null, deptno=10}
JAMES:Emp{empno=7900, ename='JAMES', job='CLERK', mgr=7698, hiredate=Thu Dec 03 08:00:00 CST 1981, sal=950.0, common=null, deptno=30}
SMITH:Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}
ADAMS:Emp{empno=7876, ename='ADAMS', job='CLERK', mgr=7788, hiredate=Mon Jul 13 09:00:00 CDT 1987, sal=1100.0, common=null, deptno=20}

2.4 ResultMap

2.4.1 映射级联属性

全局的settings设置
- autoMappingBehavior默认是PARTIAL，开启自动映射的功能，唯一的要求就是列名和javaBean属性名一致。
- 如果autoMappingBehavior设置为null会取消自动映射
- 数据库字段命名规范，POJO属性符合驼峰命名法，如A_COLUMN->aColumn，我们可以开启自动驼峰命名规范则映射功能，mapUnderscoreToCamelCase=true
自定义的resultMap，实现高级结果集映射。

我们数据库字段和类属性不一致的时候，我们可以有两种方式

mysql> desc dog;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| dname   | varchar(255) | YES  |     | NULL    |                |
| dage    | int(255)     | YES  |     | NULL    |                |
| dgender | varchar(255) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from dog;
+----+-------+------+---------+
| id | dname | dage | dgender |
+----+-------+------+---------+
|  1 | 大黄  |    1 | 公      |
|  2 | 二黄  |    2 | 母      |
|  3 | 三黄  |    3 | 公      |
+----+-------+------+---------+
3 rows in set (0.01 sec)

Dog.class

package com.cmz.bean;

public class Dog {
    private Integer id;
    private String name;
    private Integer age;
    private String gender;

    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;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Dog{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", gender='" + gender + '\'' +
                '}';
    }
}

对比

mysql字段	java类[Dog.class]
id	id
dname	name
dage	age
dgender	gender

两者不对应，sql会对应不上。解决该问题，有以下两个方法。

sql的时候alias
或者直接使用resultmap设置他们之间的关系

DogDao.java

package com.cmz.dao;
import com.cmz.bean.Dog;
public interface DogDao {
    public Dog selectDogById(Integer id);
    public Dog selectDogById1(Integer id);
    public Dog selectDogById2(Integer id);
}

DogDao.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">
<!--在使用mybatis的时候，有些情况下需要我们封装结果集，一般情况下mybatis会帮我们自动封装（字段名跟属性值必须一一对应），
    但是如果字段的值和类中的值,不匹配的时候，怎么处理？
    1、可以在sql语句中添加别名字段，来保证赋值成功，但是太麻烦了，而且不可重用
    2、resultMap
-->

<mapper namespace="com.cmz.dao.DogDao">
    <!--自定义结果集
    id:表示当前结果集的唯一标识
    type:表示当前结果集的类型
    -->
    <resultMap id="myDog" type="com.cmz.bean.Dog">
        <!--
        id:表示指定对应的主键,底层会优化，主键也可以使用result
        property:实体类中对应的属性值
        column：表中字段的名称
        -->
        <id property="id" column="id"></id>
        <!--除主键外的其他字段映射-->
        <result property="name" column="dname"></result>
        <result property="age" column="dage"></result>
        <result property="gender" column="dgender"></result>
    </resultMap>

    <!--resultMap 自定义结果集映射规则-->
    <select id="selectDogById" resultMap="myDog">
        select * from dog where id = #{id}
    </select>

    <!--直接是不显示的-->
    <select id="selectDogById1" resultType="com.cmz.bean.Dog">
        select * from dog where id = #{id}
    </select>

    <!--通过别名显示-->
    <select id="selectDogById2" resultType="com.cmz.bean.Dog">
        select id id,dname name,dage age,dgender gender from dog where id = #{id}
    </select>
</mapper>

MyTest.class

import com.cmz.bean.Dog;
import com.cmz.dao.DogDao;
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 org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;

public class MyTest2 {
    SqlSessionFactory sqlSessionFactory =  null;
    @Before
    public void init(){
        String resource = "mybatis_config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void test() throws IOException {
        //获取与数据库相关的会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取对应的映射接口对象
        DogDao mapper = sqlSession.getMapper(DogDao.class);
        //执行具体的sql语句
        Dog dog = mapper.selectDogById(1);
        System.out.println(dog);
        //关闭会话
        sqlSession.close();
    }

    @Test
    public void test01() throws IOException {

        //获取与数据库相关的会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取对应的映射接口对象
        DogDao mapper = sqlSession.getMapper(DogDao.class);
        //执行具体的sql语句
        Dog dog = mapper.selectDogById1(1);
        System.out.println(dog);
        //关闭会话
        sqlSession.close();
    }
    @Test
    public void test02() throws IOException {

        //获取与数据库相关的会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取对应的映射接口对象
        DogDao mapper = sqlSession.getMapper(DogDao.class);
        //执行具体的sql语句
        Dog dog = mapper.selectDogById2(1);
        System.out.println(dog);
        //关闭会话
        sqlSession.close();
    }
}

直接执行，不显示结果

Dog dog = mapper.selectDogById1(1);

输出

DEBUG [main] - ==>  Preparing: select * from dog where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, dname, dage, dgender
TRACE [main] - <==        Row: 1, 大黄, 1, 公
DEBUG [main] - <==      Total: 1
Dog{id=1, name='null', age=null, gender='null'}

通过别名，来显示结果

Dog dog = mapper.selectDogById2(1);

输出

DEBUG [main] - ==>  Preparing: select id id,dname name,dage age,dgender gender from dog where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, name, age, gender
TRACE [main] - <==        Row: 1, 大黄, 1, 公
DEBUG [main] - <==      Total: 1
Dog{id=1, name='大黄', age=1, gender='公'}

通过resultMap自定义

Dog dog = mapper.selectDogById(1);

输出

DEBUG [main] - ==>  Preparing: select * from dog where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, dname, dage, dgender
TRACE [main] - <==        Row: 1, 大黄, 1, 公
DEBUG [main] - <==      Total: 1
Dog{id=1, name='大黄', age=1, gender='公'}

自定义resultMap

此时就是自己定制了规则，和驼峰命名啥的也没有啥子鸟关系了。自己定义了类属性和数据库中的字段的对应关系。

2.4.2 映射关联查询

Dept.class

package com.cmz.bean;

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

public class Dept implements Serializable {
    private Integer deptno;
    private String dname;
    private String loc;

    private List<Emp> emps;

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }


    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptno=" + deptno +
                ", dname='" + dname + '\'' +
                ", loc='" + loc + '\'' +
                ", emps=" + emps +
                '}';
    }
}

Emp.class

package com.cmz.bean;

import java.util.Date;

/**
 * @author summer
 * @create 2020-04-08 16:33
 */
public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double common;
    private Dept dept;

    public Emp() {
    }

    public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Double common, Dept dept) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.common = common;
        this.dept = dept;
    }

    public Integer getEmpno() {
        return empno;
    }

    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getMgr() {
        return mgr;
    }

    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public Double getSal() {
        return sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Double getCommon() {
        return common;
    }

    public void setCommon(Double common) {
        this.common = common;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", common=" + common +
                ", dept=" + dept +
                '}';
    }
}

DeptDao.class

package com.cmz.dao;
import com.cmz.bean.Dept;
public interface DeptDao {
    public Dept selectDeptByDeptno(Integer deptno);
    public Dept selectDeptByStep(Integer deptno);
    public Dept selectDeptByStemp2(Integer deptno);
}

EmpDao.class

package com.cmz.dao;
import com.cmz.bean.Emp;
public interface EmpDao {
    public Emp seletEmpByEmpno(Integer empno);
    public Emp selectEmpByStep(Integer empno);
}

DeptDao.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.cmz.dao.DeptDao">

    <resultMap id="myDept" type="com.cmz.bean.Dept">
        <id column="deptno" property="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <collection property="emps" ofType="com.cmz.bean.Emp">
            <id column="empno" property="empno"></id>
            <result column="ename" property="ename"></result>
            <result column="job" property="job"></result>
            <result column="mgr" property="mgr"></result>
            <result column="hiredate" property="hiredate"></result>
            <result column="sal" property="sal"></result>
            <result column="comm" property="comm"></result>
        </collection>
    </resultMap>
    <select id="selectDeptByDeptno" resultMap="myDept">
       select * from dept left join emp on dept.deptno = emp.deptno where dept.deptno = #{deptno}
    </select>

    <select id="selectDeptByStep" resultType="com.cmz.bean.Dept">
        select * from dept where deptno = #{deptno}
    </select>

    <select id="selectDeptByStemp2" resultMap="deptEmp">
        select * from dept where deptno = #{deptno}
    </select>
    <resultMap id="deptEmp" type="com.cmz.bean.Dept">
        <id column="deptno" property="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <collection property="emps" ofType="com.cmz.bean.Emp" select="com.cmz.dao.EmpDao.selectEmpByStep2" column="deptno" fetchType="lazy">

        </collection>
    </resultMap>
</mapper>

EmpDao.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.cmz.dao.EmpDao">

    <!--将每一个属性值都映射成对象中的数据，如果有实体类对象，就写成对象.属性的方式-->
    <!--<resultMap id="myEmp" type="com.cmz.bean.Emp">
        <id column="empno" property="empno"></id>
        <id column="ename" property="ename"></id>
        <id column="mgr" property="mgr"></id>
        <id column="hiredate" property="hiredate"></id>
        <id column="sal" property="sal"></id>
        <id column="comm" property="comm"></id>
        <id column="deptno" property="dept.deptno"></id>
        <id column="dname" property="dept.dname"></id>
        <id column="loc" property="dept.loc"></id>
    </resultMap>
    <select id="seletEmpByEmpno" resultMap="myEmp">
        select * from emp left join dept on emp.deptno=dept.deptno where empno = #{empno}
    </select>-->

    <!--个人推荐第二种方式-->
    <resultMap id="myEmp" type="com.cmz.bean.Emp">
        <id column="empno" property="empno"></id>
        <id column="ename" property="ename"></id>
        <id column="mgr" property="mgr"></id>
        <id column="hiredate" property="hiredate"></id>
        <id column="sal" property="sal"></id>
        <id column="comm" property="comm"></id>
        <association property="dept" javaType="com.cmz.bean.Dept">
            <id property="deptno" column="deptno"></id>
            <result property="dname" column="dname"></result>
            <result property="loc" column="loc"></result>
        </association>
    </resultMap>
    <select id="seletEmpByEmpno" resultMap="myEmp">
        select * from emp left join dept on emp.deptno=dept.deptno where empno = #{empno}
    </select>

    <select id="selectEmpByStep" resultMap="empDept">
        select * from emp where empno = #{empno}
    </select>

    <resultMap id="empDept" type="com.cmz.bean.Emp">
        <id column="empno" property="empno"></id>
        <id column="ename" property="ename"></id>
        <id column="mgr" property="mgr"></id>
        <id column="hiredate" property="hiredate"></id>
        <id column="sal" property="sal"></id>
        <id column="comm" property="comm"></id>
        <association property="dept" select="com.cmz.dao.DeptDao.selectDeptByStep" column="deptno"></association>
    </resultMap>
</mapper>

查询员工信息，加上部门[外链]

方式1

将每一个属性值都映射成对象中的数据，如果有实体类对象，就写成对象.属性的方式

<!--将每一个属性值都映射成对象中的数据，如果有实体类对象，就写成对象.属性的方式-->
    <resultMap id="myEmp1" type="com.cmz.bean.Emp">
        <id column="empno" property="empno"></id>
        <id column="ename" property="ename"></id>
        <id column="mgr" property="mgr"></id>
        <id column="hiredate" property="hiredate"></id>
        <id column="sal" property="sal"></id>
        <id column="comm" property="comm"></id>
        <id column="deptno" property="dept.deptno"></id>
        <id column="dname" property="dept.dname"></id>
        <id column="loc" property="dept.loc"></id>
    </resultMap>
    <select id="seletEmpByEmpno1" resultMap="myEmp1">
        select * from emp left join dept on emp.deptno=dept.deptno where empno = #{empno}
    </select>

方式1

方式2

@Test
public void test01() throws IOException {
    //获取与数据库相关的会话
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //获取对应的映射接口对象
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    Emp emp = mapper.seletEmpByEmpno(7369);
    System.out.println(emp);
    //关闭会话
    sqlSession.close();
}

EmpDao.xml

<!--个人推荐第二种方式-->
<resultMap id="myEmp" type="com.cmz.bean.Emp">
    <id column="empno" property="empno"></id>
    <id column="ename" property="ename"></id>
    <id column="mgr" property="mgr"></id>
    <id column="hiredate" property="hiredate"></id>
    <id column="sal" property="sal"></id>
    <id column="comm" property="comm"></id>
    <!--associtaion 可以指定联合的javBean对象
    property="dept" 指定哪个属性是联合的对象
    javaType: 指定这个属性对象的类型
    -->
    <association property="dept" javaType="com.cmz.bean.Dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
    </association>
</resultMap>
<select id="seletEmpByEmpno" resultMap="myEmp">
    select * from emp left join dept on emp.deptno=dept.deptno where empno = #{empno}
</select>

方式2

输出

DEBUG [main] - ==>  Preparing: select * from emp left join dept on emp.deptno=dept.deptno where empno = ? 
DEBUG [main] - ==> Parameters: 7369(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DEPTNO, DNAME, LOC
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20, 20, RESEARCH, DALLAS
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='null', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, dept=Dept{deptno=20, dname='RESEARCH', loc='DALLAS', emps=null}}

mysql> select emp.*,dept.* from emp left join dept on emp.deptno=dept.deptno where empno = 7369;
+-------+-------+-------+------+------------+------+------+--------+--------+----------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | DEPTNO | DNAME    | LOC    |
+-------+-------+-------+------+------------+------+------+--------+--------+----------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 |  800 | NULL |     20 |     20 | RESEARCH | DALLAS |
+-------+-------+-------+------+------------+------+------+--------+--------+----------+--------+
1 row in set (0.00 sec)

mysql> select * from emp where empno = 7369;
+-------+-------+-------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+-------+-------+------+------------+------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 |  800 | NULL |     20 |
+-------+-------+-------+------+------------+------+------+--------+
1 row in set (0.00 sec)

个人推荐此种方式

2.4.3 association 分步查询

也就是sql分开查询。我们查询员工信息，顺带查询员工的部门

public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double common;
    private Dept dept;

也就是查询emp的信息。连同他的dept也一起查询。

`EmpDao.java

public Emp selectEmpByStep(Integer empno);

EmpDao.xml

<select id="selectEmpByStep" resultMap="empDept">
    select * from emp where empno = #{empno}
</select>
    <!--
    1. 先根据员工的id，查出员工信息
    2. 然后根据员工信息中的deptno的值去部门表查询出部门信息
    -->
<resultMap id="empDept" type="com.cmz.bean.Emp">
    <id column="empno" property="empno"></id>
    <id column="ename" property="ename"></id>
    <id column="mgr" property="mgr"></id>
    <id column="hiredate" property="hiredate"></id>
    <id column="sal" property="sal"></id>
    <id column="comm" property="comm"></id>

    <!--ssociation定义关联对象的封装原装
        select: 表明当前属性是调用select指定的方法查出的结果
        column: 指定哪一列的值传递给这个方法
        流程： 使用select指定的方法(传入column指定的这列参数的值)查出对象，并封装给property[也就是dept]指定的属性
    -->
    <association property="dept" select="com.cmz.dao.DeptDao.selectDeptByStep" column="deptno"></association>
</resultMap>

MyTest.class

@Test
public void test03() throws IOException {
    //获取与数据库相关的会话
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //获取对应的映射接口对象
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    Emp emp = mapper.selectEmpByStep(7369);
    System.out.println(emp);
    //关闭会话
    sqlSession.close();
}

输出,分开两步查询了

DEBUG [main] - ==>  Preparing: select * from emp where empno = ? 
DEBUG [main] - ==> Parameters: 7369(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - ====>  Preparing: select * from dept where deptno = ? 
DEBUG [main] - ====> Parameters: 20(Integer)
TRACE [main] - <====    Columns: DEPTNO, DNAME, LOC
TRACE [main] - <====        Row: 20, RESEARCH, DALLAS
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, dept=Dept{deptno=20, dname='RESEARCH', loc='DALLAS', emps=null}}

分布查询

关联查询

先根据员工的id，查出员工信息

然后根据员工信息中的deptno的值去部门表查询出部门信息

流程：使用select指定的方法(传入column指定的这列参数的值)查出对象，并封装给property[也就是dept]指定的属性

2.4.4 association 延时加载

所谓延时加载。

Emp中包含Dept，我们每次查询Emp对象的时候，都将一起查询出来。
将部门信息在我们使用的时候再去查询，在分步查询基础上加两个配置即可完成，延时加载

mybatis_config.xml

<!--显示的指定每个我们需要更改的配置的值，即便他是默认的，防止版本更新代来的问题-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

当我们只打印员工信息的时候，sql就执行一条

@Test
public void test03() throws IOException {
    //获取与数据库相关的会话
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //获取对应的映射接口对象
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    Emp emp = mapper.selectEmpByStep(7369);
    System.out.println(emp.getEname()); //不打印部门，就不会去调用第二条sql
    //关闭会话
    sqlSession.close();
}

输出

DEBUG [main] - ==>  Preparing: select * from emp where empno = ? 
DEBUG [main] - ==> Parameters: 7369(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - ====>  Preparing: select * from dept where deptno = ? 
DEBUG [main] - ====> Parameters: 20(Integer)
TRACE [main] - <====    Columns: DEPTNO, DNAME, LOC
TRACE [main] - <====        Row: 20, RESEARCH, DALLAS
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 1
SMITH

若是打印员工部门的时候。就再执行一条了。也就是两条

@Test
public void test03() throws IOException {
    //获取与数据库相关的会话
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //获取对应的映射接口对象
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    Emp emp = mapper.selectEmpByStep(7369);
    System.out.println(emp);
    //关闭会话
    sqlSession.close();
}

输出

DEBUG [main] - ==>  Preparing: select * from emp where empno = ? 
DEBUG [main] - ==> Parameters: 7369(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select * from dept where deptno = ? 
DEBUG [main] - ==> Parameters: 20(Integer)
TRACE [main] - <==    Columns: DEPTNO, DNAME, LOC
TRACE [main] - <==        Row: 20, RESEARCH, DALLAS
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, dept=Dept{deptno=20, dname='RESEARCH', loc='DALLAS', emps=null}}

懒加载

开启懒加载就是在分步查询的时候区别，后期数据是否使用到，使用到就执行sql，不使用到数据就不执行sql，前提是在mybatis_config.xml中配置两个字段lazyLoadingEnabled和aggressiveLazyLoading

不开启懒加载的话，不管啥时候，sql都执行

2.4.5 collection

collection嵌套结果集的方法，定义关联的结合类型元素的封装规则

我从部门表中查询出所有员工信息。

public class Dept implements Serializable {
    private Integer deptno;
    private String dname;
    private String loc;
    private List<Emp> emps;

查询出某个部门下的所有员工信息。

DeptDao.xml

<resultMap id="myDept" type="com.cmz.bean.Dept">
    <id column="deptno" property="deptno"></id>
    <result property="dname" column="dname"></result>
    <result property="loc" column="loc"></result>
    <!--
        collection: 定义关联集合类型的属性的封装规则
        ofType: 指定集合里面元素的类型
    -->
    <collection property="emps" ofType="com.cmz.bean.Emp">
        <id column="empno" property="empno"></id>
        <result column="ename" property="ename"></result>
        <result column="job" property="job"></result>
        <result column="mgr" property="mgr"></result>
        <result column="hiredate" property="hiredate"></result>
        <result column="sal" property="sal"></result>
        <result column="comm" property="comm"></result>
    </collection>
</resultMap>

<select id="selectDeptByDeptno" resultMap="myDept">
   select * from dept left join emp on dept.deptno = emp.deptno where dept.deptno = #{deptno}
</select>

MyTest.class

/*collection*/
@Test
public void test02() throws IOException {
    //获取与数据库相关的会话
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //获取对应的映射接口对象
    DeptDao mapper = sqlSession.getMapper(DeptDao.class);
    Dept dept = mapper.selectDeptByDeptno(10);
    System.out.println(dept);
    //关闭会话
    sqlSession.close();
}

collection

输出

DEBUG [main] - ==>  Preparing: select * from dept left join emp on dept.deptno = emp.deptno where dept.deptno = ? 
DEBUG [main] - ==> Parameters: 10(Integer)
TRACE [main] - <==    Columns: DEPTNO, DNAME, LOC, EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 10, ACCOUNTING, NEW YORK, 7782, CLARK, MANAGER, 7839.0, 1981-06-09, 2450.0, null, 10
TRACE [main] - <==        Row: 10, ACCOUNTING, NEW YORK, 7839, KING, PRESIDENT, null, 1981-11-17, 5000.0, null, 10
TRACE [main] - <==        Row: 10, ACCOUNTING, NEW YORK, 7934, MILLER, CLERK, 7782.0, 1982-01-23, 1300.0, null, 10
DEBUG [main] - <==      Total: 3
Dept{deptno=10, dname='ACCOUNTING', loc='NEW YORK', emps=[Emp{empno=7782, ename='CLARK', job='MANAGER', mgr=7839, hiredate=Tue Jun 09 08:00:00 CST 1981, sal=2450.0, common=null, dept=null}, Emp{empno=7839, ename='KING', job='PRESIDENT', mgr=null, hiredate=Tue Nov 17 08:00:00 CST 1981, sal=5000.0, common=null, dept=null}, Emp{empno=7934, ename='MILLER', job='CLERK', mgr=7782, hiredate=Sat Jan 23 08:00:00 CST 1982, sal=1300.0, common=null, dept=null}]}

emps 查出所有员工信息

2.4.6 collection分步查询

DeptDao.xml

<select id="selectDeptByStep2" resultMap="deptEmp">
    select * from dept where deptno = #{deptno}
</select>
<resultMap id="deptEmp" type="com.cmz.bean.Dept">
    <id column="deptno" property="deptno"></id>
    <result property="dname" column="dname"></result>
    <result property="loc" column="loc"></result>
    <!--
    collection: 定义关联集合类型的属性的封装规则
    ofType: 指定集合里面元素的类型
    -->
    <!--<collection property="emps" ofType="com.cmz.bean.Emp" select="com.cmz.dao.EmpDao.selectEmpByStep2" column="deptno" fetchType="lazy"></collection>-->
    <collection property="emps" ofType="com.cmz.bean.Emp" select="com.cmz.dao.EmpDao.selectEmpByStep2" column="deptno"></collection>
</resultMap>

EmpDao.xml

<select id="selectEmpByStep2" resultType="com.cmz.bean.Emp">
    select * from emp where deptno = #{deptno}
</select>

MyTest.class

/*collection 分布式查询*/
@Test
public void test05() throws IOException {
    //获取与数据库相关的会话
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //获取对应的映射接口对象
    DeptDao mapper = sqlSession.getMapper(DeptDao.class);
    Dept dept = mapper.selectDeptByStep2(10);
    System.out.println(dept);
    //关闭会话
    sqlSession.close();
}

输出结果

DEBUG [main] - ==>  Preparing: select * from dept where deptno = ? 
DEBUG [main] - ==> Parameters: 10(Integer)
TRACE [main] - <==    Columns: DEPTNO, DNAME, LOC
TRACE [main] - <==        Row: 10, ACCOUNTING, NEW YORK
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select * from emp where deptno = ? 
DEBUG [main] - ==> Parameters: 10(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7782, CLARK, MANAGER, 7839.0, 1981-06-09, 2450.0, null, 10
TRACE [main] - <==        Row: 7839, KING, PRESIDENT, null, 1981-11-17, 5000.0, null, 10
TRACE [main] - <==        Row: 7934, MILLER, CLERK, 7782.0, 1982-01-23, 1300.0, null, 10
DEBUG [main] - <==      Total: 3
Dept{deptno=10, dname='ACCOUNTING', loc='NEW YORK', emps=[Emp{empno=7782, ename='CLARK', job='MANAGER', mgr=7839, hiredate=Tue Jun 09 08:00:00 CST 1981, sal=2450.0, common=null, dept=null}, Emp{empno=7839, ename='KING', job='PRESIDENT', mgr=null, hiredate=Tue Nov 17 08:00:00 CST 1981, sal=5000.0, common=null, dept=null}, Emp{empno=7934, ename='MILLER', job='CLERK', mgr=7782, hiredate=Sat Jan 23 08:00:00 CST 1982, sal=1300.0, common=null, dept=null}]}

通过部门查询某个部门下的所有员工

2.4.7 collection懒加载

和associate懒加载一样。

懒加载

2.4.8 fetchType

collection: 定义关联集合类型的属性的封装规则
ofType: 指定集合里面元素的类型
fetchType:
    lazy: 延时加载
    eager: 立即加载，即便开启了全局的延时加载，这样此处配置了立即加载还是会立即加载的

2.4.9 多参数传递

我们都是通过column配置将参数传递给下去。但是都是传递一个参数，我们如何传递多个参数呢。

多列的值传递过去
将多列的值封装map传递
column=“{key1=column1,key2=column2}”

例如

<resultMap id="deptEmp" type="com.cmz.bean.Dept">
        <id column="deptno" property="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <!--
        collection: 定义关联集合类型的属性的封装规则
        ofType: 指定集合里面元素的类型
        fetchType:
            lazy: 延时加载
            eager: 立即加载
        -->
        <collection property="emps" ofType="com.cmz.bean.Emp" select="com.cmz.dao.EmpDao.selectEmpByStep2" column="{deptno=deptno}" fetchType="eager"></collection>
    </resultMap>

column=”{deptno=deptno}” ,效果一样。

2.5 `#{}`,`${}`

<!--参数的获取值的方式：
每次在向sql语句中设置结果值的时候，可以使用#{}，还可以使用${}这样的方式，那么哪种比较好？
    #{}：select * from emp where empno = ?
    ${}: select * from emp where empno = 7369
    通过sql语句可以得出结论：
        #{}的处理方式是使用了参数预编译的方式，不会引起sql注入的问题
        ${}的处理方式是直接拼接sql语句，得到对应的sql语句，会有sql注入的危险
        因此，我们推荐大家使用#{}的方式
        但是要注意，${}也是有自己的使用场景的？
            当需要传入动态的表名，列名的时候就需要使用${},就是最直接的拼接字符串的行为

比如分表，排序，，，按照年份分表拆分

select * from ${year}_salary where xxx;
-->

2.6 参数获取

当查询语句中包含多个参数的时候，我们应该如何获取需要的参数

如果是单个参数，

基本数据类型：那么可以使用#{}随便获取
引用数据类型:使用#{}获取值的是必须要使用对象的属性名

基本数据类型

基本数据类型：那么可以使用#{参数值}随便获取，参数值，可以随便写，都可以获取到结果。

Emp中的empno类型改为基本数据类型int，Emp.class

public class Emp {
//    private Integer empno;
    private int empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double common;
    private Integer deptno;

此时EmpDao.xml中参数可以随便写

<select id="selectEmpByEmpno" resultType="com.cmz.bean.Emp" databaseId="mysql">
    select * from emp where empno = #{empno}
</select>

正常我们写empno = #{empno},但是#{empno}中的empno随便是写啥都可以。

测试

@Test
public void test01() throws IOException {
    /*String resource = "mybatis_config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);*/

    //获取与数据库相关的会话
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //获取对应的映射接口对象
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    //执行对应的sql
    Emp emp = mapper.selectEmpByEmpno(7369);
    System.out.println(emp);
    sqlSession.close();
}

输出

DEBUG [main] - ==>  Preparing: select * from emp where empno = ? 
DEBUG [main] - ==> Parameters: 7369(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}

改为

<select id="selectEmpByEmpno" resultType="com.cmz.bean.Emp">
    select * from emp where empno = #{xxxx}
</select>

再次运行

DEBUG [main] - ==>  Preparing: select * from emp where empno = ? 
DEBUG [main] - ==> Parameters: 7369(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}

结果还是一样输出。

引用数据类型

<select id="selectEmpByEmpnoAndSal" resultType="com.cmz.bean.Emp">
    select * from emp where empno = #{empno} and sal > #{sal}
</select>

EmpDao.class

public interface EmpDao {

    public Integer save(Emp emp);
    public Integer update(Emp emp);
    public Integer delete(Integer empno);
    public Emp selectEmpByEmpno(Integer empno);
    public List<Emp> selectAll();
    public List<Emp> selectEmpByEmpnoAndSal(Emp emp);
}

测试

@Test
public void test09(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    Emp emp = new Emp();
    emp.setEmpno(7369);
    emp.setSal(666.6);
    List<Emp> list = mapper.selectEmpByEmpnoAndSal(emp);
    for (Emp emp1 : list) {
        System.out.println(emp1);
    }
    sqlSession.commit();
    sqlSession.close();
}

运行结果

DEBUG [main] - ==>  Preparing: select * from emp where empno = ? and sal >? 
DEBUG [main] - ==> Parameters: 7369(Integer), 666.6(Double)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}

此时是能正常获取到的

<select id="selectEmpByEmpnoAndSal" resultType="com.cmz.bean.Emp">
    select * from emp where empno = #{empno} and sal > #{aaa}
</select>

再次运行

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'aaa' in 'class com.cmz.bean.Emp'
### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'aaa' in 'class com.cmz.bean.Emp'

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
    at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:144)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
    at com.sun.proxy.$Proxy9.selectEmpByEmpnoAndSal(Unknown Source)
    at MyTest.test09(MyTest.java:153)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
    at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'aaa' in 'class com.cmz.bean.Emp'
    at org.apache.ibatis.reflection.Reflector.getGetInvoker(Reflector.java:373)
    at org.apache.ibatis.reflection.MetaClass.getGetInvoker(MetaClass.java:163)
    at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:162)
    at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:49)
    at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:122)
    at org.apache.ibatis.executor.BaseExecutor.createCacheKey(BaseExecutor.java:219)
    at org.apache.ibatis.executor.CachingExecutor.createCacheKey(CachingExecutor.java:146)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:82)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
    ... 33 more

报错了，此时因为是引用类型所以参数不能服了吧瞎写。

如果是多个参数：

我们在获取参数值的时候，就不能简单的通过#{}来获取了,只能通过arg0,arg1,param1,param2...这样的方式来获取参数的值
原因在于，mybatis在传入多个参数的时候，会讲这些参数的结果封装到map结构中，在map中key值就是(arg0,arg1,...)
(param1,param2...),这种方式非常不友好，没有办法根据属性名来获取具体的参数值
如果想要使用参数的话，可以进行如下的设置：
public List<Emp> selectEmpByEmpnoAndSal2(@Param("empno") Integer empno, @Param("sal") Double sal);
    这样的方式其实是根据@Param来进行参数的获取

EmpDao.xml
<select id="selectEmpByEmpnoAndSal2" resultType="com.cmz.bean.Emp">
    select * from emp where empno = #{empno} and sal > #{sal}
</select>

EmpDao.class
public List<Emp> selectEmpByEmpnoAndSal2(Integer empno,Double sal);

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.binding.BindingException: Parameter 'empno' not found. Available parameters are [arg1, arg0, param1, param2]
### Cause: org.apache.ibatis.binding.BindingException: Parameter 'empno' not found. Available parameters are [arg1, arg0, param1, param2]

改为

EmpDao.class
public List<Emp> selectEmpByEmpnoAndSal2(@Param("empno") Integer empno, @Param("sal") Double sal);

再次运行就ok了

DEBUG [main] - ==>  Preparing: select * from emp where empno = ? and sal > ? 
DEBUG [main] - ==> Parameters: 7369(Integer), 500.0(Double)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}

如果多个参数正好是我们业务逻辑的数据模型，不经常使用的话，我们也可以直接传入map.

#{key}，取出map中对应的值。

EmpDao.class，EmpDao.xml，MyTest.class配置修改。

public List<Emp> selectEmpByEmpnoAndSal3(Map<String,Object> map);

<select id="selectEmpByEmpnoAndSal3" resultType="com.cmz.bean.Emp">
    select * from emp where empno = #{empno} and sal > #{sal}
</select>

测试

@Test
public void test11(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    EmpDao mapper = sqlSession.getMapper(EmpDao.class);
    Map<String,Object> map = new HashMap<String, Object>();
    map.put("empno",7369);
    map.put("sal",500.0);
    List<Emp> list = mapper.selectEmpByEmpnoAndSal3(map);
    for (Emp emp : list) {
        System.out.println(emp);
    }
    sqlSession.commit();
    sqlSession.close();
}

输出

DEBUG [main] - ==>  Preparing: select * from emp where empno = ? and sal > ? 
DEBUG [main] - ==> Parameters: 7369(Integer), 500.0(Double)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, deptno=20}

如果多个参数不是业务模型中的数据，但是经常要使用，推荐来编写一个TO[Transfer Object]数据对象。
```
Page{
    int index;
    int size;
}
```

思考场景

public List<Emp> selectEmpByEmpnoAndSal2(@Param("empno") Integer empno, Double sal);
--》取值  empno==》#{empno}或者#{param1}   sal==》#{param2}

public List<Emp> selectEmpByEmpnoAndSal2(Integer empno,Emp emp);
--》取值  empno==》#{param1}   sal==》#{param2.sal}

public List<Emp> selectEmpByEmpnoAndSal2(Integer empno,@Param("emp")Emp emp);
--》取值  empno==》#{param1}   sal==》#{emp.sal}

// 特别注意，如果是Collectuon[list,set]类型，或者数组，也会特殊处理
也就是传入的list或者数组封装到map中。key,Collection(collection),如果是List还可以使用这个key(),若是数组就封装成array。
public List<Emp> selectEmpById(List<Integer> ids);
--》取值  取出第一个id的值  #{list[0]}

3 动态sql

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

3.1 if

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分

<select id="selectEmpByCondition" resultType="com.cmz.bean.Emp">
    select * from emp
    <where>
        <if test="empno!=null">
            empno > #{empno}
        </if>
    </where>
</select>

这条语句提供了可选的查找文本功能。如果不传入 “empno”，那么所有处于 “null” 状态的 emp都会返回；如果传入了 “empno” 参数，那么就会对 “empno” 一列进行模糊查找并返回对应的 emp结果（细心的读者可能会发现，“empno” 的参数值需要包含查找掩码或通配符字符）。

如果希望通过 “empno” 和 “ename” 两个参数进行可选搜索该怎么办呢？首先，我想先将语句名称修改成更名副其实的名称；接下来，只需要加入另一个条件即可。

<select id="selectEmpByCondition" resultType="com.cmz.bean.Emp">
    select * from emp
    <where>
        <if test="empno!=null">
            empno > #{empno}
        </if>
        <if test="ename!=null">
            and ename=#{ename}
        </if>
        <if test="sal > 500">
            and sal > #{sal}
        </if>
    </where>
</select>

使用where的时候，需要将连接的and写在最前面，若是次没匹配到话，会自动删除

动态sql-if

3.2 choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

<!---->
<select id="selectEmpByCondition"
        resultType="com.cmz.bean.Emp">
    select * from emp
    <where>
        <choose>
            <when test="empno!=null">
                empno = #{empno}
            </when>
            <when test="ename!=null">
                ename = #{ename}
            </when>
            <otherwise>
                sal > #{sal}
            </otherwise>
        </choose>
    </where>
</select>

策略变为：传入了 “empno” 就按 “empno” 查找，传入了 “ename” 就按 “ename” 查找的情形。若两者都没有传入，就根据sal的参数做查找。

选择查找

也就是类型c++的switch，只要有匹配到就break了。

3.3 trim

和where类似，定制where。

<!--动态sql
trim:截取字符串，可以自定义where的格式
prefix：为sql语句整体添加一个前缀
prefixOverrides:去除整体sql语句前面多余的字符串
suffixOverriede:去除整体sql语句后面多余的字符串
    若是想去掉多个字符，中间可以使用|连接
-->
<select id="selectEmpByCondition"
        resultType="com.cmz.bean.Emp">
    select * from emp
    <trim prefix="where" prefixOverrides="and" suffixOverrides="and | or">
        <if test="empno!=null">
            empno = #{empno} and
        </if>
        <if test="ename!=null">
            ename = #{ename} or
        </if>
    </trim>
</select>

trim

3.4 foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

<!--动态sql的foreach
foreach:遍历集合中的元素
    collection:指定要遍历的集合
    separator:分隔符
    open:以什么开始
    close：以什么结束
    item:遍历过程中的每一个元素值
    index:表示索引
-->
<select id="selectEmpByDeptnos" resultType="com.cmz.bean.Emp">
    select * from emp where  deptno in
    <foreach collection="deptnos" separator="," open="(" item="deptno" index="idx" close=")">
        #{deptno}
    </foreach>
</select>

foreach

4. 缓存

知识点复习：

mybatis中的缓存机制：
    如果没有缓存，那么每次查询的时候都需要从数据库中加载数据，这回造成io的性能问题，所以，在很多情况下
    如果连续执行两条相同的sql语句，可以直接从缓存中获取，如果获取不到，那么再去查询数据库，这意味着查询完成的结果
    需要放到缓存中。

    缓存分类：
        1、一级缓存：表示sqlSession级别的缓存，每次查询的时候会开启一个会话，此会话相当于一次连接，关闭之后自动失效
        2、二级缓存：全局范围内的缓存，sqlsession关闭之后才会生效
        3、第三方缓存：继承第三方的组件，来充当缓存的作用
    ==================
    一级缓存：表示将数据存储在sqlsession中，关闭之后自动失效，默认情况下是开启的
        在同一个会话之内，如果执行了多个相同的sql语句，那么除了第一个之外，所有的数据都是从缓存中进行查询的

        在某些情况下，一级缓存可能会失效？
            1、在同一个方法中，可能会开启多个会话，此时需要注意，会话跟方法没有关系，不是一个方法就只能由一个会话，所以严格
                记住,缓存的数据是保存在sqlsession中的
            2、当传递对象的时候，如果对象中的属性值不同，也不会走缓存
            3、在同一个连接中，如果修改了数据，那么缓存会失效，不同连接之间是不受影响的
            4、如果在一个会话过程中，手动清空了缓存，那么缓存也会失效
    二级缓存：表示的是全局缓存，必须要等到sqlsession关闭之后才会生效
        默认是不开启的，如果需要开启的话，需要进行如下设置
            1、修改全局配置文件，在settings中添加配置
                
            2、指定在哪个映射文件中使用缓存的配置
                
            3、对应的java实体类必须要实现序列化的接口
        在使用二级缓存的时候，可以包含多个属性值：
            eviction：缓存淘汰机制：
                    LRU： 最近最少使用
                    FIFO:先进先出，按照添加缓存的顺序执行
                    SOFT – 软引用：基于垃圾回收器状态和软引用规则移除对象。
                    WEAK – 弱引用：更积极地基于垃圾收集器状态和弱引用规则移除对象。
            flushInterval：设置多长时间进行缓存刷新
            size:引用的条数，是一个正整数，缓存中可以存储多少个对象，一般不设置，如果设置的话不要太大，会导致内存溢出
            readonly:只读属性：
                    true:只读缓存，会给所有的调用的方法返回该对象的实例，不安全
                    false:读写缓存，只是返回缓存对象的拷贝，比较安全

            一级缓存跟二级缓存有没有可能同时存在数据？
                    不会同时存在，因为二级缓存生效的时候，是在sqlsession关闭的时候
            当查询数据的时候，我们是先查询一级缓存还是先查询二级缓存？
                    先查询二级缓存，然后再查询一级缓存

4.1 缓存机制

如果没有缓存，那么每次查询的时候都需要从数据库中加载数据，这回造成io的性能问题，所以，在很多情况下如果连续执行两条相同的sql语句，可以直接从缓存中获取，如果获取不到，那么再去查询数据库，这意味着查询完成的结果需要放到缓存中。

4.2 缓存分类

一级缓存：表示sqlSession级别的缓存，每次查询的时候会开启一个会话，此会话相当于一次连接，关闭之后自动失效。一级缓存默认是开启的。
二级缓存：全局范围内的缓存，sqlsession关闭之后才会生效。默认不开启。
第三方缓存：继承第三方的组件，来充当缓存的作用

4.2.1 一级缓存

一级缓存：表示将数据存储在sqlsession中，关闭之后自动失效，默认情况下是开启的,在同一个会话之内，如果执行了多个相同的sql语句，那么除了第一个之外，所有的数据都是从缓存中进行查询的,在某些情况下，一级缓存可能会失效？

在同一个方法中，可能会开启多个会话，此时需要注意，会话跟方法没有关系，不是一个方法就只能由一个会话，所以严格。记住,缓存的数据是保存在sqlsession中的
当传递对象的时候，如果对象中的属性值不同，也不会走缓存
在同一个连接中，如果修改了数据，那么缓存会失效，不同连接之间是不受影响的
如果在一个会话过程中，手动清空了缓存，那么缓存也会失效

4.2.2 二级缓存

二级缓存：表示的是全局缓存，必须要等到sqlsession关闭之后才会生效，默认是不开启的，如果需要开启的话，需要进行如下设置

修改全局配置文件mybatis_config.xml，在settings中添加配置

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

指定在哪个映射文件中使用缓存的配置，需要在哪个类上使用就加载哪个类的xml上

<cache></cache>

对应的java实体类必须要实现序列化的接口

否则报错

org.apache.ibatis.cache.CacheException: Error serializing object.  Cause: java.io.NotSerializableException: com.cmz.bean.Emp

/*二级缓存测试*/
@Test
public void test14() throws IOException {
    SqlSession sqlSession1 = sqlSessionFactory.openSession();
    SqlSession sqlSession2 = sqlSessionFactory.openSession();
    EmpDao mapper1 = sqlSession1.getMapper(EmpDao.class);
    EmpDao mapper2 = sqlSession2.getMapper(EmpDao.class);

    Emp emp1 = mapper1.seletEmpByEmpno(7369);
    System.out.println(emp1);
    sqlSession1.close();  // 关闭后一级缓存已经失效了

    System.out.println("-----------------");
    Emp emp2 = mapper2.seletEmpByEmpno(7369);
    System.out.println(emp2);
    sqlSession2.close();
}

运行结果

DEBUG [main] - Cache Hit Ratio [com.cmz.dao.EmpDao]: 0.0
DEBUG [main] - ==>  Preparing: select * from emp left join dept on emp.deptno=dept.deptno where empno = ? 
DEBUG [main] - ==> Parameters: 7369(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DEPTNO, DNAME, LOC
TRACE [main] - <==        Row: 7369, SMITH, CLERK, 7902.0, 1980-12-17, 800.0, null, 20, 20, RESEARCH, DALLAS
DEBUG [main] - <==      Total: 1
Emp{empno=7369, ename='SMITH', job='null', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, dept=Dept{deptno=20, dname='RESEARCH', loc='DALLAS', emps=null}}
-----------------
DEBUG [main] - Cache Hit Ratio [com.cmz.dao.EmpDao]: 0.5
Emp{empno=7369, ename='SMITH', job='null', mgr=7902, hiredate=Wed Dec 17 08:00:00 CST 1980, sal=800.0, common=null, dept=Dept{deptno=20, dname='RESEARCH', loc='DALLAS', emps=null}}

此时可以看出在一级缓存失效后，并没有去执行sql也就是直接从二级缓存中拿数据了。DEBUG [main] - Cache Hit Ratio [com.cmz.dao.EmpDao]: 0.5命中率也能看出来。

4.3 缓存淘汰

<cache
  eviction="FIFO"
  flushInterval="60000"
  size="512"
  readOnly="true"/>

这个更高级的配置创建了一个 FIFO 缓存，每隔 60 秒刷新，最多可以存储结果对象或列表的 512 个引用，而且返回的对象被认为是只读的，因此对它们进行修改可能会在不同线程中的调用者产生冲突。

在使用二级缓存的时候，可以包含多个属性值：

eviction：缓存淘汰机制：
- LRU – 最近最少使用: 移除最长时间不被使用的对象 [默认是该配置]
- FIFO – 先进先出: 按对象进入缓存的顺序来移除它们
- SOFT – 软引用：基于垃圾回收器状态和软引用规则移除对象。
- WEAK – 弱引用：更积极地基于垃圾收集器状态和弱引用规则移除对象。
flushInterval：刷新间隔，属性可以被设置为任意的正整数，设置的值应该是一个以毫秒为单位的合理时间量。默认情况是不设置，也就是没有刷新间隔，缓存仅仅会在调用语句时刷新。
size:引用的条数，是一个正整数，缓存中可以存储多少个对象，一般不设置，如果设置的话不要太大，会导致内存溢出，属性可以被设置为任意正整数，要注意欲缓存对象的大小和运行环境中可用的内存资源。默认值是 1024。
readonly:只读属性：
- true: 只读缓存，会给所有的调用的方法返回该对象的实例，不安全
- false:读写缓存，只是返回缓存对象的拷贝，比较安全，【默认是该配置】

提示二级缓存是事务性的。这意味着，当 SqlSession 完成并提交时，或是完成并回滚，但没有执行 flushCache=true 的 insert/delete/update 语句时，缓存会获得更新

Q: 一级缓存跟二级缓存有没有可能同时存在数据？
A: 不会同时存在，因为二级缓存生效的时候，是在sqlsession关闭的时候

Q: 当查询数据的时候，我们是先查询一级缓存还是先查询二级缓存？
A: 先查询二级缓存，然后再查询一级缓存

4.4 第三方缓存

在某些情况下我们也可以自定义实现缓存，或为其他第三方缓存方案创建适配器，来完全覆盖缓存行为。

导入对应的maven依赖【pom.xml】

<dependency>
  <groupId>org.mybatis.caches</groupId>
  <artifactId>mybatis-ehcache</artifactId>
  <version>1.2.0</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-api</artifactId>
  <version>2.0.0-alpha1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-log4j12 -->
<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-log4j12</artifactId>
  <version>2.0.0-alpha1</version>
  <scope>test</scope>
</dependency>

导入ehcache配置文件

在resources下新建ehcache.xml [名字别瞎写]

<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd">
<!-- 磁盘保存路径 -->
<diskStore path="D:\ehcache" />

<defaultCache 
 maxElementsInMemory="1" 
 maxElementsOnDisk="10000000"
 eternal="false" 
 overflowToDisk="true" 
 timeToIdleSeconds="120"
 timeToLiveSeconds="120" 
 diskExpiryThreadIntervalSeconds="120"
 memoryStoreEvictionPolicy="LRU">
</defaultCache>
</ehcache>

<!-- 
属性说明：
l diskStore：指定数据在磁盘中的存储位置。
l defaultCache：当借助CacheManager.add("demoCache")创建Cache时，EhCache便会采用<defalutCache/>指定的的管理策略

以下属性是必须的：
l maxElementsInMemory - 在内存中缓存的element的最大数目 
l maxElementsOnDisk - 在磁盘上缓存的element的最大数目，若是0表示无穷大
l eternal - 设定缓存的elements是否永远不过期。如果为true，则缓存的数据始终有效，如果为false那么还要根据timeToIdleSeconds，timeToLiveSeconds判断
l overflowToDisk - 设定当内存缓存溢出的时候是否将过期的element缓存到磁盘上

以下属性是可选的：
l timeToIdleSeconds - 当缓存在EhCache中的数据前后两次访问的时间超过timeToIdleSeconds的属性取值时，这些数据便会删除，默认值是0,也就是可闲置时间无穷大
l timeToLiveSeconds - 缓存element的有效生命期，默认是0.,也就是element存活时间无穷大
diskSpoolBufferSizeMB 这个参数设置DiskStore(磁盘缓存)的缓存区大小.默认是30MB.每个Cache都应该有自己的一个缓冲区.
l diskPersistent - 在VM重启的时候是否启用磁盘保存EhCache中的数据，默认是false。
l diskExpiryThreadIntervalSeconds - 磁盘缓存的清理线程运行间隔，默认是120秒。每个120s，相应的线程会进行一次EhCache中数据的清理工作
l memoryStoreEvictionPolicy - 当内存缓存达到最大，有新的element加入的时候， 移除缓存中element的策略。默认是LRU（最近最少使用），可选的有LFU（最不常使用）和FIFO（先进先出）
-->

在mapper文件中添加自定义缓存【EmpDao.xml】

<cache type="org.mybatis.caches.ehcache.EhcacheCache"></cache>

运行测试代码后

5. 逆向工程

5.1 MyBatis3Simple

逆向工程

pom.xml配置



    4.0.0

    com.cmz
    mybatis_generator
    1.0-SNAPSHOT


    
        
            org.mybatis
            mybatis
            3.5.4
        

        
            mysql
            mysql-connector-java
            8.0.19
        

        
            log4j
            log4j
            1.2.17
        

        
            junit
            junit
            4.13
            test
        

        
            org.mybatis.caches
            mybatis-ehcache
            1.2.0
        

        
        
            org.slf4j
            slf4j-api
            2.0.0-alpha1
        
        
        
            org.slf4j
            slf4j-log4j12
            2.0.0-alpha1
            test
        
        
            org.mybatis.generator
            mybatis-generator-core
            1.4.0

mbg.xml

<!DOCTYPE generatorConfiguration PUBLIC
        "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<!--总配置标签-->
<generatorConfiguration>
    <!--具体配置的上下文环境-->
    <context id="simple" targetRuntime="MyBatis3Simple">
        <!--指向我们需要连接的数据库-->
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3306/demo?serverTimezone=UTC"
                        userId="root" password="root"/>

        <!--生成对应的实体类
        targetPackage:指定存放的包
        targetProject：指定当前工程的目录
        -->
        <javaModelGenerator targetPackage="com.cmz.bean" targetProject="src/main/java"/>

        <!--生成对应的SQL映射文件-->
        <sqlMapGenerator targetPackage="com.cmz.dao" targetProject="src/main/resources"/>

        <!--生成对应的DAO接口-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.cmz.dao" targetProject="src/main/java"/>

        <!--指定需要反向生成的表-->
        <table tableName="emp" />
        <table tableName="dept" />
    </context>
</generatorConfiguration>

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>
    <properties resource="db.properties"></properties>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

</configuration>

log4j.properties

# 全局日志配置
log4j.rootLogger=INFO, stdout
# MyBatis 日志配置
log4j.logger.com.cmz=TRACE
# 控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/demo?serverTimezone=UTC
username=root
password=root

MyTest.class

package com.cmz;

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

/**
 * @author summer
 * @create 2020-04-18 0:05
 */
public class MyTest {
    public static void main(String[] args) throws Exception {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        File configFile = new File("mbg.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
    }
}

运行后

自动生成的文件

生成的文件如下:

Dept.class

package com.cmz.bean;
public class Dept {
    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column dept.DEPTNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private Integer deptno;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column dept.DNAME
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private String dname;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column dept.LOC
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private String loc;

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column dept.DEPTNO
     *
     * @return the value of dept.DEPTNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public Integer getDeptno() {
        return deptno;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column dept.DEPTNO
     *
     * @param deptno the value for dept.DEPTNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column dept.DNAME
     *
     * @return the value of dept.DNAME
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public String getDname() {
        return dname;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column dept.DNAME
     *
     * @param dname the value for dept.DNAME
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setDname(String dname) {
        this.dname = dname;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column dept.LOC
     *
     * @return the value of dept.LOC
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public String getLoc() {
        return loc;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column dept.LOC
     *
     * @param loc the value for dept.LOC
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setLoc(String loc) {
        this.loc = loc;
    }
}

Emp.class

package com.cmz.bean;

import java.util.Date;

public class Emp {
    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column emp.EMPNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private Integer empno;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column emp.ENAME
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private String ename;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column emp.JOB
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private String job;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column emp.MGR
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private Double mgr;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column emp.HIREDATE
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private Date hiredate;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column emp.SAL
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private Double sal;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column emp.COMM
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private Double comm;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column emp.DEPTNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    private Integer deptno;

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column emp.EMPNO
     *
     * @return the value of emp.EMPNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public Integer getEmpno() {
        return empno;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column emp.EMPNO
     *
     * @param empno the value for emp.EMPNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column emp.ENAME
     *
     * @return the value of emp.ENAME
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public String getEname() {
        return ename;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column emp.ENAME
     *
     * @param ename the value for emp.ENAME
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setEname(String ename) {
        this.ename = ename;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column emp.JOB
     *
     * @return the value of emp.JOB
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public String getJob() {
        return job;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column emp.JOB
     *
     * @param job the value for emp.JOB
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setJob(String job) {
        this.job = job;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column emp.MGR
     *
     * @return the value of emp.MGR
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public Double getMgr() {
        return mgr;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column emp.MGR
     *
     * @param mgr the value for emp.MGR
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setMgr(Double mgr) {
        this.mgr = mgr;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column emp.HIREDATE
     *
     * @return the value of emp.HIREDATE
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public Date getHiredate() {
        return hiredate;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column emp.HIREDATE
     *
     * @param hiredate the value for emp.HIREDATE
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column emp.SAL
     *
     * @return the value of emp.SAL
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public Double getSal() {
        return sal;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column emp.SAL
     *
     * @param sal the value for emp.SAL
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setSal(Double sal) {
        this.sal = sal;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column emp.COMM
     *
     * @return the value of emp.COMM
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public Double getComm() {
        return comm;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column emp.COMM
     *
     * @param comm the value for emp.COMM
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setComm(Double comm) {
        this.comm = comm;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column emp.DEPTNO
     *
     * @return the value of emp.DEPTNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public Integer getDeptno() {
        return deptno;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column emp.DEPTNO
     *
     * @param deptno the value for emp.DEPTNO
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }
}

DeptMapper.class

package com.cmz.dao;

import com.cmz.bean.Dept;
import java.util.List;

public interface DeptMapper {
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table dept
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    int deleteByPrimaryKey(Integer deptno);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table dept
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    int insert(Dept record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table dept
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    Dept selectByPrimaryKey(Integer deptno);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table dept
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    List<Dept> selectAll();

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table dept
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    int updateByPrimaryKey(Dept record);
}

EmpMapper.class

package com.cmz.dao;

import com.cmz.bean.Emp;
import java.util.List;

public interface EmpMapper {
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table emp
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    int deleteByPrimaryKey(Integer empno);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table emp
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    int insert(Emp record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table emp
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    Emp selectByPrimaryKey(Integer empno);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table emp
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    List<Emp> selectAll();

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table emp
     *
     * @mbg.generated Sat Apr 18 00:28:30 CST 2020
     */
    int updateByPrimaryKey(Emp record);
}

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="com.cmz.dao.DeptMapper">
  <resultMap id="BaseResultMap" type="com.cmz.bean.Dept">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    <id column="DEPTNO" jdbcType="INTEGER" property="deptno" />
    <result column="DNAME" jdbcType="VARCHAR" property="dname" />
    <result column="LOC" jdbcType="VARCHAR" property="loc" />
  </resultMap>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    delete from dept
    where DEPTNO = #{deptno,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.cmz.bean.Dept">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    insert into dept (DEPTNO, DNAME, LOC
      )
    values (#{deptno,jdbcType=INTEGER}, #{dname,jdbcType=VARCHAR}, #{loc,jdbcType=VARCHAR}
      )
  </insert>
  <update id="updateByPrimaryKey" parameterType="com.cmz.bean.Dept">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    update dept
    set DNAME = #{dname,jdbcType=VARCHAR},
      LOC = #{loc,jdbcType=VARCHAR}
    where DEPTNO = #{deptno,jdbcType=INTEGER}
  </update>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    select DEPTNO, DNAME, LOC
    from dept
    where DEPTNO = #{deptno,jdbcType=INTEGER}
  </select>
  <select id="selectAll" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    select DEPTNO, DNAME, LOC
    from dept
  </select>
</mapper>

EmpMapper.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.cmz.dao.EmpMapper">
  <resultMap id="BaseResultMap" type="com.cmz.bean.Emp">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    <id column="EMPNO" jdbcType="INTEGER" property="empno" />
    <result column="ENAME" jdbcType="VARCHAR" property="ename" />
    <result column="JOB" jdbcType="VARCHAR" property="job" />
    <result column="MGR" jdbcType="DOUBLE" property="mgr" />
    <result column="HIREDATE" jdbcType="DATE" property="hiredate" />
    <result column="SAL" jdbcType="DOUBLE" property="sal" />
    <result column="COMM" jdbcType="DOUBLE" property="comm" />
    <result column="DEPTNO" jdbcType="INTEGER" property="deptno" />
  </resultMap>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    delete from emp
    where EMPNO = #{empno,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.cmz.bean.Emp">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    insert into emp (EMPNO, ENAME, JOB, 
      MGR, HIREDATE, SAL, COMM, 
      DEPTNO)
    values (#{empno,jdbcType=INTEGER}, #{ename,jdbcType=VARCHAR}, #{job,jdbcType=VARCHAR}, 
      #{mgr,jdbcType=DOUBLE}, #{hiredate,jdbcType=DATE}, #{sal,jdbcType=DOUBLE}, #{comm,jdbcType=DOUBLE}, 
      #{deptno,jdbcType=INTEGER})
  </insert>
  <update id="updateByPrimaryKey" parameterType="com.cmz.bean.Emp">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    update emp
    set ENAME = #{ename,jdbcType=VARCHAR},
      JOB = #{job,jdbcType=VARCHAR},
      MGR = #{mgr,jdbcType=DOUBLE},
      HIREDATE = #{hiredate,jdbcType=DATE},
      SAL = #{sal,jdbcType=DOUBLE},
      COMM = #{comm,jdbcType=DOUBLE},
      DEPTNO = #{deptno,jdbcType=INTEGER}
    where EMPNO = #{empno,jdbcType=INTEGER}
  </update>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
    from emp
    where EMPNO = #{empno,jdbcType=INTEGER}
  </select>
  <select id="selectAll" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Apr 18 00:28:30 CST 2020.
    -->
    select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
    from emp
  </select>
</mapper>

5.2 MyBatis3DynamicSql

次方法只能生成注解不能生成xml

MyBatis3DynamicSql

夏天

https://caimengzhi.gitee.io/2020/04/10/mybatis/mybatis-xmlinfo/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源夏天 !

mybatis

--------本文结束感谢您的阅读!---------

❤如果文章能够帮到您的话，不妨就支持一下小编呗!❤

scratch基础

Scratch基本功能介绍、使用。

2020-04-13 少儿编程

少儿编程 scratch

scratch简介

Scratch是图形化编程工具

2020-04-10 少儿编程

少儿编程 scratch

Mybatis配置说明

Mybatis的xml映射文件

0. xml 映射文件

1. Insert, Update, Delete 元素的属性

1.1 id

1.2 statementType

1.3 useGeneratedKeys

1.3.1 未配置

1.3.2 配置

2. select元素属性

2.1 resultType/resultMap

2.1.1 resultType

2.2 结果返回list集合

2.3 结果返回Map集合

2.3.1 返回单条数据

2.3.2 返回多条数据

2.4 ResultMap

2.4.1 映射级联属性

2.4.2 映射关联查询

2.4.3 association 分步查询

2.4.4 association 延时加载

2.4.5 collection

2.4.6 collection分步查询

2.4.7 collection懒加载

2.4.8 fetchType

2.4.9 多参数传递

2.5 #{},${}

2.6 参数获取

3 动态sql

3.1 if

3.2 choose、when、otherwise

3.3 trim

3.4 foreach

4. 缓存

4.1 缓存机制

4.2 缓存分类

4.2.1 一级缓存

4.2.2 二级缓存

4.3 缓存淘汰

4.4 第三方缓存

5. 逆向工程

5.1 MyBatis3Simple

5.2 MyBatis3DynamicSql

❤如果文章能够帮到您的话，不妨就支持一下小编呗!❤

你的赏识是我前进的动力

2.5 `#{}`,`${}`