MyBatis使用说明与总结

MyBatis使用文档

1、开篇两个小知识点

• 针对MyBatis的配置文件没有提示的解决方案（引入dtd约束）：利用解压缩文件打开mybatis.jar，解压出来org.apache.ibatis.builder.xml中的两个dtd文件，如http://mybatis.org/dtd/mybatis-3-config.dtd和http://mybatis.org/dtd/mybatis-3-mapper.dtd，选择解压出来的dtd文件，然后重新打开配置文件即可
  

• 利用logback在控制台打印sql日志

//在pom.xml引入logback日志依赖
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.2.3</version>
    <scope>test</scope>
</dependency>

//在src/main/resources目录下，引入logback配置文件
<?xml version="1.0" encoding="UTF-8"?>
<configuration
    xmlns="http://ch.qos.logback/xml/ns/logback"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://ch.qos.logback/xml/ns/logback https://raw.githubusercontent.com/enricopulatzo/logback-XSD/master/src/main/xsd/logback.xsd">

    <!-- 输出到控制台 -->
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender" >

        <!-- 输出的格式 -->
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50}:  %msg%n</pattern>
        </encoder>
    </appender>

    <!--Mapper接口所在的包名-->
    <logger name="com.security.dao" level="DEBUG" />

    <root level="INFO">
        <appender-ref ref="STDOUT" />
    </root>

</configuration>

2、MyBatis的简单测试使用

• MySQL数据库资源配置文件db.properties

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

• MyBatis全局配置文件

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

    <!-- mybatis可以引入外部的配置文件 resource:引入类路径下的资源 url:引入网络路径或者磁盘路径下的资源 -->
    <properties resource="db.properties"></properties>

    <settings>
        <!-- 开启驼峰命名 -->
        <setting name="mapUnderscoreToCamelCase" value="true" />
        <!-- 懒加载,当开启时关联的对象都会延迟加载;在特定的关联关系中可通过设置fetchType属性来覆盖该状态 -->
        <setting name="lazyLoadingEnabled" value="true" />
        <!--当开启时,懒加载时会加载该对象的所有属性;否则,每个属性按需加载 -->
        <setting name="aggressiveLazyLoading" value="false" />
        <!-- 开启缓存 -->
        <setting name="cacheEnabled" value="true" />
        <!-- 主键自增长 -->
        <setting name="useGeneratedKeys" value="true" />
    </settings>

    <typeAliases>
        <!-- 配置包下的实体类Bean可以使用别名 -->
        <package name="com.security.model" />
    </typeAliases>

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

    <mappers>
        <!--映射方式一:将包内的映射器接口实现全部注册为映射器(请注意:必须在src/main/resources目录下建立与Mapper接口相同的目录结构)-->
        <package name="com.security.dao" />
        <!--映射方式二:使用相对于类路径的资源引用
        <mapper resource="com/security/dao/UserMapper.xml"/> -->
        <!--映射方式三:使用映射器接口实现类的完全限定类名
        <mapper class="com.security.dao.UserMapper"/> -->
    </mappers>

</configuration>

• 在src/test/java目录下建立JUnit测试类，获取SqlSessionFactory，并且测试使用

public class MyBatisTest {

    private SqlSessionFactory sqlSessionFactory;

