码代码的冰果果

个人在线笔记

0%

结果集映射-案例

发表于 更新于 分类于 ， ， ， 阅读次数： Valine：
本文字数： 15k 阅读时长 ≈ 27 分钟

springBoot+MyBatis+pagehelper，结果集映射案例。

  • 结果集映射可以解决属性名与字段名不一致的问题。实体类中的属性名与数据表中的字段名。案例见这篇博客。
  • 可以对复杂的查询结果（一对多查询）进行结果集映射。例如：查询某个教师信息同时查询所教的班级。所教的班级有多个，可以使用一个List集合保存（教师对象中添加一个List变量）。将一个个班级信息保存到教师对象的List变量中就需要使用结果集映射来解决。案例如下。

案例

结果集映射，查询“一对多”关系的数据，有两种实现思路：

  • 结果嵌套查询
  • 子查询嵌套查询

现在有三个表入库单store_in、入库明细表store_indet、物资表store_mat。入库单保存入库信息，入库明细表保存入库明细（入库单主键、物资主键、数量等），物资表保存物资信息。

需求：（分页）查询入库单，同时返回入库单和其中包含的物资。

准备

Maven依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
<!--主要：-->

<!--spring-boot-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<!--mybatis-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version>
</dependency>
<!--分页插件pagehelper-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>

<!--次要：-->
<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--json工具-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.60</version>
</dependency>

实体类

入库单store_in实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
package com.qsdbl.xxx.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StoreIn {
    private String in_code;
    private String auditing;
    ...//前边的字段根据表添加，后边的字段根据业务需要添加
    private List<StoreMat> mats;//物资列表
    private String modify_username;//修改人姓名
}

入库明细表store_indet实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
package com.qsdbl.xxx.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StoreIndet {
    private String mat_id;
    private String in_num;
    ...//根据表添加字段
    private String tenant_id;
}

物资表store_mat实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
package com.qsdbl.xxx.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StoreMat {
    private String mat_code;
    private String mat_name;
    //前边的字段根据表添加，后边的字段根据业务需要添加
    private String in_num;//入库数量
}

请求对象

请求对象PageInfo_qo，用于接收前端分页查询相关的数据。本项目中使用到Pagehelper分页插件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
package com.qsdbl.xxx.entity.qo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;

/**
 * 用于接收前端分页查询相关的数据
 * @author 码代码的冰果果
 * @since 2021-07-16
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageInfo_qo implements Serializable{
    //每页显示的数据大小
    private Integer pageSize = 10;//默认值
    //查询的当前页
    private Integer currentPage = 1;
}

视图对象

视图对象DataVo，用于封装返回给前端的数据。根据前后端约定好的规则设计视图对象。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
package com.qsdbl.xxx.entity.vo;

import lombok.Data;
import java.util.List;

/**
 * vo - 视图对象
 * jsonData，json（字符串）数据
 */
@Data
public class DataVo<T> {
    private String success = "true";
    private String message;
    private List<T> datas;//返回的数据为一个 集合
    private T data;//返回的数据为一个 对象
}

mapper层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
package com.qsdbl.xxx.mapper;

import com.qsdbl.XXX.entity.StoreIn;
import org.apache.ibatis.annotations.*;
import java.util.List;

/**
 * 入库单表 Mapper层
 * @author 码代码的冰果果
 * @since 2021-07-16
 */
@Mapper
public interface StoreInMapper {
    //查询所有数据
    public List<StoreIn> queryAll();
}

service层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
package com.qsdbl.xxx.service;

import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.qsdbl.XXX.entity.StoreIn;
import com.qsdbl.XXX.entity.qo.PageInfo_qo;
import com.qsdbl.XXX.entity.vo.DataVo;
import com.qsdbl.XXX.mapper.StoreInMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

/**
 * 入库单表 服务层
 * @author 码代码的冰果果
 * @since 2021-07-18
 */
@Service
public class StoreInService {

    /**
     * 入库单表 Mapper对象
     */
    @Autowired
    private StoreInMapper storeInMapper;

