JDBC工具类

由于工作需要，自己造了一个JDBC的工具类。虽然有重复造轮子的嫌疑，不过适用自己的才是最好的。这个工具类包含了基本的CRUD功能以及封装好的返回map和list功能，算是简单的提升了一些工作效率，现在记录一下，一方面方便自己日后查找，另一方面也是分享给大家，如果有需要可以做个参考。欢迎高手指导。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.NamingException;


public class DBConnection {
    private static String url=Global.DBURL;
    private static String user=Global.USER;
    private static String pwd=Global.PWD;
    private Connection dbConn = null;
    private Statement stmt=null;
    private PreparedStatement pstmt = null;
    private ResultSet rs=null;
    /*加载数据库驱动*/
    private Connection getconnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            dbConn = DriverManager.getConnection(url, user, pwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dbConn;
    }

    /*获得Statement对象*/
    private Statement getstatement() throws SQLException{
        stmt=getconnection().createStatement();
        return stmt;
    }

    // 获取语句对象
    private PreparedStatement getPrepareStatement(String sql)
            throws NamingException, SQLException {
        pstmt = getconnection().prepareStatement(sql);
        return pstmt;
    }

    /*获得ResultSet对象*/
    public ResultSet getResultSet(String sql) throws SQLException{
        rs =getstatement().executeQuery(sql);
        return rs;
    }

    /*更新数据库*/
    public int updateData(String sql) throws SQLException{
        int result=0;
        try{
            result=getstatement().executeUpdate(sql);
        }finally{
            close();
        }
        return result;
    }

    /*更新数据库*/
    public int getGeneratedKeys(String sql) throws SQLException{
        int id=0;
        try{
            stmt=getconnection().createStatement();
            stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
            rs=stmt.getGeneratedKeys();
            if (rs.next()) {  
                id = rs.getInt(1);
            }  
        }finally{
            close();
        }
        return id;
    }

    /*更新数据库*/
    public int updateData(String sql,List<Object> params) throws SQLException, NamingException{
        int recNo = 0;// 表示受影响的记录行数
        try{
            pstmt = this.getPrepareStatement(sql);
            for (int i = 0; i < params.size(); i++)
                pstmt.setObject(i + 1, params.get(i));
            recNo = pstmt.executeUpdate();// 执行更新操作
        }finally{
            close();
        }
        return recNo;
    }

    /*获得指定列的数据*/
    public String getValue(String valueName,String sql) throws SQLException{
        ResultSet rs;
        String result=null;
        try{
            rs = getstatement().executeQuery(sql);
            if(rs.next()){
                result= rs.getString(valueName);
            }
        }finally{
            close();
        }
        return result;
    }

    public static void main(String[] args) throws SQLException, NamingException {
        DBConnection db = new DBConnection();
        String sql = "INSERT INTO car_emission (licensenumber,vin,enginenum,userid,orderstatus,createtime,isurgent) VALUES (?,?,?,?,?,?,?)";
        List<Object> paramList = new ArrayList<Object>();
        paramList.add("123");
        paramList.add("vin");
        paramList.add("ENG001");
        paramList.add(701);
        paramList.add(1);
        paramList.add("2016-03-29 00:00:00");
        paramList.add(1);
        db.updateData(sql, paramList);
    }

    /*关闭连接*/
    public void close() {
        try {
            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
            if (dbConn != null)
                dbConn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*批量处理多个sql，如果sql异常则回滚*/
    public void updateSQLs(List<String> sqls) throws SQLException{
        Connection conn =getconnection();
        try {
            conn.setAutoCommit(false);
            stmt=conn.createStatement();
            for(String sql:sqls){
                stmt.execute(sql);
            }
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }

    // 遍历参数数组，将数组中的值按位置一一对应地对pstmt所代表的SQL语句中的参数进行设置
    private void setParams(String sql, Object[] params) throws NamingException,
            SQLException {
        pstmt = this.getPrepareStatement(sql);
        for (int i = 0; i < params.length; i++)
            pstmt.setObject(i + 1, params[i]);
    }

    // 将结果集中封装成一个List
    private List<Map<String,Object>> getListFromRS() throws NamingException, SQLException {
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        // 获取元数据
        ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next()) {
            Map<String,Object> m = new HashMap<String,Object>();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                // 获取当前行第i列的数据类型
                String colType = rsmd.getColumnTypeName(i);
                // 获取当前行第i列的列名
                String colName = rsmd.getColumnName(i);
                String s = rs.getString(colName);
                if (s != null) {
                    if (colType.equals("INTEGER")||colType.equals("INT"))
                        m.put(colName, new Integer(rs.getInt(colName)));
                    else if (colType.equals("FLOAT"))
                        m.put(colName, new Float(rs.getFloat(colName)));
                    else{
                        // 其余类型均作为String对象取出
                        m.put(colName, rs.getString(colName));
                        //System.out.println("==="+m);
                    }

                }
            }
            list.add(m);
        }
        return list;
    }

    // 查询获取List对象
    public List<Map<String,Object>> getList(String sql, Object[] params) {
        List<Map<String,Object>> list = null;// 定义保存查询结果的集合对象
        try {
            setParams(sql, params);// 根据sql语句和params，设置pstmt对象
            rs = pstmt.executeQuery();// 执行SQL语句，得到结果集
            list = getListFromRS();// 根据RS得到list
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    public List<Map<String,Object>> getList(String sql) {
        return getList(sql, new Object[] {});
    }

    // 查询获取Map对象
    public Map<String,Object> getMap(String sql, Object[] params) {
        Map<String,Object> m = null;
        try {
            setParams(sql, params);// 根据sql语句和params，设置pstmt对象
            rs = pstmt.executeQuery();
            List<Map<String,Object>> l = getListFromRS();
            if (l.size() != 0)
                m = l.get(0);// 根据RS得到Map

        } catch (Exception e) {
            LogUtils.writeLogByDay("DataErr", LogUtils.getStackMsg(e));
            e.printStackTrace();
        } finally {
            close();
        }
        return m;
    }

    public Map<String,Object> getMap(String sql) {
        return getMap(sql, new Object[] {});
    }

}