时间戳

首页 > 解决方案 > 如何编写 mybatis 映射器以便在中间创建的临时表中插入？

java - 如何编写 mybatis 映射器以便在中间创建的临时表中插入？

问题描述

我正在尝试使用注释 mybatis 为以下 sql 提取编写映射器：

CREATE TEMP TABLE XYZ
.
/* Something */
.


CREATE TEMP TABLE ABC
as
Select ptype,pvalue,id,djob
from XYZ
where  pvalue> 0 
and CASE WHEN djob = ptype
then pvalue = d_job_partition_value END;

Insert into ABC
Select ptype,pvalue,id,djob
from XYZ
where 
CASE WHEN ptype = 'region' AND d_job_partition_type = 'region' 
THEN pvalue in (Select distinct CAST(region_id as NUMERIC(38,0)) from {db}.{table} where region_id=d_job_partition_value) END;

我尝试编写的映射器类请记住，它ABC是在两者之间创建的临时表。

@Select("with XYZ as 
         (
         /* Something */
         ),
         ABC as (
         Select ptype,pvalue,id,djob
         from XYZ
         where  pvalue> 0 
         and CASE WHEN djob = ptype
         then pvalue = d_job_partition_value END),

         insert into ABC
         (ptype,pvalue,id,djob) values(
           Select ptype,pavalue,id,djob
           from XYZ
           CASE WHEN ptype = 'region' AND d_job_partition_type = 'region' 
           THEN pvalue in (Select distinct CAST(region_id as NUMERIC(38,0)) from {db}.{table} 
            where region_id=d_job_partition_value) END ")

错误是：

ERROR: syntax error at or near "into"
    [junit]   Position: 5828
    [junit]     at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
    [junit]     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107)
    [junit]     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
    [junit]     at org.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:282)
    [junit]     at com.sun.proxy.$Proxy28.selectList(Unknown Source)
    [junit]     at org.apache.ibatis.session.SqlSessionManager.selectList(SqlSessionManager.java:171)
    [junit]     at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:114)
    [junit]     at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
    [junit]     at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
    [junit]     at com.sun.proxy.$Proxy29.okey(Unknown Source)

我知道那@Insert是用来插入值的。但在这种情况下，临时表的创建会产生问题。

提前致谢。

标签： javapostgresqlamazon-redshiftmybatismybatis-mapper

解决方案

@Insert您可以在映射器方法的注释中指定多个语句（包括创建临时表） ：

@Insert(
  "create temp table xyz as select * from whatever; " +
  "CREATE TEMP TABLE ABC as Select ptype,pvalue,id,djob from XYZ;" +
  "Insert into ABC Select ptype,pvalue,id,djob from XYZ;"
)
void insertMultiple();

该解决方案不仅适用于 postgres。我已经检查过了，它也适用于 H2。

请注意，您正在尝试的选项即CTE aka with-query 不会创建临时表。

它们看起来像临时表，甚至文档都说它们can be thought of as defining temporary tables that exist just for one query，但这仅用于解释目的。它们不是临时表，重要的区别是不能插入到这个辅助语句中。

推荐阅读