ervlet首先建立数据库连接类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;