MyBatis 第三十一章 MyBatis 的多表操作


MyBatis —— 第三十一章 MyBatis 的多表操作


1. 一对一查询

1.1. 一对一查询的模型

用户表和订单表的关系为:一个用户有多个订单,一个订单只从属于一个用户

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

OneToOneQuery

1.2. 示例代码

order 数据表

类型 允许空值 是否主键 是否外键
id int false true false
order_time varchar(255) false false false
total double false false false
uid int false false true
create table orders
(
    id         int auto_increment
        primary key,
    order_time varchar(255) not null,
    total      double       not null,
    uid        int          not null,
    constraint orders_id_uindex
        unique (id),
    constraint orders_user_id_fk
        foreign key (uid) references user (id)
);

sqlMapConfig.xml

<?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>
    <!-- Load the external properties file through the properties tag -->
    <properties resource="jdbc.properties"></properties>
    <!-- Define alias -->
    <typeAliases>
        <typeAlias type="com.gregperlinli.domain.User" alias="user" />
        <typeAlias type="com.gregperlinli.domain.Order" alias="order" />
    </typeAliases>
    <!-- Data source environment -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="$&#123;jdbc.driver&#125;"/>
                <property name="url" value="$&#123;jdbc.url&#125;"/>
                <property name="username" value="$&#123;jdbc.username&#125;"/>
                <property name="password" value="$&#123;jdbc.password&#125;"/>
            </dataSource>
        </environment>
    </environments>
    <!-- Load mapping profile -->
    <mappers>
        <mapper resource="com/gregperlinli/mapper/UserMapper.xml"></mapper>
        <mapper resource="com/gregperlinli/mapper/OrderMapper.xml"></mapper>
    </mappers>
</configuration>

com/yourname/mapper/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.gregperlinli.mapper.OrderMapper">
    <resultMap id="orderMap" type="order">
        <!-- Manually specify how fields are mapped to entity attributes
                column: Field name of the data table
                property: Property name of the entity
        -->
        <id column="oid" property="id" />
        <result column="order_time" property="orderTime" />
        <result column="total" property="total" />
        <result column="uid" property="user.id" />
        <!--
            property: The property name (private User user) of the current entity (order)
            javaType: The type of property in the current entity
         -->
        <association property="user" javaType="user">
            <id column="uid" property="id" />
            <result column="username" property="username" />
            <result column="password" property="password" />
            <result column="birthday" property="birthday" />
        </association>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        select *, o.id oid from orders o,user u where o.uid = u.id
    </select>
</mapper>

com.yourname.domain.User

/**
 * @author gregPerlinLi
 * @since 2021-10-08
 */
public class User &#123;
    private int id;
    private String username;
    private String password;

    public int getId() &#123;
        return id;
    &#125;
    public void setId(int id) &#123;
        this.id = id;
    &#125;
    public String getUsername() &#123;
        return username;
    &#125;
    public void setUsername(String username) &#123;
        this.username = username;
    &#125;
    public String getPassword() &#123;
        return password;
    &#125;
    public void setPassword(String password) &#123;
        this.password = password;
    &#125;
    @Override
    public String toString() &#123;
        return "User&#123;" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '&#125;';
    &#125;
&#125;

com.yourname.domain.Order

/**
 * @author gregPerlinLi
 * @since 2021-10-13
 */
public class Order &#123;
    private int id;
    private Date orderTime;
    private double total;
    private int uid;
    /**
     * Which user does the current order belong to
     */
    private User user;

    public int getId() &#123;
        return id;
    &#125;
    public void setId(int id) &#123;
        this.id = id;
    &#125;
    public Date getOrderTime() &#123;
        return orderTime;
    &#125;
    public void setOrderTime(Date orderTime) &#123;
        this.orderTime = orderTime;
    &#125;
    public double getTotal() &#123;
        return total;
    &#125;
    public void setTotal(double total) &#123;
        this.total = total;
    &#125;
    public int getUid() &#123;
        return uid;
    &#125;
    public void setUid(int uid) &#123;
        this.uid = uid;
    &#125;
    public User getUser() &#123;
        return user;
    &#125;
    public void setUser(User user) &#123;
        this.user = user;
    &#125;
    @Override
    public String toString() &#123;
        return "Order&#123;" +
                "id=" + id +
                ", orderTime=" + orderTime +
                ", total=" + total +
                ", uid=" + uid +
                ", user=" + user +
                '&#125;';
    &#125;
&#125;

com.yourname.mapper.OrderMapper

/**
 * @author gregPerlinLi
 * @since 2021-10-13
 */
public interface OrderMapper &#123;
    /**
     * Find all order
     * @return order list
     */
    List<Order> findAll();
&#125;

com.yourname.test.MybatisTest

/**
 * @author gregPerlinLi
 * @since 2021-10-12
 */
public class MybatisTest &#123;
    @Test
    public  void test1() throws IOException &#123;
        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();
        orderList.forEach(System.out::println);
        sqlSession.close();
    &#125;
