1.date_format(）函数 需要改成人大金仓支持的函数 to_date('2023-09-10', 'yyyy-mm-dd')

2. mybatis配置文件中存在list时，报错Unable to make public int java.util.Arrays$ArrayList.size()

配置如下：

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

报错如下：

### Error querying database. Cause: java.lang.reflect.InaccessibleObjectException: Unable to make public int java.util.Arrays$ArrayList.size() accessible: module java.base does not "opens java.util" to unnamed module @302c971f ### Cause: java.lang.reflect.InaccessibleObjectException: Unable to make public int java.util.Arrays$ArrayList.size() accessible: module java.base does not "opens java.util" to unnamed module @302c971f

解决方案：

<if test="educationDegree != null and !educationDegree.isEmpty">
    and (education_degree in
         <foreach collection="educationDegree" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>)
</if>

原因是ArrayList.size() 是java内部函数，mybatis如果要调用的话，需要加如下参数：

你可以通过修改JVM启动参数，强制Java模块系统开放所需的包。具体来说，可以在启动JVM时添加以下参数：

--add-opens java.base/java.util=ALL-UNNAMED

错误: 函数 CONCAT(bigint, bigint) 不是唯一的

配置如下：

<if test="typeList != null and !typeList.isEmpty ">
  and CONCAT(dbao.disability_category,dbao.disability_level) in
    <foreach collection="typeList" item="item" open="(" close=")" separator=",">
         #{item}
    </foreach>
</if>

这个错误提示表明Kingbase数据库无法确定使用哪个CONCAT函数来连接两个bigint类型的列。为了解决这个问题，你需要显式地将bigint类型的列转换为字符串类型，然后再进行连接。

解决方案：

你可以使用CAST函数将bigint类型的列转换为VARCHAR类型，然后再进行连接。以下是修改后的SQL语句：

<if test="typeList != null and !typeList.isEmpty ">
  and CONCAT(CAST(dbao.disability_category AS VARCHAR), CAST(dbao.disability_level AS VARCHAR)) in
    <foreach collection="typeList" item="item" open="(" close=")" separator=",">
         #{item}
    </foreach>
</if>

解决迁移数据后，主键冲突问题

现象：迁移数据后，由于未执行过nextval，所以首次执行只会获取最大的主键值并不会加一，从而导致冲突。

解决方案：

DO $

DECLARE

seq_name text;

schema_name text;

table_name text;

column_name text;

max_value bigint;

BEGIN

FOR seq_name, schema_name, table_name, column_name IN

SELECT c.relname AS sequence_name, n.nspname AS schema_name, t.relname AS table_name, a.attname AS column_name

FROM pg_class c

JOIN pg_sequence s ON c.oid = s.seqrelid

JOIN pg_namespace n ON c.relnamespace = n.oid

JOIN pg_depend d ON d.objid = c.oid AND d.deptype = 'a'

JOIN pg_class t ON d.refobjid = t.oid

JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid

WHERE c.relkind = 'S'

AND n.nspname NOT IN ('SYS_HM', 'other_restricted_schema') -- 添加其他受限模式

LOOP

BEGIN

-- 获取表的主键最大值

EXECUTE format('SELECT MAX(%I) FROM %I.%I', column_name, schema_name, table_name)

INTO max_value;

-- 如果表中没有数据，max_value 将为 NULL

IF max_value IS NULL THEN

max_value := 0;

END IF;

-- 设置序列的当前值为最大值加一

EXECUTE format('SELECT setval(''%I.%I'', %L)', schema_name, seq_name, max_value + 1);

RAISE NOTICE 'Sequence % in schema % set to %', seq_name, schema_name, max_value + 1;

EXCEPTION

WHEN undefined_table THEN

RAISE NOTICE 'Sequence % does not exist in schema %', seq_name, schema_name;

-- 创建缺失的序列

EXECUTE format('CREATE SEQUENCE %I.%I', schema_name, seq_name);

RAISE NOTICE 'Sequence % in schema % created', seq_name, schema_name;

END;

END LOOP;

END $;

在 Kingbase 中，::date 这种类型转换语法可能不被支持，导致的报错

报错现象：

Preparing: SELECT * FROM activity_user WHERE activity_start_time::date = CURRENT_DATE - 1 AND status = 1;

09:00:00.084 [quartzScheduler_Worker-1] ERROR c.a.d.f.s.StatFilter - [mergeSql,150] - merge sql error, dbType kingbase, druid-1.2.14, sql : SELECT *

FROM activity_user

WHERE activity_start_time::date = CURRENT_DATE - 1 AND status = 1;

问题分析：

Kingbase 数据库是基于 PostgreSQL 的，但可能在某些语法上有所不同。

在 Kingbase 中，::date 这种类型转换语法可能不被支持，或者需要使用不同的语法。你可以尝试使用 CAST 函数来进行类型转换，并确保日期计算的语法正确。

解决方案：

SELECT *

FROM disability_activity_user

WHERE CAST(activity_start_time AS DATE) = CURRENT_DATE - INTERVAL '1 day'

AND status = 1;