MyBatis

Mybatis

ref:

https://www.bilibili.com/video/BV1VP4y1c7j7

获取

一、Github下载源码与官方文档

👉 GitHub - mybatis -> README -> Essentials -> Download Latest -> assets -> mybatis-3.5.11.zip

二、Maven引入

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.9</version>
</dependency>

起步

添加依赖

• 新建maven项目并配置pom.xml，dependencies下添加如下

  • mybatis框架

    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.9</version>
    </dependency>
    

  • mysql驱动

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

• 其他（测试工具）

  • junit

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

配置mybatis-config.xml

• transactionManager 事务管理方式：JDBC
• dataSource 连接数据库信息
• POOLED 使用数据库连接池（缓存）
• mapper 接口映射xml文档位置（包下用"."，resources下用"/"）

<?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>
    <environments defalut="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/xxx" />
                <property name="username" value="xxx" />
                <property name="password" value="xxx" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/UserMapper.xml" />
    </mappers>
</configuration>

示例

• com.xxx.mybatis.model创建User类，属性与表一致
• com.xxx.mybatis.mapper创建UserMapper接口
• resources/mappers创建UserMapper.xml。namespace值为对应接口全名，id值为接口方法名

User

public class User {
    private String username;
    private String password;
    private Integer roleid;
}

UserMapper

public interface UserMapper {
    int insertUser();
}

UserMapper.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.learn.mybatis.mapper.UserMapper">
    <insert id="insertUser" >
        insert into t_user values(null,'zhangsan','123456',1)
    </insert>
</mapper>

• 最后需要在mybatis-config.xml配置文件中引入

</configuration>
... 
    <mappers>
        <mapper resource="mappers/UserMapper.xml" />
    </mappers>
</configuration>

测试

test/java

com.xxx.mybatis.test

使用字节输入流获取配置文件，传入工厂构建器实例构建得到工厂对象，工厂获取会话，通过会话获取对应接口实例。

public class MyBatisTest {

    @Test
    public void testMyBatis() throws IOException {
        // 加载核心配置文件
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        // 获取SqlSessionFactoryBuilder
        SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
        // 获取SqlSessionFactory
        SqlSessionFactory ssf = ssfb.build(is);
        // 获取SqlSession    Java与数据库之间的会话
        SqlSession sqlSession = ssf.openSession();
        // 找到对应实现类
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        // 测试
        int b = mapper.insertUser();
        // 手动提交JDBC事务
        sqlSession.commit();
        System.out.println("insertUser: " + b);
    }
}

事务自动提交

SqlSession sqlSession = ssf.openSession(true);

openSessions的源码

public SqlSession openSession(boolean autoCommit) {
    return this.sqlSessionFactory.openSession(autoCommit);
}

引入log4j

级别：fatal（致命） > error（错误） > warn > info > debug

设置等级为debug，所有信息将被打印（error、info等）

pom.xml

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

resources/log4j.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <param name="Encoding" value="UTF-8"/>
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m(%F:%L) \n"/>
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug"/>
    </logger>
    <logger name="org.apache.ibatis">
        <level value="info"/>
    </logger>
    <root>
        <level value="debug"/>
        <appender-ref ref="STDOUT"/>
    </root>
</log4j:configuration>

运行示例

DEBUG 11-02 04:17:29,630 ==>  Preparing: insert into t_user values(null,'zhangsan','123456',1)(BaseJdbcLogger.java:137) 
DEBUG 11-02 04:17:29,674 ==> Parameters: (BaseJdbcLogger.java:137) 
DEBUG 11-02 04:17:29,676 <==    Updates: 1(BaseJdbcLogger.java:137) 
insertUser: 1

Process finished with exit code 0

*Mapper.xml

• namespace 值为对应接口全类名
• insert 等标签的 id 值为接口的方法名
• select查询标签需要返回类型resultType（类属性名与数据库字段名一致时）或resultMap（类属性名与数据库字段名不一致时，如类的id实际上是表的t_id，需要自定义映射关系）

mybatis-config.xml

• environments 配置多个连接数据库的环境，属性default表示默认环境（对应环境id值）

• environment 具体环境，以id为标识

• transactionManager 事务管理方式 JDBC|MANAGED ，JDBC表示原生JDBC事务管理方式，MANAGED表示被谁管理，如Spring

• dataSource 数据源 POOLED表示使用连接池（缓存），UNPOOLED表示不使用连接池，JNDI表示使用上下文数据源

• settings mybatis全局配置

• 标签有先后顺序要求

  元素类型为 "configuration" 的内容必须匹配 "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?)"
  

• <!--    mybatis全局配置-->
      <settings>
  <!--        启用驼峰-->
          <setting name="mapUnderscoreToCamelCase" value="true"/>
  <!--        启用延迟加载-->
          <setting name="lazyLoadingEnabled" value="true"/>
      </settings>
  

