jdbcutil简化版

之前写过的jdbc工具类有一个朋友用到，不过只是简单的调用，不需要多个数据库，也不需要Redis缓存，并且是JDK6，改了不少东西才OK，现在把这个版本也做个记录。

// 唯一的一个依赖包 commons-beanutils-1.9.3.jar
import org.apache.commons.beanutils.ConvertUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.text.MessageFormat;
import java.util.*;

public class DataFetcher {
    // 数据库参数
    private static final String DB_URL = "";
    private static final String DB_DRIVER = "";
    private static final String DB_USER = "";
    private static final String DB_PASS = "";

    // 写了常用的两种数据库分页，需要使用其他数据库时可以查看下MessageFormat.format()方法的使用
    // mysql
    // private static final String LIMIT_SQL = "{0} LIMIT {1}, {2}";
    // oracle
    private static final String LIMIT_SQL = "SELECT * FROM(SELECT TA.*, ROWNUM RN FROM ({0})TA WHERE ROWNUM<= {2})WHERE RN>={1}";

    // 封装私有构造方法
    private DataFetcher() {
        loadDriver();
    }

    /**
     * 加载驱动类
     */
    private void loadDriver() {
        try {
            Class.forName(DB_DRIVER);
            System.out.println(String.format("JDBC Driver Class load completion:%s", DB_DRIVER));
        } catch (ClassNotFoundException e) {
            System.err.println(String.format("JDBC Driver Class [%s] is not Found!", DB_DRIVER));
        }
    }

    // 设为单例
    private static class DataFetcherHolder {
        private static final DataFetcher INSTANCE = new DataFetcher();
    }
    public static DataFetcher getInstance() {
        return DataFetcherHolder.INSTANCE;
    }

    /**
     * 获取数据库连接connection
     */
    private Connection getConn() {
        try {
            return DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        }catch (Exception e) {
            throw new RuntimeException("Please check your config!", e);
        }
    }

