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

servlet首先建立数据库连接类ConnectFactory：

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");

}

}外部调用链接：

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;

}存储过程：

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;