&#125;

2. 一对多查询

2.1. 一对多查询的模型

用户表和订单的关系为:一个用户有多个订单,一个订单只从属于一个用户

一对多查询的需求: 查询一个用户,与此同时查询出该用户具有的订单

OneToMultiQuery

2.2. 示例代码

com/yourname/mapper/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.gregperlinli.mapper.UserMapper">
    <resultMap id="userMap" type="user">
        <id column="uid" property="id" />
        <result column="username" property="username" />
        <result column="password" property="password" />
        <result column="birthday" property="birthday" />
        <!-- Configure list information
                property: List name
                ofType: The data type of the current list
         -->
        <collection property="orderList" ofType="order">
            <!-- Encapsulate the data of order -->
            <id column="oid" property="id" />
            <result column="order_time" property="orderTime" />
            <result column="total" property="total" />
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        select *, o.id oid from user u, orders o where u.id = o.uid
    </select>
</mapper>

com.yourname.mapper.UserDao

/**
 * @author gregPerlinLi
 * @since 2021-10-08
 */
public interface UserMapper &#123;
    /**
     * FInd all
     *
     * @return User list
     */
    List<User> findAll();
&#125;

com.yourname.test.MybatisTest

/**
 * @author gregPerlinLi
 * @since 2021-10-12
 */
public class MybatisTest &#123;
    @Test
    public  void test2() throws IOException &#123;
        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();
        userList.forEach(System.out::println);
        sqlSession.close();
    &#125;
&#125;

3. 多对多查询

3.1. 多对多查询的模型

用户表和角色表的关系为:一个用户有多个角色,一个角色被多个用户使用

多对多查询的需求: 查询用户同时查询出该用户的所有角色

MultiToMulti

3.2. 示例代码

role 数据表

类型 允许空值 是否主键 是否外键
id int false true false
role_name varchar(50) false false false
role_desc varchar(50) true false false
create table role
(
    id        int auto_increment
        primary key,
    role_name varchar(255) not null,
    role_desc varchar(50)  null,
    constraint role_id_uindex
        unique (id),
    constraint role_role_name_uindex
        unique (role_name)
);

user_role 列表

类型 允许空值 是否主键 是否外键
user_id int false false true
role_id int false false true
create table user_role
(
    user_id int not null,
    role_id int not null,
    constraint user_role_role_id_fk
        foreign key (role_id) references role (id),
    constraint user_role_user_id_fk
        foreign key (user_id) references user (id)
);

sqlMapConfig.xml

<?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>
    <!-- Load the external properties file through the properties tag -->
    <properties resource="jdbc.properties"></properties>
    <!-- Define alias -->
    <typeAliases>
        <typeAlias type="com.gregperlinli.domain.User" alias="user" />
        <typeAlias type="com.gregperlinli.domain.Order" alias="order" />
        <typeAlias type="com.gregperlinli.domain.Role" alias="role" />
    </typeAliases>
    <!-- Data source environment -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="$&#123;jdbc.driver&#125;"/>
                <property name="url" value="$&#123;jdbc.url&#125;"/>
                <property name="username" value="$&#123;jdbc.username&#125;"/>
                <property name="password" value="$&#123;jdbc.password&#125;"/>
            </dataSource>
        </environment>
    </environments>
    <!-- Load mapping profile -->
    <mappers>
        <mapper resource="com/gregperlinli/mapper/UserMapper.xml"></mapper>
        <mapper resource="com/gregperlinli/mapper/OrderMapper.xml"></mapper>
    </mappers>
</configuration>

com/yourname/mapper/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.gregperlinli.mapper.UserMapper">
    <resultMap id="userRoleMap" type="user">
        <!-- user information -->
        <id column="user_id" property="id" />
        <result column="username" property="username" />
        <result column="password" property="password" />
        <result column="birthday" property="birthday" />
        <!-- roleList information inside user -->
        <collection property="roleList" ofType="role">
            <id column="role_id" property="id" />
            <result column="role_name" property="roleName" />
            <result column="role_desc" property="roleDesc" />
        </collection>
    </resultMap>
    <select id="findUserAndRole" resultMap="userRoleMap">
        select * from user u, user_role ur, role r where u.id = ur.user_id and ur.role_id = r.id
    </select>
</mapper>

com.yourname.mapper.UserMapper

/**
 * @author gregPerlinLi
 * @since 2021-10-08
 */
public interface UserMapper &#123;
    /**
     * Find user ans role
     * @return user list with role
     */
    List<User> findUserAndRole();
&#125;

com.yourname.test.MybatisTest

/**
 * @author gregPerlinLi
 * @since 2021-10-12
 */
public class MybatisTest &#123;
    @Test
    public  void test3() throws IOException &#123;
        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.findUserAndRole();
        userList.forEach(System.out::println);
        sqlSession.close();
    &#125;
&#125;

4. 知识要点

MyBatis 多表配置方式:

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


文章作者: gregPerlinLi
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 gregPerlinLi !
  目录