MyBatis多表操作

2022-08-17

MyBatis 的多表操作：一对一、一对多、多对多

一对一：一个订单只能属于一个人

一对多：一个人可以有多个订单

多对多：一个人可以有多个角色，但这些角色也可以被其他人拥有

一对一：一个订单只能属于一个人

order 表结构：

user 表结构：

1、首先根据数据库中的 order 表创建 order 的实体类，并在 order 的实体类中添加一个 user 字段

import java.util.Date;
 
public class Order {
    private int id;
    private Date ordertime;
    private double total;
 
    // 当前订单属于哪一个用户
    private User user;
 
    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", total=" + total +
                ", user=" + user +
                '}';
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public Date getOrdertime() {
        return ordertime;
    }
 
    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }
 
    public double getTotal() {
        return total;
    }
 
    public void setTotal(double total) {
        this.total = total;
    }
 
    public User getUser() {
        return user;
    }
 
    public void setUser(User user) {
        this.user = user;
    }
}

2、在持久层添加一个查询全部的方法 findAll ()

import java.util.List;
 
public interface OrderMapper {
    // 查询全部的方法
    public List<Order> findAll();
}

3、编写映射文件

方式一

<?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="cn.henu.mapper.OrderMapper">
 
    <resultMap id="orderMap" type="Order">
        <!--手动指定字段与实体属性的映射关系
            colum：数据表的字段名称
            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>
</mapper>

映射文件中各个部分的对应关系

方式二

<?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="cn.henu.mapper.OrderMapper">
 
    <resultMap id="orderMap" type="Order">
        <!--手动指定字段与实体属性的映射关系
            colum：数据表的字段名称
            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>-->
 
        <!--property:当前实体（order）中的属性名称（private User user）
            javaType:当前实体（order）中的属性（private User 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>
 
    <select id="findAll" resultMap="orderMap">
        select *,o.id oid from orders o,user u where o.uid = u.id
    </select>
</mapper>

映射文件中各个部分的对应关系

注解方式一

import cn.henu.domain.Order;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
 
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();
}

注解方式二

public interface UserMapper {
    // 根据id查询
    @Select("select * from user where id = #{id}")
    public User findById(int id);
}


public interface OrderMapper {
    @Select("select * from orders")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(
                    property = "user",//要封装的属性名称
                    column = "uid",//根据order表的哪个字段去查询user表的数据
                    javaType = User.class,//要封装的实体类型
                    //one = @One 表示一对一查询
                    //select属性 表示查询哪个接口的方法获得数据
                    one = @One(select = "cn.henu.mapper.UserMapper.findById")
            )
    })
    // 查询全部的方法
    public List<Order> findAll();
}

4、编写测试类

import cn.henu.domain.Order;
import cn.henu.domain.User;
import cn.henu.mapper.OrderMapper;
import cn.henu.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
 
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
 
public class MybatisTest {
    // 测试一对一（order表中的一条数据对应于user表中的一条数据，即一个订单只能属于一个人的）
    @Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
 
        sqlSession.commit();
        sqlSession.close();
    }
}
 
运行结果：
Order{id=1, ordertime=Fri Feb 15 14:59:37 CST 2019, total=3000.0, user=User{id=1, username='zhangsan', password='123', birthday=null, roleList=null}}
Order{id=2, ordertime=Wed Oct 10 15:00:00 CST 2018, total=5800.0, user=User{id=1, username='zhangsan', password='123', birthday=null, roleList=null}}
Order{id=3, ordertime=Thu Feb 28 15:00:14 CST 2019, total=323.0, user=User{id=2, username='lisi', password='123', birthday=null, roleList=null}}
Order{id=4, ordertime=Thu Feb 21 15:00:25 CST 2019, total=2345.0, user=User{id=1, username='zhangsan', password='123', birthday=null, roleList=null}}
Order{id=5, ordertime=Mon Feb 04 15:00:37 CST 2019, total=100.0, user=User{id=2, username='lisi', password='123', birthday=null, roleList=null}}
Order{id=6, ordertime=Thu Jun 07 15:00:52 CST 2018, total=2009.0, user=User{id=3, username='wangwu', password='123', birthday=null, roleList=null}}

一对多：一个人可以有多个订单

order 表结构：

user 表结构：

1、首先根据数据库中的 user 表创建 user 的实体类，并在 user 的实体类中添加一个 order 字段

import java.util.Date;
import java.util.List;
 
public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
 
    // 描述的是当前用户存在哪些订单
    private List<Order> orderList;
 
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", orderList=" + orderList +
                '}';
    }
 
    public List<Order> getOrderList() {
        return orderList;
    }
 
    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    public Date getBirthday() {
        return birthday;
    }
 
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

2、在持久层添加一个查询全部的方法 findAll ()

import cn.henu.domain.User;
 
import java.util.List;
 
public interface UserMapper {
    public List<User> findAll();
}

