将iBatis的choose语句及Begin...Exception块迁移至PostgreSQL+Hibernate

一、处理iBatis <choose> 逻辑的迁移 #

原iBatis中通过<choose>根据userCred布尔值动态设置created_by和modified_by的值，在Hibernate中有两种简洁的实现方式：

1. Java层提前计算参数（推荐） #

在调用Hibernate方法前，先在业务代码中计算好目标值，再传入查询，避免在SQL中嵌入复杂逻辑：

// 业务层代码
String operator = userCred ? "profile" : "basic";
userRepository.insert(userId, personalId, operator, operator);

// Hibernate Repository方法
@Modifying
@Query("INSERT INTO users(user_id, personal_id, created_by, modified_by) " +
       "VALUES (:userId, :personalId, :createdBy, :modifiedBy)")
void insert(@Param("userId") long userId,
            @Param("personalId") String personalId,
            @Param("createdBy") String createdBy,
            @Param("modifiedBy") String modifiedBy);

2. 在SQL中使用CASE表达式 #

如果需要在SQL层面直接处理条件判断，可使用PostgreSQL支持的CASE WHEN语法（JPQL和原生SQL均支持）：

@Modifying
@Query(value = """
        INSERT INTO users(user_id, personal_id, created_by, modified_by)
        VALUES (:userId, :personalId,
                CASE WHEN :userCred THEN 'profile' ELSE 'basic' END,
                CASE WHEN :userCred THEN 'profile' ELSE 'basic' END)
        """, nativeQuery = true)
void insert(@Param("userId") long userId,
            @Param("personalId") String personalId,
            @Param("userCred") boolean userCred);

二、适配PostgreSQL的异常处理块 #

原Oracle PL/SQL中的EXCEPTION WHEN HIST_INDEX需要转换为PostgreSQL的PL/pgSQL语法，核心是异常类型的映射：

1. 确定异常映射关系 #

• 如果HIST_INDEX是Oracle中捕获唯一索引冲突的异常，对应PostgreSQL的异常名为unique_violation（SQLSTATE码23505）。
• 如果是自定义异常，需先在PostgreSQL中创建对应异常（如自定义错误码），再在异常块中捕获。

2. 实现PL/pgSQL异常处理块 #

通过Hibernate原生SQL执行包含异常处理的PL/pgSQL代码：

@Modifying
@Query(value = """
        DO $$
        BEGIN
            INSERT INTO users(user_id, personal_id, created_by, modified_by)
            VALUES (:userId, :personalId,
                    CASE WHEN :userCred THEN 'profile' ELSE 'basic' END,
                    CASE WHEN :userCred THEN 'profile' ELSE 'basic' END);
        EXCEPTION
            WHEN unique_violation THEN -- 替换为实际需要捕获的异常
                UPDATE users 
                SET modified_by = CASE WHEN :userCred THEN 'profile' ELSE 'basic' END
                WHERE user_id = :userId; -- 补全原代码中的UPDATE逻辑
        END $$;
        """, nativeQuery = true)
void insert(@Param("userId") long userId,
            @Param("personalId") String personalId,
            @Param("userCred") boolean userCred);

注意事项 #

• PostgreSQL的PL/pgSQL块需用DO $$ ... $$包裹（作为匿名执行块）。
• 若需捕获自定义异常，需先在PostgreSQL中定义，例如：

  CREATE OR REPLACE FUNCTION raise_hist_index() RETURNS trigger AS $$
  BEGIN
      RAISE EXCEPTION 'HIST_INDEX' USING ERRCODE = 'P0001';
  END;
  $$ LANGUAGE plpgsql;
  

  之后在异常块中用WHEN SQLSTATE 'P0001' THEN捕获。

内容的提问来源于stack exchange，提问作者Peter Penzov