针对JPA/MyBatis解决Oracle语句执行参数/返回结果长度限制

参数长度限制：

ORA-01795: maximum number of expressions in a list is 1000

查询返回结果长度限制：

ORA-01489: result of string concatenation is too long

先说JPA持久化，

对于需要修改少量字段的场景，往往先查出来后用代码修改完最后用saveAll来保存，但对于大批量处理的情况会出现查询数量过多，则干脆直接用update语句实现，同时又带来update语句IN条件的长度超1000的问题，那干脆就分组update，999条数据一组

Map<Integer,List<Long>> liangmixianIdsMap = liangmixianIds.stream().collect(Collectors.groupingBy(liangmixianId->{
    int index = liangmixianIds.indexOf(liangmixianId);
    return index/998;
}));
liangmixianIdsMap.forEach((k,childLiangmixianIds)-> liangmixianRepository.updatePayedStatus(childLiangmixianIds,PayedStatus.PAYED));

其中liangmixianRepository.updatePayedStatus(childLiangmixianIds,PayedStatus.PAYED));实现如下

    @Transactional
    @Modifying
    @Query(value = "update liangmixianpbo pbo set pbo.payedStatus = :payedStatus, pbo.version = pbo.version + 1 where pbo.liangmixianId in (:liangmixianIds)")
    void updatePayedStatus(@Param("liangmixianIds")List<Long> liangmixianIds, @Param("payedStatus") PayedStatus payed);

然后是JPA查询怎么解决长度超1000的问题：

也是类似上述的分组逻辑，每组执行完再把结果组合到一起

比如修改前是这样的：

List<BalanceTransfer> balanceTransfers = balanceTransferRepository.findByBalanceTransferEventIdIn(balanceTransferEventIds);

修改后是这样的：

List<BalanceTransfer> balanceTransfers =  balanceTransferRepository.findByPartition1000(balanceTransferRepository::findByBalanceTransferEventIdIn,balanceTransferEventIds);

 要注意长度超一千的那个参数放在最前头

    /**
     * 按照传入的函数来进行1000个元素的分组查询
     *
     */
    default <PT1> List<ET> findByPartition1000(Function<List<PT1>, List<ET>> function, List<PT1> idList) {
        List<PT1> list = idList.stream().distinct().collect(Collectors.toList());
        
        return Lists.partition(list, PARTITION_COUNT_1000).stream().map(function).flatMap(List::stream).collect(Collectors.toList());
    }

接下来说说MyBatis的XML格式实现查询时，如何解决ORA-01795: maximum number of expressions in a list is 1000

比如这么实现，那customerIds列表超一千肯定报错

        <if test="customerIds != null  and customerIds.size()>0">
            and o.customer_id in
            <foreach item="item" index="index" collection= "customerIds" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>

 也需要按999一组为最小单元来拆分：

        <if test="customerIds != null and customerIds.size>0">
            and (o.customer_id in
            <foreach item="item" index="index" collection="customerIds" open="(" separator="," close=")">
                <if test="(index % 999) == 998"> NULL ) OR o.customer_id IN (</if>#{item}
            </foreach>
            )
        </if>

最后说下MyBatis查询结果的其中一个字段超4000长度的情况，

这样用LISTAGG函数拼接一个字段

SELECT LISTAGG(id, ',') WITHIN GROUP(order by id) id,... from ...

很容易超过Oracle 四千的长度限制，那就这么改下：

 SELECT rtrim(xmlagg(xmlparse(content id || ',' wellformed) ORDER BY id).getclobval(),',') id,...from ...

也就是用的clob大字段属性，避开普通字段长度限制

注意，resultType指定对象也需要支持clob处理

resultType="com.liangmixian.ResultMap"

则ResultMap需要这么修改：

package com.liangmixian;

import com.liangmixian.date.SystemLocalDateUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.HashMap;


public class ResultMap extends HashMap {

    private static final long serialVersionUID = -1L;
    private static final Logger LOGGER = LoggerFactory.getLogger(ResultMap.class);

    @SuppressWarnings("unchecked")
    @Override
    public Object put(Object key, Object value) {
        Object newValue = value;
       
        if (value instanceof java.sql.Timestamp) {
            DateFormat df = new java.text.SimpleDateFormat(SystemLocalDateUtil.getInstance().YYYY_MM_DD_FORMATTER);
            newValue = df.format(Date.from(((java.sql.Timestamp) value).toInstant()));
        }
        //支持clob字段类型
        if(value instanceof java.sql.Clob){
            try {
                newValue = value == null ? null : ((java.sql.Clob)value).getSubString(1L, (int)((java.sql.Clob)value).length());
            } catch (SQLException e) {
                LOGGER.warn("", e);
            }
        }
        if (key instanceof String) {
            return super.put(getKeyAsProperty((String) key), newValue);
        } else {
            return super.put(key, newValue);
        }
    }

    /**
     * 将如OPERATOR_ID的字符串格式化为operatorId
     *
     * @param key
     * @return
     */
    private String getKeyAsProperty(String key) {
        String toLowerCaseKey = key.toLowerCase();
        String[] tokens = toLowerCaseKey.split("_");
        StringBuilder result = new StringBuilder("");
        for (int i = 0; i < tokens.length; i++) {
            if (tokens[i] != null && tokens[i].length() > 0) {
                if (result.length() > 0) {
                    char first = tokens[i].charAt(0);
                    result.append(String.valueOf(first).toUpperCase() + tokens[i].substring(1));
                } else {
                    result.append(tokens[i].toLowerCase());
                }
            }
        }
        return result.toString();
    }

    public Integer getInt(Object key) {
        Object value = get(key);
        if (value == null || value instanceof Integer) {
            return (Integer) value;
        } else {
            return Integer.valueOf(value.toString());
        }
    }

    public Long getLong(Object key) {
        Object value = get(key);
        if (value == null || value instanceof Long) {
            return (Long) value;
        } else {
            return Long.valueOf(value.toString());
        }
    }

    public Double getDouble(Object key) {
        Object value = get(key);
        if (value == null || value instanceof Double) {
            return (Double) value;
        } else {
            return Double.valueOf(value.toString());
        }
    }

    public Float getFloat(Object key) {
        Object value = get(key);
        if (value == null || value instanceof Float) {
            return (Float) value;
        } else {
            return Float.valueOf(value.toString());
        }
    }

    public Date getDate(Object key) {
        Object value = get(key);
        if (value == null || value instanceof Date) {
            return (Date) value;
        } else {
            DateFormat df = DateFormat.getDateTimeInstance();
            try {
                return df.parse(value.toString());
            } catch (ParseException e) {
                throw new IllegalArgumentException("Not a valid date format", e);
            }
        }
    }

    public LocalDate getLocalDate(Object key) {
        Object value = get(key);
        if (value == null || value instanceof LocalDate) {
            return (LocalDate) value;
        } else {
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            try {
                return (LocalDate) dateTimeFormatter.parse(value.toString());
            } catch (Exception e) {
                throw new IllegalArgumentException("Not a valid date format", e);
            }
        }
    }
}