问题描述
因为项目中业务需求,postgreSQL使用了group by与array_agg函数将varchar或者bigint类型变为一个数组,这样多行结果就变为了一行结果,但是mybatis的jdbcType没有对应的数据类型。如果用string类型去接收,还得用java代码处理成数组,返回给前端有点麻烦。
array_agg函数
array_agg函数 和string_agg 函数类似,最主要的区别为返回的类型为数组,数组数据类型同输入数据类型一致,array_agg函数支持两种语法。
准备工作
(1)MyBatis 中的 TypeHandler 类型处理器用于 JavaType 与 JdbcType 之间的转换,假设我们数据库表中有字段"ids"类型为字符串。而对应的实体类的字段"ids"类型为数组。由于与数据库字段类型不匹配,如果不做任何处理的话无论是查询还是插入都会报错。下面介绍介绍两种方式来解决这个问题!
解决
方式一:
通过设置mybatis xml配置和创建自定义数据类型处理类解决。
教程
maper类 以mybatis-plus常规分页写法示例,先创建一个AdvancedDetectionMapper接口,声明查询或者增删改的接口方法:
import java.util.List;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.standardmodel.mineupdown.dto.AdvancedDetectionTunnelDTO;
import org.springblade.standardmodel.mineupdown.entity.AdvancedDetection;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
public interface AdvancedDetectionMapper extends BaseMapper<AdvancedDetection> {
List<AdvancedDetectionTunnelDTO> tunnelPage(IPage page, String tunnelName);
}
import java.util.List;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.standardmodel.mineupdown.dto.AdvancedDetectionTunnelDTO;
import org.springblade.standardmodel.mineupdown.entity.AdvancedDetection;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
public interface AdvancedDetectionMapper extends BaseMapper<AdvancedDetection> {
List<AdvancedDetectionTunnelDTO> tunnelPage(IPage page, String tunnelName);
}
创建一个AdvancedDetectionMapper.xml的文件,在AdvancedDetectionMapper.xml文件中设置查询sql,返回结果用resultMap,定义resultMap返回结果集,类型AdvancedDetectionTunnelDTO类,加入result 标签,指定AdvancedDetectionTunnelDTO类中ids属性和数据库ids字段的预处理类ArrayTypeHandler。
<resultMap id="BaseResultMap" type="org.springblade.standardmodel.mineupdown.dto.AdvancedDetectionTunnelDTO">
<result column="ids" jdbcType="ARRAY" property="ids" typeHandler="org.springblade.standardmodel.common.handler.ArrayTypeHandler"/>
</resultMap>
<select id="tunnelPage" resultMap="BaseResultMap">
SELECT
ids,
tunnel_id,
tunnel_name,
anomalous_region_num
FROM
(
SELECT
"array_agg" ( ID ) ids,
tunnel_id,
tunnel_name,
"sum" ( anomalous_region_num ) anomalous_region_num
FROM
(
SELECT
ad.ID,
ad.tunnel_id,
tb.hd_hdmc AS tunnel_name,
( SELECT COUNT ( 1 ) FROM gis_standard_abnormal_area aa WHERE aa.detection_id = ad.ID ) AS anomalous_region_num
FROM
gis_standard_advanced_detection ad
LEFT JOIN gis_standard_tunnel_base tb ON ad.tunnel_id = tb.ID
) T
GROUP BY
T.tunnel_id,
T.tunnel_name
) r
<where>
<if test="tunnelName!=null">
and r.tunnel_name like CONCAT('%',#{tunnelName},'%')
</if>
</where>
</select>
自定义数组类型处理类 ArrayTypeHandler
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;
import java.sql.*;
public class ArrayTypeHandler extends BaseTypeHandler<Object[]> {
private static final String TYPE_NAME_VARCHAR = "varchar";
private static final String TYPE_NAME_INTEGER = "integer";
private static final String TYPE_NAME_BOOLEAN = "boolean";
private static final String TYPE_NAME_NUMERIC = "numeric";
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object[] parameter, JdbcType jdbcType) throws SQLException {
String typeName = null;
if (parameter instanceof Integer[]) {
typeName = TYPE_NAME_INTEGER;
} else if (parameter instanceof String[]) {
typeName = TYPE_NAME_VARCHAR;
} else if (parameter instanceof Boolean[]) {
typeName = TYPE_NAME_BOOLEAN;
} else if (parameter instanceof Double[]) {
typeName = TYPE_NAME_NUMERIC;
}
if (typeName == null) {
throw new TypeException("ArrayTypeHandler parameter typeName error, your type is " + parameter.getClass().getName());
}
Connection conn = ps.getConnection();
Array array = conn.createArrayOf(typeName, parameter);
ps.setArray(i, array);
}
@Override
public Object[] getNullableResult(ResultSet resultSet, String s) throws SQLException {
return getArray(resultSet.getArray(s));
}
@Override
public Object[] getNullableResult(ResultSet resultSet, int i) throws SQLException {
return getArray(resultSet.getArray(i));
}
@Override
public Object[] getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
return getArray(callableStatement.getArray(i));
}
private Object[] getArray(Array array) {
if (array == null) {
return null;
}
try {
return (Object[]) array.getArray();
} catch (Exception e) {
}
return null;
}
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;
import java.sql.*;
public class ArrayTypeHandler extends BaseTypeHandler<Object[]> {
private static final String TYPE_NAME_VARCHAR = "varchar";
private static final String TYPE_NAME_INTEGER = "integer";
private static final String TYPE_NAME_BOOLEAN = "boolean";
private static final String TYPE_NAME_NUMERIC = "numeric";
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object[] parameter, JdbcType jdbcType) throws SQLException {
String typeName = null;
if (parameter instanceof Integer[]) {
typeName = TYPE_NAME_INTEGER;
} else if (parameter instanceof String[]) {
typeName = TYPE_NAME_VARCHAR;
} else if (parameter instanceof Boolean[]) {
typeName = TYPE_NAME_BOOLEAN;
} else if (parameter instanceof Double[]) {
typeName = TYPE_NAME_NUMERIC;
}
if (typeName == null) {
throw new TypeException("ArrayTypeHandler parameter typeName error, your type is " + parameter.getClass().getName());
}
Connection conn = ps.getConnection();
Array array = conn.createArrayOf(typeName, parameter);
ps.setArray(i, array);
}
@Override
public Object[] getNullableResult(ResultSet resultSet, String s) throws SQLException {
return getArray(resultSet.getArray(s));
}
@Override
public Object[] getNullableResult(ResultSet resultSet, int i) throws SQLException {
return getArray(resultSet.getArray(i));
}
@Override
public Object[] getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
return getArray(callableStatement.getArray(i));
}
private Object[] getArray(Array array) {
if (array == null) {
return null;
}
try {
return (Object[]) array.getArray();
} catch (Exception e) {
}
return null;
}
实体类返回对象AdvancedDetectionTunnelDTO
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @author TARZAN
*/
@Data
public class AdvancedDetectionTunnelDTO {
@ApiModelProperty("巷道名称")
private String tunnelName;
@ApiModelProperty("异常区域数")
private Integer anomalousRegionNum;
@ApiModelProperty("超前探id集合")
private Long[] ids;
}
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @author TARZAN
*/
@Data
public class AdvancedDetectionTunnelDTO {
@ApiModelProperty("巷道名称")
private String tunnelName;
@ApiModelProperty("异常区域数")
private Integer anomalousRegionNum;
@ApiModelProperty("超前探id集合")
private Long[] ids;
}
方式二:
通过 mybatis-plus @TableField(typeHandler = ArrayTypeHandler.class) 注解,此种方法可以不用谢自定义sql,可以使用mybatis plus 自带的增删改查方法实现,数据库字段类型转换预处理!
数据库映射实体类返回对象AdvancedDetectionTunnel,代码如下:
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.experimental.Accessors;
import org.apache.ibatis.type.ArrayTypeHandler;
/**
* @author TARZAN
*/
@Data
@Accessors(chain = true)
@TableName(autoResultMap = true)
public class AdvancedDetectionTunnel {
@ApiModelProperty("巷道名称")
private String tunnelName;
@ApiModelProperty("异常区域数")
private Integer anomalousRegionNum;
@ApiModelProperty("超前探id集合")
@TableField(typeHandler = ArrayTypeHandler.class)
private Long[] ids;
}
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.experimental.Accessors;
import org.apache.ibatis.type.ArrayTypeHandler;
/**
* @author TARZAN
*/
@Data
@Accessors(chain = true)
@TableName(autoResultMap = true)
public class AdvancedDetectionTunnel {
@ApiModelProperty("巷道名称")
private String tunnelName;
@ApiModelProperty("异常区域数")
private Integer anomalousRegionNum;
@ApiModelProperty("超前探id集合")
@TableField(typeHandler = ArrayTypeHandler.class)
private Long[] ids;
}
ArrayTypeHandler.class类代码,同上面方式一的ArrayTypeHandler类代码!
注意事项:
需要用到两个注解才能生效
- @TableName(autoResultMap = true)
- @TableField(typeHandler = ArrayTypeHandler.class)
相关知识
PostgreSQL是一种开源的关系型数据库管理系统(RDBMS),它致力于提供可靠性、数据完整性和性能的高级解决方案。以下是关于PostgreSQL的简介:
特点:
- 开源软件:PostgreSQL是一个完全开源的数据库系统,可以免费使用和修改。
- 关系型数据库:PostgreSQL基于关系模型,使用SQL语言进行数据管理。
- 可扩展性:PostgreSQL支持水平和垂直扩展,可以处理大规模数据和高并发访问。
- ACID事务:PostgreSQL支持ACID(原子性、一致性、隔离性和持久性)事务,确保数据的完整性和一致性。
- 多版本并发控制:PostgreSQL使用多版本并发控制(MVCC)机制,实现高并发读写操作。
- 支持复杂数据类型:PostgreSQL支持各种复杂的数据类型,如数组、JSON、XML等。
- 强大的扩展性:PostgreSQL拥有丰富的可扩展性,支持自定义函数、存储过程、插件等。
功能和组件:
- 数据库管理:支持创建数据库、表、索引、视图、触发器等。
- 数据查询:使用SQL语言进行数据查询和操作。
- 数据完整性约束:支持定义主键、外键、唯一约束、检查约束等,确保数据的完整性。
- 备份和恢复:提供备份和恢复工具,以确保数据的安全性。
- 安全性:支持用户和角色管理、访问控制、SSL加密等安全功能。
- 复制和高可用性:支持流复制(Streaming Replication)和逻辑复制(Logical Replication),实现数据的冗余和高可用性。
应用场景:
- Web应用程序:PostgreSQL被广泛应用于各种Web应用程序,如电子商务、社交媒体、博客平台等。
- 地理信息系统(GIS):PostgreSQL提供了丰富的地理空间功能和插件,适用于存储和分析地理数据。
- 数据仓库和大数据:由于其扩展性和高性能,PostgreSQL可以应用于数据仓库和大数据分析领域。
- 科学研究:PostgreSQL在科学研究领域得到广泛应用,用于存储和分析大量实验数据。
- 金融和银行业务:由于其对事务处理的支持和数据安全性,PostgreSQL常用于金融和银行业务。
总而言之,PostgreSQL是一个强大、可靠且高度可扩展的关系型数据库管理系统,具有广泛的应用领域和丰富的功能。它是一个受欢迎的替代选项,可与其他商业数据库系统相媲美。