java-classic-code/springjdbc-sql-template/DAOService.java

package com.primeton.filetransfer.server.template;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import javax.validation.constraints.NotNull;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 为了升级元数据，兼容 hibernate 的 API
 *
 * <pre>
 *
 * Created by zhaopx.
 * User: zhaopx
 * Date: 2020/6/3
 * Time: 11:12
 *
 * </pre>
 *
 * @author zhaopx
 */
@Slf4j
public class DAOService {


    /**
     * 构造方法传入
     */
    private final JdbcTemplate jdbcTemplate;


    public DAOService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /** Get HQL from xxxSql.xml file*/
    public String getSQL(String name) {
        return HibernateStatementSessionFactoryBean.getStatementText(name);
    }

    /** Get dynamic HQL from xxxSql.xml file, voOrMap is a VO or a map.*/
    public String getSQL(String name, Object voOrMap) {
        return HibernateStatementSessionFactoryBean.getStatementText(name, voOrMap);
    }

    /** Get dynamic Params from xxxSql.xml file, voOrMap is a VO or a map.*/
    public Object[] getParam(String name, Object voOrMap) {
        return HibernateStatementSessionFactoryBean.getStatementParam(name, voOrMap);
    }

    /** Get dynamic Params from xxxSql.xml file, voOrMap is a VO or a map.*/
    public Map getMapParam(String name, Object voOrMap) {
        return HibernateStatementSessionFactoryBean.getStatementParamMap(name, voOrMap);
    }


    /**
     * 执行查询 SQL 无参数
     *
     * @param sql
     * @return
     */
    public List<Map> queryForMap(String sql) {
        return queryForMap(sql, new String[]{});
    }

    /**
     * 执行一次查询
     *
     * @param sql
     * @param params
     * @return
     */
    public List<Map> queryForMap(String sql, Object[] params) {
        params = Optional.ofNullable(params).orElse(new Object[]{});
        return (List) jdbcTemplate.query(sql, new ColumnMapRowMapper(), params);
    }


    /**
     * 执行一次查询
     * @param sql
     * @param entityClass
     * @param <T>
     * @return
     */
    public <T> T queryForObject(String sql, Class<T> entityClass) {
        return queryForObject(sql, null, entityClass);
    }

    /**
     * 执行一次查询
     *
     * @param sql
     * @param params
     * @param entityClass
     * @return
     */
    public <T> T queryForObject(String sql, Object[] params, Class<T> entityClass) {
        params = Optional.ofNullable(params).orElse(new Object[]{});
        final ResultTransformer resultTransformer = (entityClass == null || entityClass == Map.class) ? new MapResultTransformer() : new DOResultTransformer(entityClass);
        EntityRowCallbackHandler<T> rowCallbackHandler = new EntityRowCallbackHandler(resultTransformer);
        jdbcTemplate.query(sql, params, rowCallbackHandler);
        final List<T> result = rowCallbackHandler.getResult();
        return result.isEmpty() ? null : result.get(0);
    }


    /**
     * 执行SQL查询
     * @param id statement id
     * @return Object[]/Object
     */
    public List<Map<String, Object>> queryById(String id) {
        return queryById(id, null, null);
    }

    /**
     * 执行SQL查询
     * @param id statement id
     * @return Object[]/Object
     */
    public <T> List<T> queryById(String id, Class<T> entityClass) {
        return queryById(id, null, entityClass);
    }


    /**
     * 执行SQL查询
     * @param id 脚本
     * @param params 参数
     * @return Object[]/Object
     */
    public List<Map<String, Object>> queryById(String id, Map<String, Object> params){
        return queryById(id, params, null);
    }


    /**
     * 执行 statement id 的查询，返回  ResultTransformer 的转换结果
     * @param id statement id
     * @param params 参数
     * @param entityClass 结果转换
     * @return 返回 ResultTransformer 的转换结果
     */
    public <T> List<T> queryById(String id, Map<String, Object> params, Class<T> entityClass) {
        final String sql = getSQL(id, params);
        if (StringUtils.isBlank(sql)) {
            throw new IllegalArgumentException("undefined statement id: " + id);
        }
        Object[] sqlParams = (params == null ? null : getParam(id, params));
        sqlParams = Optional.ofNullable(sqlParams).orElse(new Object[]{});
        final ResultTransformer resultTransformer = (entityClass == null || entityClass == Map.class) ? new MapResultTransformer() : new DOResultTransformer(entityClass);
        EntityRowCallbackHandler<T> rowCallbackHandler = new EntityRowCallbackHandler(resultTransformer);
        jdbcTemplate.query(sql, sqlParams, rowCallbackHandler);
        return rowCallbackHandler.getResult();
    }

    /**
     * 执行一个 更新 SQL
     *
     * @param sql
     * @return
     */
    public int updateSQL(String sql) {
        return updateSQL(sql, new String[]{});
    }


    /**
     * 执行一个 更新 SQL
     *
     * @param sql
     * @param params
     * @return
     */
    public int updateSQL(String sql, Object[] params) {
        params = Optional.ofNullable(params).orElse(new Object[]{});
        return jdbcTemplate.update(sql, params);
    }

    /**
     * 执行一个 更新 SQL
     *
     * @param id
     * @return
     */
    public int updateById(String id) {
        return updateSQL(getSQL(id), new Object[]{});
    }


    /**
     * 执行一个 更新 SQL
     *
     * @param id
     * @param params
     * @return
     */
    public int updateById(String id, Map<String, Object> params) {
        return updateSQL(getSQL(id, params), getParam(id, params));
    }


    /**
     * 批量执行 SQL
     *
     * @param batchSql
     * @return
     */
    public int[] batchUpdateSQL(String... batchSql) throws SQLException {
        if (batchSql.length == 0) {
            return new int[]{};
        }

        return jdbcTemplate.batchUpdate(batchSql);
    }

    class EntityRowCallbackHandler<T> implements RowCallbackHandler {

        private final ResultTransformer<T> resultTransformer;

        /**
         * 结果集
         */
        private final List<T> result = new LinkedList<>();

        public EntityRowCallbackHandler(ResultTransformer<T> resultTransformer) {
            this.resultTransformer = resultTransformer;
        }

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            final int columnCount = rs.getMetaData().getColumnCount();
            // 返回的字段名字
            List<String> columnNames = new ArrayList<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                // 转为大写
                columnNames.add(StringUtils.upperCase(rs.getMetaData().getColumnName(i)));
            }

            // 字段列表
            String[] aliass = columnNames.toArray(new String[0]);
            // 循环遍历获取值
            while (rs.next()) {
                List<Object> tuple = new ArrayList<>(columnCount);
                for (String columnName : columnNames) {
                    tuple.add(rs.getObject(columnName));
                }
                result.add(resultTransformer.transformTuple(tuple.toArray(new Object[0]), aliass));
            }
        }


        @NotNull
        public List<T> getResult() {
            return result;
        }
    }

}