    /**
     * 分页查询 入库单数据
     * @param qo 分页查询相关的数据，包括每页显示的数据大小pageSize（默认为10）、查询的当前页currentPage（默认为1）
     * @return 入库单详细数据（包含物资）
     */
    public String queryAll(PageInfo_qo qo) {
        //设置对应的分页参数
        //第一个参数pageNum：需要查询的当前页。pageSize：需要显示的数据量大小
        PageHelper.startPage(qo.getCurrentPage(),qo.getPageSize());
        //查询
        List<StoreIn> list = storeInMapper.queryAll();
        //封装成分页数据
        PageInfo pageInfo = new PageInfo(list);
        //fastjson工具，将Java对象转换为json字符串
        JSONObject jsonObject = new JSONObject();
        DataVo dataVo = new DataVo();//视图对象，按规定好的格式返回数据给前端
        dataVo.setMessage("----测试：查询入库单数据！----");
        dataVo.setData(pageInfo);
        return jsonObject.toJSONString(dataVo);
    }

}

web层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
package com.qsdbl.xxx.web;

import com.qsdbl.xxx.service.StoreInService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.qsdbl.xxx.entity.qo.PageInfo_qo;

/**
 * 入库单表 控制层
 * @author 码代码的冰果果
 * @since 2021-07-18
 */
@RestController
@RequestMapping("/xxx/v1/")
@CrossOrigin
public class StoreInController {

    /**
     * 入库单表 Service对象
     */
    @Autowired
    private StoreInService storeInService;

    /**
     * 分页查询 入库单数据
     * @param qo 分页查询相关的数据，包括每页显示的数据大小pageSize（默认为10）、查询的当前页currentPage（默认为1）
     * @return 入库单详细数据（包含物资）
     */
    @GetMapping("storeins")
    public String queryAll(PageInfo_qo qo){
        return storeInService.queryAll(qo);
    }
}

配置文件

springBoot配置文件application.properties，配置服务端口、数据库、mybatis等相关参数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
#服务端口
server.port=8090

