MyBatis快速上手

测试环境

## 人员表
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

## 部门表
CREATE TABLE `dept` (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

基础增删改查

<?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.example.mybatis.mapper.UserMapper">

    <!--void insertUser(User user); -->
    <insert id="insertUser">
        insert into user values (null,#{userName},#{sex},#{age},#{deptId})
    </insert>

    <!--void deleteUser(@Param("userId") Integer userId);-->
    <delete id="deleteUser">
        delete from user where user_id = #{userId}
    </delete>

    <!--void updateUser(@Param("userId") Integer userId);-->
    <update id="updateUser">
        update user set sex = '女' where user_id = #{userId}
    </update>

    <!--List<User> selectUserList();-->
    <select id="selectUserList" resultType="User">
        select * from user
    </select>
</mapper>

@Param注解

只要参数不是实体类对象,统统都用@Param;

void updateUser(@Param("userId") Integer userId , @Param("userName") String userName);

MyBatis重要配置

	# 将下划线自动映射为驼峰
    map-underscore-to-camel-case: true
    # 开启延迟加载
    lazy-loading-enabled: true

resultType 和 resultMap的区别

做查询操作时,一定要带上 resultType 或 resultMap

resultType 设置默认的映射关系,比如查询单一实体类

resultMap 设置自定义的映射关系,比如一对多,多对一的查询操作

模糊查询

    <!--User selectUserByLike(@Param("userName") String userName);-->
    <select id="selectUserByLike" resultType="User">
        select * from user where user_name like "%"#{userName}"%"
    </select>

获取新增时自增的主键

    <!--void insertUser(User user);-->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="userId">
        insert into user values (null,#{userName},#{sex},#{age},#{deptId})
    </insert>

对多一解决方案

多对一对应对象

通过级联属性赋值

@Data
public class User {

    private Integer userId;
    private String userName;
    private String sex;
    private Integer age;
    private Integer deptId;
	
	//新增Dept对象属性
    private Dept dept;
}

<mapper namespace="com.example.mybatis.mapper.UserMapper">
    <resultMap id="selectUserAndDept" type="User">
        <id property="userId" column="user_id"></id>
        <result property="userName" column="user_name"></result>
        <result property="sex" column="sex"></result>
        <result property="age" column="age"></result>
        <result property="deptId" column="dept_id"></result>
        <result property="dept.deptId" column="dept_id"></result>
        <result property="dept.deptName" column="dept_name"></result>
    </resultMap>

    <!--List<User> selectListByMap();-->
    <select id="selectListByMap" resultMap="selectUserAndDept">
        select * from user left join dept on user.dept_id = dept.dept_id
    </select>
</mapper>

通过association 解决多对一

@Data
public class User {

    private Integer userId;
    private String userName;
    private String sex;
    private Integer age;
    private Integer deptId;
	
	//新增Dept对象属性
    private Dept dept;
}

<mapper namespace="com.example.mybatis.mapper.UserMapper">
 <resultMap id="selectUserAndDept" type="User">
        <id property="userId" column="user_id"></id>
        <result property="userName" column="user_name"></result>
        <result property="sex" column="sex"></result>
        <result property="age" column="age"></result>
        <result property="deptId" column="dept_id"></result>
        <association property="dept" javaType="Dept">
            <id property="deptId" column="dept_id"></id>
            <result property="deptName" column="dept_name"></result>
        </association>
    </resultMap>

    <!--List<User> selectListByMap();-->
    <select id="selectListByMap" resultMap="selectUserAndDept">
        select * from user left join dept on user.dept_id = dept.dept_id
    </select>
</mapper>

通过分步查询解决多对一

@Data
public class User {

    private Integer userId;
    private String userName;
    private String sex;
    private Integer age;
    private Integer deptId;
	
	//新增Dept对象属性
    private Dept dept;
}

<mapper namespace="com.example.mybatis.mapper.UserMapper">
    <resultMap id="selectUserAndDept" type="User">
        <id property="userId" column="user_id"></id>
        <result property="userName" column="user_name"></result>
        <result property="sex" column="sex"></result>
        <result property="age" column="age"></result>
        <result property="deptId" column="dept_id"></result>
        <association property="dept"
                     select="com.example.mybatis.mapper.DeptMapper.stepTwo"
                     column="dept_id">
        </association>
    </resultMap>

    <!--User stepOne(@Param("userId") Integer userId);-->
    <select id="stepOne" resultMap="selectUserAndDept">
        select * from user where user_id = #{userId}
    </select>
</mapper>

<mapper namespace="com.example.mybatis.mapper.DeptMapper">

    <!--Dept stepTwo(@Param("deptId") String deptId);-->
    <select id="stepTwo" resultType="Dept">
        select * from dept where dept_id = #{deptId}
    </select>
</mapper>

一对多解决方案

一对多对应集合

通过collection 解决一对多

@Data
public class Dept {

    private Integer deptId;
    private String deptName;
    
    //一个部门下有多个用户,新增用户集合
    List<User> userList;
}

<mapper namespace="com.example.mybatis.mapper.DeptMapper">
    <resultMap id="deptAndUser" type="Dept">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="userList" ofType="User">
            <id property="userId" column="user_id"></id>
            <result property="userName" column="user_name"></result>
            <result property="sex" column="sex"></result>
            <result property="age" column="age"></result>
            <result property="deptId" column="dept_id"></result>
        </collection>
    </resultMap>
    
    <!--Dept findUserByDept(@Param("deptId") Integer deptId);-->
    <select id="findUserByDept" resultMap="deptAndUser">
        select * from dept left join user on dept.dept_id = user.dept_id where dept.dept_id = #{deptId}
    </select>
</mapper>

通过分步查询解决一对多

@Data
public class Dept {

    private Integer deptId;
    private String deptName;
    
    //一个部门下有多个用户,新增用户集合
    List<User> userList;
}

<mapper namespace="com.example.mybatis.mapper.DeptMapper">
    <resultMap id="deptAndUser" type="Dept">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="userList"
                    select="com.example.mybatis.mapper.UserMapper.stepTwo"
                    column="dept_id">
        </collection>
    </resultMap>
    
    <!--Dept findUserByDept(@Param("deptId") Integer deptId);-->
    <select id="stepOne" resultMap="deptAndUser">
        select * from dept where dept_id = #{deptId}
    </select>
</mapper>

<mapper namespace="com.example.mybatis.mapper.UserMapper">

    <!--List<User> stepTwo(@Param("deptId") Integer deptId);-->
    <select id="stepTwo" resultType="User">
        select * from user where dept_id = #{deptId}
    </select>
</mapper>

动态SQL

where 标签和 if 标签

<mapper namespace="com.example.mybatis.mapper.UserMapper">

    <!--List<User> findUser(User user);-->
    <select id="findUser" resultType="User">
        select * from user
        <where>
            <if test="userId != null and userId != ''">
                user_id = #{userId}
            </if>
            <if test="userName != null and userName !=''">
                and user_name = #{userName}
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
            <if test="age != null and age !=''">
                and age = #{age}
            </if>
        </where>
    </select>
</mapper>

trim 标签

prefix|suffix : 将trim标签中的内容前面或者后面添加指定内容

prefixOverrides|suffixOverrides : 将trim标签中的内容前面或者后面删除指定内容

<mapper namespace="com.example.mybatis.mapper.UserMapper">

    <!--List<User> findUser(User user);-->
    <select id="findUser" resultType="User">
        select * from user
        <trim prefix="where" prefixOverrides="and|or">
            <if test="userId != null and userId != ''">
                user_id = #{userId}
            </if>
            <if test="userName != null and userName !=''">
                and user_name = #{userName}
            </if>
            <if test="sex != null and sex != ''">
                or sex = #{sex}
            </if>
            <if test="age != null and age !=''">
                and age = #{age}
            </if>
        </trim>
    </select>
</mapper>

choose 标签,when 标签, otherwise 标签

相当于if....else if的语法

<mapper namespace="com.example.mybatis.mapper.UserMapper">
	<!-- List<User> findUser(User user); -->
    <select id="findUser" resultType="User">
        select * from user
        <where>
            <choose>
                <when test="userId != null and userId != ''">
                    user_id = #{userId}
                </when>
                <when test="userName != null and userName !=''">
                    user_name = #{userName}
                </when>
                <when test="sex != null and sex != ''">
                    sex = #{sex}
                </when>
                <when test="age != null and age !=''">
                    age = #{age}
                </when>
                <otherwise>
                    dept_id = 1
                </otherwise>
            </choose>
        </where>
    </select>
</mapper>

foeach 标签批量删除

delete from user where user_id in (2,3,4);

<mapper namespace="com.example.mybatis.mapper.UserMapper">

    <!--void deleteUids(@Param("uids") Integer[] uids);-->
    <delete id="deleteUids">
        delete from user where user_id in
        <foreach collection="uids" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
    </delete>
</mapper>

delete from user where user_id = ? or user id = ?;

<mapper namespace="com.example.mybatis.mapper.UserMapper">
    <!--void deleteUids(@Param("uids") Integer[] uids);-->
    <delete id="deleteUids">
        delete from user
        <where>
            <foreach collection="uids" item="userId" separator="or">
                user_id = #{userId}
            </foreach>
        </where>
    </delete>
</mapper>

foreach 标签批量添加

<mapper namespace="com.example.mybatis.mapper.UserMapper">
    <!--int insertByList(@Param("userList") List<User> userList);-->
    <insert id="insertByList">
        insert into user values
        <foreach collection="userList" item="user" separator=",">
            (null,#{user.userName},#{user.sex},#{user.age},#{user.deptId})
        </foreach>
    </insert>
</mapper>

sql片段

<mapper namespace="com.example.mybatis.mapper.UserMapper">

    <sql id="findUser">
        select * from user where 1=1
    </sql>

    <!--List<User> findAll();-->
    <select id="findAll" resultType="user">
        <include refid="findUser"></include>
    </select>
</mapper>

原文链接：https://blog.csdn.net/qq_40670787/article/details/123162939