java servlet 调用oracle数据库存储过程。

servlet首先建立数据库连接类ConnectFactory：
[html] view plaincopyprint?
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class ConnectFactory {
private Connection conn;
private DataSource connnectionPool;

//链接中间件的连接池
public ConnectFactory(){
try {
InitialContext ctx = new InitialContext();
connnectionPool = (DataSource) ctx.lookup(“PaymentDS”);
}
catch (Exception e) {
e.printStackTrace();
}
}

//获得连接池的链接对象
public Connection getConn() throws Exception {
try {
conn=connnectionPool.getConnection();
return conn;
}
catch (SQLException sqle) {
throw new Exception();
}
}

static{
try{
Class.forName(“oracle.jdbc.driver.OracleDriver”);
}catch(ClassNotFoundException e){
throw new ExceptionInInitializerError(e);
}
}

//获得手写链接字符串的链接
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(“jdbc:oracle:thin:@192.168.2.37:1521:sdecp”,”eca”,”errors”);
}

}
外部调用链接：
[html] view plaincopyprint?
public Map getBusinessB2ADeal(String b2aNo) throws Exception{
Map res = null;
Connection con = ConnectFactory.getConnection();
/ Connection con = new ConnectFactory().getConn();
CallableStatement cs = con.prepareCall(“{call proc_business_b2aNo(?,?,?,?,?,?)}”);
cs.setString(“b2aNo”, b2aNo);
cs.registerOutParameter(“b2aBank”, Types.VARCHAR);
cs.registerOutParameter(“b2aAmount”, Types.DOUBLE);
cs.registerOutParameter(“b2aType”, Types.VARCHAR);
cs.registerOutParameter(“b2aNotify”, Types.VARCHAR);
cs.registerOutParameter(“b2aBill”, Types.VARCHAR);
cs.execute();

String b2aBank = cs.getString(“b2aBank”);
String b2aAmount = cs.getString(“b2aAmount”);
String b2aType = cs.getString(“b2aType”);
String b2aNotify = cs.getString(“b2aNotify”);
String b2aBill = cs.getString(“b2aBill”);

if(!StringUtils.isEmpty(b2aBank)){
res = new HashMap();
res.put(“bankId”, b2aBank);
res.put(“amount”, b2aAmount);
res.put(“b2aType”, b2aType);
res.put(“b2aNotify”, b2aNotify);
res.put(“b2aBill”, b2aBill);
}
cs.close();
con.close();
return res;
}
存储过程：
[sql] view plaincopyprint?
CREATE OR REPLACE PROCEDURE proc_business_b2aNo(
b2aNo in varchar2,
b2aBank out varchar2,
b2aAmount out NUMBER,
b2aType out varchar2,
b2aNotify out varchar2,
b2aBill out varchar2
)
IS
BEGIN
SELECT tb2a.b2a_bank,tb2a.b2a_amount,tb2a.b2a_Type,tb2a.b2a_NOTIFY,tb2a.b2a_Bill
INTO b2aBank,b2aAmount,b2aType,b2aNotify,b2aBill
FROM t_b2a tb2a WHERE tb2a.b2a_no = b2aNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
– Consider logging the error and then re-raise
RAISE;
END proc_business_b2aNo;