Spring 将集合传人Oracle

在业务开发过程中经常会遇到将大量的参数传人到Oracle的存储过程中,此时会用到Oracle的集合.

--创建条件参数对象类型
create or replace
TYPE TEAM_TRIP_OBJECT AS OBJECT(
    TICKET_NO NVARCHAR2(20),
    DEPARTURE_DATE NVARCHAR2(20),
    DEPARTURE_AIRPOART_CODE NVARCHAR2(10),
    ARRIVAL_AIRPORT_CODE NVARCHAR2(10)
);
--上述的类型的字段都用NVARCHAR2,否则传人到oracle中参数如果有中文都是乱码.

--创建条件集合类型
create or replace
TYPE TEAM_TRIP_OBJECT_ARRAY
AS TABLE OF TEAM_TRIP_OBJECT;

--创建存储过程
 PROCEDURE UPDATE_TEAM_PSG_TRIP_SUM(TEAM_TRIP_OBJECT_CONDITIONS IN TEAM_TRIP_OBJECT_ARRAY) AS
  BEGIN
     FOR i IN 1..TEAM_TRIP_OBJECT_CONDITIONS.COUNT
     LOOP
        //操作
     END LOOP;
  EXCEPTION 
     WHEN OTHERS
     THEN 
       RAISE; 
  END UPDATE_TEAM_PSG_TRIP_SUM;

在java中创建与TEAM_TRIP_OBJECT相对应的类TeamTripCondition.java,如下所示:

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
public class TeamTripCondition implements SQLData {
	//在oracle数据库中需要创建此对象名称
	private String sqlType = "TEAM_TRIP_OBJECT";
	/**
	 * 票号
	 */
	private String ticketNo;
	/**
	 * 出发日期
	 */
	private String departDate;
	/**
	 * 出发机场三字码
	 */
	private String departCity;
	/**
	 * 到达机场三字码
	 */
	private String arrivalCity;
	public TeamTripCondition(){

	}
	public TeamTripCondition(String ticketNo,String departDate,String departCity,String arrivalCity){
		this.ticketNo = ticketNo;
		this.departDate = departDate;
		this.departCity = departCity;
		this.arrivalCity = arrivalCity;
	}
	/**
	 * define a get method to return the SQL type of the object  
	 */
	@Override
	public String getSQLTypeName() throws SQLException {
		return sqlType;
	}
	/**
	 * define the required readSQL() method
	 */
	@Override
	public void readSQL(SQLInput stream, String typeName) throws SQLException {
		sqlType = typeName;
		ticketNo = stream.readString();
		departDate = stream.readString();
		departCity = stream.readString();
		arrivalCity = stream.readString();
	}

	@Override
	public void writeSQL(SQLOutput stream) throws SQLException {
		stream.writeString(ticketNo);
		stream.writeString(departDate);
		stream.writeString(departCity);
		stream.writeString(arrivalCity);
	}

	public String getTicketNo() {
		return ticketNo;
	}

	public void setTicketNo(String ticketNo) {
		this.ticketNo = ticketNo;
	}

	public String getDepartDate() {
		return departDate;
	}

	public void setDepartDate(String departDate) {
		this.departDate = departDate;
	}

	public String getDepartCity() {
		return departCity;
	}

	public void setDepartCity(String departCity) {
		this.departCity = departCity;
	}

	public String getArrivalCity() {
		return arrivalCity;
	}

	public void setArrivalCity(String arrivalCity) {
		this.arrivalCity = arrivalCity;
	}

}
/**
 *创建使用Oracle存储过程的类,他需要继承StoredProcedure
 */
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.stereotype.Service;

import com.**.team.dto.TeamTripCondition;

@Service("teamTripService")
public class TeamTripService extends StoredProcedure {

	private static final Logger logger = Logger.getLogger(TeamTripService.class);
        //UPDATE_TEAM_PSG_TRIP_SUM是在Oracle包TEAM_INFO_PACKAGE的存储过程的名字
	private static final String SPROC_NAME = "TEAM_INFO_PACKAGE.UPDATE_TEAM_PSG_TRIP_SUM";
        //需要声明上述的Oracle存储过程UPDATE_TEAM_PSG_TRIP_SUM中传人参数的名字TEAM_TRIP_OBJECT_CONDITIONS
	private static final String TEAM_TRIP_OBJECT_CONDITIONS = "TEAM_TRIP_OBJECT_CONDITIONS";//
	@Autowired
	public TeamTripSchedulerServiceImpl(DataSource dataSource){
		super(dataSource, SPROC_NAME);
		declareParameter(new SqlParameter(TEAM_TRIP_OBJECT_CONDITIONS, Types.ARRAY,"TEAM_TRIP_OBJECT_ARRAY"));
		this.compile();
	}

	public void executeProc(final List<TeamTripCondition> conditions) {
		Map<String,Object> inParams = new HashMap<String,Object>();
		try{    
                        //将conditions转化成Oracle的集合
			SqlTypeValue value = new AbstractSqlTypeValue() {
				  protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
				    ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName, conn);
				    Object[] array = conditions.toArray();
				    ARRAY idArray = new ARRAY(arrayDescriptor, conn, array);
				    return idArray;
				  }
			};
			inParams.put(TEAM_TRIP_OBJECT_CONDITIONS, value);
			this.execute(inParams);
		}catch(Exception e){
			logger.error("updatePassengerTrip->executeProc*******ERROR:", e);
		}
	}

}