Java Mybatis 接收postgreSQL中array_agg函数结果集

梦里梦外; 2024-03-26 16:32 17阅读 0赞

## **问题描述** ##

因为项目中业务需求，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"类型为数组。由于与[数据库字段类型][Link 1]不匹配，如果不做任何处理的话无论是查询还是插入都会报错。下面介绍介绍两种方式来解决这个问题！

## **解决** ##

### 方式一： ###

通过设置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);
    
    }

创建一个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;
        }

实体类返回对象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;
    
    }

### 方式二： ###

通过 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;
    
    }

ArrayTypeHandler.class类代码，同上面方式一的ArrayTypeHandler类代码！

**注意事项：**

需要用到两个注解才能生效

1.  @TableName(autoResultMap = true)

1.  @TableField(typeHandler = ArrayTypeHandler.class)

## 相关知识 ##

PostgreSQL是一种开源的关系型数据库管理系统（RDBMS），它致力于提供可靠性、数据完整性和性能的高级解决方案。以下是关于PostgreSQL的简介：

1.  ### 特点： ###
    
     *  开源软件：PostgreSQL是一个完全开源的数据库系统，可以免费使用和修改。
     *  关系型数据库：PostgreSQL基于关系模型，使用SQL语言进行数据管理。
     *  可扩展性：PostgreSQL支持水平和垂直扩展，可以处理大规模数据和高并发访问。
     *  ACID事务：PostgreSQL支持ACID（原子性、一致性、隔离性和持久性）事务，确保数据的完整性和一致性。
     *  多版本并发控制：PostgreSQL使用多版本并发控制（MVCC）机制，实现高并发读写操作。
     *  支持复杂数据类型：PostgreSQL支持各种复杂的数据类型，如数组、JSON、XML等。
     *  强大的扩展性：PostgreSQL拥有丰富的可扩展性，支持自定义函数、存储过程、插件等。
2.  ### 功能和组件： ###
    
     *  数据库管理：支持创建数据库、表、索引、视图、触发器等。
     *  数据查询：使用SQL语言进行数据查询和操作。
     *  数据完整性约束：支持定义主键、外键、唯一约束、检查约束等，确保数据的完整性。
     *  备份和恢复：提供备份和恢复工具，以确保数据的安全性。
     *  安全性：支持用户和角色管理、访问控制、SSL加密等安全功能。
     *  复制和高可用性：支持流复制（Streaming Replication）和逻辑复制（Logical Replication），实现数据的冗余和高可用性。
3.  ### 应用场景： ###
    
     *  Web应用程序：PostgreSQL被广泛应用于各种Web应用程序，如电子商务、社交媒体、博客平台等。
     *  地理信息系统（GIS）：PostgreSQL提供了丰富的地理空间功能和插件，适用于存储和分析地理数据。
     *  数据仓库和大数据：由于其扩展性和高性能，PostgreSQL可以应用于数据仓库和大数据分析领域。
     *  科学研究：PostgreSQL在科学研究领域得到广泛应用，用于存储和分析大量实验数据。
     *  金融和银行业务：由于其对事务处理的支持和数据安全性，PostgreSQL常用于金融和银行业务。

总而言之，PostgreSQL是一个强大、可靠且高度可扩展的关系型数据库管理系统，具有广泛的应用领域和丰富的功能。它是一个受欢迎的替代选项，可与其他商业数据库系统相媲美。

[Link 1]: https://so.csdn.net/so/search?q=%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AD%97%E6%AE%B5%E7%B1%BB%E5%9E%8B&spm=1001.2101.3001.7020