Spring JdbcTemplate 调用oracle存储过程,实现批量数据插入

研究了一下，写出来，方便有同样需求的兄弟。如果哪位大神有更好的方法，欢迎赐教。

在数据库层面需要做一下步骤

1：创建数据库表、创建数据库类型

--创建测试数据库
create table USERS
(
  USERCODE  VARCHAR2(20),
  LOGINNAME VARCHAR2(20)
)
--定义oracle对象
create or replace type user_obj as object
(
   idx  varchar2(20),
   name varchar2(30)
);
create or replace type user_tab as table of user_obj;

--创建一个测试存储过程
create or replace procedure test_batch
(
   p_items in user_tab, 
   p_out   out string
) is
  mob user_obj;
begin
  for idx in p_items.first() .. p_items.last() 
  loop
    mob   := p_items(idx);  
    insert into users(usercode, loginname)
    values(mob.idx, mob.name); 
  end loop;
  commit;
  p_out := '成功';
  return;
end;

2： Java代码开发

// 定义JAVA实体对象,该对象与数据库实体OBJ对应
import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;
import oracle.jpub.runtime.MutableStruct;
import oracle.sql.Datum;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;

public class User implements ORAData{
	private String idx;
	private String name;
	
	public static final String typeName = "USER_OBJ"; //这里需要大写     
    protected MutableStruct struct;  
    static int[] sqlType = { OracleTypes.VARCHAR, OracleTypes.VARCHAR};  
    static ORADataFactory[] factory = new ORADataFactory[sqlType.length];  
	    
    public User() {  
        struct = new MutableStruct(new Object[sqlType.length], sqlType, factory);  
    } 
    public User(String idx,String name) {  
        this();          
        this.idx=idx;
        this.name=name;
    }
    @Override
	public Datum toDatum(Connection conn) throws SQLException {
		struct.setAttribute(0, this.idx);
        struct.setAttribute(1, this.name);
        return struct.toDatum(conn, typeName);  
	}
	public String getIdx() {
		return idx;
	}
	public void setIdx(String idx) {
		this.idx = idx;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

// 定义测试方法
@Test
public void testProcedure() {
	List<User> userList = new ArrayList<User>();
	User s1 = new User();
	User s2 = new User();
	
	s1.setIdx("110");
	s1.setName("zz");
	
	s2.setIdx("111");
	s2.setName("xx");
	
	userList.add(s1);
	userList.add(s2);
	
	String sql = "{call test_batch(?,?)}";
	String resp = jdbcTemplate.execute(sql, new CallableStatementCallback<String>() {
		public String doInCallableStatement(CallableStatement cstmt) throws SQLException, DataAccessException {
			Connection con = cstmt.getConnection().getMetaData().getConnection();
			ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("USER_TAB", con);
			ARRAY vArray = new ARRAY(tabDesc, con, userList.toArray());
			cstmt.setArray(1, vArray);
			cstmt.registerOutParameter(2, Types.VARCHAR);
			cstmt.execute();
			
			return cstmt.getString(2);
		}
	});
	System.out.println(resp);
}

3： 测试结果

测试成功，数据库查询结果，成功。

 

转载于:https://my.oschina.net/u/436167/blog/879350