首页 > 代码库 > 使用DbUtils对JDBC封装实现面向实体查询

使用DbUtils对JDBC封装实现面向实体查询

直接上代码

package org.smart4j.chapter2.helper;import org.apache.commons.dbcp2.BasicDataSource;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.smart4j.chapter2.util.CollectionUtil;import org.smart4j.chapter2.util.PropsUtil;import java.sql.Connection;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.Properties;/** * DbUtils 提供对 JDBC 的轻量级封装 *  通过 DbUtils 提供的 QueryRunner 对象可以面向实体(Entity)进行查询。 *  它的原理是:执行 SQL 语句并返回一个 ResultSet,随后通过反射去创建并初始化实体对象。 *  Created by zhengbinMac on 2017/3/25. */public class DatabaseHelper {    private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseHelper.class);    // 线程连接    private static final ThreadLocal<Connection> CONNECTION_THREAD_LOCAL;    // DbUtils    private static final QueryRunner QUERY_RUNNER;    // 线程池    private static final BasicDataSource DATA_SOURCE;    static {        CONNECTION_THREAD_LOCAL = new ThreadLocal<>();        QUERY_RUNNER = new QueryRunner();        Properties conf = PropsUtil.loadProps("config.properties");        // 设置连接池,使用 DBCP 来获取数据库连接        DATA_SOURCE = new BasicDataSource();        DATA_SOURCE.setDriverClassName(conf.getProperty("jdbc.driver"));        DATA_SOURCE.setUrl(conf.getProperty("jdbc.url"));        DATA_SOURCE.setUsername(conf.getProperty("jdbc.username"));        DATA_SOURCE.setPassword(conf.getProperty("jdbc.password"));    }    /**     * 获取数据库连接     */    public static Connection getConnection() {        // 首先从 ThreadLocal 中获取        Connection connection = CONNECTION_THREAD_LOCAL.get();        // 若不存在,则创建一个新的 Connection,并最终将其放入 ThreadLocal 中        if (connection == null) {            try {                connection = DATA_SOURCE.getConnection();            } catch (SQLException e) {                LOGGER.error("get connection failure", e);            } finally {                CONNECTION_THREAD_LOCAL.set(connection);            }        }        return connection;    }    /**     * 批量查询实体     */    public static <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... params) {        List<T> entityList = null;        try {            Connection conn = getConnection();            entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(entityClass), params);        } catch (SQLException e) {            LOGGER.error("query entity list failure", e);        }        return entityList;    }    /**     * 查询单个实体     */    public static <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {        T entity = null;        try {            Connection conn = getConnection();            entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(entityClass), params);        } catch (SQLException e) {            LOGGER.error("query entity failure", e);            e.printStackTrace();        }        return entity;    }    public static List<Map<String, Object>> executeQuery(String sql, Object... params) {        List<Map<String, Object>> result = null;        try {            Connection conn = getConnection();            result = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);        } catch (SQLException e) {            LOGGER.error("query entity failure", e);            e.printStackTrace();        }        return result;    }    /**     * 执行更新语句(update、insert 和 delete)     */    public static int executeUpdate(String sql, Object... params) {        int updateRows = 0;        try {            Connection conn = getConnection();            updateRows = QUERY_RUNNER.update(conn, sql, params);        } catch (SQLException e) {            LOGGER.error("execute update failure", e);            e.printStackTrace();        }        return updateRows;    }    /**     * 插入实体     */    public static <T> boolean insertEntity(Class<T> entityClass, Map<String, Object> fieldMap) {        if (CollectionUtil.isEmpty(fieldMap)) {            LOGGER.error("can not insert entity: fieldMap is empty");            return false;        }        String sql = "INSERT INTO " + getTableName(entityClass);        StringBuilder colums = new StringBuilder("(");        StringBuilder values = new StringBuilder("(");        // 插入实体的字段名,和字段值的占位符        for (String colum : fieldMap.keySet()) {            colums.append(colum).append(", ");            values.append("?, ");        }        colums.replace(colums.lastIndexOf(", "), colums.length(), ")");        values.replace(values.lastIndexOf(", "), values.length(), ")");        sql += colums + " VALUES " + values;        // 插入实体的值        Object[] params = fieldMap.values().toArray();        return executeUpdate(sql, params) == 1;    }    /**     * 更新实体     */    public static <T> boolean updateEntity(Class<T> entityClass, long id, Map<String, Object> fieldMap) {        if (CollectionUtil.isEmpty(fieldMap)) {            LOGGER.error("can not update entity: fieldMap is empty");            return false;        }        // 更具 fieldMap 拼接出更新 SQL 语句        String sql = "UPDATE " + getTableName(entityClass) + " SET ";        StringBuilder columns = new StringBuilder();        // 更新实体的字段        for (String colums : fieldMap.keySet()) {            columns.append(columns).append("=?, ");        }        // 去掉 SQL 最后一个 ‘, ‘        sql += columns.substring(0, columns.lastIndexOf(", ")) + " WHERE id=?";        // 更新实体的值        List<Object> paramList = new ArrayList<Object>();        paramList.addAll(fieldMap.values());        paramList.add(id); // 增加主键 id        Object[] params = paramList.toArray();        return executeUpdate(sql, params) == 1;    }    /**     * 删除实体     */    public static <T> boolean deleteEntity(Class<T> entityClass, long id) {        String sql = "DELTE FROM " + getTableName(entityClass) + "WHERE id=?";        return executeUpdate(sql, id) == 1;    }    /**     * 获取操作表的表名,即实体的类名     */    private static String getTableName(Class<?> entityClass) {        return entityClass.getSimpleName();    }}

