java8 lambda表达式 编写jdbc基类

支持lambda表达式的接口类

@FunctionalInterface
public interface ResultSetHandler <T> {
    T handle(ResultSet rs) throws Exception;
}

jdbc基类

其中doQuery方法支持lamda表达式参数传入，这样就把基本的jdbc处理步骤封装在一个方法里了

import com.alibaba.fastjson.JSON;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.util.*;

public abstract class BaseJdbcDao<T> {

    private static final Logger logger = LoggerFactory.getLogger(BaseJdbcDao.class);

    private Class<T> entityClass;

    public BaseJdbcDao() {
        Type genType = getClass().getGenericSuperclass();
        Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
        entityClass = (Class) params[0];
    }

    public abstract Connection getConnection() throws Exception;
    public abstract void closeConnection(Connection connection) throws Exception;

    /**
     * map转对象
     * @param orgin
     * @param target
     * @param <T>
     * @return
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws InstantiationException
     */
    protected  <T> List<T> populateData(List<Map<String, Object>> orgin, Class<T> target) throws IllegalAccessException, InvocationTargetException, InstantiationException {
        if (CollectionUtils.isEmpty(orgin)) {
            return Collections.emptyList();
        }
        try {
            List<T> objs = new ArrayList<>(orgin.size());
            T obj = null;
            for (Map<String, Object> map : orgin) {
                obj = target.newInstance();
                BeanUtils.populate(obj, map);
                objs.add(obj);
            }
            return objs;
        } catch (Exception e) {
            logger.error("populate error!", e);
            throw e;
        }
    }

    /**
     * map转对象
     * @param orgin
     * @param target
     * @param <T>
     * @return
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws InstantiationException
     */
    protected  <T> T populateData(Map<String, Object> orgin, Class<T> target) throws IllegalAccessException, InvocationTargetException, InstantiationException {
        if (orgin == null) {
            return null;
        }
        try {
            T obj = target.newInstance();
            BeanUtils.populate(obj, orgin);
            return obj;
        } catch (Exception e) {
            logger.error("populate error!", e);
            throw e;
        }
    }

    /**
     * 判断class是不是基本数据类型,String,Integer,Long,Float,Double
     * @param clazz
     * @return
     */
    protected boolean isBasicClassType(Class<?> clazz) {
        return String.class == clazz || Integer.class == clazz || Long.class == clazz || Float.class == clazz || Double.class == clazz || Date.class == clazz;
    }

    /**
     * 查询单条记录,
     * @param <T>
     * @param clazz class的属性必须是小写
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    protected <T> T getObjectBySQL(Class<T> clazz, String sql, Object ... params) throws Exception {
        if (isBasicClassType(clazz)) {
            return this.doQuery(sql, rs -> {
                if (rs.next()) {
                    return this.getBasicClassType(clazz, rs);
                }
                return null;
            }, params);
        }
        return this.populateData(this.getMapBySQL(sql, params), clazz);
    }

    /**
     * 查询一条记录,class的属性必须是小写
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    protected T getObjectBySQL(String sql, Object ... params) throws Exception {
        return this.getObjectBySQL(this.entityClass, sql, params);
    }

    /**
     * 查询一个list, class中的属性必须都是小写的
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    protected List<T> getListBySQL(String sql, Object ... params) throws Exception {
        return this.getListBySQL(this.entityClass, sql, params);
    }

    /**
     *
     * @param clazz
     * @param rs
     * @return
     * @throws Exception
     */
    private <T> T getBasicClassType(Class<T> clazz, ResultSet rs) throws Exception {
        if (String.class == clazz) {
            return (T)rs.getString(1);
        } else if (Integer.class == clazz) {
            return (T)Integer.valueOf(rs.getInt(1));
        } else if (Long.class == clazz) {
            return (T)Long.valueOf(rs.getLong(1));
        } else if (Float.class == clazz) {
            return (T)Float.valueOf(rs.getFloat(1));
        } else if (Double.class == clazz) {
            return (T)Double.valueOf(rs.getDouble(1));
        } else if (Date.class == clazz) {
            return (T)rs.getDate(1);
        }
        return null;
    }

    /**
     * 查询一个list
     * @param clazz, class中的属性必须都是小写的
     * @param sql
     * @param params
     * @param <T>
     * @return
     * @throws Exception
     */
    protected <T> List<T> getListBySQL(Class<T> clazz, String sql, Object ... params) throws Exception {
        if (isBasicClassType(clazz)) {
            return this.doQuery(sql, rs -> {
                List<T> result = new ArrayList<>();
                while (rs.next()) {
                    result.add(this.getBasicClassType(clazz, rs));
                }
                return result;
            }, params);
        }
        return this.populateData(this.getListMapBySQL(sql, params), clazz);
    }

