package cn.lingqi.tool;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import oracle.jdbc.OracleTypes;public class DBConnection {    // for Oracle    static private String strDriver = "com.mysql.jdbc.Driver";    static private String strUrl = "jdbc:mysql://localhost:3306/testdb";    static private String strUser = "scmgroup2";    static private String strPwd = "123";    private Connection conn = null;    private Statement stmt = null;    private ResultSet rs = null;    static {        try {            Class.forName(strDriver);        } catch (ClassNotFoundException ex) {            System.out.println("Error load" + strDriver);        }    }    public DBConnection() {    }    public ResultSet executeQuery(String sql) {        try {            rs = getStatement().executeQuery(sql);        } catch (SQLException ex) {            System.err.println("query error:" + ex.getMessage());        }        return rs;    }    public boolean executeBatch(java.util.ArrayList sqls) {        boolean bRet = false;        try {            conn = getConnection();            conn.setAutoCommit(false);            stmt = conn.createStatement();            for (int i = 0; i < sqls.size(); i++) {                String sql = (String) sqls.get(i);                stmt.addBatch(sql);            }            stmt.executeBatch();            conn.commit();            bRet = true;        } catch (Exception ex) {            try {                conn.rollback();            } catch (Exception ex2) {                System.err.println("rollback failed:" + ex2.getMessage());            }            ex.printStackTrace();        } finally {        }        return bRet;    }    public int executeUpdate(String sql) {        int resultNum = 0;        try {            resultNum = getStatement().executeUpdate(sql);        } catch (SQLException ex) {            System.err.println("update error:" + ex.getMessage());        } finally {        }        return resultNum;    }    public void close() {        try {            if (rs != null) {                rs.close();                rs = null;            }            if (stmt != null) {                stmt.close();                stmt = null;            }            if (conn != null) {                conn.close();                conn = null;            }        } catch (Exception ex) {            System.err.println("close error:" + ex.getMessage());        }    }    private Connection getConnection() {        try {            if (conn == null || conn.isClosed())                conn = DriverManager.getConnection(strUrl, strUser, strPwd);        } catch (Exception ex) {            ex.printStackTrace();            return null;        }        return conn;    }    private Statement getStatement() {        try {            if (stmt == null)                stmt = getConnection().createStatement();        } catch (Exception ex) {            ex.printStackTrace();            return null;        }        return stmt;    }        public static void callFunctionCursor(String dept_id){		try{			DBConnection db = new DBConnection();			Connection conn = db.getConnection();			//拿到調用statement			CallableStatement func = conn.prepareCall("{ ? = call my_test_fun ( ? )  }"); 			func.registerOutParameter(1,OracleTypes.CURSOR); 			func.setString(2,dept_id);			func.execute(); 		    ResultSet rset = (ResultSet)func.getObject(1); 		    while (rset.next ()) {		        System.out.println( "empid:"+ rset.getString (1)+"\t empname:" +  rset.getString (2) + "\t deptid:" + rset.getString((3)) );		    }			func.close();			db.close();		}catch(Exception e){			e.printStackTrace();		}			}        public static void callProcedureIn(int tt,String empid,String empname,String deptid){    	try {        	DBConnection db = new DBConnection();        	Connection conn = db.getConnection();			CallableStatement func = conn.prepareCall("{call test1_pac.pro_test1(?,?,?,?)}");			func.registerOutParameter(1, OracleTypes.INTEGER);			func.setInt(1, tt);			func.setString(2, empid);			func.setString(3, empname);			func.setString(4, deptid);			func.execute();			int aa = (Integer) func.getObject(1);			if (aa ==1){System.out.println("error");}			if (aa ==0){System.out.println("insert successful");}		} catch (SQLException e) {			// TODO Auto-generated catch block			e.printStackTrace();		}    }    public static void main(String[] args) {		// TODO Auto-generated method stub		callProcedureIn(99,"9990","emp_9990","1");//		callFunction("1");//		callFunctionNoArgu();//		callFunctionCursor("1");//		callProcedureIn("22","jjh","1");//		callProcedureInOut("2");    	//callFunctionCursor("1");	}}