mybatis 动态查询

传入需要查询的表名称，表字段，查询条件动态返回数据结果集

1.表名称，表字段，查询条件封装

package com.yunpi.aas.msvs.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yunpi.aas.msvs.bean.GasTimeTable;
import com.yunpi.aas.msvs.bean.GpsTimeTableColumn;
import com.yunpi.aas.msvs.mapper.GasTimeTableMapper;
import com.yunpi.aas.msvs.mapper.GpsTimeTableColumnMapper;
import com.yunpi.aas.msvs.model.vo.TrajectoryTimeVo;
import com.yunpi.aas.msvs.service.IGasTimeTableService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author chengyan
 * @since 2022-07-14
 */
@Slf4j
@Service
public class GasTimeTableServiceImpl extends ServiceImpl<GasTimeTableMapper, GasTimeTable> implements IGasTimeTableService {

    @Autowired
    private GpsTimeTableColumnMapper gpsTimeTableColumnMapper;

    @Autowired
    private GasTimeTableMapper gpsTimeTableMapper;

    @Value("${sequence.timeColumn}")
    private  String timeColumn;

    @Override
    public JSONObject getGpsData(TrajectoryTimeVo trajectorySequenceVo) {
        GpsTimeTableColumn gpsTimeTableColumn=new GpsTimeTableColumn();
        gpsTimeTableColumn.setVertexLabel(trajectorySequenceVo.getVertexLabel());
        List<GpsTimeTableColumn> list=gpsTimeTableColumnMapper.getByBean(gpsTimeTableColumn);
        if(CollectionUtils.isEmpty(list)){
            log.info("数据异常，未查询到结果[轨迹时序的表字段],实体类型值{}",trajectorySequenceVo.getVertexLabel());
            return null;
        }
        List<GpsTimeTableColumn> matchColumn = list.stream().filter(a -> 1==a.getIsMatch()).collect(Collectors.toList());

        if(ObjectUtils.isEmpty(matchColumn)){
            log.info("数据异常，未查询到结果[轨迹时序的表字段中未找到唯一匹配字段]");
            return null;
        }
        GpsTimeTableColumn column=matchColumn.get(0);
        String tableName = column.getTableName();
        // 指定排序方式
        String sort="asc";
        // where条件字段
        Map<String,Object> params = new HashMap<>(16);
        params.put("graphId", trajectorySequenceVo.getGraphId());
        params.put("startTime", trajectorySequenceVo.getStartTime());
        params.put("endTime", trajectorySequenceVo.getEndTime());
        // 查询字段
        Map<String,String> columns=new LinkedHashMap<>(list.size());
        list.forEach(item->{
            columns.put(item.getPropertyName(),item.getShowName());
        });
        List<LinkedHashMap<String,Object>> res = gpsTimeTableMapper.selectGpsData(tableName,timeColumn,sort,params,columns);
        if(!CollectionUtils.isEmpty(res)){
	        JSONObject  result=new JSONObject(true);
	        result.put("data",res);
        }
        return result;
    }
}

2.Mapper接口定义

package com.yunpi.aas.msvs.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.yunpi.aas.msvs.bean.GasTimeTable;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yunpi.aas.msvs.bean.GpsTimeTableColumn;
import org.apache.ibatis.annotations.Param;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author chengyan
 * @since 2022-07-14
 */
public interface GasTimeTableMapper extends BaseMapper<GasTimeTable> {
    /**
     * 轨迹时序查询
     * @param tableName 表名
     * @param params 参数
     * @param timeColumn 时间字段
     * @param sort 排序类型
     * @param columns 列
     * @return
     */
    List<LinkedHashMap<String,Object>> selectGpsData(@Param("tableName") String tableName,
                                                     @Param("timeColumn") String timeColumn,
                                                     @Param("sort") String sort,
                                                     @Param("params") Map<String, Object> params,
                                                     @Param("columns") Map<String, String> columns);

}

3.mapper.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.yunpi.aas.msvs.mapper.GasTimeTableMapper">

    <!---mysql sql-->
    <select id="selectGpsDataMsql" parameterType="java.util.HashMap" resultType="java.util.HashMap">
        select
            <foreach collection="columns" item="v" index="k" separator=",">
                ${k} as  ${v}
            </foreach>
        from ${tableName}
        <where>
            <foreach collection="params" item="v" index="k" separator="and">
                <if test="v != null and v != ''">
                    <choose>
                        <when test="k == 'startTime'">
                            <![CDATA[    DATE_FORMAT(${timeColumn}, '%Y-%m-%d %H:%T:%s') >= DATE_FORMAT(#{v} , '%Y-%m-%d %H:%T:%s') ]]>
                        </when>
                        <when test="k == 'endTime'">
                            <![CDATA[    DATE_FORMAT(${timeColumn}, '%Y-%m-%d %H:%T:%s') <= DATE_FORMAT(#{v} , '%Y-%m-%d %H:%T:%s') ]]>
                        </when>
                        <otherwise>
                            ${k} = #{v}
                        </otherwise>
                    </choose>
                </if>
                <!--<choose>
                    <when test="v instanceof integer">
                        <if test="v != null">
                            ${k} = #{v}
                        </if>
                    </when>
                    <when test="v instanceof list">
                        <if test="v != null and v.size() > 0">
                            ${k} in
                            <foreach collection="list" item="v" separator="," open="(" close=")">
                                #{v}
                            </foreach>
                        </if>
                    </when>
                    <when test="v instanceof array">
                        <if test="v != null and v.length > 0">
                            ${k} in
                            <foreach collection="array" item="v" separator="," open="(" close=")">
                                #{v}
                            </foreach>
                        </if>
                    </when>
                    <otherwise>
                        <if test="v != null and v != ''">
                            ${k} = #{v}
                        </if>
                    </otherwise>
                </choose>-->
            </foreach>
        </where>
        order by ${timeColumn} ${sort}
    </select>


    <!---greenplum sql-->
    <select id="selectGpsData" parameterType="java.util.LinkedHashMap" resultType="java.util.LinkedHashMap">
        select
        <foreach collection="columns" item="v" index="k" separator=",">
            &quot;${k}&quot; as  ${v}
        </foreach>
        from ${tableName}
        <where>
            <foreach collection="params" item="v" index="k" separator="and">
                <if test="v != null and v != ''">
                    <choose>
                        <when test="k == 'startTime'">
                             to_date(&quot;${timeColumn}&quot;, 'YYYY-MM-DD HH24:MI:SS')<![CDATA[ >= ]]> to_date(#{v} , 'YYYY-MM-DD HH24:MI:SS')
                        </when>
                        <when test="k == 'endTime'">
                             to_date(&quot;${timeColumn}&quot;, 'YYYY-MM-DD HH24:MI:SS')<![CDATA[ <=]]> to_date(#{v} , 'YYYY-MM-DD HH24:MI:SS')
                        </when>
                        <otherwise>
                            &quot;${k}&quot; = #{v}
                        </otherwise>
                    </choose>
                </if>
            </foreach>
        </where>
         order by &quot;${timeColumn}&quot;  ${sort}
    </select>
</mapper>