存储过程对于程序维护有诸多便利,以下即是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连接池
在mapper.xml中调用存储过程时,要声明参数的类型,时输入参数还是输出参数:
{call ORACEL_WEI.SELECT_ALL(
#{NAME,mode=IN,jdbcType=VARCHAR},
#{CURSOR, mode=OUT,jdbcType=CURSOR} )}
解析存储过程返回的结果:
public static List> resultSet(Map map,String cursor) throws SQLException {
ResultSet rs;
ResultSetMetaData rsmd;
int numberOfColumns;
Map resultMap = new HashMap();
List> list = new ArrayList>();
rs = (ResultSet) map.get("CURSOR");
rs.clearWarnings();
rsmd = rs.getMetaData();
numberOfColumns = rsmd.getColumnCount();
while (rs.next()) {
resultMap = new HashMap(numberOfColumns);
for (int r = 1; r <= numberOfColumns; r++) {
resultMap.put(rsmd.getColumnName(r), rs.getObject(r));
}
list.add(resultMap);
}
rs.close();
return list;
}