java 調用預存程序

標籤：

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import java.util.TreeMap;import oracle.jdbc.OracleTypes;/** * <p>Title: executeProcedure</p> * <p>Description:執行預存程序，返回結果集 </p> * @param dbvo * @return */public   static List<?> executeProcedure(DBVO dbvo) {Connection conn = null;CallableStatement stmt = null;ResultSet rs = null;String sParams = "";String proName = dbvo.getProName();List<?> paramsList = dbvo.getParamsList();for (int i = 0; i <= paramsList.size() - 1; i++) {if (i == 0) {sParams = "?";} else {sParams += ",?";}}try {conn = DAOFactory.getInstance().getConnection();boolean isOracle = DateTools.IsOracle(conn);if (isOracle) {stmt = conn.prepareCall("{ call " + proName + "(" + sParams+ ",?)}");for (int i = 0; i < paramsList.size(); i++) {stmt.setString(i + 1, (String) paramsList.get(i).toString().trim());}stmt.registerOutParameter(paramsList.size()+1, OracleTypes.CURSOR);stmt.execute();rs = (ResultSet) stmt.getObject(paramsList.size()+1);} else {stmt = conn.prepareCall("{ call " + proName + "(" + sParams+ ")}");for (int i = 0; i <= paramsList.size() - 1; i++) {stmt.setString(i + 1, (String) paramsList.get(i).toString().trim());}rs = stmt.executeQuery();}ResultSetMetaData rsmd = rs.getMetaData();int columnCount = rsmd.getColumnCount();Map itemMap;List list = new ArrayList();while (rs.next()) {itemMap = new TreeMap<String, Object>();for (int i = 1; i <= columnCount; i++) {String fldName = rsmd.getColumnLabel(i);// 別名String fldValue = rs.getString(i);itemMap.put(fldName, fldValue);}list.add(itemMap);}return list;} catch (BusinessException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {DAOFactory.closeConnection(conn, stmt, rs);}return null;}

　　

java 調用預存程序