-- 创建自增序列
CREATE SEQUENCE t_demo_seqSTART WITH 1INCREMENT BY 1NO MINVALUENO MAXVALUECACHE 1;
-- 创建表结构
CREATE TABLE public.t_demo (created_time timestamp NULL, -- 创建时间has_sm varchar(20) NULL, -- 实名属性 1是 0否age int4 NULL, -- 年龄xb varchar(255) NULL, -- 性别task_id int8 NOT NULL, -- 任务idid serial4 NOT NULL, -- 主键idtags _varchar NULL, -- 标签数组放code 字符串数据"indexes" _int4 NULL, -- 下标合集space_belong jsonb NULL, -- jsonb字段CONSTRAINT t_demo_pkey PRIMARY KEY (id)
);
COMMENT ON TABLE public.t_demo IS '示例表';
COMMENT ON COLUMN public.t_demo.id IS '自增ID';
-- 设置id使用的自增序列
ALTER TABLE public.t_demoALTER COLUMN id SET DEFAULT nextval('t_demo_seq');使用Mybatisplus 构建映射实体
@TableName(value = "t_demo",autoResultMap = true)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TDemo implements Serializable, Cloneable {/*** 主键id*/@TableId(type = IdType.AUTO)private Long id;/*** 性别*/private String xb;/*** 任务id*/@TableField(value = "task_id")private Long taskId;/*** 标签数组放code*/@TableField(value ="tags", typeHandler = MbStringArrayHandler.class)private List<String> tags;/*** 所属时空id多个,隔开*/@TableField(typeHandler = ObjectJsonHandler.class, value = "space_belong")private JSONArray spaceBelong;/*** 下标合集*/@TableField(value = "indexes",typeHandler = IntArrayHandler.class)private List<Integer> indexes;/*** 实名属性 1是 0否*/@TableField(value = "has_sm")private String hasSm;/*** 年龄*/@TableField(value = "age")private Integer age;/*** 编号*/@TableField(value = "device_id")private String deviceId;/*** 时间*/@TableField(value = "captured_time")@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")private LocalDateTime capturedTime;}实体类里面对应的配置类
MbStringarrayHandler 如下
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
// 字符串数据处理映射类
@MappedTypes(List.class)
public class MbStringArrayHandler extends BaseTypeHandler<List<String>> {@Overridepublic void setNonNullParameter(PreparedStatement preparedStatement, int i, List<String> Strings, JdbcType jdbcType) throws SQLException {if (Strings != null) {Array array = preparedStatement.getConnection().createArrayOf(JdbcType.VARCHAR.name(), Strings.toArray(new String[Strings.size()]));preparedStatement.setArray(i, array);}}@Overridepublic List<String> getNullableResult(ResultSet resultSet, String s) throws SQLException {Array array = resultSet.getArray(s);if (array == null) {return null;}String[] result = (String[]) array.getArray();array.free();return new ArrayList<>(Arrays.asList(result));}@Overridepublic List<String> getNullableResult(ResultSet resultSet, int i) throws SQLException {Array array = resultSet.getArray(i);if (array == null) {return null;}String[] result = (String[]) array.getArray();array.free();return new ArrayList<>(Arrays.asList(result));}@Overridepublic List<String> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {Array array = callableStatement.getArray(i);if (array == null) {return null;}String[] result = (String[]) array.getArray();array.free();return new ArrayList<>(Arrays.asList(result));}
}IntArrayHandler 类如下
/*** @author * @version 1.0* @description int数组处理映射类* @date 2024/9/9 9:47**/
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;@MappedTypes(List.class)
public class IntArrayHandler extends BaseTypeHandler<List<Integer>> {@Overridepublic void setNonNullParameter(PreparedStatement preparedStatement, int i, List<Integer> integers, JdbcType jdbcType) throws SQLException {if (integers != null) {Array array = preparedStatement.getConnection().createArrayOf(JdbcType.INTEGER.name(), integers.toArray(new Integer[integers.size()]));preparedStatement.setArray(i, array);}}@Overridepublic List<Integer> getNullableResult(ResultSet resultSet, String s) throws SQLException {Array array = resultSet.getArray(s);if (array == null) {return null;}Integer[] result = (Integer[]) array.getArray();array.free();return new ArrayList<>(Arrays.asList(result));}@Overridepublic List<Integer> getNullableResult(ResultSet resultSet, int i) throws SQLException {Array array = resultSet.getArray(i);if (array == null) {return null;}Integer[] result = (Integer[]) array.getArray();array.free();return new ArrayList<>(Arrays.asList(result));}@Overridepublic List<Integer> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {Array array = callableStatement.getArray(i);if (array == null) {return null;}Integer[] result = (Integer[]) array.getArray();array.free();return new ArrayList<>(Arrays.asList(result));}
}
ObjectJsonHandler类如下
package com.*.*.config;import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.DeserializationFeature;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;/*** 自定义jsonb字段映射处理* @date 2023-12-05 下午18:21* @param <T>*/
@Slf4j
@MappedJdbcTypes(JdbcType.VARCHAR)
public class ObjectJsonHandler<T> extends BaseTypeHandler<T> {private static final ObjectMapper objectMapper;private final Class<T> type;static {objectMapper = new ObjectMapper();objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);}public ObjectJsonHandler(Class<T> type) {if (log.isTraceEnabled()) {log.trace("JacksonTypeHandler(" + type + ")");}if (null == type) {throw new PersistenceException("Type argument cannot be null");}this.type = type;}private T parse(String json) {if (json == null || json.isEmpty()) {return null;}return JSON.parseObject(json, type);}private String toJsonString(T obj) {try {return objectMapper.writeValueAsString(obj);} catch (JsonProcessingException e) {throw new RuntimeException(e);}}@Overridepublic T getNullableResult(ResultSet rs, String columnName) throws SQLException {return parse(rs.getString(columnName));}@Overridepublic T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {return parse(rs.getString(columnIndex));}@Overridepublic T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {return parse(cs.getString(columnIndex));}@Overridepublic void setNonNullParameter(PreparedStatement ps, int columnIndex, T parameter, JdbcType jdbcType)throws SQLException {ps.setString(columnIndex, toJsonString(parameter));}
}
对应的service接口如下
public interface TDemoService extends IService<TDemo> {/*** 基于任务获取详情* @param dTO* @param current 当前页* @param size 页数* @return*/Res selectPageList(TDemoDTO dTO, long current, long size);}service实现类如下
@Slf4j
@Service
@RequiredArgsConstructor
public class TDemoServiceImpl extends ServiceImpl<TDemoMapper, TDemo> implements TDemoService{//分页列表查询@Overridepublic Res selectPageList(TDemoDTO dTO, long current, long size) {Page<TDemo> pagin = new Page<>(current , size , true);IPage<TDemo> page = tDemoMapper.selectPageList(pagin, dTO);return Res.ok();}
}对应发mapper
@Repository
public interface TDemoMapper extends BaseMapper<TDemo> {/*** 分页查询指定行数据* @param page* @param dto* @return*/IPage<TCollisionResultFace> selectPageList(IPage<TDemo> page, @Param("params")TDemoDTo dto);}对应的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="*.repository.TDemoMapper"><resultMap id="exampleResultMap" type="TDemo"><result property="id" column="id"/><result property="taskId" column="taskId"/><result property="xb" column="xb"/><result property="created_time" column="createdTime"/><result property="tags" column="tags" javaType="java.util.List" jdbcType="ARRAY" typeHandler="MbStringArrayHandler"/><result property="indexes" column="indexes" javaType="java.util.List" jdbcType="ARRAY" typeHandler="IntArrayHandler"/><result property="spaceBelong" column="spaceBelong" javaType="com.alibaba.fastjson.JSONArray" jdbcType="VARCHAR" typeHandler="*.ObjectJsonHandler"/><result property="hasSm" column="hasSm"/><result property="age" column="age"/></resultMap><select id="selectPageList" resultMap="exampleResultMap" parameterType="com.*.dto.MYDTO">selectxb as xb,task_id as ,id as id,tags as tags,indexes:: INT [] as indexes,space_belong as spaceBelong,has_sm as hasSm,age as age,created_time,fromt_collision_result_face t<where><if test="params.tags !=null and params.tags.size > 0">and t.tags<choose><when test="params.tags.size() > 1"><![CDATA[ && ]]></when><otherwise><![CDATA[ @> ]]></otherwise></choose><foreach collection="params.tags" item="item" open="array[" separator="," close="]::varchar[]">#{item}</foreach></if><if test="params.taskId !=null ">and t.task_id = #{params.taskId}</if><if test="params.sfzdry != null and params.sfzdry != ''">and t.sfzdry = #{params.sfzdry}</if><if test="params.hasSm != null and params.hasSm != ''">and t.has_sm = #{params.hasSm}</if><if test="params.minAge != null and params.maxAge != null ">and t.age between #{params.minAge} and #{params.maxAge}</if></where>order by id asc</select></mapper>
