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

一、背景描述

因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);
        }
    }
1
2
3
4
5
6
7
8
9

三、参考处理方案

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

批量插入的处理参考《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
3
4
5
6
7
8

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>
1
2
3
4
5

增加自定义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  元素类型
 * @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;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

三个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;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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);
1
2
3
4
5
6
7
8

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>

1
2
3
4
5
6