JDBC 工具类

1. [代码]IResultSetCall.java     


import java.sql.ResultSet;
import java.sql.SQLException;
 
public interface IResultSetCall<T> {
 
    public T invoke(ResultSet rs) throws SQLException;
 
}
2. [代码]DBUtil.java     


import java.io.IOException;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
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.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
 
import oracle.jdbc.OracleTypes;
import util.db.test.DBUtilTest;
 
/**
 * 
 * DBUtil，数据库访问工具类<br/>
 * 对应测试类： {@link DBUtilTest}
 * @preserve all
 */
public class DBUtil {
 
    private static Connection con = null;
 
    public static Connection openConnection() throws SQLException, ClassNotFoundException, IOException {
        if (null == con || con.isClosed()) {
            Properties p = new Properties();
            p.load(DBUtil.class.getResourceAsStream("/config-db.properties"));
            Class.forName(p.getProperty("db_driver"));
            con = DriverManager.getConnection(p.getProperty("db_url"), p.getProperty("db_username"),
                    p.getProperty("db_password"));
        }
        return con;
    }
 
    public static void closeConnection() throws SQLException {
        try {
            if (null != con)
                con.close();
        } finally {
            con = null;
            System.gc();
        }
    }
 
    public static List<Map<String, Object>> queryMapList(Connection con, String sql) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
        Statement preStmt = null;
        ResultSet rs = null;
        try {
            preStmt = con.createStatement();
            rs = preStmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (null != rs && rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    String name = rsmd.getColumnName(i + 1);
                    Object value = rs.getObject(name);
                    map.put(name, value);
                }
                lists.add(map);
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }
 
