java版本的sqlHelper

標籤：

以下版本的sqlHelper可以支援普通的DDL，DML和查詢語句，暫不支援預存程序，事務等

package com.bobo.util;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import com.mysql.jdbc.Connection;import java.sql.*;public class SqlHelper {    // 回頭想辦法，不要在這裡寫入程式碼    private static String driverName = "com.mysql.jdbc.Driver";    private static String urlName = "jdbc:mysql://127.0.0.1:3306/bobo_test";    private static String user = "root";    private static String password = "bobo";    // 該類禁止執行個體化    private SqlHelper() {    }    private static java.sql.Connection getConnection() {        try {            Class.forName(driverName);            return DriverManager.getConnection(urlName, user, password);        } catch (Exception e) {            // TODO Auto-generated catch block            return null;        }    }    private static void prepareCommand(PreparedStatement pstmt, String[] parms) {        try {            if (parms != null) {                for (int i = 0; i < parms.length; i++) {                    try {                        pstmt.setDate(i + 1, java.sql.Date.valueOf(parms[i]));                    } catch (Exception e) {                        try {                            pstmt.setDouble(i + 1, Double.parseDouble(parms[i]));                        } catch (Exception e1) {                            try {                                pstmt.setInt(i + 1, Integer.parseInt(parms[i]));                            } catch (Exception e2) {                                try {                                    pstmt.setString(i + 1, parms[i]);                                } catch (Exception e3) {                                    System.out                                            .print("SQLHelper-PrepareCommand Err1:"                                                    + e3);                                }                            }                        }                    }                }            }        } catch (Exception e1) {            System.out.print("SQLHelper-PrepareCommand Err2:" + e1);        }    }    /**     * 用於執行語句(insert,update,delete)     *      * @param sqlText     *            sql語句     * @param params     *            參數集合     * @return int，sql語句受影響的行數     * @throws Exception     */    public static int ExecuteNonQuery(String sqlText, String[] params)            throws Exception {        PreparedStatement ps = null;        java.sql.Connection con = null;        try {            con = getConnection();            ps = con.prepareStatement(sqlText);            prepareCommand(ps, params);            return ps.executeUpdate();        } catch (Exception e) {            throw new Exception("executeNonQuery方法出錯:" + e.getMessage());        }    }    /**     * 用於擷取結果集語句（eg：selete * from table）     *      * @param cmdtext     *            sql語句     * @param parms     * @return ArrayList 裡面的每一個元素是一個object[列數]的數組     * @throws Exception     */    public static ArrayList ExecuteReader(String cmdtext, String[] parms)            throws Exception {        PreparedStatement pstmt = null;        java.sql.Connection conn = null;        try {            conn = getConnection();            pstmt = conn.prepareStatement(cmdtext);            prepareCommand(pstmt, parms);            ResultSet rs = pstmt.executeQuery();            ArrayList al = new ArrayList();            ResultSetMetaData rsmd = rs.getMetaData();            int column = rsmd.getColumnCount();            while (rs.next()) {                Object[] ob = new Object[column];                for (int i = 1; i <= column; i++) {                    ob[i - 1] = rs.getObject(i);                }                al.add(ob);            }            rs.close();            return al;        } catch (Exception e) {            throw new Exception("executeSqlResultSet方法出錯:" + e.getMessage());        } finally {            try {                if (pstmt != null)                    pstmt.close();                if (conn != null)                    conn.close();            } catch (Exception e) {                throw new Exception("executeSqlResultSet方法出錯:" + e.getMessage());            }        }    }    /**     * 用於擷取單欄位值語句（用名字指定欄位）     *      * @param cmdtext     *            SQL語句     * @param name     *            列名     * @param parms     *            OracleParameter[]     * @return Object 返回object類型的資料     * @throws Exception     */    public static Object ExecuteScalar(String cmdtext, String name,            String[] parms) throws Exception {        PreparedStatement pstmt = null;        java.sql.Connection conn = null;        ResultSet rs = null;        try {            conn = getConnection();            pstmt = conn.prepareStatement(cmdtext);            prepareCommand(pstmt, parms);            rs = pstmt.executeQuery();            if (rs.next()) {                return rs.getObject(name);            } else {                return null;            }        } catch (Exception e) {            throw new Exception("executeSqlObject方法出錯:" + e.getMessage());        } finally {            try {                if (rs != null)                    rs.close();                if (pstmt != null)                    pstmt.close();                if (conn != null)                    conn.close();            } catch (Exception e) {                throw new Exception("executeSqlObject方法出錯:" + e.getMessage());            }        }    }    /**     * 用於擷取單欄位值語句（用序號指定欄位）     *      * @param cmdtext     *            SQL語句     * @param index     *            列名索引     * @param parms     *            OracleParameter[]     * @return Object     * @throws Exception     */    public static Object ExecuteScalar(String cmdtext, int index, String[] parms)            throws Exception {        PreparedStatement pstmt = null;        java.sql.Connection conn = null;        ResultSet rs = null;        try {            conn = getConnection();            pstmt = conn.prepareStatement(cmdtext);            prepareCommand(pstmt, parms);            rs = pstmt.executeQuery();            if (rs.next()) {                return rs.getObject(index);            } else {                return null;            }        } catch (Exception e) {            throw new Exception("executeSqlObject方法出錯:" + e.getMessage());        } finally {            try {                if (rs != null)                    rs.close();                if (pstmt != null)                    pstmt.close();                if (conn != null)                    conn.close();            } catch (Exception e) {                throw new Exception("executeSqlObject方法出錯:" + e.getMessage());            }        }    }}

 

java版本的sqlHelper