oracle 存储过程,入参为集合

数据库：oracle 10g

jdk: 1.6.0_10

 

一定要记得导入orai18n.jar，否则一遇到字符串就乱码、添加不到数据。

 

1. 因为入参是一个java.util.List集合，所以需要一个Type 类型来存放这个List数据

CREATE OR REPLACE TYPE type_license_notice IS OBJECT(
      -- 许可证编号
      license_code VARCHAR2(36),
      -- 零售客户名称
      cust_name VARCHAR2(50),
      corporation_name VARCHAR2(100),
      manager VARCHAR2(50),
      manager_tel VARCHAR2(20)
);
/

CREATE OR REPLACE TYPE license_notice_list AS VARRAY(1000) OF type_license_notice;
/

 

2. 存储过程

CREATE OR REPLACE PROCEDURE P_LICENSE_NOTICE(LICENSE_ARRAY IN LICENSE_NOTICE_LIST,
                                             RESCODE       OUT NUMBER,
                                             RESSTR        OUT VARCHAR2) IS
BEGIN

  FOR I IN 1 .. LICENSE_ARRAY.COUNT LOOP
    BEGIN
      --NOTICE := LICENSE_ARRAY(I);
      INSERT INTO YC_BUSINESS_SEND
        (ID,
         BUSINESS_ID,
         MOBILE,
         RECIPIENT_NAME,
         TITLE,
         CONTENT,
         SEND_NUMBER,
         SEND_TIME,
         STATUS,
         CREATE_TIME)
      VALUES
        (SYS_GUID(),
         '1067e964-e7df-4eee-99e6-a533735c88d7',
         LICENSE_ARRAY(I).MANAGER_TEL,
         LICENSE_ARRAY(I).CORPORATION_NAME,
         '许可证领证通知',
         '零售客户,你好，你申请的许可证[' || LICENSE_ARRAY(I).LICENSE_CODE ||
         ']已审批完成，请在2天后过来领取.',
         0,
         NULL,
         0,
         SYSDATE);
      COMMIT;
    EXCEPTION
      -- 如果有一条数据插入异常，则继续下一条
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
  RESCODE := 1;
  RESSTR  := '添加成功';
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('save license notice to YC_BUSINESS_SEND fail!' ||
                         SQLCODE || SQLERRM);
    RESCODE := 0;
    RESSTR  := '添加失败' || SQLCODE || SQLERRM;
    ROLLBACK;
  
END P_LICENSE_NOTICE;

 

3. 数据配置文件 application.properties (放在 classpath 下)

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.1.49:1523:DB02
jdbc.username=fsycsms2
jdbc.password=fsycsms2

 

4. 初始化数据库配置文件

 

public class InitServlet extends HttpServlet {

	/**
	 * @Fields serialVersionUID : 序列化id
	 */
	private static final long serialVersionUID = -283273527502774557L;

	private static final Logger log = Logger.getLogger(InitServlet.class);

	@Override
	public void init() throws ServletException {

		log.info("Start to init InitServlet.");

		// 必须要调用父类的初始化方法
		super.init();


		// 解析并读取连接DB的配置属性文件
		ParsePropertiesFile.getInstance().getConfig("application.properties");

		log.info("End to init InitServlet.");
	}
}

 

5. 在 web.xml 中配置 InitServlet

<!-- 初始化 -->
  <servlet>
  	<servlet-name>initServlet</servlet-name>
  	<servlet-class>cn.com.do1.component.init.InitServlet</servlet-class>
  	<load-on-startup>2</load-on-startup>
  </servlet>
  <servlet-mapping>
  	<servlet-name>initServlet</servlet-name>
  	<url-pattern>/initServlet</url-pattern>
  </servlet-mapping>

 

6. java调用存储过程

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import org.apache.log4j.Logger;

