【SpringMVC】MyBatis多表操作与注解开发

作者: abboo | 来源:发表于2020-11-06 23:16 被阅读0次

一、多表操作

1. 一对一查询

想象一下你在淘宝，在购买东西后会有个订单支付的页面，下单后用户和订单就关联起来了，同时订单有一个唯一的编号叫订单号。用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

即 order对user是一对一的，反过来，user对order是一对多的

1.1 建一张order表

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ordertime` bigint(20) DEFAULT NULL,
  `total` double DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

1.2 user和order的一对一查询

select * ,o.id oid from orders o,user u where o.uid=u.id

这个查询结果看起来很怪，有很多个id，不着急，后面我们在进行配置文件的配置的时候可以将id忽略。

1.3 创建Order和User实体类

User和以前一样

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    //代表当前订单从属于哪一个客户  
    private User user;

1.4 创建OrderMapper接口

public interface OrderMapper {
    public List<Order> findAll();
}

1.5 配置OrderMapper.xml

*********** OrderMapper.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="org.example.mapper.OrderMapper">

    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性的映射关系
            column: 数据表ode字段名称
            property: 实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
    select * ,o.id oid from orders o,user u where o.uid=u.id
    </select>
    <insert id="insert" parameterType="order">
        <!--        insert into user (username,password) values(#{username},#{password})-->
        insert into orders values(0,#{ordertime},#{total},#{user.id})
    </insert>
</mapper>

*********** sqlMapperConfig.xml *************
    <!--    自定义别名-->
    <typeAliases>
        <typeAlias type="org.example.domain.User" alias="user"></typeAlias>
        <typeAlias type="org.example.domain.Order" alias="order"></typeAlias>
    </typeAliases>

    <!--        加载映射文件-->
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>        
        <mapper resource="mapper/OrderMapper.xml"/>
    </mappers>

测试

 OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = mapper.findAll();
        System.out.println(orderList);

或者通过
<association>标签配置user

 <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性的映射关系
            column: 数据表ode字段名称
            property: 实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <!--
            property: 当前实体(Order)的属性名称（private User user）
            javaType: 当前实体(Order)中的属性的类型(User)
        -->
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>

2. 一对多查询

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

2.1 user和orders的一对多查询

select *,o.id oid from user u,orders o where u.id = o.id

image.png

2.2 修改User实体

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    private List<Order> orderList;
}

2.3 创建UserMapper

public interface UserMapper {� 
   List<User> findAll();�
}

2.4 配置UserMapper.xml

 <resultMap id="userMap" type="user">
        <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--        配置集合信息：-->
        <!--          property: 集合名称-->
        <!--          ofType: 当前集合中的数据类型-->
        <collection property="OrderList" ofType="order">
            <!--            封装order的数据-->
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>

    </resultMap>
    <!--    userMapper.findAll-->
    <select id="findAll" resultMap="userMap">
        select *,o.id oid from user u,orders o where u.id = o.id
    </select>

2.5 测试

 //一对多测试
    @Test
    public void test7() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.findAll();
        System.out.println("################################################");
        for (User user : userList) {
            System.out.println(user);
        }
       System.out.println("################################################");

        sqlSession.commit();
        sqlSession.close();
    }

打印结果

################################################
User{id=8, username='tom', password='abcd', birthday=Thu Jan 01 08:00:00 CST 1970, orderList=[Order{id=4, ordertime=Thu Nov 05 23:29:26 CST 2020, total=4000.0, user=null}]}
User{id=10, username='lucy', password='hisudhaius', birthday=Thu Jan 01 08:00:00 CST 1970, orderList=[Order{id=5, ordertime=Thu Nov 05 23:29:26 CST 2020, total=4000.0, user=null}, Order{id=8, ordertime=Thu Nov 05 23:32:44 CST 2020, total=3000.0, user=null}]}
User{id=13, username='libai', password='dasnk', birthday=Wed Nov 04 00:18:37 CST 2020, orderList=[Order{id=7, ordertime=Thu Nov 05 23:29:26 CST 2020, total=5000.0, user=null}]}
################################################

多对多查询

再来假设一个场景，小明是一个学生，他同时是班干部和学生会的干部，而学生会干部可以有多个学生，这样学生和学生会干部表就构成了多对多的关系

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色

2.1 添加role 和 user_role表

要实现这种多对多的关系，我们不仅需要role角色表，也需要一个中间表，用户和角色表之间的桥梁——用户id和角色id对应的表。

create table role(
    id int AUTO_INCREMENT,
    rolename varchar(255) DEFAULT NULL,
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table user_role(
    uid int NOT NULL,
    rid int NOT NULL,
    PRIMARY KEY (uid , rid),
    KEY(rid),
    CONSTRAINT user_role_ibfk_1 FOREIGN KEY (uid) REFERENCES user (id),
  CONSTRAINT user_role_ibfk_2 FOREIGN KEY (rid) REFERENCES role (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.2 user和role的多对多查询

select u.*,r.*,r.id rid,u.id uid from user u left join user_role ur on u.id=ur.uid inner join role r on ur.rid=r.id
或
select * from user u,user_role ur,role r where u.id = ur.uid and ur.rid =r.id

3.3 创建 role 实体类

public class Role {
    private int id;
    private String rolename;
}

User类添加

    private List<Role>roleList;

UserMapper接口添加

    public List<User> findUserAndRoleAll();

3.4 配置UserMapper.xml

<resultMap id="userRoleMap" type="user">
        <!--    user信息-->
        <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--    user内部的roleList信息-->
        <collection property="roleList" ofType="role">
            <!--            封装order的数据-->
            <id column="rid" property="id"></id>
            <result column="rolename" property="rolename"></result>
        </collection>
    </resultMap>
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        select * from user u,user_role ur,role r where u.id = ur.uid and ur.rid =r.id
    </select>

配置sqlMapperConfig.xml，给Role增加别名

        <typeAlias type="org.example.domain.Role" alias="role"></typeAlias>

3.5 测试

 //多对多测试
    @Test
    public void test8() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);


        List<User> userList = mapper.findUserAndRoleAll();
        System.out.println("################################################");
        for (User user : userList) {
            System.out.println(user);
        }
        System.out.println("################################################");

        sqlSession.commit();
        sqlSession.close();
    }

User{id=14, username='libai', password='dasnk', birthday=Wed Nov 04 01:09:39 CST 2020, orderList=null, roleList=[Role{id=1, rolename='学生'}, Role{id=4, rolename='学生会干事'}]}
User{id=9, username='libai', password='dasnk', birthday=Wed Nov 04 00:46:39 CST 2020, orderList=null, roleList=[Role{id=3, rolename='班主任'}]}
User{id=13, username='libai', password='dasnk', birthday=Wed Nov 04 01:01:32 CST 2020, orderList=null, roleList=[Role{id=3, rolename='班主任'}, Role{id=5, rolename='辅导员'}]}
User{id=7, username='libai', password='dasnk', birthday=Wed Nov 04 00:18:37 CST 2020, orderList=null, roleList=[Role{id=4, rolename='学生会干事'}]}

小结

MyBatis多表配置方式：

一对一配置：使用<resultMap>做配置
一对多配置：使用<resultMap>+<collection>做配置
多对多配置：使用<resultMap>+<collection>做配置

二、注解开发

这几年来注解开发越来越流行，Mybatis也可以使用注解开发方式，这样我们就可以减少编写Mapper映射文件了。
我们先围绕一些基本的CRUD来学习，再学习复杂映射多表操作。

1. 常见的注解

@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result 一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封装

1.2 增删改查

修改MyBatisTest.java，将公共操作抽取出来

    private UserMapper userMapper;
    private UserMapper userMapper;
    private SqlSession sqlSession;
    private SqlSessionFactory sqlSessionFactory;
    private InputStream resourceAsStream;

    @Before
    public void before() throws IOException {
        resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        sqlSession = sqlSessionFactory.openSession();
        userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @After
    public void after() {
        sqlSession.commit();
        sqlSession.close();
    }

  @Test
    public void saveTest() {
        User user = new User();
        user.setUsername("djjjj");
        user.setPassword("asssdddd");
        user.setBirthday(new Date());
        userMapper.save(user);
    }

    @Test
    public void updateTest() {
        User user = new User();
        user.setId(16);
        user.setUsername("tom");
        user.setPassword("dasdasdsadas");
        user.setBirthday(new Date());
        userMapper.update(user);
    }

    @Test
    public void deleteTest() {
        userMapper.delete(12);
    }

    @Test
    public void findByIdTest() {
        User user =userMapper.findById(11);
        System.out.println(user);
    }

    @Test
    public void findAllTest() {
        List<User> userList =userMapper.findAll();
        System.out.println("################################################");
        for (User user : userList) {
            System.out.println(user);
        }
        System.out.println("################################################");
    }

1.3 修改UserMapper.java

   @Insert("insert into user  values (0,#{username},#{password},#{birthday})")
    public void save(User user);

    @Update("update user set username=#{username},password=#{password} where id=#{id}")
    public void update(User user);

    @Delete("delete from user where id=#{id}")
    public void delete(int id);

    @Select("select * from user where id=#{id}")
    public User findById(int id);

    @Select("select * from user ")
    public List<User> findAll();

1.4 删除UserMapper.xml

删除了之后，我们没有了配置文件了呀，那怎么做呢。
我们之前在sqlMapperConfig.xml里面配置了

 <!--        加载映射文件-->
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
        <mapper resource="mapper/OrderMapper.xml"/>
    </mappers>

删除了之后我们就得加载映射关系，指定接口所在的包

    <!--    加载映射关系 TODO-->
    <mappers>
        <!--        指定接口所在的包-->
        <package name="org.example.mapper"/>
    </mappers>

1.5 测试原来的增删改查操作

没有问题！说明可以运行。

2. 使用注解实现更复杂的查询

实现复杂关系映射之前我们可以在映射文件中通过配置<resultMap>来实现，使用注解开发后，我们可以使用
@Results注解，@Result注解，@One注解，@Many注解
组合完成复杂关系的配置

前面我们用配置文件的方式实现了order和user的一对多查询,同样的我们也可以使用注解实现。

2. 1 一对一查询

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

对应的sql语句：

    select * from orders;
    select * from user where id=查询出订单的uid;

1. 修改OrderMapper

public interface OrderMapper {

    @Select("select * ,o.id oid from orders o,user u where o.uid=u.id")
    @Results({
            @Result(column = "oid",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(column = "uid",property = "user.id"),
            @Result(column = "username",property = "user.username"),
            @Result(column = "password",property = "user.password"),
            @Result(column = "birthday",property = "user.birthday")
    })
    public List<Order> findAll();

    @Insert("insert into orders values(0,#{ordertime},#{total},#{user.id})")
    public void insert(Order order);
}

测试

    @Test
    public void orderFindAllTest() {
        List<Order> orderList = orderMapper.findAll();
        System.out.println(orderList);

    }
    @Test
    public void orderInsertTest(){
        User user = userMapper.findById(10);

        Order order = new Order();
        order.setTotal(3000);
        order.setUser(user);
        order.setOrdertime(new Date());

        orderMapper.insert(order);
    }

两个都可以正常运行。
我们上面的查询方式是一次查两张表，但是我们也可以通过查order表获取uid，再通过uid去查user表，这种方法怎么实现呢？
不知道你还记不记得，前面我们使用了<association>进行order和user表的一对一查询的封装，前面图中提到了@One属性，就是它的一种替代

2 修改OrderMapper.java

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单


    @Select("select * ,o.id oid from orders o,user u where o.uid=u.id")
    @Results({
            @Result(column = "oid",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),

            @Result(
                    property = "user",//代表要封装的属性名称
                    column = "uid",//根据哪个字段去查询user表
                    javaType = User.class,//要封装的实体类型
                    //select属性，代表查询哪个接口的方法获得数据
                    one = @One(select = "org.example.mapper.UserMapper.findById")
            )
    })
    public List<Order> findAll();

可能这种方式用的比较多

2.2 一对多查询

对应的sql语句：

    select * from user;
    select * from orders where uid=查询出用户的id;

给OrderMapper.java增加一个查询方法

    @Select("select * from orders where uid=#{uid}")
    public List<Order> findByUid(int uid);

修改UserMapper.java

    @Select("select * from user")
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(column = "password", property = "password"),
            @Result(column = "birthday", property = "birthday"),
            @Result(
                    property = "orderList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "org.example.mapper.OrderMapper.findByUid")
            )
    })
    public List<User> findUserAndOrderAll();

但是这种方法，其实是查到了所有的user，其中有些有orderList，原因在于这其实是一种按顺序的查询方式，先查了user表再查了order表
和
select *,o.id oid from user u,orders o where u.id = o.id是不一样的

2.3 多对多查询

再来回顾一下多对多的查询
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色
对应的sql语句：

    select * from user;
    select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=用户的id

1. 新建一个RoleMapper接口


public interface RoleMapper {
    @Select("select * from user_role ur,role r where ur.rid = r.id and ur.uid = #{uid}")
    public List<Role> findByUid(int id);
}

2. 修改UserMapper

    @Select("select * from user")
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(column = "password", property = "password"),
            @Result(column = "birthday", property = "birthday"),
            @Result(
                    property = "roleList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "org.example.mapper.RoleMapper.findByUid")
            )
    })
    public List<User> findUserAndRoleAll();

这样子，就有个很怪的问题，注解查询每次都返回了全部的user，我不需要这么多的user啊，我只想要和role关联的那几个user，这会是一个好的方法吗。