jeecgboot工具下后端实现自定义SQL分页列表查询接口前端表格初始化_jeecg-boot的sql脚本

作者：代码探险家 | 2024-07-10 05:07:42

踩

jeecg-boot的sql脚本

Controller

导入包
import org.jeecg.common.system.query.QueryGenerator;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;

接口选择器
@ApiOperation(value=“(dmp)数据对象筛查只做前端自在线开发后期需删除-分页列表查询”, notes=“(dmp)数据对象筛查只做前端自在线开发后期需删除-分页列表查询”)
@GetMapping(value = “/list”)
public Result<IPage> queryPageList(DmpSqlUseObjectQuery dmpSqlUseObjectQuery,
@RequestParam(name=“pageNo”, defaultValue=“1”) Integer pageNo,
@RequestParam(name=“pageSize”, defaultValue=“10”) Integer pageSize,
HttpServletRequest req) {
QueryWrapper queryWrapper = QueryGenerator.initQueryWrapper(dmpSqlUseObjectQuery, req.getParameterMap());
Page page = new Page(pageNo, pageSize);
IPage pageList = dmpUseObjectQueryService.useObejectquery(page, queryWrapper);//dmpUseObjectQueryService.useObejectquery 为自定义SQL方法 queryWrapper参数为前端筛选器内的参数
return Result.OK(pageList);
}

Service

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

IPage<DmpSqlUseObjectQuery> useObejectquery(Page<DmpSqlUseObjectQuery> page, QueryWrapper<DmpSqlUseObjectQuery> queryWrapper);
1

Servicelmpl

导入包
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;

@Resource
DmpSqlUseObjectQueryMapper dmpSqlUseObjectQueryMapper;
@Override
public IPage useObejectquery(Page page, QueryWrapper queryWrapper){
List exportList = new ArrayList();
exportList = dmpSqlUseObjectQueryMapper.getAllList(page,queryWrapper);
return page.setRecords(exportList);
}

Mapper.java

导入包
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import java.util.List;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;

List<DmpSqlUseObjectQuery> getAllList(Page<DmpSqlUseObjectQuery> page, @Param(Constants.WRAPPER) Wrapper queryWrapper);
1

Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<resultMap id="BaseResultMap" type="org.jeecg.modules.insight.platform.datamanagement.entity.DmpSqlUseObjectQuery">
        <result property="id" column="id" jdbcType="VARCHAR"/>
        <result property="createBy" column="create_by" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateBy" column="update_by" jdbcType="VARCHAR"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="sysOrgCode" column="sys_org_code" jdbcType="VARCHAR"/>
        <result property="srcSystemName" column="src_system_name" jdbcType="VARCHAR"/>
        <result property="schemeName" column="scheme_name" jdbcType="VARCHAR"/>
        <result property="objectName" column="object_name" jdbcType="VARCHAR"/>
        <result property="objectDesc" column="object_desc" jdbcType="VARCHAR"/>
        <result property="objectType" column="object_type" jdbcType="VARCHAR"/>
        <result property="areaP" column="area_p" jdbcType="VARCHAR"/>
        <result property="areaM" column="area_m" jdbcType="VARCHAR"/>
        <result property="labelDataP" column="label_data_p" jdbcType="VARCHAR"/>
        <result property="labelDataM" column="label_data_m" jdbcType="VARCHAR"/>
        <result property="fieldNum" column="field_num" jdbcType="VARCHAR"/>
        <result property="attribute01" column="attribute01" jdbcType="VARCHAR"/>
        <result property="attribute02" column="attribute02" jdbcType="VARCHAR"/>
        <result property="attribute03" column="attribute03" jdbcType="VARCHAR"/>
        <result property="attribute04" column="attribute04" jdbcType="VARCHAR"/>
        <result property="attribute05" column="attribute05" jdbcType="VARCHAR"/>
        <result property="attribute06" column="attribute06" jdbcType="VARCHAR"/>
        <result property="attribute07" column="attribute07" jdbcType="VARCHAR"/>
        <result property="attribute08" column="attribute08" jdbcType="VARCHAR"/>
        <result property="attribute09" column="attribute09" jdbcType="VARCHAR"/>
        <result property="attribute10" column="attribute10" jdbcType="VARCHAR"/>
</resultMap>

<sql id="Base_Column_List">
    id,create_by,create_time,
    update_by,update_time,sys_org_code,
    src_system_name,scheme_name,object_name,
    object_desc,object_type,area_p,area_m,
    label_data_p,label_data_m,field_num
</sql>
<sql id="Base_Table">
    (
        SELECT
            dd.id,
            dd.create_by,
            dd.create_time,
            dd.update_by,
            dd.update_time,
            dd.sys_org_code,
            ds.src_system_name,
            dd.schema_name schema_name,
            dd.object_name,
            dd.object_desc,
            dd.object_type,
            dlc.area_p,
            dlc1.area_m,
            dlc.label_class label_data_p,
            dlc1.label_class label_data_m,
            '' field_num
        FROM
            dmp_src_object dd
                LEFT JOIN dmp_src_system ds ON dd.src_system_id = ds.id
                LEFT JOIN dmp_object_label dol ON dd.id = dol.src_object_id
                LEFT JOIN ( SELECT dl1.*, dl2.label_class area_p FROM dmp_label_class dl1 LEFT JOIN dmp_label_class dl2 ON dl1.pid = dl2.id ) dlc ON dol.label_data = dlc.id
                AND dlc.label_class_type = 'businessProc'
                LEFT JOIN ( SELECT dl1.*, dl2.label_class area_m FROM dmp_label_class dl1 LEFT JOIN dmp_label_class dl2 ON dl1.pid = dl2.id ) dlc1 ON dol.label_data = dlc1.id
                AND dlc1.label_class_type = 'manageTheme'
    )
    dmp_sql_use_object_query
</sql>


<select id="getAllList" parameterType="map" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from
    <include refid="Base_Table"/>
    <if test="ew.emptyOfWhere == false">
        ${ew.customSqlSegment}
    </if>
</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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

//其中（ ${ew.customSqlSegment} ）为前端筛选框参数，一般固定写法不用更改