两个工具类

集合工具类:

技术分享
package org.smart4j.chapter2.util;import org.apache.commons.collections.MapUtils;import java.util.Collection;import java.util.Map;/** * 集合工具类 *  * Created by zhengbinMac on 2017/3/25. */public class CollectionUtil {    /**     * 判断 Collection 是否为空     */    public static boolean isEmpty(Collection<?> collection) {        return CollectionUtil.isEmpty(collection);    }    /**     * 判断 Collection 是否为非空     */    public static boolean isNotEmpty(Collection<?> collection) {        return !isEmpty(collection);    }    /**     * 判断 Map 是否为空     */    public static boolean isEmpty(Map<?, ?> map) {        return MapUtils.isEmpty(map);    }    /**     * 判断 Map 是否为非空     */    public static boolean isNotEmpty(Map<?, ?> map) {        return !isEmpty(map);    }}
View Code

属性文件(properties)工具类:

技术分享
package org.smart4j.chapter2.util;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.Properties;/** * 属性文件工具类 *  * Created by zhengbinMac on 2017/3/25. */public class PropsUtil {    private static final Logger LOGGER = LoggerFactory.getLogger(PropsUtil.class);    /**     * 加载属性文件     */    public static Properties loadProps(String fileName) {        Properties properties = null;        InputStream is = null;        try {            is = Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName);            if (is == null) {                throw new FileNotFoundException(fileName + "file is not found");            }            properties = new Properties();            properties.load(is);        } catch (IOException e) {            LOGGER.error("load properties file failure", e);        } finally {            if (is != null) {                try {                    is.close();                } catch (IOException e) {                    LOGGER.error("close inputStream failure", e);                }            }        }        return properties;    }    /**     * 获取字符型属性(默认值为空字符串)     */    public static String getString(Properties properties, String key) {        return getString(properties, key, "");    }    /**     * 获取字符型属性(可指定默认值)     */    public static String getString(Properties properties, String key, String defaultValue) {        String value = defaultValue;        if (properties.contains(key)) {            value = properties.getProperty(key);        }        return value;    }    /**     * 获取数值型属性(默认值为0)     */    public static int getInt(Properties properties, String key) {        return getInt(properties, key, 0);    }    /**     * 获取数值型属性(可指定默认值)     */    public static int getInt(Properties properties, String key, int defaultValue) {        int value =http://www.mamicode.com/ defaultValue;        if (properties.contains(key)) {            value = CastUtil.castInt(properties.getProperty(key));        }        return value;    }    /**     * 获取布尔型属性(默认值为 false)     */    public static boolean getBoolean(Properties properties, String key) {        return getBoolean(properties, key, false);    }    /**     * 获取布尔型属性(可指定默认值)     */    public static boolean getBoolean(Properties properties, String key, boolean defaultValue) {        boolean value =http://www.mamicode.com/ defaultValue;        if (properties.contains(key)) {            value = CastUtil.castBoolean(properties.getProperty(key));        }        return value;    }}
View Code

参考资料

[1] 架构探险, 第 2 章 - 为 Web 应用添加业务功能

使用DbUtils对JDBC封装实现面向实体查询