JDBC连接数据库类（主要用于存储过程）

数据库连接类（SQLCONN）在MSSQL2005上测试通过，因为决定以后好好学习英文以适应鬼佬API，所以注释都是全英。因水平有限，如果有错请指出~~

另外这里返回的记录是封装在一个ArrayList里面 所以那些实时更新的操作是无力diesi~ 其实还可以把出参入参学C#一样封装在一个参数的类里面，但是 本人非常懒 就不搞了~~~

补充两句： 最近在看资料的时候看到了很多批判JAVA效率的人和文章，有人甚至说JAVA垃圾，好吧，这些连JAVA编程思想都没有通读的人说的屌丝话我就不理睬了。因为我自己对JAVA也是理解不深，因此也不评价了。但是总觉得这种黑跟那些脑残粉一样，黑要有水平粉亦是，如果对某个物体完全不了解的情况下就肆意地去黑，随意地去粉，这跟脑残有什么区别呢？

总结下来：跟C#比 Java的数据库连接方面做得真心不多 或者说JDBC做得不多 或者说JDBC面对编程人员做的不多（考虑很久远之前的连接数据库模型，现在的JDBC不能说做得不多） 看看C#的DataSet 等等各种DB容器 我就泪奔了~ 但是这不正是JAVA美妙的地方吗？没有的东西 做一个就好！

最后 C#和Java给我的感觉就是 C#给我两个鬼带4个2 然后告诉我 这样出就赢了 而JAVA是给我一个大鬼 一个黑桃2 一个红桃2 一个方片2 一个小鬼 一个梅花2 然后跟我说 你自己看着办吧 骚年~~

=======写在下午的话======

由于实在太难用了 愤怒之下改代码了 ~~

=======写在2013-7-5的话=======

其实这个类已经被我改的差不多了。。。不过总体的思路还是没有变。。。以前一直不明白ResultSet跟DataSet有什么区别。。现在貌似是懂了 就是一个是动态数据 一个是静态数据 而现在的感觉是 这些静态容器其实没什么用。。。因为如果遵照JPA的游戏规则 按照O/R模型去玩的话 这些东西貌似又没什么必要了。。。

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dataanalysis_erp_plug.DataAnalysis;

import java.sql.*;
import java.util.*;
import java.util.logging.*;

/**
 *
 * @author Administrator
 *
 * private function: open(): open a connection; close(): close a connection;
 *
 * public function: runSQL(): run a sql code and return a number. This number should be a number of be affected rows; getResultListFromProc(): run a stored procedure and return a ResultSet
 * getResultSetFromSQL(): run sql code and return a ResultSet
 *
 */
public class SQLCONN {
    
    public ArrayList<Object[]> table = new ArrayList<Object[]>();
    public ArrayList outputParams = new ArrayList();
    
    private Connection conn = null;
    private String driver, serverName, userName, password;
    private ArrayList<Object[]> _outputParams = new ArrayList<Object[]>();
    private ArrayList<Object[]> _inputParams = new ArrayList<Object[]>();

    public SQLCONN(String driver, String serverName, String userName, String password) {
        this.serverName = serverName; //include a dbName here, ex: "jdbc:sqlserver://192.168.0.251\\GSSL; DatabaseName=Materials"
        this.driver = driver;
        this.password = password;
        this.userName = userName;
    }