import cn.com.do1.common.framebase.dqdp.BaseDAOImpl;
import cn.com.do1.component.business.licencenotice.dao.ILicencenoticeDAO;
import cn.com.do1.component.business.licencenotice.model.RmLicense;
import cn.com.do1.component.business.po.YcBusinessMessagePO;
import cn.com.do1.component.common.CommonConstants;
import cn.com.do1.component.common.ConnectionPool;


public List<Object> saveBusinessSend(List<RmLicense> list) throws Exception {

	Connection conn = getConnection();

	CallableStatement stmt = conn
			.prepareCall("{call P_LICENSE_NOTICE(?,?,?)}");

	ARRAY adArray = getOracleArray("LICENSE_NOTICE_LIST", list);
	// 入参
	stmt.setArray(1, adArray);
	
	// 出参
	stmt.registerOutParameter(2, Types.INTEGER);
	stmt.registerOutParameter(3, Types.VARCHAR);

	stmt.execute();

	// 获取返回值.0:失败, 1:成功
	int resCode = stmt.getInt(2);
	// 存储过程中返回的信息
	String resStr = stmt.getString(3);

	List<Object> result = new ArrayList<Object>();
	result.add(resCode);
	result.add(resStr);

	stmt.close();

	return result;
}

/**
 * @Title: getOracleArray
 * @Description: 设置存储过程的集合入参
 * @param typeName 集合入参名称
 * @param objlist 集合数据
 * @return ARRAY
 * @throws Exception
 * @author 
 * @date 2012-11-26
 */
public static ARRAY getOracleArray(String typeName,
		List<RmLicense> objlist) throws Exception {

	ARRAY list = null;

	String userName = CommonConstants.propMap.get("jdbc.username");
	String password = CommonConstants.propMap.get("jdbc.password");
	String url = CommonConstants.propMap.get("jdbc.url");
	// 数据库url、名称
	String dbUrl = null, dbName = null, prefix = null;

	if (null != url && !"".equals(url.trim())) {

		int index1 = url.indexOf("@");
		int lastIndex = url.lastIndexOf(":");

		prefix = url.substring(0, index1);
		dbUrl = url.substring(index1, lastIndex + 1);
		dbName = url.substring(lastIndex + 1, url.length());
	}

	StringBuffer buf = new StringBuffer();
	// jdbc:oracle:thin:
	buf.append(prefix);
	// fsycsms2/fsycsms2
	buf.append(userName).append("/").append(password);
	// @192.168.1.49:1523:
	buf.append(dbUrl);
	// DB02
	buf.append(dbName);

	OracleDataSource ods = new OracleDataSource();
	ods.setURL(buf.toString());
	// ods.setURL("jdbc:oracle:thin:fsycsms2/fsycsms2@192.168.1.49:1523:DB02");
	Connection conn = ods.getConnection();

	if (objlist != null && objlist.size() > 0) {
		StructDescriptor structdesc = new StructDescriptor(
				"TYPE_LICENSE_NOTICE", conn);
		STRUCT[] structs = new STRUCT[objlist.size()];
		Object[] result = new Object[0];

		RmLicense license = null;

		for (int i = 0; i < objlist.size(); i++) {

			license = objlist.get(i);

			result = new Object[5];
			result[0] = license.getLicenseCode();
			result[1] = license.getCustName();
			result[2] = license.getCorporationName();
			result[3] = license.getManager();
			result[4] = license.getManagerTel();

			structs[i] = new STRUCT(structdesc, conn, result);

		}
		ArrayDescriptor desc = ArrayDescriptor.createDescriptor(typeName,
				conn);
		list = new ARRAY(desc, conn, structs);
	} else {
		ArrayDescriptor desc = ArrayDescriptor.createDescriptor(typeName,
				conn);
		STRUCT[] structs = new STRUCT[0];
		list = new ARRAY(desc, conn, structs);
	}
	return list;
}

 

 

 

注：如果不是使用数据库连接池，请记得关闭 Connection