3、编写映射文件

<?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="cn.henu.mapper.UserMapper">
 
    <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>
 
    <select id="findAll" resultMap="userMap">
        select *,o.id oid from user u,orders o where u.id = o.uid
    </select>
</mapper>

映射文件中各个部分的对应关系

注解方式

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

public interface UserMapper {
    @Select("select * from user")
    @Results({
            // id = true 代表这个@Result注解就是@Id注解（也可以省略不写）
            @Result(id = true, column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "password",property = "password"),
            @Result(
                    property = "orderList",//要封装的属性名称
                    column = "id",//根据user表的哪个字段去查询order表的数据
                    javaType = List.class,//要封装的实体类型
                    //many = @Many 表示一对多查询
                    //select属性 表示查询哪个接口的方法获得数据
                    many = @Many(select = "cn.henu.mapper.OrderMapper.findByUid")
            )
    })
    public List<User> findUserAndOrderAll();
}

4、编写测试类

import cn.henu.domain.Order;
import cn.henu.domain.User;
import cn.henu.mapper.OrderMapper;
import cn.henu.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
 
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
 
public class MybatisTest {
    // 测试一对多（user表中的一个数据对应order表中的多条数据，即一个人可以有多条对应的订单）
    @Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
 
        sqlSession.commit();
        sqlSession.close();
    }
}
 
运行结果：
User{id=1, username='zhangsan', password='123', birthday=null, orderList=[Order{id=1, ordertime=Fri Feb 15 14:59:37 CST 2019, total=3000.0, user=null}, Order{id=2, ordertime=Wed Oct 10 15:00:00 CST 2018, total=5800.0, user=null}, Order{id=4, ordertime=Thu Feb 21 15:00:25 CST 2019, total=2345.0, user=null}]}
User{id=2, username='lisi', password='123', birthday=null, orderList=[Order{id=3, ordertime=Thu Feb 28 15:00:14 CST 2019, total=323.0, user=null}, Order{id=5, ordertime=Mon Feb 04 15:00:37 CST 2019, total=100.0, user=null}]}
User{id=3, username='wangwu', password='123', birthday=null, orderList=[Order{id=6, ordertime=Thu Jun 07 15:00:52 CST 2018, total=2009.0, user=null}]}

多对多：一个人可以有多个角色，但这些角色也可以被其他人拥有

sys_role 表结构：

sys_user_role 表结构（中间表）：

user 表结构：

1、首先根据数据库中的 user 表创建 user 的实体类，并在 user 的实体类中添加一个 role 字段

import java.util.Date;
import java.util.List;
 
public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
 
    // 描述的是当前用户具备哪些角色
    private List<Role> roleList;
 
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", roleList=" + roleList +
                '}';
    }
 
    public List<Role> getRoleList() {
        return roleList;
    }
 
    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    public Date getBirthday() {
        return birthday;
    }
 
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

2、在持久层添加一个查询全部的方法 findAll ()

import java.util.List;
 
public interface UserMapper {
    public List<User> findUserAndRoleAll();
}

3、编写映射文件

<?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="cn.henu.mapper.UserMapper">
    <resultMap id="userRoleMap" type="User">
        <!--封装user的信息-->
        <id column="userId" 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">
            <id column="roleId" property="id"></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
      
    </resultMap>
  
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        SELECT * FROM user u,sys_user_role ur,sys_role r where u.id = ur.userId and ur.roleId = r.id
    </select>
</mapper>

注解方式

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

public interface 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",//根据user表的哪个字段去查询role表的数据
                    javaType = List.class,//要封装的实体类型
                    //many = @Many 表示多对多查询
                    //select属性 表示查询哪个接口的方法获得数据
                    many = @Many(select = "cn.henu.mapper.RoleMapper.findByUid")
            )
    })
    public List<User> findUserAndRoleAll();
}

4、编写测试类

import cn.henu.domain.Order;
import cn.henu.domain.User;
import cn.henu.mapper.OrderMapper;
import cn.henu.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
 
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
 
public class MybatisTest {
    // 测试多对多（user表中的一个数据对应role表中的多条数据，即一个人可以有多个职位，这些职位也可以被其他人所拥有）
    @Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userAndRoleAll = mapper.findUserAndRoleAll();
        for (User user : userAndRoleAll) {
            System.out.println(user);
        }
 
        sqlSession.commit();
        sqlSession.close();
    }
}
 
运行结果：
User{id=1, username='zhangsan', password='123', birthday=null, roleList=[Role{id=1, roleName='院长', roleDesc='负责全面工作'}, Role{id=2, roleName='研究员', roleDesc='课程研发工作'}]}
User{id=2, username='lisi', password='123', birthday=null, roleList=[Role{id=2, roleName='研究员', roleDesc='课程研发工作'}, Role{id=3, roleName='讲师', roleDesc='授课工作'}]}