    /**
     * 执行SQL
     */
    public void execute(DataFetcher.SQLCallback callback) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConn();
            stmt = conn.createStatement();
            if (callback != null)
                callback.callback(stmt);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(stmt);
            close(conn);
        }
    }

    /**
     * 执行DML语句
     */
    public void executeUpdate(final CharSequence sql) {
        execute(new SQLCallback() {
            @Override
            public void callback(Statement stmt) throws SQLException {
                stmt.executeUpdate(sql.toString());
                System.out.println("execute update:" + sql);
            }
        });
    }

    /**
     * 获得组函数返回值
     * 返回第一列数据的第一个字段的值
     * @throws RuntimeException 如果返回结果中数据条数为0，或者值不是整数，抛出异常
     */
    public Long executeQuerySqlGetCount(CharSequence sql) {
        Map<String, Object> map = executeQuerySqlGetFirst(sql);
        if (map.isEmpty()) {
            throw new RuntimeException("There is no data in the query result!");
        }
        try {
            Object count = map.values().toArray()[0];
            return new Long(count.toString());
        } catch (NumberFormatException e) {
            throw new RuntimeException("The field is not a number!");
        }
    }

    /**
     * 只保留查询结果的第一条数据
     * @throws RuntimeException 如果查询结果为空，抛出异常
     */
    public Map<String, Object> executeQuerySqlGetFirst(CharSequence sql) {
        List<Map<String, Object>> data = executeQuerySql(sql);
        if (data.isEmpty()) {
            throw new RuntimeException("The result of the query is empty！");
        }
        return data.get(0);
    }

    /**
     * 返回标准结构的分页查询
     */
    public List<Map<String, Object>> executeQuerySqlWithLimit(CharSequence sql, int start, int end) {
        String sqlStr = MessageFormat.format(LIMIT_SQL, sql, start, end);
        return executeQuerySql(sqlStr);
    }

    /**
     * 返回泛型集合的分页查询
     */
    public <T> List<T> executeQuerySqlWithLimit(Class<T> clazz, CharSequence sql, int start, int end) {
        String sqlStr = MessageFormat.format(LIMIT_SQL, sql, start, end);
        return executeQuerySql(clazz, sqlStr);
    }

    /**
     * 返回一个标准结构的查询结果
     * @param sql 查询SQL
     * @return 一个map类型的集合
     */
    public List<Map<String, Object>> executeQuerySql(final CharSequence sql) {
        final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        execute(new SQLCallback() {
            @Override
            public void callback(Statement stmt) throws SQLException {
                System.out.println("execute sql : " + sql);
                ResultSet rs = null;
                try {
                    rs = stmt.executeQuery(sql.toString());
                    int columnCount = rs.getMetaData().getColumnCount();
                    while (rs.next()) {
                        Map<String, Object> data = new HashMap<String, Object>();
                        for (int i = 1; i <= columnCount; i++) {
                            String columnName = rs.getMetaData().getColumnLabel(i);
                            Object value = rs.getObject(columnName);
                            data.put(columnName, value);
                        }
                        list.add(data);
                    }
                } finally {
                    close(rs);
                }
            }
        });
        return list;
    }

    /**
     * 返回一个泛型集合
     * @param clazz 指定返回集合类型
     * @param sql 查询SQL
     */
    public  <T> List<T> executeQuerySql(final Class<T> clazz, final CharSequence sql) {
        final List<T> data = new ArrayList<T>();
        execute(new SQLCallback() {
            @Override
            public void callback(Statement stmt) throws SQLException {
                System.out.println("execute sql : " + sql);
                ResultSet rs = null;
                try {
                    rs = stmt.executeQuery(sql.toString());
                    while (rs.next()) {
                        T entity = clazz.newInstance();
                        for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                            String columnName = rs.getMetaData().getColumnLabel(i);
                            Object value = rs.getObject(columnName);

                            Field field = clazz.getDeclaredField(columnName);
                            Class<?> fieldType = field.getType();

                            if (value != null) {
                                // 如果指定的Object与此Class所表示的对象赋值不兼容
                                // 这里经常会遇到类型转换异常，如果数据库类型和Java实体一致，可以去掉，依赖包也可以不需要了
                                if (!fieldType.isInstance(value)){
                                    // 调用common.bean utils包转化值为对应类型
                                    value = ConvertUtils.convert(value, fieldType);
                                }
                                field.set(entity, value);
                            }
                        }
                        data.add(entity);
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (NoSuchFieldException e) {
                    e.printStackTrace();
                } finally {
                    close(rs);
                }
            }
        });
        return data;
    }

    private static void close(Connection closeable) {
        try {
            if (closeable != null) {
                closeable.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static void close(ResultSet closeable) {
        try {
            if (closeable != null) {
                closeable.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static void close(Statement closeable) {
        try {
            if (closeable != null) {
                closeable.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 处理查询结果的实现接口
     */
    interface SQLCallback {
        void callback(Statement stmt) throws SQLException;
    }
}

以下是调用的方法

实体

public class dict_channel{
    private Integer id1;
    private String key1;
    private String value1;

    public Integer getId1() {
        return id1;
    }

    public void setId1(Integer id1) {
        this.id1 = id1;
    }

    public String getKey1() {
        return key1;
    }

    public void setKey1(String key1) {
        this.key1 = key1;
    }

    public String getValue1() {
        return value1;
    }

    public void setValue1(String value1) {
        this.value1 = value1;
    }
}

数据库表信息

调用方法

import java.util.List;
import java.util.Map;

public class TestDataFetcher {
    public static void main(String[] args) {
        DataFetcher dataFetcher = DataFetcher.getInstance();

        String sql = "select id as id1,`key` as key1,value as value1 from dict_channel";
        // 标准结构
        List<Map<String, Object>> maps = dataFetcher.executeQuerySql(sql);
        for (Map<String, Object> map : maps) {
            for (String key : map.keySet()) {
                System.out.print("key:" + key + ", value:" + map.get(key));
            }
            System.out.println();
        }
        // 指定泛型
        List<dict_channel> dictChannelList = dataFetcher.executeQuerySql(dict_channel.class, sql);
        for (dict_channel dictChannel : dictChannelList) {
            System.out.println("key:" + dictChannel.getKey1() + ", value:" + dictChannel.getValue1());
        }
    }
}

从调用方法的SQL中可以看到，数据库字段到对象属性是通过字段名映射的，所以在编辑SQL的时候需要格外小心。

以上，通用的一个jdbc工具就完成了，除去Common-BeanUtils.jar需要引入，可以适用于几乎所有场景。当然了，我也只是用了四五个数据库，如果使用过程遇到问题，可以给我留消息。