3、MyBatis 映射文件

该栏目会系统的介绍 Redis 的知识体系，共分为相关概念、操作指令、主从复制等模块

---

---

基本结构

---

语句操作标签

描述	标签
添加标签	insert
更新标签	update
删除标签	delete
查询标签	select
# 条件标签	where
# 判断标签	if
# 循环标签	foreach
查询标签	select
SQL片段标签	sql
结果集标签	resultMap

---

案例

• 表相关SQL

<!--用户表-->
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户Id',
  `username` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `birth` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

<!--订单表-->
CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单主键',
  `user_id` int(11) DEFAULT NULL COMMENT '用户主键',
  `order_code` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '订单号',
  `total` double(11,2) DEFAULT NULL COMMENT '订单总额',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

<!--权限表-->
CREATE TABLE `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色Id',
  `role_name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '角色名称',
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

<!--用户权限表-->
CREATE TABLE `user_role` (
  `user_id` int(11) NOT NULL COMMENT '用户Id',
  `role_id` int(11) DEFAULT NULL COMMENT '角色Id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

• 实体类

/**
 * 用户实体
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {

    private int userId;

    private String username;

    private int age;

    private Date birth;

    private List<Order> orders;

    private List<Role> roles;

    public User(String username, int age, Date birth) {
        this.username = username;
        this.age = age;
        this.birth = birth;
    }

}

/**
 * 订单实体
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Order implements Serializable {

    private int orderId;

    private String orderCode;

    private double total;

    private User user;

}

/**
 * 角色实体
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Role implements Serializable {

    private int roleId;

    private String roleName;

}

• UserMapper.java接口

/**
 * 用户操作接口
 */
public interface UserMapper {

    void save(User user);

    void update(User user);

    void deleteById(int id);

    List<User> findAll();

    User findById(int id);

    List<User> findByUsername(String username);

    List<User> findByIds(List<Integer> ids);

    List<Order> findOrderAndUser();

    List<User> findUserAndOrders();

    List<User> findUserAndRoles();
    
}

• 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.itnear.mapper.UserMapper">

    <!--插入用户-->
    <insert id="save" useGeneratedKeys="true" keyProperty="userId" parameterType="user">
        insert into users(username,age,birth) values(#{username},#{age},#{birth})
    </insert>

    <!--更新用户-->
    <update id="update" parameterType="user">
        update users set username = #{username} where user_id = #{userId}
    </update>

    <!--根据Id删除用户-->
    <delete id="deleteById" parameterType="int">
        delete from users where user_id = #{userId}
    </delete>

    <!--查询所有用户-->
    <select id="findAll" resultType="user">
        select * from users;
    </select>

    <!--根据Id查询单个用户-->
    <select id="findById" resultType="user" parameterType="int">
        select * from users where user_id = #{userId}
    </select>

    <!--    -->

    <!--封装公共SQL片段-->
    <sql id="selectUser">
        select * from users
    </sql>

    <!--根据用户名模糊查询用户-->
    <select id="findByUsername" parameterType="string" resultType="user">
        <include refid="selectUser"/>
        <where>
            <if test="username != null">
                username like concat('%',#{username},'%')
            </if>
        </where>
    </select>

    <!--根据id集合查询相关用户-->
    <select id="findByIds" parameterType="list" resultType="user">
        <include refid="selectUser"/>
        <where>
            <foreach collection="list" item="userId" separator="," open="user_id in(" close=")">
                #{userId}
            </foreach>
        </where>
    </select>

    <!--一多一查询：查询订单及其所属用户-->
    <resultMap id="orderMap" type="order">
        <id column="order_id" property="orderId"/>
        <result column="order_code" property="orderCode"/>
        <result column="total" property="total"/>
        <association property="user" javaType="user">
            <id column="user_id" property="userId"/>
            <result column="username" property="username"/>
            <result column="age" property="age"/>
            <result column="birth" property="birth"/>
        </association>
    </resultMap>
    <select id="findOrderAndUser" resultMap="orderMap">
        select * from orders o,users u where o.user_id = u.user_id
    </select>

    <!--一对多查询：查询用户及其订单信息-->
    <resultMap id="userMap" type="user">
        <id column="user_id" property="userId"/>
        <result column="username" property="username"/>
        <result column="age" property="age"/>
        <result column="birth" property="birth"/>
        <collection property="orders" ofType="order">
            <id column="order_id" property="orderId"/>
            <result column="order_code" property="orderCode"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>
    <select id="findUserAndOrders" resultMap="userMap">
        select * from users u,orders o where u.user_id = o.user_id
    </select>

    <!--多对多：查询用户及其权限列表-->
    <resultMap id="userRoleMap" type="user">
        <id column="user_id" property="userId"/>
        <result column="username" property="username"/>
        <result column="age" property="age"/>
        <result column="birth" property="birth"/>
        <collection property="roles" ofType="role">
            <id column="role_id" property="roleId"/>
            <result column="role_name" property="roleName"/>
        </collection>
    </resultMap>
    <select id="findUserAndRoles" resultMap="userRoleMap">
        select * from users u,user_role ur,roles r where u.user_id = ur.user_id and ur.role_id = r.role_id
    </select>
    
</mapper>

• 测试类

/**
 * 项目启动类：配置方式
 */
public class ApplicationRunner {

    public static void main(String[] args) throws IOException {
        final InputStream stream = Resources.getResourceAsStream("mybatis-config.xml");
        final SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(stream);
        final SqlSession sqlSession = sessionFactory.openSession();

        basicSqlMapper(sqlSession);
        advancedSqlMapper(sqlSession);
    }

    /**
     * 基础SQL测试
     */
    private static void basicSqlMapper(SqlSession sqlSession) {
        final UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        // 添加用户
        final User user = new User("NearJC", 18, new Date());
        userMapper.save(user);
        sqlSession.commit();  // 提交事务

        // 修改用户名
        user.setUsername("NearBJ");
        userMapper.update(user);
        sqlSession.commit();

        // 查询单个用户
        final User dbUser = userMapper.findById(1);
        System.out.println("用户信息：" + user);

        // 查询所有用户
        final List<User> users = userMapper.findAll();
        for (User u : users) {
            System.out.println(u);
        }

        // 删除用户
        // userMapper.deleteById(user.getUserId());
        // sqlSession.commit();
    }

    /**
     * 高级SQL映射
     */
    private static void advancedSqlMapper(SqlSession sqlSession) {
        final UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 根据用户名模糊查询用户
        List<User> users = userMapper.findByUsername("Near");
        for (User user : users) {
            System.out.println("模糊查询到的用户信息：" + user);
        }

        // 根据id集合查询相关用户
        users = userMapper.findByIds(Arrays.asList(1, 2, 3, 4, 5));
        for (User user : users) {
            System.out.println("d集合查询到的用户信息：" + user);
        }

        // 一对一查询：查询订单及所属用户信息
        List<Order> orders = userMapper.findOrderAndUser();
        for (Order order : orders) {
            System.out.println("订单及用户信息" + order);
        }

        // 一对多查询：查询用户及相关订单
        users = userMapper.findUserAndOrders();
        for (User user : users) {
            System.out.println("用户及订单信息：" + user);
        }

        // 多对多查询：查询用户及相关角色
        users = userMapper.findUserAndRoles();
        for (User user : users) {
            System.out.println("用户及权限信息：" + user);
        }
    }

}