    public static List<Map<String, Object>> queryMapList(Connection con, String sql, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);// 下标从1开始
            rs = preStmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (null != rs && rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    String name = rsmd.getColumnName(i + 1);
                    Object value = rs.getObject(name);
                    map.put(name, value);
                }
                lists.add(map);
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }
 
    public static <T> List<T> queryBeanList(Connection con, String sql, Class<T> beanClass) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        Statement stmt = null;
        ResultSet rs = null;
        Field[] fields = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            fields = beanClass.getDeclaredFields();
            for (Field f : fields)
                f.setAccessible(true);
            while (null != rs && rs.next()) {
                T t = beanClass.newInstance();
                for (Field f : fields) {
                    String name = f.getName();
                    try {
                        Object value = rs.getObject(name);
                        setValue(t, f, value);
                    } catch (Exception e) {
                    }
                }
                lists.add(t);
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != stmt)
                stmt.close();
        }
        return lists;
    }
 
    public static <T> List<T> queryBeanList(Connection con, String sql, Class<T> beanClass, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        Field[] fields = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);// 下标从1开始
            rs = preStmt.executeQuery();
            fields = beanClass.getDeclaredFields();
            for (Field f : fields)
                f.setAccessible(true);
            while (null != rs && rs.next()) {
                T t = beanClass.newInstance();
                for (Field f : fields) {
                    String name = f.getName();
                    try {
                        Object value = rs.getObject(name);
                        setValue(t, f, value);
                    } catch (Exception e) {
                    }
                }
                lists.add(t);
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }
 
    public static <T> List<T> queryBeanList(Connection con, String sql, IResultSetCall<T> qdi) throws SQLException {
        List<T> lists = new ArrayList<T>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while (null != rs && rs.next())
                lists.add(qdi.invoke(rs));
        } finally {
            if (null != rs)
                rs.close();
            if (null != stmt)
                stmt.close();
        }
        return lists;
    }
 
    public static <T> List<T> queryBeanList(Connection con, String sql, IResultSetCall<T> qdi, Object... params)
            throws SQLException {
        List<T> lists = new ArrayList<T>();
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);
            rs = preStmt.executeQuery();
            while (null != rs && rs.next())
                lists.add(qdi.invoke(rs));
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }
 
    public static <T> T queryBean(Connection con, String sql, Class<T> beanClass) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<T> lists = queryBeanList(con, sql, beanClass);
        if (lists.size() != 1)
            throw new SQLException("SqlError：期待一行返回值，却返回了太多行！");
        return lists.get(0);
    }
 
    public static <T> T queryBean(Connection con, String sql, Class<T> beanClass, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = queryBeanList(con, sql, beanClass, params);
        if (lists.size() != 1)
            throw new SQLException("SqlError：期待一行返回值，却返回了太多行！");
        return lists.get(0);
    }
 
    public static <T> List<T> queryObjectList(Connection con, String sql, Class<T> objClass) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            label: while (null != rs && rs.next()) {
                Constructor<?>[] constor = objClass.getConstructors();
                for (Constructor<?> c : constor) {
                    Object value = rs.getObject(1);
                    try {
                        lists.add((T) c.newInstance(value));
                        continue label;
                    } catch (Exception e) {
                    }
                }
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != stmt)
                stmt.close();
        }
        return lists;
    }
 
    public static <T> List<T> queryObjectList(Connection con, String sql, Class<T> objClass, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);
            rs = preStmt.executeQuery();
            label: while (null != rs && rs.next()) {
                Constructor<?>[] constor = objClass.getConstructors();
                for (Constructor<?> c : constor) {
                    String value = rs.getObject(1).toString();
                    try {
                        T t = (T) c.newInstance(value);
                        lists.add(t);
                        continue label;
                    } catch (Exception e) {
                    }
                }
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != preStmt)
                preStmt.close();
        }
        return lists;
    }
 
    public static <T> T queryObject(Connection con, String sql, Class<T> objClass) throws SQLException,
            InstantiationException, IllegalAccessException {
        List<T> lists = queryObjectList(con, sql, objClass);
        if (lists.size() != 1)
            throw new SQLException("SqlError：期待一行返回值，却返回了太多行！");
        return lists.get(0);
    }
 
    public static <T> T queryObject(Connection con, String sql, Class<T> objClass, Object... params)
            throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = queryObjectList(con, sql, objClass, params);
        if (lists.size() != 1)
            throw new SQLException("SqlError：期待一行返回值，却返回了太多行！");
        return lists.get(0);
    }
 
    public static int execute(Connection con, String sql) throws SQLException {
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            return stmt.executeUpdate(sql);
        } finally {
            if (null != stmt)
                stmt.close();
        }
    }
 
    public static int execute(Connection con, String sql, Object... params) throws SQLException {
        PreparedStatement preStmt = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++)
                preStmt.setObject(i + 1, params[i]);// 下标从1开始
            return preStmt.executeUpdate();
        } finally {
            if (null != preStmt)
                preStmt.close();
        }
    }
 
    public static int[] executeAsBatch(Connection con, List<String> sqlList) throws SQLException {
        return executeAsBatch(con, sqlList.toArray(new String[] {}));
    }
 
    public static int[] executeAsBatch(Connection con, String[] sqlArray) throws SQLException {
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            for (String sql : sqlArray) {
                stmt.addBatch(sql);
            }
            return stmt.executeBatch();
        } finally {
            if (null != stmt) {
                stmt.close();
            }
        }
    }
 
    public static int[] executeAsBatch(Connection con, String sql, Object[][] params) throws SQLException {
        PreparedStatement preStmt = null;
        try {
            preStmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object[] rowParams = params[i];
                for (int k = 0; k < rowParams.length; k++) {
                    Object obj = rowParams[k];
                    preStmt.setObject(k + 1, obj);
                }
                preStmt.addBatch();
            }
            return preStmt.executeBatch();
        } finally {
            if (null != preStmt) {
                preStmt.close();
            }
        }
    }
 
    private static <T> void setValue(T t, Field f, Object value) throws IllegalAccessException {
        // TODO 以数据库类型为准绳，还是以java数据类型为准绳？还是混合两种方式？
        if (null == value)
            return;
        String v = value.toString();
        String n = f.getType().getName();
        if ("java.lang.Byte".equals(n) || "byte".equals(n)) {
            f.set(t, Byte.parseByte(v));
        } else if ("java.lang.Short".equals(n) || "short".equals(n)) {
            f.set(t, Short.parseShort(v));
        } else if ("java.lang.Integer".equals(n) || "int".equals(n)) {
            f.set(t, Integer.parseInt(v));
        } else if ("java.lang.Long".equals(n) || "long".equals(n)) {
            f.set(t, Long.parseLong(v));
        } else if ("java.lang.Float".equals(n) || "float".equals(n)) {
            f.set(t, Float.parseFloat(v));
        } else if ("java.lang.Double".equals(n) || "double".equals(n)) {
            f.set(t, Double.parseDouble(v));
        } else if ("java.lang.String".equals(n)) {
            f.set(t, value.toString());
        } else if ("java.lang.Character".equals(n) || "char".equals(n)) {
            f.set(t, (Character) value);
        } else if ("java.lang.Date".equals(n)) {
            f.set(t, new Date(((java.sql.Date) value).getTime()));
        } else if ("java.lang.Timer".equals(n)) {
            f.set(t, new Time(((java.sql.Time) value).getTime()));
        } else if ("java.sql.Timestamp".equals(n)) {
            f.set(t, (java.sql.Timestamp) value);
        } else {
            System.out.println("SqlError：暂时不支持此数据类型，请使用其他类型代替此类型！");
        }
    }
 
    public static void executeProcedure(Connection con, String procedureName, Object... params) throws SQLException {
        CallableStatement proc = null;
        try {
            proc = con.prepareCall(procedureName);
            for (int i = 0; i < params.length; i++) {
                proc.setObject(i + 1, params[i]);
            }
            proc.execute();
        } finally {
            if (null != proc)
                proc.close();
        }
    }
 
    public static boolean executeProcedureReturnErrorMsg(Connection con, String procedureName, StringBuffer errorMsg,
            Object... params) throws SQLException {
        CallableStatement proc = null;
        try {
            proc = con.prepareCall(procedureName);
            proc.registerOutParameter(1, OracleTypes.VARCHAR);
            for (int i = 0; i < params.length; i++) {
                proc.setObject(i + 2, params[i]);
            }
            boolean b = proc.execute();
            errorMsg.append(proc.getString(1));
            return b;
        } finally {
            if (null != proc)
                proc.close();
        }
    }
 
    public static <T> List<T> executeProcedureReturnCursor(Connection con, String procedureName, Class<T> beanClass,
            Object... params) throws SQLException, InstantiationException, IllegalAccessException {
        List<T> lists = new ArrayList<T>();
        CallableStatement proc = null;
        ResultSet rs = null;
        try {
            proc = con.prepareCall(procedureName);
            proc.registerOutParameter(1, OracleTypes.CURSOR);
            for (int i = 0; i < params.length; i++) {
                proc.setObject(i + 2, params[i]);
            }
            boolean b = proc.execute();
            if (b) {
                rs = (ResultSet) proc.getObject(1);
                while (null != rs && rs.next()) {
                    T t = beanClass.newInstance();
                    Field[] fields = beanClass.getDeclaredFields();
                    for (Field f : fields) {
                        f.setAccessible(true);
                        String name = f.getName();
                        try {
                            Object value = rs.getObject(name);
                            setValue(t, f, value);
                        } catch (Exception e) {
                        }
                    }
                    lists.add(t);
                }
            }
        } finally {
            if (null != rs)
                rs.close();
            if (null != proc)
                proc.close();
        }
        return lists;
    }
 
    public static <T> List<List<T>> listLimit(List<T> lists, int pageSize) {
        List<List<T>> llists = new ArrayList<List<T>>();
        for (int i = 0; i < lists.size(); i = i + pageSize) {
            try {
                List<T> list = lists.subList(i, i + pageSize);
                llists.add(list);
            } catch (IndexOutOfBoundsException e) {
                List<T> list = lists.subList(i, i + (lists.size() % pageSize));
                llists.add(list);
            }
        }
        return llists;
    }
 
 
}
3. [代码]config-db.properties     


