Postgres数据库使用any和all判断数组解决IN和NOT IN条件参数超限的问题

拼搏现实的明天。 2024-04-07 12:38 17阅读 0赞

## 一、背景描述 ##

因mysql被oracle收购以及我们和A国的关系，越来越多的商业项目开始使用postgres数据。postgres号称最先进的关系型数据库，本身的能力支持绝大多数项目是没有问题的。特别是在处理空间几何坐标数据上能力很强。

刚接触postgres，因不熟悉，也可能会遇到一些“坑”。 参数个数超过限制导致SQL执行失败就是其中之一。

## 二、问题说明 ##

postgres参数个数限制是postgres SDK代码的限制。在PGStream.java中做了判断，参数个数超过32767个时抛异常。

public void sendInteger2(int val) throws IOException {
        
            if (val >= -32768 && val <= 32767) {
        
                this._int2buf[0] = (byte)(val >>> 8);
                this._int2buf[1] = (byte)val;
                this.pg_output.write(this._int2buf);
            } else {
        
                throw new IOException("Tried to send an out-of-range integer as a 2-byte value: " + val);
            }
        }

## 三、参考处理方案 ##

实际项目开发过程中最可能导致拼接SQL参数个数超过限制的场景有两类。一类是批量插入数据拼接的SQL；另一类是条件参数IN或者NOT IN拼接的参数过多。

批量插入的处理参考《[Mybatis批量插入数据的两种方式][Mybatis]》的描述。下面讲一下条件参数过多的场景。

### 1. IN条件参数过多导致参数超限的问题处理 ###

#### 1.1 mapper接口代码 ####

/**
         * 用Any代替IN条件
         *
         * @param column 数据库字段列名
         * @param valueList IN条件值列表
         * @return 满足条件的user列表
         */
        <T> List<User> getUserByAny(@Param("column") String column, @Param("valueList") List<T> valueList);

#### 1.2 Mybatis XML SQL语句 ####

<select id="getUserByAny" resultMap="userResult">
            SELECT id, name, age
            FROM t_user
            WHERE ${column} = any(#{valueList, typeHandler=com.postgres.constant.ElonArrayTypeHandler});
        </select>

增加自定义ElonArrayTypeHandler主要是为将Java的List转换为数据库使用的Array。有两个原因需要这样做：1. 在Java程序中使用容器List的情况多于使用数组。 2. postgres sdk包默认的数组转换器只支持int, long, double, string等少数几种类型，参数类型也必须是int\[ \]这样的数组，不能用容器。

ElonArrayTypeHandler.java代码如下：

package com.postgres.constant;
    
    import com.elon.base.util.ListUtil;
    import org.apache.ibatis.type.BaseTypeHandler;
    import org.apache.ibatis.type.JdbcType;
    import org.apache.ibatis.type.MappedJdbcTypes;
    
    import java.sql.Array;
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    /**
     * 数组类型转换器. 用于将Java中的List转换为数据库识别的数组类型。
     *
     * @param <T> 元素类型
     * @author elon
     * @since 2022-09-03
     */
    @MappedJdbcTypes(JdbcType.ARRAY)
    public class ElonArrayTypeHandler<T> extends BaseTypeHandler<List<T>> {
        
        @Override
        public void setNonNullParameter(PreparedStatement preparedStatement, int i, List<T> values, JdbcType jdbcType) throws SQLException {
        
            if (ListUtil.isEmpty(values)) {
        
                return;
            }
    
            T firstElement = values.get(0);
            EnumPGDBDataType dataType = null;
    
            // 常用的字符串，整型，小数类型做处理. 也可以新增其它类型
            if (firstElement instanceof String) {
        
                dataType = EnumPGDBDataType.TEXT;
            } else if (firstElement instanceof Integer) {
        
                dataType = EnumPGDBDataType.INTEGER;
            } else if (firstElement instanceof Double) {
        
                dataType = EnumPGDBDataType.DOUBLE;
            } else {
        
                return;
            }
    
            Connection connection = preparedStatement.getConnection();
            Object[] objects = values.toArray();
            Array array = connection.createArrayOf(dataType.getDataType(), objects);
            preparedStatement.setArray(i, array);
        }
    
        @Override
        public List<T> getNullableResult(ResultSet resultSet, String s) throws SQLException {
        
            return null;
        }
    
        @Override
        public List<T> getNullableResult(ResultSet resultSet, int i) throws SQLException {
        
            return null;
        }
    
        @Override
        public List<T> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        
            return null;
        }
    }

三个get方法在这个地方没有使用，实际使用时根据需要补充。EnumPGDBDataType中仅定义了几种常用类型，也是根据需要补充。

package com.postgres.constant;
    
    import lombok.Getter;
    
    /**
     * 定义常用的postgres数据字段类型
     *
     * @author elon
     * @since 2022-09-03
     */
    public enum EnumPGDBDataType {
        
        TEXT("text"),
    
        DOUBLE("double"),
    
        INTEGER("integer");
    
        @Getter
        private String dataType;
    
        EnumPGDBDataType(String dataType) {
        
            this.dataType = dataType;
        }
    }

### 2. NOT IN条件参数过多导致参数超限的问题处理 ###

#### 2.1 mapper接口代码 ####

/**
         * 用不等于all代替not in
         *
         * @param column 数据库字段列名
         * @param valueList not in条件值列表
         * @return 不满足条件的user列表
         */
        <T> List<User> getUserByNotAll(@Param("column") String column, @Param("valueList")  List<T> valueList);

#### 2.2 mybatis xml SQL语句 ####

<select id="getUserByNotAll" resultMap="userResult">
            SELECT id, name, age
            FROM t_user
            WHERE ${
        column} != all(#{
        valueList, typeHandler=com.postgres.constant.ElonArrayTypeHandler});
        </select>

[Mybatis]: https://blog.csdn.net/ylforever/article/details/126592028