JDBC工具类

来源：互联网发布：交通优化编辑：程序博客网时间：2024/06/10 00:16
由于工作需要，自己造了一个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[] {});    }}
0 0