传入需要查询的表名称,表字段,查询条件动态返回数据结果集
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=",">
"${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'">
to_date("${timeColumn}", 'YYYY-MM-DD HH24:MI:SS')<![CDATA[ >= ]]> to_date(#{v} , 'YYYY-MM-DD HH24:MI:SS')
</when>
<when test="k == 'endTime'">
to_date("${timeColumn}", 'YYYY-MM-DD HH24:MI:SS')<![CDATA[ <=]]> to_date(#{v} , 'YYYY-MM-DD HH24:MI:SS')
</when>
<otherwise>
"${k}" = #{v}
</otherwise>
</choose>
</if>
</foreach>
</where>
order by "${timeColumn}" ${sort}
</select>
</mapper>