java调用oracle存储过程（ssm）

存储过程对于程序维护有诸多便利，以下即是java调用的Oracle存储过程的一种简单的方法。  

jdbc.properties:

#################### 服务名连接 ########################
jdbc.driver1=oracle.jdbc.driver.OracleDriver
jdbc.url1=jdbc:oracle:thin:@localhost:1521:wei
jdbc.user1=user
jdbc.password1=password

#################### 实例名连接 #######################
jdbc.driver2=oracle.jdbc.driver.OracleDriver
jdbc.url2=jdbc:oracle:thin:@//localhost:1521/wei
jdbc.user2=user
jdbc.password2=password

 

spring的配置文件：配置c3p0连接池

	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driver1}" /> 
		<property name="jdbcUrl" value="${jdbc.url1}" /> 
		<property name="user" value="${jdbc.user1}" /> 
		<property name="password" value="${jdbc.password1}" />
		<property name="initialPoolSize" value="6"></property>
		<property name="minPoolSize" value="2"></property>
		<property name="maxPoolSize" value="8"></property>
		<property name="acquireIncrement" value="2"></property>
		<property name="preferredTestQuery" value="select 1"></property>
		<property name="idleConnectionTestPeriod" value="90"></property>
		<property name="maxIdleTime" value="600"></property>
        <property name="maxIdleTimeExcessConnections" value="8"></property>
	</bean>

 在mapper.xml中调用存储过程时，要声明参数的类型，时输入参数还是输出参数：

<select id="getALL" statementType="CALLABLE" resultType="java.util.HashMap">
	{call ORACEL_WEI.SELECT_ALL(
	#{NAME,mode=IN,jdbcType=VARCHAR},
	#{CURSOR, mode=OUT,jdbcType=CURSOR} )}	
</select>

解析存储过程返回的结果：

public static List<Map<String, Object>> resultSet(Map<String, Object> map,String cursor) throws SQLException {
		
		ResultSet rs;
		ResultSetMetaData rsmd;
		int numberOfColumns;
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		rs = (ResultSet) map.get("CURSOR");
		rs.clearWarnings();
		rsmd = rs.getMetaData();
		numberOfColumns = rsmd.getColumnCount();
		while (rs.next()) {
			resultMap = new HashMap<String, Object>(numberOfColumns);
			for (int r = 1; r <= numberOfColumns; r++) {
				resultMap.put(rsmd.getColumnName(r), rs.getObject(r));
			}
			list.add(resultMap);
		}
		rs.close();
		return list;
	}