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)

接口参数为对象

 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结构

posted @ 2022-11-09 06:05 夏末秋初~ 阅读(33) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· MyBatis-Plus

· SSM 整合

· Mybatis学习笔记

· MyBatis

· MyBatis-9

阅读排行：
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 字符编码：从基础到乱码解决
· 提示词工程——AI应用必不可少的技术

公告

昵称：夏末秋初~
园龄： 4年8个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

随笔档案

阅读排行榜

评论排行榜

1. VirtualBox虚拟机与主机之间双向复制粘贴(1)

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

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

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

	<?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>

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

	<?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>

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

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

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

	<?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

	<!-- 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>

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

	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

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

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

	parameters: zhangsan(String)

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

Mybatis

获取

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

二、Maven引入

起步

添加依赖

配置mybatis-config.xml

示例

测试

事务自动提交

引入log4j

*Mapper.xml

mybatis-config.xml

jdbc.properties

模板设置

获取参数值

接口参数为对象

多种查询

批量删除

获取自增主键

类属性名与列名保持一致

处理n:1映射关系

处理1:n映射关系

延迟加载

全局开启

单个开启

动态SQL

if

where

trim

choose、when、otherwise

forEach

sql

MyBatis缓存

一级缓存

二级缓存

二级缓存相关配置

缓存查询顺序

整合第三方EHCache

配置

配置解释

使用ehcache

使用slf4j日志

mybatis-generator

配置

MyBatis使用示例

分页插件

配置

使用

PageInfo结构

公告

搜索

常用链接

随笔档案

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

	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>

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

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

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

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

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

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

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