    public int runSQL(String sql) {
        int returnNumber = -1;
        try {
            this.open();
            try (Statement stmt = conn.createStatement()) {
                returnNumber = stmt.executeUpdate(sql);
            }
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(SQLCONN.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.close();
            return returnNumber;
        }
    }

    public ResultSet getResultSetFromSQL(String sql) {
        ResultSet returnResultSet = null;
        try {
            this.open();
            try (Statement stmt = conn.createStatement()) {
                returnResultSet = stmt.executeQuery(sql);
                returnResultSet.close();
            }
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(SQLCONN.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.close();
            return returnResultSet;
        }
    }

    /*
     * input Params:
     * procName: database stored process's name (String)
     * inputs: an ArrayList with an Object Array in it. Objcet[0] means input params name (String), object[1] means input params value (Object)
     * outputs: an ArrayList with an Object Array in it. Objcet[0] means output params name (String), object[1] means output params type (int from java.sql.Types.*)
     * 
     * output Params:
     * is a ArrayList. [0] is a ArrayList<Object[]> descript ResultSet. [1 ... n] is a set of database stored process output Params.
     * 
     * throw an exception when the number of inputparam don't match
     * 
     */
    
    public void runProc(String procName) throws Exception {
         int i = 0;
         this.outputParams.clear();
         this.table.clear();
         String callString = "{call " + procName + "(";
         if (this._inputParams.isEmpty() && this._outputParams.isEmpty()) {
             callString = callString + ")}";
         } else {
             callString = callString + "?";
             if (!this._inputParams.isEmpty()) {
                 for (i = 1; i < this._inputParams.size(); i++) {
                     callString = callString + ",?";
                 }
             }
             if (!this._outputParams.isEmpty()) {
                 for (i = (this._inputParams.isEmpty() ? 1 : 0); i < this._outputParams.size(); i++) {
                     callString = callString + ",?";
                 }
             }
             callString = callString + ")}";
         }
         try {
             this.open();
             try (CallableStatement cstmt = conn.prepareCall(callString)) {
                 //init input params
                 if (this._inputParams != null) {
                     for (i = 0; i < this._inputParams.size(); i++) {
                         switch(this._inputParams.get(i).length){
                             case 2:
                                 cstmt.setObject((String) this._inputParams.get(i)[0], this._inputParams.get(i)[1]);
                                 break;
                             case 3:
                                 cstmt.setObject((String) this._inputParams.get(i)[0], this._inputParams.get(i)[1], (Integer) this._inputParams.get(i)[2]);
                                 break;
                             case 4:
                                 cstmt.setObject((String) this._inputParams.get(i)[0], this._inputParams.get(i)[1], (Integer) this._inputParams.get(i)[2], (Integer) this._inputParams.get(i)[3]);
                                 break;
                             default:
                                 throw new Exception("Error: the number of input param's input param is not match");
                         }
                     }
                 }
                 //init output params
                 if (this._outputParams != null) {
                     for (i = 0; i < this._outputParams.size(); i++) {
                         switch(this._outputParams.get(i).length){
                             case 2:
                                 cstmt.registerOutParameter((String) this._outputParams.get(i)[0], (Integer) this._outputParams.get(i)[1]);
                                 break;
                             case 3:
                                 cstmt.registerOutParameter((String) this._outputParams.get(i)[0], (Integer) this._outputParams.get(i)[1], (Integer) this._outputParams.get(i)[2]);
                                 break;
                             default:
                                 throw new Exception("Error: the number of output param's input param is not match");
                         }
                     }
                 }
                 cstmt.execute();
                 //bind the ResultSet to HashMap
                 ResultSet rs = cstmt.getResultSet();
                 ResultSetMetaData rsmd = rs.getMetaData();
                 if (rs != null) {
                     while (rs.next()) {
                         Object[] col = new Object[rsmd.getColumnCount()];
                         for (i = 0; i < rsmd.getColumnCount(); i++) {
                             col[i] = rs.getObject(i + 1);
                         }
                         this.table.add(col);
                     }
                 } else {
                     this.table = null;
                 }
                 //get output params
                 for (i = 0; i < this._outputParams.size(); i++) {
                     this.outputParams.add(cstmt.getObject((String) this._outputParams.get(i)[0]));
                 }
             }
         } catch (ClassNotFoundException | SQLException ex) {
             Logger.getLogger(SQLCONN.class.getName()).log(Level.SEVERE, null, ex);
         } finally {
             this._inputParams.clear();
             this._outputParams.clear();
             this.close();
         }
    }
    
    /*
     * set input params
     */
    public void addInputParam(String paramName, Object value, int SQLType, int scale) {
        Object[] output = new Object[4];
        output[0] = paramName;
        output[1] = value;
        output[2] = SQLType;
        output[3] = scale;
        this._inputParams.add(output);
    }
    
    public void addInputParam(String paramName, Object value, int SQLType) {
        Object[] output = new Object[3];
        output[0] = paramName;
        output[1] = value;
        output[2] = SQLType;
        this._inputParams.add(output);
    }
    
    public void addInputParam(String paramName, Object value) {
        Object[] output = new Object[2];
        output[0] = paramName;
        output[1] = value;
        this._inputParams.add(output);
    }
    
    /*
     * set output params
     */
    public void addOutputParams(String paramName, int SQLType, int scale) {
        Object[] output = new Object[3];
        output[0] = paramName;
        output[1] = SQLType;
        output[2] = scale;
        this._outputParams.add(output);
    }
    
    public void addOutputParams(String paramName, int SQLType) {
        Object[] output = new Object[2];
        output[0] = paramName;
        output[1] = SQLType;
        this._outputParams.add(output);
    }

    private void open() throws ClassNotFoundException, SQLException {
        Class.forName(this.driver);
        this.conn = DriverManager.getConnection(this.serverName, this.userName, this.password);
        System.out.println("connected");
    }

    private void close() {
        try {
            this.conn.close();
            System.out.println("close");
        } catch (SQLException ex) {
            Logger.getLogger(SQLCONN.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

调用：

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dataanalysis_erp_plug;

import dataanalysis_erp_plug.DataAnalysis.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class DataAnalysis_ERP_Plug {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here
        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String userName = "SQL用户名";
        String password = "密码";
        String serverName = "jdbc:sqlserver://SQL服务器名\\实例名;";
        String dbName = "DatabaseName=数据库名"; //allow empty, default value = "";
        SQLCONN sqlconn = new SQLCONN(driver, serverName + dbName, userName, password);
        
        //define input params
        sqlconn.addInputParam("input", "caca", java.sql.Types.VARCHAR, 50);
        //define output params
        sqlconn.addOutputParams("return", java.sql.Types.VARCHAR);
        try {
            sqlconn.runProc("testProc_noIOParam");
        } catch (Exception ex) {
            Logger.getLogger(DataAnalysis_ERP_Plug.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (sqlconn.table.isEmpty()) {
            System.out.println("null");
        } else {
            System.out.println(sqlconn.table.get(0)[0]);
            System.out.println(sqlconn.outputParams.get(0));
            System.out.println("not null");
        }
    }
}