    /**
     * 获取SqlSessionFactory
     */
    @Before
    public void getSessionFactory(){
        String resource = "mybatis-config.xml";
        try {
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory= new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 测试UserMapper
     */
    @Test
    public void testUserMapper() {
        SqlSession session=sqlSessionFactory.openSession();
        UserMapper userMapper=session.getMapper(UserMapper.class);
        User user=userMapper.findEntityById(1);
        System.out.println(user);
    }
}

3、MyBatis参数处理

• #{}和${}的区别：#{}是以预编译的形式，将参数设置到sql语句中,PreparedStatement防止sql注入；${}取出的值直接拼装在sql语句中,可能出现安全问题；大多情况下，建议使用#{}，特殊情况除外，如定义结果的排序order by ${}

public List<Employee> getEmployeeOrderBy(@Param("name")String name);

<select id="getEmployeeOrderBy" resultType="Employee">
   select * from Employee order by ${name} desc;
</select>
//sql语句为：select * from Employee order by id desc; 

<select id="getEmployeeOrderBy" resultType="Employee">
   select * from Employee order by #{name} desc;
</select>
//sql语句为:select * from Employee order by ? desc; 

• 使用Insert语句时主键自增长：对于mysql获取自增主键的值，需要在insert方法中利用两个属性：useGeneratedKeys="true"使用自增主键获取主键值策略；keyProperty指定对应的主键属性，也就是mybatis获取到主键值以后，将这个值封装给JavaBean的哪个属性

<insert id="addEmployee" parameterType="employee"
    useGeneratedKeys="true"  keyProperty="id">
    insert into employee(name,gender,email) values(#{name},#{gender},#{email});
</insert>

• 在sql语句中只需要一个参数的情况：mybatis不会做特殊处理，#{参数名/任意名}即可取出参数值

public void add(User user);

<!-- parameterType：参数类型，可以省略， 
    获取自增主键的值：
    useGeneratedKeys="true"：使用自增主键获取主键值策略
    keyProperty：指定对应的主键属性,也就是mybatis获取到主键值以后,将这个值封装给javaBean的哪个属性
-->
<insert id="addUser" useGeneratedKeys="true" keyProperty="id" >
    insert into user(name,age) values(#{name},#{age})
</insert>

• 在sql语句中需要两个或者以上参数的情况：

public void addByUserAndAge(String name,int age);

<insert id="addByUserAndAge">
    insert into user(name,age) values(#{0},#{1})
</insert>

Or
<insert id="addByUserAndAge">
    insert into user(name,age) values(#{param1},#{param2})
</insert>

Or将接口中的方法改为:
public void addByUserAndAge(@Param("name")String name,@Param("age")int age);

<insert id="addByUserAndAge">
    insert into user(name,age) values(#{name},#{age})
</insert>

• MyBatis种sql语句多参数处理时会把多个参数封装为一个map，key：param1...paramN,或者参数的索引也可以；value：传入的参数值#{}就是从map中获取指定的key的值

①public User getUserByMap(Map<String,Object> map);

<select id="getUserByMap" resultType="user">
    select * from user where id=#{id} and name=#{name}
</select>

测试：Map<String,Object> map=new HashMap<>();
    map.put("id",1);
    map.put("name","冯朗");
    System.out.println(mapper.getUserByMap(map));

---------------------------------------------------------------------------
②public Employee getEmp(@Param("id")Integer id,String lastName);
 取值：id==>#{id/param1}   lastName==>#{param2}


------------------------------------------------------------------------
③public Employee getEmp(Integer id,@Param("e")Employee emp);
取值：id==>#{param1}    lastName===>#{param2.lastName/e.lastName}

----------------------------------------------------------------
④##特别注意：如果是Collection（List、Set）类型或者是数组，也会特殊处理。也是把传入的list或者数组封装在map中。
    key：Collection（collection）,如果是List还可以使用这个key(list)
    数组(array)
public Employee getEmpById(List<Integer> ids);
取值：取出第一个id的值： #{list[0]}

• MyBatis的两个内置参数_parameter和_databaseId，其中_parameter代表整个参数，当sql方法需要单个参数时_parameter即为该参数，若需要多个参数时，参数会被封装为一个map,_parameter即代表这个map；_databaseId:如果配置了databaseIdProvider标签，则_databaseId就是代表当前数据库的别名

public List<User> getUsersWithParameter(String name);

<select id="getUsersWithParameter" resultType="user">
    select * from user 
    <where>
        name like #{_parameter}
    </where>
</select>

----------------------------------------------------------------------

public List<User> getUsersWithParameterBean(Map<String,Object> map);

<select id="getUsersWithParameterBean"  resultType="user">
    select * from user 
    <where>
        <if test="_parameter.name!=null and !_parameter.name.equals(&quot;&quot;)">
            name like #{_parameter.name}
        </if>
        <if test="_parameter.age!=null">
            or age = #{_parameter.age}
        </if>
    </where>
</select>

• bind和sql标签的使用：bind可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值；sql用于抽取可重用的片段，方便后期引用，include用于引用已抽取的sql片段

public List<User> getUsersWithBinds(@Param("name")String name);

<sql id="selectuser" >
    select id,name,age from user
</sql>

<select id="getUsersWithBinds" resultType="user">
    <bind name="myName" value="'%'+name+'%'"/>
    <include refid="selectuser"></include>
        <where>
            <if test="_parameter!=null">
                name like #{myName}
            </if>
        </where>
</select>

4、MyBatis中的动态SQL

• where、if的使用

public List<User> getUserByConditionIf(User user);

<select id="getUserByConditionIf" resultType="user">
    select * from user
    <where>
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="name!=null and !&quot;&quot;.equals(name)">
            and name like #{name}
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
    </where>
</select>

• trim的使用

public List<User> getUserByConditionTrim(User user);


<!-- 后面多出的and或者or where标签不能解决 
    prefix="" 前缀：trim标签体中是整个字符串拼串后的结果。
            prefix给拼串后的整个字符串加一个前缀 
    prefixOverrides=""
            前缀覆盖：去掉整个字符串前面多余的字符
    suffix=""后缀
            suffix给拼串后的整个字符串加一个后缀 
    suffixOverrides=""
            后缀覆盖：去掉整个字符串后面多余的字符

 -->
 <!-- 自定义字符串的截取规则 -->

<select id="getUserByConditionTrim" resultType="user">
    select * from user
    <trim prefix="where" suffixOverrides="or">
        <if test="id!=null">
            id=#{id} or
        </if>
        <if test="name!=null and !name.equals(&quot;&quot;)">
            name like #{name} or
        </if>
        <if test="age!=null">
            age = #{age}
        </if>
    </trim>
</select>

• set的使用

public void updateEmp(User user);

<update id="updateEmp">
    update user
    <set>
        <if test="name!=null and !name.equals(&quot;&quot;)">
            name=#{name},
        </if>
        <if test="age!=null">
            age=#{age}
        </if>
    </set>
    <where>
        id=#{id}
    </where>
</update>

• foreach的使用

方式一：
public List<User> getUserByConditionForeach(List<Integer> list);

<select id="getUserByConditionForeach"  resultType="user">
    select * from user

    <!--
        collection：指定要遍历的集合：
            list类型的参数会特殊处理封装在map中，map的key就叫list
        item：将当前遍历出的元素赋值给指定的变量
        separator:每个元素之间的分隔符
        open：遍历出所有结果拼接一个开始的字符
        close:遍历出所有结果拼接一个结束的字符
        index:索引。遍历list的时候是index就是索引，item就是当前值
                      遍历map的时候index表示的就是map的key，item就是map的值
        #{变量名}就能取出变量的值也就是当前遍历出的元素
      -->
    <foreach collection="list" item="item" separator="," open="where id in("
        close=")">
        #{item}
    </foreach>
</select>

• 批量插入数据

public void addByBatch(List<User> list);

<insert id="addByBatch">
    insert into user(name,age,did) values
    <foreach collection="list" item="u" separator=",">
        (#{u.name},#{u.age},#{u.department.id})
    </foreach>
</insert>

---------------------------------------------------------------------------------------
方式二：
<!-- 本种方式需要数据库连接属性allowMultiQueries=true：这种分号分隔多个sql可以用于其他的批量操作（删除，修改）,如：
    jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true -->

<insert id="saveEmployeeWithForEach" >
   <foreach collection="list" item="emp">
       insert into employee(name,gender,email,d_id) values(#{emp.name},#{emp.gender},#{emp.email},#{emp.dept.id});
   </foreach>
</insert>

5、MyBatis中的模糊查询

• 常规方式：利用bind进行数据绑定

public List<Employee> getEmployeesWithNameLike(@Param("name")String name);

<select id="getEmployeesWithNameLike" resultType="employee">
    //绑定数据
    <bind name="pattern" value="'%'+name+'%'"></bind>

    select * from employee
    <where>
        <if test="name!=null">
            name like #{pattern}
        </if>
    </where>

</select>

• 利用MyBatis的内置函数_parameter

public List<Employee> testInnerParameter(String name);

<select id="testInnerParameter" resultType="employee">

    <if test="_parameter!=null">
        <bind name="pattern" value="'%'+_parameter+'%'"/>
    </if>

    select * from employee 
    <where>
        <if test="_parameter!=null">
            name like #{pattern}
        </if>
    </where>

</select>

• 利用MySQL的内置函数concat(str1,str2,…)

public List<Employee> getEmployeesWithNameLike(@Param("name")String name);
    select * from employee
    <where>
        <if test="name!=null">
            name like concat('%',#{name},'%')
        </if>
    </where>
</select>

• 升级版模糊查询

public List<Employee> getEmployeeWithUncertainQuery(String query);

<select id="getEmployeeWithUncertainQuery" resultType="employee">

    <if test="_parameter!=null">
        <bind name="param" value="'%'+_parameter+'%'"/>
    </if>

    select * from employee
    <where>
        <if test="_parameter!=null">
            email like #{param}
        </if>
        <if test="_parameter!=null">
            or name like #{param}
        </if>
    </where>

</select>

6、MyBatis的级联查询

6.1场景一：Employee对应一个Department，查询Employee的同时查询员工对应的部门

• 使用association进行单步查询，association代表的是一对一关系

<!-- 
    使用association定义关联的单个对象的封装规则
 -->
<resultMap type="com.security.ben.Employee" id="MyEmp1">
    <id column="id" property="id" />
    <!-- 定义普通列封装规则,其中column代表mysql中表的列名,property代表实体类的属性-->
    <result column="name" property="name" />
    <result column="gender" property="gender" />
    <result column="email" property="email" />
    <association property="dept" javaType="com.security.ben.Department">
        <id column="id" property="id" />
        <!-- 定义普通列封装规则 -->
        <result column="deptName" property="deptName" />
    </association>
</resultMap>

<select id="getEmpAndDept" resultMap="MyEmp1">
    select e.id id,e.name
    name,e.email email,e.gender gender,e.d_id d_id,d.id did,d.deptName
    deptName from employee e,department d
    where e.d_id=d.id and e.id=#{id}
</select>

• 使用association进行分布查询

<!-- 使用association进行分步查询：
    1、先按照员工id查询员工信息
    2、根据查询员工信息中的d_id值去部门表查出部门信息
    3、部门设置到员工中；
 -->

<resultMap type="com.security.ben.Employee" id="MyEmp2">
    <id column="id" property="id" />
    <!-- 定义普通列封装规则 -->
    <result column="name" property="name" />
    <result column="gender" property="gender" />
    <result column="email" property="email" />
    <!-- association定义关联对象的封装规则
         select:表明当前属性是调用select指定的方法查出的结果
         column:指定将哪一列的值传给这个方法
         流程：使用select指定的方法（传入column指定的这列参数的值）查出对象，并封装给property指定的属性
      -->
    <association property="dept" column="d_id"     select="com.security.mapper.DepartmentMapper.getDeptById">
    </association>
</resultMap>


<select id="getEmpByStep" resultMap="MyEmp2">
    select * from employee where id=#{id}
</select>

6.2场景二：一个Department对应多个Employee，查询部门信息的时候将其多对应的所有员工信息也查询出来

• 使用collection单步查询，collection代表一对多关系

<!--嵌套结果集的方式，使用collection标签定义关联的集合类型的属性封装规则  -->
<resultMap type="com.security.ben.Department" id="SimpleDept">
    <id column="id" property="id"/>
    <result column="deptName" property="deptName"/>
    <collection property="emps" ofType="com.security.ben.Employee">
        <id column="eid" property="id"/>
        <result column="name" property="name"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </collection>

</resultMap>

<select id="getDeptByIdPlus" resultMap="SimpleDept">
    select d.id id,d.deptName deptName,e.id eid,e.name name,e.email email,e.gender gender
     from department d left join employee e 
     on d.id=e.d_id where d.id=#{id}
</select>

• 使用collection进行分步查询

<!-- 扩展：多列的值传递过去：
       将多列的值封装map传递；
       column="{key1=column1,key2=column2}"
   fetchType="lazy"：表示使用延迟加载；
           - lazy：延迟
           - eager：立即
-->
<resultMap type="com.security.ben.Department" id="DifDept">
   <id column="id" property="id"/>
   <result column="deptName" property="deptName"/>
   <association property="emps" select="com.security.mapper.EmployeeMapperPlus.getEmpsByDeptId" column="id"  fetchType="lazy"></association>
</resultMap>


<select id="getDeptByIdStep" resultMap="DifDept">
   select * from department where id=#{id}
</select>

<!--com.security.mapper.EmployeeMapperPlus中的方法-->
<select id="getEmpsByDeptId" resultType="employee">
   select * from employee where d_id=#{d_id}
</select>

6.3场景三：用户和角色属于多对多关联关系，参考MyBatis多对多关联查询

• 建立数据表，use和role是多对多的关系，需要采用关联表t_user_role，建立相应的实体类

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer id;

    private String name;

    private String email;

    private List<Role> roles;
}


@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Role {

    private Integer id;
    private String name;
    private List<User> users;

}

• 建立UserMapper和RoleMapper的接口，并定义相应的方法

public interface UserMapper {

    public User findUserById(Integer id);

    public List<User> findUserByRoleId(Integer roleId);

}

public interface RoleMapper {

    public Role findRoleById(Integer id);

    public List<Role> findRoleByUserId(Integer userId);

}

• 建立相应的UserMppaer.xml，并且定义sql语句

<?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.security.dao.UserMapper">

    <resultMap id="userMapper" type="user">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="email" property="email" />
        <collection property="roles" column="id"
            select="com.security.dao.RoleMapper.findRoleByUserId"></collection>
    </resultMap>

    <select id="findUserById" resultMap="userMapper">
        select id,name,email 
        from user where id=#{id}
    </select>

    <select id="findUserByRoleId" resultType="user">
        select a.id,a.name,a.email
        from user a,t_user_role b
        where a.id=b.userId and
        b.roleId=#{roleId}
    </select>

    <!-- <select id="findUserByRoleId" resultType="user">
        select id,name,email 
        from user 
        where id in
        (select userId from t_user_role where roleId=#{roleId})
    </select> -->

</mapper>

• 建立相应的RoleMppaer.xml，并且定义sql语句

<?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.security.dao.RoleMapper">

    <resultMap id="roleMapper" type="role">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <collection property="users" column="id"
            select="com.security.dao.UserMapper.findUserByRoleId"></collection>
    </resultMap>

    <select id="findRoleById" resultMap="roleMapper">
        select id,name from role
        where id=#{id}
    </select>

    <select id="findRoleByUserId" resultType="role">
        select a.id,a.name
        from role a,t_user_role b
        where a.id=b.roleId and b.userId=#{userId}
    </select>

    <!-- <select id="findRoleByUserId" resultType="role">
        select id,name
        from role 
        where id in
        (select roleId from t_user_role where userId=#{userId})
    </select> -->

</mapper>

• 多对多关联关系测试

public class MoreToMoreTest {

    private SqlSessionFactory sqlSessionFactory;

    /**
     * 获取SqlSessionFactory
     */
    @Before
    public void getSessionFactory() {
        String resource = "mybatis-config.xml";
        try {
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testMoreToMore() {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            /*RoleMapper roleMapper =      session.getMapper(RoleMapper.class);
            Role role = roleMapper.findRoleById(1);
            System.out.println(role);*/
            UserMapper userMapper=session.getMapper(UserMapper.class);
            User user=userMapper.findUserById(2);
            System.out.println(user);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null) {
                session.close();
            }
        }
    }
}