•     <mappers>
  <!--        设置映射文件所在的包,所有的映射的文件都会被引入到核心配置文件中,
              要求 1.mapper接口所在的包与映射文件所在的包一致,如接口在com.xxx,则映射文件也应该在resources下的com/xxx
                  2.接口要和映射文件名字一致-->
          <package name="com.xxx.mybatis.mapper"/>
      </mappers>
  

• <!--    类型别名-->
      <typeAliases>
  <!--        没有alias默认为类名,不区分大小写-->
          <typeAlias type="com.learn.mybatis.model.User" alias="User"/>
  <!--        以包为单位,设置默认别名为其类名,不区分大小写-->
          <package name="com.learn.mybatis.model"/>
      </typeAliases>
  

jdbc.properties

resources/mybatis-config.xml

<properties resource="jdbc.properties"/>

<dataSource type="POOLED">
    <property name="driver" value="${jdbc.driver}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
</dataSource>

resources/jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/xxx?useUnicode=true&charsetEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai
jdbc.username=xxx
jdbc.password=xxx

模板设置

settings > Editor > File and CodeTemplates

获取参数值

单个参数时，参数名是什么无关紧要，但是一般与参数名保持一致

多个参数时，需要一一对应，否则不知道匹配哪一个。参数存储在map中

• 方式一：${} 拼接

例：

<update id="updateUser">
    <!--aaa可以是任意的，可以叫bb或者ccc，因为只有一个参数-->
    update t_user set username = '张三' where id = '${aaa}'
</update>

update t_user set username = '张三' where username = 'zhangsan'

• 方式二：#{} 占位

例

<update id="updateUser">
    update t_user set username = '张三' where id = #{aaa}
</update>

此时日志输出

update t_user set username = '张三' where username = ?

parameters: zhangsan(String)

接口参数为对象

• Map

User checkLoginByMap(Map<String, Object> map);

<select id="checkLoginByMap" resultType="User">
    select * from t_user where username = #{username} and password = #{password}
</select>

Map<String,Object> map = new HashMap<>();
map.put("username", "111");
map.put("password", "1111");
User user = mapper.checkLoginByMap(map);// user ==> User{id=13, username='111', password='1111', roleId=1}

• 实体类User

int insertUser(User user);

