JinLinC个人在线文档库

Appearance

多表操作 ​

随着业务复杂性的提高，单表操作往往满足不了我们的需求，我们需要进行多表操作

一对一查询 ​

如用户表和订单表的关系就是一对一查询，一个用户可以有多个订单，一个订单只从属于一个用户

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

image-20250602212711298

基本步骤：使用<resultMap>进行配置

  1. 创建实体类Order和User

    在src/main/java文件夹下创建：

    java
    package com.jlc.domain;

import java.util.Date;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    
    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; }
}
    java
    package com.jlc.domain;

import java.util.Date;

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    // 当前订单属于哪一个用户
    private 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; }
}

  2. 创建对应的接口

    编写OrderMapper接口

    java
    package com.jlc.mapper;

import com.jlc.domain.Order;
import java.util.List;

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

  3. 创建映射文件UserMapper.xml和OrderMapper.xml

    在映射文件UserMapper.xml文件中进行配置：

    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.jlc.dao.UserMapper">   
    
</mapper>

    在映射文件OrderMapper.xml文件中进行配置：

    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.jlc.dao.OrderMapper">   
    <resultMap id ="orderMap" type="com.jlc.domain.Order">
    	<!--手动指定字段与实体属性的映射关系-->
        <!--column：数据表的字段名称   property：实体的属性名称-->
        <id column="oid" property="id"></id>   <!--主键使用的标签是<id>-->
        <!--普通属性使用的<result>标签-->
        <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
    <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>

    还有一种其他的封装方式：

    xml
    <!--
	property：当前实体Order中的属性名称（private User user）
	javaType：当前实体Order中的属性类型(User)
-->
<association property="user" javaType="com.jlc.domain.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>

  4. 在核心配置文件sqlMapConfig.xml中引用映射文件

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

一对多查询 ​

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

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

基本步骤：使用<resultMap>+<collection>进行配置

  1. 创建实体类Order和User

    在src/main/java文件夹下创建：

    java
    package com.jlc.domain;

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;
    
    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; }
    
    public List<Order> getOrderList() { return orderList; }
    public void setOrderList(List<Order> orderList) { this.orderList = orderList; }
}
    java
    package com.jlc.domain;

import java.util.Date;

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    
    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; }
}

  2. 创建对应的接口

    在mapper层编写UserMapper接口

    java
    package com.jlc.mapper;

import com.jlc.domain.User;
import java.util.List;

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

  3. 创建映射文件UserMapper.xml和OrderMapper.xml

    在映射文件UserMapper.xml文件中进行配置：

    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.jlc.dao.UserMapper">   
    <resultMap id ="userMap" type="com.jlc.domain.User">
    	<!--手动指定字段与实体属性的映射关系-->
        <!--column：数据表的字段名称   property：实体的属性名称-->
        <id column="uid" property="id"></id>   <!--主键使用的标签是<id>-->
        <!--普通属性使用的<result>标签-->
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>
        <!--配置集合信息：property：集合名称  ofType：当前集合中的数据类型-->
        <collection property="orderList" ofType="com.jlc.domain.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, oeders o WHERE u.id = o.id
    </select>
</mapper>

    在映射文件OrderMapper.xml文件中进行配置：

    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.jlc.dao.OrderMapper">   

</mapper>

  4. 在核心配置文件sqlMapConfig.xml中引用映射文件

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

多对多查询 ​

用户表和角色表的关系为：一个用户有多个角色，一个角色可以被多个用户使用

多对多查询的需求：查询用户同时查询该用户的所有角色（多对多的建表原则是要引入一张中间表，来维护两个外键）

image-20250603150635404

基本步骤：使用<resultMap>+<collection>进行配置

  1. 创建实体类User和Role

    在src/main/java文件夹下创建：

    java
    package com.jlc.domain;

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;
    
    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; }
    
    public List<Role> getRoleList() { return roleList; }
}
    java
    package com.jlc.domain;

public class Role {
    private int id;
    private String roleName;
    private String roleDesc;
    
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    
    public String getRoleName() { return roleName; }
    public void setRoleName(String roleName) { this.roleName = roleName; }
    
    public String getRoleDesc() { return roleDesc; }
    public void setRoleDesc(String roleDesc) { this.roleDesc = roleDesc; }
}

  2. 创建对应的接口

    在mapper层编写UserMapper接口

    java
    package com.jlc.mapper;

import com.jlc.domain.User;
import java.util.List;

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

  3. 创建映射文件UserMapper.xml和OrderMapper.xml

    在映射文件UserMapper.xml文件中进行配置：

    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.jlc.dao.UserMapper">   
    <resultMap id ="userRoleMap" type="com.jlc.domain.User">
    	<!--手动指定字段与实体属性的映射关系-->
        <!--1.封装user的信息-->
        <!--column：数据表的字段名称   property：实体的属性名称-->
        <id column="userId" property="id"></id>   <!--主键使用的标签是<id>-->
        <!--普通属性使用的<result>标签-->
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>
        <!--2.封装user内部的roleList信息-->
        <!--配置集合信息：property：集合名称  ofType：当前集合中的数据类型-->
        <collection property="roleList" ofType="com.jlc.domain.Role">
            <!--封装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>

多对多查询和一对多查询的唯一区别就是SQL 语句编写的不同（多对多额外有一张中间表）

Released under the MIT License.

Copyright © 2023-present JinLinC