    /**
     * 查询一个list,返回的map中的key都是小写
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    protected List<Map<String, Object>> getListMapBySQL(String sql, Object ... params) throws Exception {
        return this.doQuery(sql, rs -> {
            ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
            int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
            List<String> columns = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                columns.add(md.getColumnLabel(i));
            }
            List<Map<String, Object>> result = new ArrayList<>();
            Map<String, Object> rowData = null;
            while (rs.next()) {
                rowData = new LinkedHashMap<>(columnCount);
                for (String column : columns) {
                    rowData.put(column.toLowerCase(), rs.getObject(column));
                }
                result.add(rowData);
            }
            return result;
        }, params);
    }

    /**
     * 查询一条记录,返回的map中的key都是小写
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    protected Map<String, Object> getMapBySQL(String sql, Object ... params) throws Exception {
        return this.doQuery(sql, rs -> {
            if (rs.next()) {
                ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
                int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
                List<String> columns = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    columns.add(md.getColumnLabel(i));
                }
                Map<String, Object> rowData = new HashMap<>(columnCount);
                for (String column : columns) {
                    rowData.put(column.toLowerCase(), rs.getObject(column));
                }
                return rowData;
            }
            return Collections.EMPTY_MAP;
        }, params);
    }

    /**
     * 查询总记录数
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    protected int getCountBySQL(String sql, Object ... params) throws Exception {
        return this.doQuery(sql, rs -> {
            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        }, params);
    }

    /**
     * 执行sql查询
     * @param sql
     * @param handler
     * @param params
     * @param <T>
     * @return
     * @throws Exception
     */
    protected  <T> T doQuery(String sql, ResultSetHandler<T> handler, Object ... params) throws Exception {
        logger.debug(sql);
        logger.debug("parameters:{}", JSON.toJSONString(params));
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            if(params != null) {
                for(int e = 0; e < params.length; ++e) {
                    this.setParameters(stat, params[e], e + 1);
                }
            }
            rs = stat.executeQuery();
            return handler.handle(rs);
        } catch (Exception e) {
            logger.error("errorSQL:{}", sql);
            logger.error("parameters:{}", JSON.toJSONString(params));
            logger.error("",e);
            throw e;
        } finally {
            closeResultSet(rs);
            closeStatement(stat);
            closeConnection(conn);
        }
    }

    /**
     *
     * @param ps
     * @param value
     * @param columnIndex
     * @return
     * @throws Exception
     */
    protected void setParameters(PreparedStatement ps, Object value, int columnIndex) throws Exception{
        if(value != null){
            Class<?> valueType = value.getClass();
            if (valueType.equals(String.class)) {
                ps.setString(columnIndex, value.toString());
            } else if(valueType.equals(int.class) || valueType.equals(Integer.class)) {
                ps.setInt(columnIndex, Integer.parseInt(value.toString(), 10));
            } else if(valueType.equals(long.class) || valueType.equals(Long.class)) {
                ps.setLong(columnIndex, Long.parseLong(value.toString()));
            } else if(valueType.equals(short.class) || valueType.equals(Short.class)) {
                ps.setShort(columnIndex, Short.parseShort(value.toString()));
            } else if(valueType.equals(java.util.Date.class)) {
                ps.setTimestamp(columnIndex, new java.sql.Timestamp(((java.util.Date)value).getTime()));
            } else if(valueType.equals(boolean.class) || valueType.equals(Boolean.class)) {
                ps.setBoolean(columnIndex, Boolean.parseBoolean(value.toString()));
            } else if(valueType.equals(double.class) || valueType.equals(Double.class)) {
                ps.setDouble(columnIndex, Double.parseDouble(value.toString()));
            } else if(valueType.equals(float.class) || valueType.equals(Float.class)) {
                ps.setFloat(columnIndex, Float.parseFloat(value.toString()));
            } else if(valueType.equals(byte.class) || valueType.equals(Byte.class)) {
                ps.setByte(columnIndex, Byte.parseByte(value.toString()));
            } else if(valueType.equals(byte[].class) || valueType.equals(Byte[].class)) {
                ps.setBytes(columnIndex, (byte[])value);
            } else if(valueType.equals(BigDecimal.class)) {
                ps.setBigDecimal(columnIndex, new BigDecimal(value.toString()));
            } else if(valueType.equals(Timestamp.class)) {
                ps.setTimestamp(columnIndex, (Timestamp)value);
            } else if(valueType.equals(java.sql.Date.class)) {
                ps.setTimestamp(columnIndex, new java.sql.Timestamp(((java.sql.Date)value).getTime()));
            } else {
                ps.setObject(columnIndex, value);
            }
        } else {
            ps.setObject(columnIndex, null);
        }
    }


    /**
     * 关闭statement
     * 
     * @param stmt
     */
    protected void closeStatement(Statement stmt){
        if(stmt!=null){
            try{
                stmt.close();
            }catch(Exception ex){
                logger.error(ex.getMessage(),ex);
            }
        }
    }

    /**
     * 关闭resultSet
     * 
     * @param rs
     */
    protected void closeResultSet(ResultSet rs){
        if(rs!=null){
            try{
                rs.close();
            }catch(Exception ex){
                logger.error(ex.getMessage(),ex);
            }
        }
    }
}