<insert id="insertUser" >
    insert into t_user values(null,#{username},#{password},#{roleId})
</insert>

mapper.insertUser(new User(null, "111", "1111", 1));// 1

• 使用注解@Param

User checkLoginByMap(@Param("uname") String username, @Param("upwd") String password);
// 以Param的值（uname）为键，username的值为值

<select id="checkLoginByParam" resultType="User">
    select * from t_user where username = #{uname} and password = #{upwd}
</select>

mapper.checkLoginByParam("111", "1111");// User{id=13, username='111', password='1111', roleId=1}

多种查询

• 返回单条数据

User getUserById(@Param("id") Integer id);

<select id="getUserById" resultType="User">
    select * from t_user where id = #{id}
</select>

• 返回多条数据

List<User> getAllUser();

<select id="getAllUser" resultType="User">
    select * from t_user
</select>

• 返回记录数

Long getUserCount();

<select id="getUserCount" resultType="java.lang.Long">
    select count(*) from t_user
</select>

Long userCount = mapper.getUserCount();// 5

• 单条数据返回map（场景，查询到的结果没有相应的实体接收，如多表查询）

Map<String,Object> getUserByIdToMap(@Param("id") Integer id);

<select id="getUserByIdToMap" resultType="map">
    select * from t_user where id = #{id}
</select>

Map<String, Object> map = mapper.getUserByIdToMap(1);// {password=123, roleid=1, id=1, username=zhangsan1}

• 多条数据返回map

List<Map<String,Object>> getAllUserToMap();

<select id="getAllUserToMap" resultType="map">
    select * from t_user
</select>

List<Map<String, Object>> list = mapper.getAllUserToMap();//[{password=123, roleid=1, id=1, username=zhangsan1}, {password=123, roleid=1, id=2, username=zhangsan2},....]

• 使用@MapKey（id作为Map的键，对象内容作为值）

@MapKey("id")
Map<String,Object> getAllUserToMap();

<select id="getAllUserToMap" resultType="map">
    select * from t_user
</select>

Map<String, Object> map = mapper.getAllUserToMap();//{1={password=123, roleid=1, id=1, username=zhangsan1}, 2={password=123, roleid=1, id=2, username=zhangsan2}, 3={password=123, roleid=1, id=3, username=zhangsan3}, 4={password=123, roleid=3, id=4, username=zhangsan4}, 13={password=1111, roleid=1, id=13, username=111}}

• 模糊查询

'%${username}%' | concat('%',#{username},'%') | "%"#{username}"%"

User getUserByLike(@Param("username") String username);

<select id="getUserByLike" resultType="User">
    <!-- %#{username}% == > org.apache.ibatis.exceptions.PersistenceException  -->
    select * from t_user where username like '%${username}%'
</select>
<!--或者 使用 concat 拼接-->
<select id="getUserByLike" resultType="User">
    select * from t_user where username like concat('%',#{username},'%')
</select>
<!--或者-->
<select id="getUserByLike" resultType="User">
    select * from t_user where username like "%"#{username}"%"
</select>

User user = mapper.getUserByLike("2");// User{id=2, username='zhangsan2', password='123', roleId=1}

• 动态表名

${tableName}

List<User> getAllUserByTableName(@Param("tableName")String tableName);

<select id="getAllUserByTableName" resultType="User">
    select * from ${tableName}
</select>

List<User> t_user = mapper.getAllUserByTableName("t_user");

批量删除

org.apache.ibatis.exceptions.PersistenceException:

Integer deleteMore(@Param("ids")String ids);

<delete id="deleteMore">
    <!--#{ids}自动加单引号-->
    delete from t_user where id in (${ids})
</delete>

int i = mapper.deleteMore("6,7");// 0

获取自增主键

useGeneratedKeys： 设置当前sql使用了自动递增的主键

keyProperty：将自增的主键的值赋值给参数的某个属性

void insertUserGetKey(User user);

<insert id="insertUserGetKey" useGeneratedKeys="true" keyProperty="id">
    <!-- 获取自动递增主键 useGeneratedKeys="true" 将主键放到类的某个属性 keyProperty="id" -->
    insert into t_user values (null,#{username},#{password},#{roleId})
</insert>

User user = new User(null, "张三666", "123456", 1);
mapper.insertUserGetKey(user);// User{id=14, username='张三666', password='123456', roleId=1}

类属性名与列名保持一致

• 方式一、修改sql

public class User {
    private String tName;
}

select t_name as tName from t_user -- t_name是数据库的字段名，与实体属性名tName不一致，因此给t_name起别名

• 方式二、mybatis配置 启用驼峰

<!--    mybatis全局配置-->
    <settings>
<!--        启用驼峰-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

• 方式三、resultMap

标签id ：主键映射关系

标签result ：普通字段映射关系

association ：处理多对一映射关系 n:1

collection ：处理一对多映射关系 1:n

public class Role {
    private Integer rId;
    private String rName;
    private String rRemark;
}

List<Role> getAllRole();

<select id="getAllRole" resultMap="roleResultMap">
    select * from t_role
</select>
<resultMap id="roleResultMap" type="Role">
    <!--column 是数据库列名，property是实体属性名，将它们做映射-->
    <id property="rId" column="r_id"/>
    <result property="rName" column="r_name"/>
    <result property="rRemark" column="r_remark"/>
</resultMap>

处理n:1映射关系

• 方式一 级联属性赋值

role.rId ： role表示User类中Role的变量名role，rId 表示role的属性名

public class User {
    private Integer id;
    private String username;
    private String password;
    private Integer roleId;
    private Role role;
}

User getUserAndRoleById(@Param("id")Integer id);

<select id="getUserAndRoleById" resultMap="userAndRole">
    SELECT * FROM t_user u LEFT JOIN t_role r on u.roleid = r.r_id WHERE u.id = 1
</select>
<resultMap id="userAndRole" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <result property="roleId" column="roleid"/>
    <result property="role.rId" column="r_id"/>
    <result property="role.rName" column="r_name"/>
    <result property="role.rRemark" column="r_remark"/>
</resultMap>

User user = mapper.getUserAndRoleById(1);// User{id=1, username='zhangsan1', password='123', roleId=1, role=Role{rId=1, rName='普通用户', rRemark='用户中心'}}

• 方式二 association

解释：将查询到的字段r_id映射到类型Role变量名role的属性rId

<select id="getUserAndRoleById" resultMap="userAndRole">
    SELECT * FROM t_user u LEFT JOIN t_role r on u.roleid = r.r_id WHERE u.id = 1
</select>
<resultMap id="userAndRole" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <result property="roleId" column="roleid"/>
    <association property="role" javaType="Role">
        <id property="rId" column="r_id"/>
        <result property="rName" column="r_name"/>
        <result property="rRemark" column="r_remark"/>
    </association>
</resultMap>

• 方式三 分步查询

第一步，根据id查询用户

查询到的column="roleid"（roleid列）的值给select传递过去，作为com.learn.mybatis.mapper.RoleMapper.getRole的参数

User getUserAndRoleById(@Param("id")Integer id);

<select id="getUserAndRoleById" resultMap="userAndRole">
    SELECT * FROM t_user where id = #{id}
</select>
<resultMap id="userAndRole" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <result property="roleId" column="roleid"/>
    <association property="role"
                 column="roleid" 
                 select="com.learn.mybatis.mapper.RoleMapper.getRole">
    </association>
</resultMap>

第二步，查询Role，这里可以拿到id信息，其值为第一步查询roleid列的值

Role getRole(@Param("id") Integer id);

<select id="getRole" resultType="Role">
    select * from t_role where r_id = #{id}
</select>

日志

DEBUG 11-07 13:46:36,206 ==>  Preparing: SELECT * FROM t_user where id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 13:46:36,244 ==> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 13:46:36,273 ====>  Preparing: select * from t_role where r_id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 13:46:36,274 ====> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 13:46:36,277 <====      Total: 1(BaseJdbcLogger.java:137) 
DEBUG 11-07 13:46:36,280 <==      Total: 1(BaseJdbcLogger.java:137) 
User{id=1, username='zhangsan1', password='123', roleId=1, role=Role{rId=1, rName='普通用户', rRemark='用户中心'}}

处理1:n映射关系

• 多表查询

public class Role {
    private Integer rId;
    private String rName;
    private String rRemark;
    private List<User> userList;
}

Role getRoleAndUser(@Param("rId")Integer rId);

<select id="getRoleAndUser" resultMap="userAndRoleMap">
    SELECT * FROM t_role r LEFT JOIN t_user u on r.r_id = u.roleid WHERE r.r_id = #{rId}
</select>
<resultMap id="userAndRoleMap" type="Role">
    <id property="rId" column="r_id"/>
    <result property="rName" column="r_name"/>
    <result property="rRemark" column="r_remark"/>
    <collection property="userList" ofType="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="roleId" column="roleid"/>
    </collection>
</resultMap>

Role role = mapper.getRoleAndUser(1);
System.out.println(role);// Role{rId=1, rName='普通用户', rRemark='用户中心', userList=[User{id=1, username='zhangsan1', password='123', roleId=1, role=null}]}

• 分步查询

第一步

Role getRoleAndUserStepOne(@Param("rId")Integer rId);

<select id="getRoleAndUserStepOne" resultMap="getRoleAndUserStepOneMap">
    select * from t_role where r_id = #{rId}
</select>
<resultMap id="getRoleAndUserStepOneMap" type="Role">
    <id property="rId" column="r_id"/>
    <result property="rName" column="r_name"/>
    <result property="rRemark" column="r_remark"/>
    <collection property="userList"
                select="com.learn.mybatis.mapper.UserMapper.getRoleAndUserStepTwo"
                column="r_id"/>
</resultMap>

第二步

List<User> getRoleAndUserStepTwo(@Param("id")Integer id);

<select id="getRoleAndUserStepTwo" resultType="User">
    select * from t_user where id = #{id}
</select>

日志

Role role = mapper.getRoleAndUserStepOne(1);
System.out.println(role);

DEBUG 11-07 16:10:54,533 ==>  Preparing: select * from t_role where r_id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 16:10:54,566 ==> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 16:10:54,595 ====>  Preparing: select * from t_user where id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 16:10:54,596 ====> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 16:10:54,598 <====      Total: 1(BaseJdbcLogger.java:137) 
DEBUG 11-07 16:10:54,601 <==      Total: 1(BaseJdbcLogger.java:137) 
Role{rId=1, rName='普通用户', rRemark='用户中心', userList=[User{id=1, username='zhangsan1', password='123', roleId=1, role=null}]}

延迟加载

全局开启

<settings>
<!--        启用延迟加载-->
	<setting name="lazyLoadingEnabled" value="true"/>
</settings>

示例一

User user = mapper.getUserAndRoleById(1);
System.out.println(user.getUsername());

lazyLoadingEnabled 不同值时运行效果

• lazyLoadingEnabled = true（有访问才会执行）

DEBUG 11-07 14:00:24,073 ==>  Preparing: SELECT * FROM t_user where id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:00:24,112 ==> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:00:24,194 <==      Total: 1(BaseJdbcLogger.java:137) 
zhangsan1

• lazyLoadingEnabled = false（没访问不执行对应SQL）

DEBUG 11-07 14:02:06,382 ==>  Preparing: SELECT * FROM t_user where id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:02:06,421 ==> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:02:06,453 ====>  Preparing: select * from t_role where r_id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:02:06,453 ====> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:02:06,456 <====      Total: 1(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:02:06,459 <==      Total: 1(BaseJdbcLogger.java:137) 
zhangsan1

示例二

User user = mapper.getUserAndRoleById(1);
System.out.println(user.getUsername());
System.out.println(user.getRole());

• lazyLoadingEnabled = true

DEBUG 11-07 14:05:18,478 ==>  Preparing: SELECT * FROM t_user where id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:05:18,513 ==> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:05:18,593 <==      Total: 1(BaseJdbcLogger.java:137) 
zhangsan1
DEBUG 11-07 14:05:18,595 ==>  Preparing: select * from t_role where r_id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:05:18,596 ==> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:05:18,598 <==      Total: 1(BaseJdbcLogger.java:137) 
Role{rId=1, rName='普通用户', rRemark='用户中心'}

• lazyLoadingEnabled = false

DEBUG 11-07 14:08:22,557 ==>  Preparing: SELECT * FROM t_user where id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:08:22,593 ==> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:08:22,620 ====>  Preparing: select * from t_role where r_id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:08:22,621 ====> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:08:22,623 <====      Total: 1(BaseJdbcLogger.java:137) 
DEBUG 11-07 14:08:22,626 <==      Total: 1(BaseJdbcLogger.java:137) 
zhangsan1
Role{rId=1, rName='普通用户', rRemark='用户中心'}

单个开启

fetchType="lazy | eager" 优先级高于全局配置

开启（延迟执行） fetchType="lazy"

<association property="role"
             column="roleid"
             fetchType="lazy"
             select="com.learn.mybatis.mapper.RoleMapper.getRole">
</association>

关闭（立即执行） fetchType="eager

<association property="role"
             column="roleid"
             fetchType="eager"
             select="com.learn.mybatis.mapper.RoleMapper.getRole">
</association>

动态SQL

Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。

if

List<User> getUserByCondition(User user);

<select id="getUserByCondition" resultType="User">
    select * from t_user where 1=1
    <if test="id != null and id != ''">
        and id = #{id}
    </if>
    <if test="username != null and username != ''">
        and username = #{username}
    </if>
    <if test="password != null and password != ''">
        and password = #{password}
    </if>
    <if test="roleId != null and roleId != ''">
        and roleid = #{roleId}
    </if>
</select>

where

自动添加where关键字，自动处理where里面的内容

会自动去掉 if 标签内容位于前面多余的and和or等

<select id="getUserByCondition" resultType="User">
    select * from t_user
    <where>
        <if test="id != null and id != ''">
            id = #{id}
        </if>
        <if test="username != null and username != ''">
            and username = #{username}
        </if>
        <if test="password != null and password != ''">
            or password = #{password}
        </if>
        <if test="roleId != null and roleId != ''">
            and roleid = #{roleId}
        </if>
    </where>
</select>

trim

prefix|suffix ： 在trim标签前面或者后面添加指定内容

prefixOverrides|suffixOverrides ： 在trim标签前面或者后面去掉指定内容

<trim prefix="where" suffixOverrides="and|or"> 在前面添加where，去掉后面的and或者or

<select id="getUserByCondition" resultType="User">
    select * from t_user
    <trim prefix="where" suffixOverrides="and|or">
        <if test="id != null and id != ''">
            id = #{id} and
        </if>
        <if test="username != null and username != ''">
            username = #{username} and
        </if>
        <if test="password != null and password != ''">
            password = #{password} and
        </if>
        <if test="roleId != null and roleId != ''">
            roleid = #{roleId} and
        </if>
    </trim>
</select>

choose、when、otherwise

相当于Java的if...else if...else...

满足任意一个when时，立即停止后面的when判断。当所有的when都不满足，使用otherwise的内容

when至少一个

otherwise至多一个

<select id="getUserByCondition" resultType="User">
    select * from t_user
    <where>
        <choose>
            <when test="id != null and id != ''">
                id = #{id}
            </when>
            <when test="username != null and username != ''">
                username = #{username}
            </when>
            <otherwise>
                id = 1
            </otherwise>
        </choose>
    </where>
</select>

forEach

collection 数组或集合

item 每一个数据

open 开始符号

close 结束符号

separator 分隔符

• 示例1

int deleteUserMore(@Param("ids") int[] ids);

<delete id="deleteUserMore">
    delete from t_user where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</delete>

int i = mapper.deleteUserMore(new int[]{16, 17});
System.out.println(i);

DEBUG 11-07 17:07:05,751 ==>  Preparing: delete from t_user where id in ( ? , ? )(BaseJdbcLogger.java:137) 
DEBUG 11-07 17:07:05,796 ==> Parameters: 16(Integer), 17(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 17:07:05,962 <==    Updates: 2(BaseJdbcLogger.java:137) 
2

• 示例2

int insertUserMore(@Param("users") List<User> users);

<insert id="insertUserMore">
    delete from t_user where id in
    <foreach collection="users" item="user" separator=",">
        (null,#{user.username},#{user.password},#{user.roleId},null)
    </foreach>
</insert>

sql

sql片段，需要时引入

设置片段

<sql id="getUserByIdColumns">
    id,username,roleid
</sql>

引用片段

<select id="getUserById" resultType="User">
    select <include refid="getUserByIdColumns"/> from t_user where id = #{id}
</select>

MyBatis缓存

缓存只对查询有效

一级缓存

默认开启

一级缓存是SqlSession级别的， 通过同一个SqISession查询的数据会被缓存，下次查询相同的数据，就会从缓存中
直接获取，不会从数据库重新访问

使一级缓存失效的四种情况:

• 【不同的SqlSession】对应不同的一级缓存

• 同-一个SqlSession但是【查询条件不同】

• 同一个SqlSession两次查询期间执行了任何一次【增删改操作】

• 同一个SqISession两次查询期间【手动清空了缓存】

  sqlSession.clearCache();
  

缓存效果示例

UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user1 = mapper.getUserById(2);
System.out.println(user1);
User user2 = mapper.getUserById(2);
System.out.println(user2);
User user3 = mapper.getUserById(2);
System.out.println(user3);
// 或者
UserMapper mapper1 = sqlSession.getMapper(UserMapper.class);
UserMapper mapper2 = sqlSession.getMapper(UserMapper.class);
UserMapper mapper3 = sqlSession.getMapper(UserMapper.class);
User user1 = mapper1.getUserById(2);
System.out.println(user1);
User user2 = mapper2.getUserById(2);
System.out.println(user2);
User user3 = mapper3.getUserById(2);
System.out.println(user3);

日志

DEBUG 11-07 17:30:58,459 ==>  Preparing: select id,username,roleid from t_user where id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 17:30:58,493 ==> Parameters: 2(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 17:30:58,522 <==      Total: 1(BaseJdbcLogger.java:137) 
User{id=2, username='zhangsan2', password='null', roleId=1, role=null}
User{id=2, username='zhangsan2', password='null', roleId=1, role=null}
User{id=2, username='zhangsan2', password='null', roleId=1, role=null}

失效（两个不同的SqlSession不共享同一个缓存）

SqlSession sqlSession1 = SqlSessionUtils.getSqlSession();
SqlSession sqlSession2 = SqlSessionUtils.getSqlSession();

二级缓存

需要手动开启

二级缓存是SqlSessionFactory级别， 通过同一个SqlSessionFactory创建的SqISession查询的结果会被缓存；此后若再次执行相同的查询语句，结果就会从缓存中获取

二级缓存开启的条件：(四个条件缺一不可)

• 在核心配置文件中，设置全局配置属性cacheEnabled="true",默认为true,不需要设置

• 在映射文件（*Mapper.xml）中设置标签<cache />

  <mapper namespace="">
      <cache/>
  </mapper>
  

• 二级缓存必须在SqlSession关闭或提交之后有效

  sqlSession.commit();
  // 或者
  sqlSession.close();
  

• 查询的数据所转换的实体类类型必须实现序列化的接口

  public class User implements Serializable {}
  

使二级缓存失效的情况：

• 两次查询之间执行了任意的增删改，会使一级和二级缓存同时失效

满足上面四个条件之后，缓存效果示例

InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
SqlSessionFactory ssf = ssfb.build(is);
SqlSession sqlSession1 = ssf.openSession(true);
UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
System.out.println(mapper1.getUserById(1));
sqlSession1.close();
SqlSession sqlSession2 = ssf.openSession(true);
UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
System.out.println(mapper2.getUserById(1));
sqlSession2.close();

DEBUG 11-07 17:58:14,621 Cache Hit Ratio [com.learn.mybatis.mapper.UserMapper]: 0.0(LoggingCache.java:60) 
DEBUG 11-07 17:58:14,831 ==>  Preparing: select id,username,roleid from t_user where id = ?(BaseJdbcLogger.java:137) 
DEBUG 11-07 17:58:14,875 ==> Parameters: 1(Integer)(BaseJdbcLogger.java:137) 
DEBUG 11-07 17:58:14,909 <==      Total: 1(BaseJdbcLogger.java:137) 
User{id=1, username='zhangsan1', password='null', roleId=1, role=null}
WARN  11-07 17:58:14,919 As you are using functionality that deserializes object streams, it is recommended to define the JEP-290 serial filter. Please refer to https://docs.oracle.com/pls/topic/lookup?ctx=javase15&id=GUID-8296D8E8-2B93-4B9A-856E-0A65AF9B8C66(SerialFilterChecker.java:45) 
DEBUG 11-07 17:58:14,922 Cache Hit Ratio [com.learn.mybatis.mapper.UserMapper]: 0.5(LoggingCache.java:60) 
User{id=1, username='zhangsan1', password='null', roleId=1, role=null}

Cache Hit Ratio - 缓存命中率

二级缓存相关配置

在mapper配置文件中添加的cache标签可以设置一些属性:

• eviction：缓存回收策略，默认的是LRU
  • LRU (Least Recently Used) - 最近最少使用的:移除最长时间不被使用的对象。
  • FIFO (First in First out) - 先进先出:按对象进入缓存的顺序来移除它们。
  • SOFT -软引用:移除基于垃圾回收器状态和软引|用规则的对象。
  • WEAK -弱引用:更积极地移除基于垃圾收集器状态和弱引用规则的对象。
• flushInterval：刷新间隔，单位毫秒。默认情况是不设置，也就是没有刷新间隔，缓存仅仅调用语句（非查询/更新）时刷新
• size：引用数目，正整数，代表缓存最多可以存储多少个对象，太大容易导致内存溢出
• readOnly:：只读，true/false
  • true:只读缓存;会给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这提供了很重要的性
    能优势。
  • false:读写缓存;会返回缓存对象的拷贝(通过序列化)。这会慢一-些, 但是安全，因此默认是false。

缓存查询顺序

• 先查询二级缓存，因为二级缓存中可能会有其他程序已经查出来的数据，可以拿来直接使用。（范围大，更可能拿到）
• 如果二级缓存没有命中，再查询一级缓存
• 如果一级缓存也没有命中，则查询数据库
• SqlSession关闭之后，一级缓存中的数据会写入二级缓存;

整合第三方EHCache

配置

第三方只能代替二级缓存，一级缓存没有办法被代替

添加依赖

<!--        缓存-->
<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.2.1</version>
</dependency>

<!--        slf4j日志门面的具体实现-->
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.2.3</version>
</dependency>

resources/ehcache.xml

<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="../config/ehcache.xsd">
    <diskStore path="E:\ehcache" />
    <defaultCache
            maxElementsInMemory="1000"
            maxElementsOnDisk="10000000"
            eternal="false"
            overflowToDisk="false"
            timeToIdleSeconds="120"
            timeToLiveSeconds="120"
            diskExpiryThreadIntervalSeconds="120"
            memoryStoreEvictionPolicy="LRU">
    </defaultCache>
</ehcache>

配置解释

diskStore：指定数据在磁盘中的存储位置。

defaultCache：当借助CacheManager.add(“demoCache”)创建Cache时，EhCache便会采用指定的的管理策略

以下属性是必须的：

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

以下属性是可选的：

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

使用ehcache

resources/ehcache.xml

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

使用slf4j日志

resources/logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false">

    <!--定义日志文件的存储地址 勿在 LogBack 的配置中使用相对路径-->
    <property name="LOG_HOME" value="E:\logback" />

    <!--控制台日志， 控制台输出 -->
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度,%msg：日志消息，%n是换行符-->
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
        </encoder>
    </appender>

    <!--文件日志， 按照每天生成日志文件 -->
    <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <!--日志文件输出的文件名-->
            <FileNamePattern>${LOG_HOME}/TestWeb.log.%d{yyyy-MM-dd}.log</FileNamePattern>
            <!--日志文件保留天数-->
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
        </encoder>
        <!--日志文件最大的大小-->
        <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
            <MaxFileSize>10MB</MaxFileSize>
        </triggeringPolicy>
    </appender>

    <!-- show parameters for hibernate sql 专为 Hibernate 定制 -->
    <logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="TRACE" />
    <logger name="org.hibernate.type.descriptor.sql.BasicExtractor" level="DEBUG" />
    <logger name="org.hibernate.SQL" level="DEBUG" />
    <logger name="org.hibernate.engine.QueryParameters" level="DEBUG" />
    <logger name="org.hibernate.engine.query.HQLQueryPlan" level="DEBUG" />

    <!--myibatis log configure-->
    <logger name="com.apache.ibatis" level="TRACE"/>
    <logger name="java.sql.Connection" level="DEBUG"/>
    <logger name="java.sql.Statement" level="DEBUG"/>
    <logger name="java.sql.PreparedStatement" level="DEBUG"/>

    <!-- 日志输出级别 -->
    <root level="DEBUG">
        <appender-ref ref="STDOUT" />
        <appender-ref ref="FILE"/>
    </root>
    <logger name="com.learn.mybatis.mapper" level="DEBUG"/>
</configuration>

mybatis-generator

根据表生成实体类

配置

pom.xml

<!--        mybatis generator 生成器-->
<dependency>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-core</artifactId>
    <version>1.4.1</version>
</dependency>

<build>
    <plugins>
        <plugin>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-maven-plugin</artifactId>
            <version>1.4.1</version>
            <configuration>
                <verbose>true</verbose>
                <overwrite>true</overwrite>
                <configurationFile>
                    /src/main/resources/generatorConfig.xml
                </configurationFile>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>8.0.28</version>
                </dependency>
                <dependency>
                    <groupId>org.mybatis.generator</groupId>
                    <artifactId>mybatis-generator-core</artifactId>
                    <version>1.4.1</version>
                </dependency>
            </dependencies>
            <configuration>
                <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
                <verbose>true</verbose>
                <overwrite>true</overwrite>
            </configuration>
        </plugin>
    </plugins>
</build>

generatorConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--    targetRuntime : MyBatis3Simple | MyBatis3  简介与带条件的CRUD-->
    <context id="DB2Table" targetRuntime="MyBatis3">
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/xxx"
                        userId="root"
                        password="0101">
        </jdbcConnection>
<!--        bean生成配置-->
        <javaModelGenerator targetPackage="com.learn.mybatis.model" targetProject="src\main\java">
<!--            是否能使用子包 当值为false  com.learn.mybatis.model 表示一个目录-->
            <property name="enableSubPackages" value="true"/>
<!--            去除字符串前后空格 字段名有空格时生成的bean属性名去空-->
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

<!--        SQL映射文件生成配置-->
        <sqlMapGenerator targetPackage="com.learn.mybatis.mapper" targetProject="src\main\resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
<!--        Mapper接口生成配置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.learn.mybatis.mapper" targetProject="src\main\java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

<!--        tableName 为*时 对应所有表 此时不写domainObjectName tableName：表名 domainObjectName：实体名-->
        <table tableName="t_xxx1" domainObjectName="xxx1"/>
        <table tableName="t_xxx2" domainObjectName="xxx2"/>
    </context>
</generatorConfiguration>

MyBatis使用示例

• 示例1

@Test
public void test02() throws IOException {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
    // 查询所有数据
    List<TUser> tUsers = mapper.selectByExample(null);
    tUsers.forEach(System.out::println);
    // 根据条件查询
    TUserExample example = new TUserExample();
    example.createCriteria()
        .andUsernameLike("%123%")
        .andUserstatusEqualTo(1);
    List<TUser> list = mapper.selectByExample(example);
    list.forEach(System.out::println);
	// select id, username, pwd, headImg, createTime, loginTime, userStatus from t_xxx WHERE ( username like ? and userStatus = ? )
}

• 示例2

// 根据条件查询
TUserExample example = new TUserExample();
example.createCriteria()
        .andUsernameLike("%123%")
        .andUserstatusEqualTo(1);
example.or()
        .andUsernameLike("%aa%")
        .andUserstatusEqualTo(10);
List<TUser> list = mapper.selectByExample(example);
list.forEach(System.out::println);

// select id, username, pwd, headImg, createTime, loginTime, userStatus from t_xxx WHERE ( username like ? and userStatus = ? ) or( username like ? and userStatus = ? )

• 示例3

// 根据条件查询
TUser user = new TUser(null,"mybatis","mybatis","","","",3);
mapper.insert(user);// insert into t_xxx (id, username, pwd, headImg, createTime, loginTime, userStatus) values (?, ?, ?, ?, ?, ?, ?)
user.setCreatetime("2022-11-11");
user.setUserstatus(2);
// 有选择更新
mapper.updateByPrimaryKeySelective(user);// update t_xxx SET username = ?, pwd = ?, headImg = ?, createTime = ?, loginTime = ?, userStatus = ? where id = ?

...

分页插件

配置

pom.xml

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.3.0</version>
</dependency>

mybatis-config-xml

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

使用

• 示例1

@Test
public void test() throws IOException {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
    PageHelper.startPage(1,3);
    mapper.selectByExample(null);
}

所执行的SQL

SELECT count(0) FROM t_xxx

select id, username, pwd, headImg, createTime, loginTime, userStatus from t_xxx LIMIT ?

• 示例2

@Test
public void test02() throws IOException {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
    PageHelper.startPage(1,3);
    List<TUser> list = mapper.selectByExample(null);
    // 参数list -> 当前页内容，3 -> 导航显示页码，若当前页为3，则为 2 3 4（3个页码，奇数）
    PageInfo<TUser> pageInfo = new PageInfo<>(list,3);
    System.out.println(pageInfo);
    //PageInfo{pageNum=1, pageSize=3, size=3, startRow=1, endRow=3, total=15, pages=5, list=Page{count=true, pageNum=1, pageSize=3, startRow=0, endRow=3, total=15, pages=5, reasonable=false, pageSizeZero=false}[TUser{id=23, username='dsadsa11', pwd='', headimg='', createtime='', logintime='', userstatus=1}...], prePage=0, nextPage=2, isFirstPage=true, isLastPage=false, hasPreviousPage=false, hasNextPage=true, navigatePages=3, navigateFirstPage=1, navigateLastPage=3, navigatepageNums=[1, 2, 3]}
}

PageInfo结构