#-----------mybatis-----------
#指定实体类所在路径
mybatis.type-aliases-package=com.qsdbl.xxx.entity
#指定映射文件所在路径。（用于扫描mapper接口对应的xml文件）
mybatis.mapperLocations=classpath:mapper/*.xml
#设置驼峰命名规则自动转换
#mybatis.configuration.map-underscore-to-camel-case=true

#数据库驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据连接，allowMultiQueries=true是支持批量执行SQL语句，语句之前是";"隔开
spring.datasource.url=jdbc:mysql://localhost:3306/jxstar_cloud?serverTimezone=UTC&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
#数据库登录账号、密码
spring.datasource.username=root
spring.datasource.password=123456

根据上边的mybatis.mapperLocations的配置信息，xml文件应当保存在resource文件夹下的mapper文件夹内。具体规则可查看这篇博客

结果嵌套查询

结果嵌套查询：直接查询出入库单和入库单包含的物资。

  • 优点：写一个select即可
  • 缺点：若不同表存在字段相同情况，需要起别名。使用pagehelper插件会出现bug，会将嵌套查询的数据作为分页数据（即List集合中的数据作为分页依据）。
1
2
3
 
SELECT a.*,c.*,b.in_num from store_in a
left join store_indet b on a.in_id = b.in_id
inner join store_mat c on b.mat_id = c.mat_id

xml文件如下：只写一个select语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
<?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.qsdbl.nazox_demo.mapper.StoreInMapper">

    <resultMap id="queryAllMap" type="com.qsdbl.nazox_demo.entity.StoreIn">
        <result column="aadd_userid" property="add_userid"/>
        <result column="aadd_date" property="add_date"/>
        <result column="amodify_userid" property="modify_userid"/>
        <result column="amodify_date" property="modify_date"/>
        <result column="atenant_id" property="tenant_id"/>
        <result column="in_code" property="in_code"/>
        <result column="auditing" property="auditing"/>
        <result column="in_date" property="in_date"/>
        <result column="house_id" property="house_id"/>
        <result column="house_name" property="house_name"/>
        <result column="edit_user" property="edit_user"/>
        <result column="edit_userid" property="edit_userid"/>
        <result column="send_user" property="send_user"/>
        <result column="in_desc" property="in_desc"/>
        <result column="in_money" property="in_money"/>
        <result column="pur_user" property="pur_user"/>
        <result column="pur_userid" property="pur_userid"/>
        <result column="in_id" property="in_id"/>
        <collection property="mats" ofType="com.qsdbl.nazox_demo.entity.StoreMat" javaType="java.util.List">
            <result column="mat_code" property="mat_code"/>
            <result column="mat_name" property="mat_name"/>
            <result column="mat_size" property="mat_size"/>
            <result column="mat_unit" property="mat_unit"/>
            <result column="mat_price" property="mat_price"/>
            <result column="mat_desc" property="mat_desc"/>
            <result column="mat_id" property="mat_id"/>
            <result column="type_id" property="type_id"/>
            <result column="type_code" property="type_code"/>
            <result column="type_name" property="type_name"/>
            <result column="cadd_userid" property="add_userid"/>
            <result column="cadd_date" property="add_date"/>
            <result column="cmodify_userid" property="modify_userid"/>
            <result column="cmodify_date" property="modify_date"/>
            <result column="ctenant_id" property="tenant_id"/>
            <result column="in_num" property="in_num"/>
        </collection>
    </resultMap>
    <select id="queryAll" resultMap="queryAllMap">
        SELECT 
            a.in_code,a.auditing,a.in_date,a.house_id,a.house_name,a.edit_user,a.edit_userid,a.send_user,a.in_desc,a.in_money,a.pur_user,a.pur_userid,a.in_id,
            a.add_userid aadd_userid,c.add_userid cadd_userid,a.add_date aadd_date,c.add_date cadd_date,a.modify_date amodify_date,c.modify_date cmodify_date,a.modify_userid amodify_userid,c.modify_userid cmodify_userid,a.tenant_id atenant_id,c.tenant_id ctenant_id,
            c.mat_code,c.mat_name,c.mat_size,c.mat_unit,c.mat_price,c.type_code,c.type_id,c.type_name,c.mat_desc,c.mat_id,
            b.in_num
        from store_in a
                 left join
             store_indet b on a.in_id = b.in_id
                 inner join
             store_mat c on b.mat_id = c.mat_id
        order by a.modify_date desc
    </select>

</mapper>

由于store_in表与store_mat表存在一些重复字段名，所以需要起别名，而且每个字段 - 属性都要进行映射。但依然存在一个bug，PageHelper插件对查询出来的store_mat表数据进行分页，而不是对store_in表的数据进行分页显示。需要使用子查询嵌套查询来解决。

子查询嵌套查询

子查询嵌套查询：先查询入库单数据，再根据入库单数据去查询所包含的物资。

  • 优点：一般不需要起别名。可以完美配合pagehelper插件使用。
  • 缺点：写多个select，难度增大。

第一步：查询入库单数据

1
2
3
4
5
 
select b.user_name modify_username,a.*
from store_in a,sys_user b
where a.modify_userid = b.user_id
group by in_code
order by modify_date desc

第二步：查询所包含的物资。in_id，为入库单store_in表的主键，在第一步中查询出来。

1
2
3
4
 
select b.*,a.in_num
from store_indet a
inner join store_mat b on a.mat_id = b.mat_id
where a.in_id = #{in_id}

xml文件如下：拆分成了两个select语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
<?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开始就是自己定义的了-->
<mapper namespace="com.qsdbl.nazox_demo.mapper.StoreInMapper"><!--对应的mapper类的包路径-->

    <resultMap id="queryAllMap" type="com.qsdbl.nazox_demo.entity.StoreIn">
<!--嵌套查询，查询入库单中包含的物资，select=“select的id”指定嵌套查询的语句，column=“当前查询语句所查询出来的列名”指定传递给所调用的查询语句的参数-->
        <collection property="mats" ofType="com.qsdbl.nazox_demo.entity.StoreMat" javaType="java.util.List" select="getMatByMatID" column="in_id"/>
    </resultMap>
    <select id="queryAll" resultMap="queryAllMap">
        select b.user_name modify_username,a.*
        from store_in a,sys_user b
        where a.modify_userid = b.user_id
        group by in_code
        order by modify_date desc
    </select>
    <select id="getMatByMatID" resultType="com.qsdbl.nazox_demo.entity.StoreMat">
        select b.*,a.in_num
        from store_indet a
                 inner join store_mat b
                            on a.mat_id = b.mat_id
        where a.in_id = #{in_id}
    </select>

</mapper>

测试

发起请求

1
 
GET http://localhost:8090/xxx/v1/storeins?pageSize=2&currentPage=1

返回的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
{
    "data": {
        "endRow": 2,
        "hasNextPage": true,
        "hasPreviousPage": false,
        "isFirstPage": true,
        "isLastPage": false,
        "list": [
            {
                "add_date": "2021-07-20 14:44:52",
                "add_userid": "administrator",
                "auditing": "1",
                "edit_user": "刘某",
                "edit_userid": "jxstar-082-694",
                "house_id": "jxstar-150-51",
                "house_name": "番禺仓库",
                "in_code": "RK202107000017",
                "in_date": "2021-07-20 00:00:00",
                "in_money": "17868480.000000",
                "mats": [
                    {
                        "add_date": "2021-07-20 14:11:58",
                        "add_userid": "jxstar-859-744",
                        "in_num": "20.000000",
                        "mat_code": "100203",
                        "mat_id": "jxstar-196-155",
                        "mat_name": "推土机t36",
                        "mat_price": "653424.000000",
                        "mat_size": "t36",
                        "mat_unit": "台",
                        "tenant_id": "jxstar",
                        "type_code": "1002",
                        "type_id": "10010002",
                        "type_name": "推土机"
                    },
                    {
                        "add_date": "2021-06-24 16:28:07",
                        "add_userid": "jxstar",
                        "in_num": "40.000000",
                        "mat_code": "100301",
                        "mat_id": "jxstar-801-101",
                        "mat_name": "特种车辆",
                        "mat_price": "120000.000000",
                        "mat_size": "X90",
                        "mat_unit": "台",
                        "tenant_id": "jxstar",
                        "type_code": "1003",
                        "type_id": "10010003",
                        "type_name": "特种车辆"
                    }
                ],
                "modify_date": "2021-07-20 14:45:06",
                "modify_userid": "administrator",
                "modify_username": "admin",
                "pur_user": "admin",
                "pur_userid": "administrator",
                "send_user": "叶文洁",
                "tenant_id": "jxstar"
            },
            {
                "add_date": "2021-07-20 14:43:58",
                "add_userid": "administrator",
                "auditing": "1",
                "edit_user": "陈时壮",
                "edit_userid": "jxstar-349-596",
                "house_id": "jxstar-772-101",
                "house_name": "华北1号仓",
                "in_code": "RK202107000016",
                "in_date": "2021-07-20 00:00:00",
                "in_money": "3600000.000000",
                "mats": [
                    {
                        "add_date": "2021-06-24 16:28:07",
                        "add_userid": "jxstar",
                        "in_num": "30.000000",
                        "mat_code": "100301",
                        "mat_id": "jxstar-801-101",
                        "mat_name": "特种车辆",
                        "mat_price": "120000.000000",
                        "mat_size": "X90",
                        "mat_unit": "台",
                        "tenant_id": "jxstar",
                        "type_code": "1003",
                        "type_id": "10010003",
                        "type_name": "特种车辆"
                    }
                ],
                "modify_date": "2021-07-20 14:44:07",
                "modify_userid": "administrator",
                "modify_username": "admin",
                "pur_user": "admin",
                "pur_userid": "administrator",
                "send_user": "罗辑",
                "tenant_id": "jxstar"
            }
        ],
        "navigateFirstPage": 1,
        "navigateLastPage": 8,
        "navigatePages": 8,
        "navigatepageNums": [
            1,
            2,
            3,
            4,
            5,
            6,
            7,
            8
        ],
        "nextPage": 2,
        "pageNum": 1,
        "pageSize": 2,
        "pages": 10,
        "prePage": 0,
        "size": 2,
        "startRow": 1,
        "total": 19
    },
    "message": "----测试：查询入库单数据！----",
    "success": "true"
}

前端应用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
#全局数组this.data，即入库单数据，其内的数组mats就是入库单所包含的物资。按需使用pagehelper控件提供的字段即可
loadData() {
	// 加载页面数据
	this.$http({
		method: 'get',
		url: '/storeins',
		responseType: 'json',
		params: {
			pageSize: this.pageSize,
			currentPage: this.currentPage
		}
	}).then((resp) => {
		//ajax请求正常响应的回调
		var data = resp.data.data;
		if (resp.data.success == 'true') {
			this.data = data.list;
			this.total = data.total;
		} else {
			alert(resp.data.message);
		}
	}).catch(() => {
		//ajax请求异常的回调
		alert("网络异常！");
	});
}

注意：案例中axios设置了defaults.baseURL，且起别名为$http

若图片不能正常显示，请在浏览器中打开

欢迎关注我的其它发布渠道