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

package com.primeton.dsp.upgrader;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.parser.ParseSettings;
import org.jsoup.parser.Parser;
import org.jsoup.select.Elements;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

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


    /**
     * SQL #var# 类型的替换
     */
    public static final Pattern SQL_VAR_PATTERN = Pattern.compile("(#(\\w|\\.|-|_)+#)");


    public final JdbcTemplate jdbcTemplate;


    /**
     * SQL statement 缓存
     */
    private final static Map<String, String> sqlCache = new HashMap<>();


    public DAOServiceImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        if (sqlCache.isEmpty()) {
            // 未初始化，则初始化一次
            try {
                init(jdbcTemplate);
            } catch (Exception e) {
                throw new IllegalStateException("无法加载 SQL 声明文件。", e);
            }
        }
    }


    /**
     * 注意：静态，同步方法
     *
     * @param jdbcTemplate
     * @throws Exception
     */
    private synchronized static void init(JdbcTemplate jdbcTemplate) throws Exception {
        if (!sqlCache.isEmpty()) {
            return;
        }
        Connection connection = jdbcTemplate.getDataSource().getConnection();
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            String productName = metaData.getDatabaseProductName();
            log.info("database product name: {}", productName);

            String sqlXmlPath = String.format("META-INF/sqlxml/%s/upgradeSql.xml", Optional.ofNullable(productName).orElse("mysql").toLowerCase());
            ClassPathResource pathResource = new ClassPathResource(sqlXmlPath);
            try (InputStream in = pathResource.getInputStream()) {
                String content = IOUtils.toString(in, StandardCharsets.UTF_8);
                sqlCache.putAll(readProperties(content));
            }

            log.info("load sql xml file: {} success.", sqlXmlPath);
        } finally {
            try {
                connection.close();
            } catch (Exception e) {
            }
        }

    }


    protected static Map<String, String> readProperties(String xml) {
        Map<String, String> prop = new HashMap<>();

        // 保持 properties 中的配置是大小写区分的，否则全是小写的
        Parser parser = Parser.xmlParser();

        // 大小写敏感
        parser.settings(ParseSettings.preserveCase);

        Document doc = Jsoup.parse(xml, "http://www.primeton.com/dsp", parser);
        Elements statements = doc.select("statement");
        if (statements.size() > 0) {
            for (Element statement : statements) {
                prop.put(statement.attr("id"), statement.text());
            }
        }
        return prop;
    }


    /**
     * 获取一个无须替换参数的 SQL
     *
     * @param name
     * @return
     */
    public String getSQL(String name) {
        return getSQL(name, new HashMap());
    }


    /**
     * 获取 SQL， 替换模板
     *
     * @param name
     * @param params
     * @return
     */
    public String getSQL(String name, Map<String, Object> params) {
        params = Optional.ofNullable(params).orElse(new HashMap());
        String sql = sqlCache.get(name);
        if (params.isEmpty()) {
            return sql;
        }

        Map<String, String> varParam = new HashMap<>(params.size());
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            varParam.put(entry.getKey(), "?");
        }
        return getFullString(sql, varParam);
    }


    /**
     * 根据 String 的 #field# 按照 map 中的参数替换，组成一个新的 SQL 返回。
     *
     * @param sql    支持 #field# 的变量替换
     * @param params 参数，如果没有参数则会原路返回
     * @return 返回新的变量替换的 SQL
     */
    public static String getFullString(String sql, Map<String, String> params) {
        Matcher matcher = SQL_VAR_PATTERN.matcher(sql);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            String group = matcher.group();
            String field = StringUtils.trim(group.substring(1, group.length() - 1));
            String val = params.get(field);
            if (val == null) {
                continue;
            }
            matcher.appendReplacement(sb, val);
        }
        matcher.appendTail(sb);

        return sb.toString();
    }

    /**
     * 执行查询 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 params
     * @return
     */
    public Map queryForObjectMap(String sql, Object[] params) {
        List<Map> rs = queryForMap(sql, params);
        return rs.isEmpty() ? Collections.emptyMap() : rs.get(0);
    }


    /**
     * 执行一个 更新 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 batchSql
     * @return
     */
    public int[] batchUpdateSQL(String... batchSql) throws SQLException {
        if (batchSql.length == 0) {
            return new int[]{};
        }

        return jdbcTemplate.batchUpdate(batchSql);
    }
}