java版本的sqlHelper

以下版本的sqlHelper可以支持普通的DDL，DML和查询语句，对于连接池，事务等的支持还有待改进 

1)将数据库连接相关信息存储为属性文件，如database.properties,建立DataBase相关的辅助类进行读取

package com.bobo.db;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DataBase {
    private static String url;
    private static String driver;
    private static String username;
    private static String password;
    // 数据源名称
    private static String dataSource;
    /*
     * 数据库访问类型，是连接池还是普通链接
     */
    private static String type;

    private static String fileName = "database.properties";
    private static ThreadLocal<Connection> connection = new ThreadLocal<Connection>();
    // 下面这种语法在java中叫做初始化块，初始化块无法接受参数，通常用于对类中field字段的统一初始化操作
    // 类初始化块在类初始化的时候调用
    // 对象初始化块在对象初始化时，先于构造函数调用
    static {
        config();
    }

    public static void main(String[] args) {
        Connection con = getConnection();
        releaseConnection(con);
    }

    private static void config() {
        // 位于src目录下的文件，需要使用类加载器来读取
        String path = DataBase.class.getClassLoader().getResource(fileName)
                .getPath();
        // 从配置文件中读取数据库相关参数
        Properties pro = new Properties();
        try {
            FileInputStream fis = new FileInputStream(path);
            pro.load(fis);

            if (pro.containsKey("driver")) {
                driver = pro.getProperty("driver");
            }
            if (pro.containsKey("url")) {
                url = pro.getProperty("url");
            }
            if (pro.containsKey("username")) {
                username = pro.getProperty("username");
            }
            if (pro.containsKey("password")) {
                password = pro.getProperty("password");
            }
            if (pro.containsKey("type")) {
                type = pro.getProperty(type);
            }
            System.out.println("DataBase:" + "driver:" + driver + "," + "url:"
                    + url + "," + "username:" + username + "," + "password:"
                    + password);
            fis.close();

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public static Connection getConnection() {
        Connection con = connection.get();
        try {
            if (con != null && !con.isClosed()) {
                return con;
            }
            if ("pool".equalsIgnoreCase("type")) {
                // 数据库链接池中获得连接，这里暂且不管
            } else {
                // 直接使用JDBC驱动连接
                Class providerClass = Class.forName(driver);
                con = DriverManager.getConnection(url, username, password);
                con.setAutoCommit(false);
                connection.set(con);
                return con;

            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    public static void releaseConnection(Connection con) {

        try {
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            con = null;
        }
    }

    public static void commit() {
        Connection con = (Connection) connection.get();
        try {
            con.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void rollback() {
        Connection con = (Connection) connection.get();
        try {
            con.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

2）SqlHelper类

注意：

a：这里有些地方使用的是Object进行封装，考虑到从数据库中读出的都可以是字符串，因此对于记录中的每一项，根据需求也可以使用String封装

b：因为将查询结果封装为了Object或者String，在SqlHelper类中其实也可以关闭Connection,不过这样的一个弊端是，每一次单独的查询都需要打开和关闭connection，对于一次查询需要借助多个表的时候，这加大了资源消耗，因此本类实现中没有这么做，而是在调用sqlHelper的时候，再进行连接的打开和关闭 

package com.bobo.util;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.SortedMap;
import java.sql.*;

import com.bobo.db.DataBase;

public class SqlHelper {
    private Connection con;

    public SqlHelper() {

    }

    public void setConnection(Connection con) {
        this.con = con;

    }

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

    /**
     * 执行插入语句，返回对应行的自增key值
     * 
     * @param sqlText
     * @param params
     * @return
     * @throws Exception
     */
    public int ExecuteInsertReturnKey(String sqlText, String[] params)
            throws Exception {
        PreparedStatement ps = null;
        java.sql.Connection con = null;
        int key = -1;
        ResultSet rs = null;
        try {

            ps = con.prepareStatement(sqlText, Statement.RETURN_GENERATED_KEYS);
            prepareCommand(ps, params);
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                key = rs.getInt(1);

            }
        } catch (Exception e) {
            throw new Exception("ExecuteInsertReturnKey出错:" + e.getMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }

        }
        return key;
    }

    /**
     * 执行非查询sql语句(insert,update,delete)
     * 
     * @param sqlText
     *            sql命令
     * @param params
     *            参数值
     * @return int 返回操作影响的记录条数
     * @throws Exception
     */
    public int ExecuteNonQuery(String sqlText, String[] params)
            throws Exception {
        PreparedStatement ps = null;
        java.sql.Connection con = null;
        try {

            ps = con.prepareStatement(sqlText);
            prepareCommand(ps, params);
            return ps.executeUpdate();
        } catch (Exception e) {
            throw new Exception("executeNonQuery出错:" + e.getMessage());
        } finally {

            if (ps != null) {
                ps.close();
            }

        }
    }

    /**
     * 
     * @param cmdtext
     *            查询语句
     * @param parms查询参数
     * @return String[] 返回查询结果对应的列信息
     */
    public String[] executeColumnInfo(String cmdtext, String[] parms) {
        PreparedStatement pstmt = null;
        String[] result = null;
        try {

            pstmt = con.prepareStatement(cmdtext);

            prepareCommand(pstmt, parms);
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int column = rsmd.getColumnCount();
            result = new String[column];
            for (int i = 1; i <= column; i++) {
                result[i - 1] = rsmd.getColumnName(i);
            }

        } catch (Exception e) {

        } finally {

            if (pstmt != null)
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

        }
        return result;
    }

    /**
     * 执行查询语句,返回记录内容
     * 
     * @param cmdtext
     *            sql指令
     * @param parms
     *            参数
     * @return ArrayList 返回一个list，里面是String[列数]对象
     * @throws Exception
     */
    public ArrayList<String[]> ExecuteReader(String cmdtext, String[] parms)
            throws Exception {
        PreparedStatement pstmt = null;

        try {

            pstmt = con.prepareStatement(cmdtext);

            prepareCommand(pstmt, parms);
            ResultSet rs = pstmt.executeQuery();

            ArrayList<String[]> al = new ArrayList<String[]>();
            ResultSetMetaData rsmd = rs.getMetaData();
            int column = rsmd.getColumnCount();
            while (rs.next()) {
                String[] ob = new String[column];
                for (int i = 1; i <= column; i++) {
                    ob[i - 1] = rs.getString(i);
                }
                al.add(ob);
            }

            rs.close();
            return al;

        } catch (Exception e) {
            throw new Exception("executeSqlResultSet出错:" + e.getMessage());
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();

            } catch (Exception e) {
                throw new Exception("executeSqlResultSet出错:" + e.getMessage());
            }
        }
    }

    /**
     * 
     * @param cmdtext
     *            查询的sql语句
     * @param parms
     *            查询参数
     * @return 仅仅返回符合条件的第一条记录
     * @throws Exception
     */
    public String[] ExecuteFirstRecorder(String cmdtext, String[] parms)
            throws Exception {
        PreparedStatement pstmt = null;

        try {

            pstmt = con.prepareStatement(cmdtext);

            prepareCommand(pstmt, parms);
            ResultSet rs = pstmt.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            int column = rsmd.getColumnCount();
            String[] ob = null;
            if (rs.next()) {
                ob = new String[column];
                for (int i = 1; i <= column; i++) {
                    ob[i - 1] = rs.getString(i);
                }

            }

            rs.close();
            return ob;

        } catch (Exception e) {
            throw new Exception("executeSqlResultSet出错:" + e.getMessage());
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();

            } catch (Exception e) {
                throw new Exception("executeSqlResultSet出错:" + e.getMessage());
            }
        }
    }

    /***
     * 
     * @param cmdtext
     *            查询的sql语句
     * @param parms
     *            查询参数
     * @return 返回ArrayList<HashMap<String, String>>，map的结构是列名：列值
     * @throws Exception
     */
    public ArrayList<HashMap<String, String>> ExecuteMapReader(String cmdtext,
            String[] parms) throws Exception {
        PreparedStatement pstmt = null;

        try {

            pstmt = con.prepareStatement(cmdtext);

            prepareCommand(pstmt, parms);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();
            ResultSetMetaData rsmd = rs.getMetaData();
            int column = rsmd.getColumnCount();
            System.out.println("SqlHelper:" + rsmd.getColumnName(column));
            while (rs.next()) {
                HashMap<String, String> map = new HashMap<String, String>();
                for (int k = 1; k <= column; k++) {
                    map.put(rsmd.getColumnName(k), rs.getString(k));
                }
                al.add(map);
            }
            rs.close();
            return al;

        } catch (Exception e) {
            throw new Exception("executeSqlResultSet出错:" + e.getMessage());
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();

            } catch (Exception e) {
                throw new Exception("executeSqlResultSet出错:" + e.getMessage());
            }
        }
    }

    /**
     * 执行查询语句，返回符合条件的记录数目
     * 
     * @param cmdtext
     *            sql指令
     * @param parms
     *            参数
     * @return int 返回符合条件的记录数目，如果没有返回-1
     * @throws Exception
     */
    public int ExecuteRowCountQuery(String cmdtext, String[] parms)
            throws Exception {
        PreparedStatement pstmt = null;

        int result = -1;
        try {

            pstmt = con.prepareStatement(cmdtext);

            prepareCommand(pstmt, parms);
            ResultSet rs = pstmt.executeQuery();

            rs.next();
            result = rs.getInt(1);
            rs.close();

        } catch (Exception e) {
            throw new Exception("executeSqlResultSet出错:" + e.getMessage());
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();

            } catch (Exception e) {
                throw new Exception("executeSqlResultSet出错:" + e.getMessage());
            }
        }
        return result;
    }

    /**
     * 执行单结果单列查询语句，如果记录存在，返回首条记录的对应列，否则返回空（按照列名查询）
     * 
     * @param cmdtext
     *            SQL命令
     * @param name
     *            列名称
     * @param parms
     *            OracleParameter[]
     * @return Object 返回列对象
     * @throws Exception
     */
    public Object ExecuteScalar(String cmdtext, String name, String[] parms)
            throws Exception {
        PreparedStatement pstmt = null;

        ResultSet rs = null;

        try {

            pstmt = con.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();

            } catch (Exception e) {
                throw new Exception("executeSqlObject出错:" + e.getMessage());
            }
        }
    }

    /**
     * 执行单结果单列查询语句，如果记录存在，返回首条记录的对应列，否则返回空（按照列索引查询）
     * 
     * @param cmdtext
     *            SQL命令
     * @param index
     *            第几列
     * @param parms
     *            OracleParameter[]
     * @return Object
     * @throws Exception
     */
    public Object ExecuteScalar(String cmdtext, int index, String[] parms)
            throws Exception {
        PreparedStatement pstmt = null;

        ResultSet rs = null;

        try {

            pstmt = con.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();

            } catch (Exception e) {
                throw new Exception("executeS   qlObject出错:" + e.getMessage());
            }
        }
    }

}

3，sqlHelper调用

public ArrayList<User> getAllUser() {
        Connection conn = DataBase.getConnection();
        ArrayList<User> result = new ArrayList<User>();

        sqlHelper.setConnection(conn);
        String sql = "SELECT * FROM NP_USER ";
        try {
            ArrayList<String[]> rs = sqlHelper.ExecuteReader(sql, null);
            for (int i = 0; i < rs.size(); i++) {
                User user = new User();
                String[] temp = rs.get(i);

                user.setId(temp[0]);
                user.setUsername(temp[1]);
                user.setPassword(temp[2]);
                user.setName(temp[3]);
                user.setApartment(temp[4]);
                user.setTitle(temp[5]);
                user.setPhonenumber(temp[6]);
                user.setType(Integer.parseInt(temp[7]));
                user.setAuthority(Integer.parseInt(temp[8]));
                // 根据城市码查询城市
                int ProviceCode = Integer.parseInt(temp[9]);
                Province pro = new Province();
                pro.setProvinceCode(temp[9]);
                String provinceSql = "select PROVINCE_NAME from np_province where PROVINCE_CODE=?";
                String[] provinceParams = { temp[9] };
                Object proObject = sqlHelper.ExecuteScalar(provinceSql,
                        "PROVINCE_NAME", provinceParams);
                pro.setProvinceName(proObject + "");
                // System.out.println("UserServie:" + pro);
                user.setPro(pro);
                user.setProvince(Integer.parseInt(temp[9]));
                user.setCity(Integer.parseInt(temp[10]));
                // 同上，通过城市码查找城市
                String citySql = "select * from np_city where PROVINCE_CODE=? and city_code=?";
                String[] cityParams = { temp[9], temp[10] };
                String[] cityRs = sqlHelper.ExecuteFirstRecorder(citySql,
                        cityParams);
                City ci = new City();
                ci.setCityCode(temp[10]);
                ci.setProvinceCode(temp[9]);
                ci.setProvinceName(cityRs[0]);
                ci.setCityName(cityRs[2]);
                ci.setProvince(pro);
                // System.out.println("UserService:"+ci);
                user.setCit(ci);
            //    user.setCreator(Integer.parseInt(temp[11]));
                // todo:还需要得到角色和类型这两张表
                result.add(user);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DataBase.releaseConnection(conn);
        }
        return result;
    }