dbconnection.java

Java语言恐怕是稳居网路应用程序语言的首选了

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


}