db_url=jdbc:oracle:thin:@localhost:1521:orcl
db_driver=oracle.jdbc.driver.OracleDriver
db_username=hr
db_password=


4. [代码]DBUtilTest.java    


import static org.junit.Assert.fail;
import static util.db.DBUtil.executeAsBatch;
import static util.db.DBUtil.executeProcedure;
import static util.db.DBUtil.openConnection;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
 
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
 
import util.db.DBUtil;
import util.db.IResultSetCall;
 
/**
 * 注意： 可以替换Employess1为Employess2,看看查询结果有什么区别。。
 */
public class DBUtilTest {
 
    private Connection con = null;
 
    @Before
    public void setUp() throws Exception {
        try {
            con = DBUtil.openConnection();
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    }
 
    @After
    public void tearDown() throws Exception {
        try {
            DBUtil.closeConnection();
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testQueryBeanListConnectionStringClassOfT() {
        String sql = "SELECT * FROM employees";
        try {
            List<Employees1> emList = DBUtil.queryBeanList(con, sql, Employees1.class);
            print(emList);
        } catch (Exception e) {
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testQueryBeanListConnectionStringClassOfTObjectArray() {
        String sql = "SELECT * FROM employees t WHERE t.salary > ? and T.JOB_ID = ?";
        try {
            List<Employees1> emList = DBUtil.queryBeanList(con, sql, Employees1.class, 5000, "ST_MAN");
            print(emList);
        } catch (Exception e) {
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testQueryBeanListConnectionStringIResultSetCallOfTObjectArray() {
        String sql = "SELECT first_name, last_name, salary FROM employees t WHERE t.salary > ? and T.JOB_ID = ?";
        try {
            List<Employees1> emList = DBUtil.queryBeanList(con, sql, new IResultSetCall<Employees1>() {
                public Employees1 invoke(ResultSet rs) throws SQLException {
                    Employees1 e = new Employees1();
                    e.setFirst_name(rs.getString("first_name"));
                    e.setLast_name(rs.getString("last_name"));
                    e.setSalary(rs.getDouble("salary"));
                    return e;
                }
            }, 5000, "ST_MAN");
            print(emList);
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testQueryObjectListConnectionStringClassOfT() {
        String sql = "SELECT email FROM employees t";
        try {
            List<String> lists = DBUtil.queryObjectList(con, sql, String.class);
            print(lists);
        } catch (Exception e) {
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testQueryObjectListConnectionStringClassOfTObjectArray() {
        String sql = "SELECT salary FROM employees t WHERE t.salary > ? and T.JOB_ID = ?";
        try {
            List<Double> lists = DBUtil.queryObjectList(con, sql, Double.class, 2000, "ST_MAN");
            print(lists);
        } catch (Exception e) {
            fail(e.getMessage());
            e.printStackTrace();
        }
    }
 
    @Test
    public void testQueryBeanConnectionStringClassOfT() {
        String sql = "SELECT * FROM employees t WHERE t.employee_id in (120)";
        try {
            Employees1 emp = DBUtil.queryBean(con, sql, Employees1.class);
            print(emp);
        } catch (Exception e) {
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testQueryBeanConnectionStringClassOfTObjectArray() {
        String sql = "SELECT * FROM employees t WHERE t.employee_id = ?";
        try {
            Employees1 emp = DBUtil.queryBean(con, sql, Employees1.class, 120);
            print(emp);
        } catch (Exception e) {
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testQueryObjectConnectionStringClassOfT() {
        String sql = "SELECT email FROM employees t WHERE t.employee_id in (120)";
        try {
            String s = DBUtil.queryObject(con, sql, String.class);
            print(s);
        } catch (Exception e) {
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testQueryObjectConnectionStringClassOfTObjectArray() {
        String sql = "SELECT salary FROM employees t WHERE t.employee_id in (?)";
        try {
            Double d = DBUtil.queryObject(con, sql, Double.class, 12);
            print(d);
        } catch (Exception e) {
            fail(e.getMessage());
            e.printStackTrace();
        }
    }
 
    @Test
    public void testExecuteConnectionStringObjectArray() {
        String sql = "UPDATE employees t SET t.salary =? WHERE t.employee_id =?";
        try {
            con.setAutoCommit(false);
            int count = DBUtil.execute(con, sql, 20000, 120);
            Assert.assertTrue(count == 1);
            sql = "SELECT t.salary FROM employees t WHERE t.employee_id =?";
            Double d = DBUtil.queryObject(con, sql, Double.class, 120);
            Assert.assertTrue(d - 20000 == 0);
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        } finally {
            try {
                con.rollback();
                con.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    @Test
    public void testQueryMapList() {
        String sql = "SELECT first_name, last_name, salary FROM employees t WHERE t.salary > ? and T.JOB_ID = ?";
        try {
            List<Map<String, Object>> lists = DBUtil.queryMapList(con, sql, 3000, "ST_MAN");
            print(lists);
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testExecuteProcedure() {
        try {
            executeProcedure(openConnection(), "{CALL prc_updatedata_for_daochong(?,?,?,?)}", "3000000993447731",
                    "060000019213", "50", "0010701848");
            System.out.println("执行存储过程更新采购订单表上的数据成功");
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testExecuteAsBatch() {
        try {
            List<String> sqlList = new ArrayList<String>();
            sqlList.add("UPDATE sm_user t SET t.password = 'ok' WHERE t.row_id = '232s43' ");
            sqlList.add("UPDATE sm_user t SET t.password = 'ok' WHERE t.row_id = '232f42' ");
            sqlList.add("UPDATE sm_user t SET t.password = 'ok' WHERE t.row_id = '23g2423' ");
            sqlList.add("UPDATE sm_user t SET t.password = 'ok' WHERE t.row_id = '232434s' ");
            executeAsBatch(openConnection(), sqlList);
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }
 
    @Test
    public void testExecuteAsBatchForPre() {
        try {
            executeAsBatch(con, "UPDATE employees t SET t.first_name = ? WHERE t.last_name = ? ", new Object[][] {
                    { "ok", "235jklsd" }, { "no", "jg4ti324" }, { "no1", "111" }, { "no2", "32423" } });
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }
 
    private void print(Object obj) {
        if (obj instanceof List) {
            List list = (List) obj;
            for (Object o : list) {
                if (o instanceof Map) {
                    Map<String, Object> map = (Map<String, Object>) o;
                    Set<String> set = map.keySet();
                    for (String key : set) {
                        Object value = map.get(key);
                        System.out.print(key + ":" + value + "\t");
                    }
                    System.out.println();
                } else {
                    System.out.println(o);
                }
            }
            System.out.println("总共查询出数据数量是：" + list.size());
        } else {
            System.out.println(obj);
        }
    }
 
}