使用 MyBatis 和 Oracle 存储过程进行批量更新

问题描述

致力于使用 MyBatis 和 Oracle 的存储过程进行批量数据库更新的遗留解决方案.当前版本的 Mapper 与此类似

Working on a legacy solution that uses MyBatis and Oracle's stored procedures for bulk database updates. Current version of Mapper looks similar to this

@Mapper
public interface MyMapper {
   void doUpdate(@Param("in") Map<String, List> in,
                 @Param("out") Map<String, List> out);
}

这个想法是提供一个具有相同长度的列表的映射，字段值作为in"参数，以使用这些列表作为参数来调用这样的存储过程

The idea is to provide a map of lists of the same length with fields values as "in" parameter to use those lists as an arguments to call a stored procedure like this

 <select id="doUpdate"
        statementType="CALLABLE">
    <![CDATA[
    {
    CALL doUpdate(
            #{in.field1, mode=IN, jdbcType=ARRAY, jdbcTypeName=MY_TYPE,     typeHandler=NumberTypeHandler },
            #{in.field2, mode=IN, jdbcType=ARRAY, jdbcTypeName=MY_TYPE,     typeHandler=NumberTypeHandler},
            #{in.field3, mode=IN, jdbcType=ARRAY, jdbcTypeName=MY_TYPE,     typeHandler=NumberTypeHandler},
            #{out.field1, mode=IN, jdbcType=ARRAY, jdbcTypeName=MY_TYPE,    typeHandler=NumberTypeHandler })}]]>
 </select>

然后在存储过程中遍历这些数组以一一更新实体.

And then iterate over these arrays in stored procedure to update entities one by one.

问题是我必须在调用之前初始化所有映射/数组并手动填充它们，并且还手动将结果转换回 Java 对象.所以现在它看起来太复杂和冗长，我正在努力寻找更准确的解决方案.

The issue is that I have to initialize all the Maps/Arrays and fill them manually before the call and also convert the results back to Java objects manually also. So right now it looks too complicated and verbose and I'm trying to find a more accurate solution.

所以问题是:有没有更简单的方法来为 MyBatis 的存储过程提供对象列表?我尝试过 parameterMap 但实际参数类型在我的情况下应该是 List 并且该 List 的元素应该是自定义 Java 对象所以我没有设法使用这种方法找到合适的解决方案.

So the question is: is there an easier way to provide the list of objects to stored procedure with MyBatis? I tried parameterMap but the actual parameter type should be List in my case and elements of that List should be custom Java objects so I did not managed to find a suitable solution using this approach.

推荐答案

过程可以接受表类型参数，您可以编写执行转换的自定义类型处理程序.

A procedure can take table type parameters and you can write a custom type handler that performs the conversion.

使用具体对象可能更容易解释.
我将使用 S_USER_OBJ ...

It may be easier to explain using concrete objects.
Instead of MY_TYPE, I'll use S_USER_OBJ ...

create or replace type S_USER_OBJ as object (
  id integer,
  name varchar(20)
);

...一张桌子...

create table users (
  id integer,
  name varchar(20)
);

...还有一个 POJO.

...and a POJO.

public class User {
  private Integer id;
  private String name;
  // setter/getter
}

这是一个新类型，它是 S_USER_OBJ 的集合.

Here is the new type which is a collection of S_USER_OBJ.

create or replace type S_USER_OBJ_LIST as table of S_USER_OBJ;

该过程可以将表类型作为参数.例如

The procedure can take the table type as parameters. e.g.

create or replace procedure doUpdate(
  user_list in S_USER_OBJ_LIST,
  user_out out S_USER_OBJ_LIST
) is
begin
  -- process IN param
  for i in user_list.first .. user_list.last loop
    update users
      set name = user_list(i).name)
      where id = user_list(i).id;
  end loop;
  -- set OUT param
  select * bulk collect into user_out
    from (
      select S_USER_OBJ(u.id, u.name) from users u
    );
end;

Mapper 如下所示:

Mapper would look as follows:

void doUpdate(
  @Param("users") List<User> users,
  @Param("outParam") Map<String, ?> outParam);

<update id="doUpdate" statementType="CALLABLE">
  {call doUpdate(
    #{users,typeHandler=pkg.UserListTypeHandler},
    #{outParam.outUsers,jdbcType=ARRAY,jdbcTypeName=S_USER_OBJ_LIST,mode=OUT,typeHandler=pkg.UserListTypeHandler}
  )}
</update>

UserListTypeHandler 是一个自定义类型处理程序，用于将 List 与 STRUCT 的 ARRAY 进行转换.

UserListTypeHandler is a custom type handler that converts List<User> to/from an ARRAY of STRUCT.

import java.math.BigDecimal;
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.sql.Struct;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import oracle.jdbc.driver.OracleConnection;

public class UserListTypeHandler extends
    BaseTypeHandler<List<User>>{

  @Override
  public void setNonNullParameter(
      PreparedStatement ps, int i, List<User> parameter,
      JdbcType jdbcType) throws SQLException {
    Connection conn = ps.getConnection();
    List<Struct> structs = new ArrayList<Struct>();
    for (int idx = 0; idx < parameter.size(); idx++) {
      User user = parameter.get(idx);
      Object[] result = { user.getId(), user.getName() };
      structs.add(conn.createStruct("S_USER_OBJ", result));
    }
    Array array = ((OracleConnection) conn)
      .createOracleArray("S_USER_OBJ_LIST",
      structs.toArray());
    ps.setArray(i, array);
    array.free();
  }

  @Override
  public List<User> getNullableResult(
      CallableStatement cs,
      int columnIndex) throws SQLException {
    List<User> result = new ArrayList<>();
    Array array = cs.getArray(columnIndex);
    Object[] objs = (Object[]) array.getArray();
    for (Object obj : objs) {
      Object[] attrs = ((Struct) obj).getAttributes();
      result.add(new User(
        ((BigDecimal) attrs[0]).intValue(),
        (String) attrs[1]));
    }
    array.free();
    return result;
  }
  ...
}

使用该方法的代码看起来像这样.

The code using the method would look something like this.

Map<String, ?> outParam = new HashMap<>();
mapper.doUpdate(userList, outParam);
List<User> outUsers = outParam.get("outUsers");

对于OUT参数，还有另一种使用refcursor和result map的方法.
在 mapper 语句中，指定 OUT 参数如下.

---

For OUT parameter, there also is another way using refcursor and result map.
In the mapper statement, specify the OUT parameter as follows.

#{outParam.outUsers,jdbcType=CURSOR,javaType=java.sql.ResultSet,mode=OUT,resultMap=userRM}

结果图非常简单.

<resultMap type="test.User" id="userRM">
  <id property="id" column="id" />
  <result property="name" column="name" />
</resultMap>

在程序中，将OUT参数声明为SYS_REFCURSOR

In the procedure, declare OUT parameter as SYS_REFCURSOR

create or replace procedure doUpdate(
  user_list in S_USER_OBJ_LIST,
  user_out out SYS_REFCURSOR
) is
begin
  ...
  -- set OUT param
  open user_out for select * from users;
end;

这是一个可执行的演示:
https://github.com/harawata/mybatis-issues/tree/master/so-56834806

Here is an executable demo:
https://github.com/harawata/mybatis-issues/tree/master/so-56834806

这篇关于使用 MyBatis 和 Oracle 存储过程进行批量更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！