org.test4j.module.database.script.DBHelper Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of fluent-dbtest Show documentation
fluent database test
There is a newer version: 1.1.2
package org.test4j.module.database.script;

import org.test4j.module.database.enviroment.normalise.TypeNormaliser;
import org.test4j.module.database.enviroment.normalise.TypeNormaliserFactory;
import org.test4j.tools.commons.ClazzHelper;
import org.test4j.tools.commons.StringHelper;
import org.test4j.tools.reflector.FieldAccessor;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.BiConsumer;

import static java.lang.Math.min;
import static org.test4j.tools.commons.StringHelper.join;

@SuppressWarnings({"rawtypes", "unchecked"})
public final class DBHelper {

    /**
     * 将ResultSet的当前行转换为Map数据返回
     *
     * @param rs 数据库结果ResultSet
     * @return Map
     */
    public static Map getMapFromResult(ResultSet rs, boolean isCamelName) throws Exception {
        ResultSetMetaData rsmd = rs.getMetaData();
        if (rs.next()) {
            return buildMap(rs, isCamelName, rsmd);
        } else {
            return null;
        }
    }

    /**
     * 将ResultSet的转换为Map List数据返回
     *
     * @param rs          结果集
     * @param isCamelName 转成驼峰命名
     * @return data list
     */
    public static List getListMapFromResult(ResultSet rs, boolean isCamelName)
        throws Exception {
        List list = new ArrayList<>();
        ResultSetMetaData rsmd = rs.getMetaData();
        while (rs.next()) {
            Map map = buildMap(rs, isCamelName, rsmd);
            list.add(map);
        }

        return list;
    }


    /**
     * 将ResultSet的当前行转换为PoJo数据返回
     *
     * @param    结果类型
     * @param rs    结果集
     * @param clazz 结果类型
     * @return 结果
     */
    public static  T getPoJoFromResult(ResultSet rs, Class clazz) throws Exception {
        if (rs.next()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            return buildPoJo(rs, clazz, rsmd);
        } else {
            return null;
        }
    }

    private static  T buildPoJo(ResultSet rs, Class clazz, ResultSetMetaData rsmd) throws SQLException {
        int count = rsmd.getColumnCount();
        T pojo = count < 2 ? null : ClazzHelper.newInstance(clazz);
        Object firstField = buildObject(rs, true, rsmd, (key, value) -> {
            if (pojo != null) {
                FieldAccessor.field(clazz, key).set(pojo, value);
            }
        });
        return pojo == null ? (T) firstField : pojo;
    }


    private static Map buildMap(ResultSet rs, boolean isCamelName, ResultSetMetaData rsmd) {
        Map map = new HashMap<>();
        buildObject(rs, isCamelName, rsmd, map::put);
        return map;
    }

    private static Object buildObject(ResultSet rs, boolean isCamelName, ResultSetMetaData rsmd, BiConsumer consumer) {
        try {
            int count = rsmd.getColumnCount();
            Object first = null;
            for (int index = 1; index <= count; index++) {
                String key = getCamelFieldName(rsmd, index, isCamelName);
                String columnClaz = rsmd.getColumnClassName(index);
                Object o;
                if (java.sql.Timestamp.class.getName().endsWith(columnClaz)) {
                    o = rs.getTimestamp(index);
                } else if (java.sql.Clob.class.getName().endsWith(columnClaz)) {
                    o = rs.getClob(index);
                } else {
                    o = rs.getObject(index);
                }
                Object value = DBHelper.normaliseValue(columnClaz, o);
                consumer.accept(key, value);
                if (index == 1) {
                    first = value;
                }
            }
            return first;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 将ResultSet的当前行转换为PoJo列表数据返回
     *
     * @param    结果类型
     * @param rs    结果集
     * @param clazz 结果类型
     * @return data list
     */
    public static  List getListPoJoFromResult(ResultSet rs, Class clazz)
        throws Exception {
        List list = new ArrayList();
        while (rs.next()) {
            T o = getPoJoFromResult(rs, clazz);
            list.add(o);
        }
        return list;
    }

    /**
     * 返回数据库查询结果集第index个字段的camel名称
     *
     * @param rsmd        结果集元数据
     * @param index       index
     * @param isCamelName 是否过滤非法字符后返回驼峰命名

     *                    false:返回数据库的原生字段名称
     * @return 字段名称
     */
    private static String getCamelFieldName(ResultSetMetaData rsmd, int index, boolean isCamelName) throws SQLException {
        String columnName = rsmd.getColumnName(index);
        if (isCamelName) {
            columnName = columnName.replaceAll("[^a-zA-Z0-9]", " ");
            return StringHelper.camel(columnName.toLowerCase());
        } else {
            return columnName;
        }
    }

    /**
     * 分解sql语句为单条可执行的sql语句集合,并过滤注释
     *
     * @param content 多条sql语句(可能包含注释，换行等信息)
     * @return 分隔的sql语句集
     */
    public static String[] parseSQL(String content) {
        char[] chars = content.toCharArray();
        List statements = new ArrayList<>();

        StatementStatus status = StatementStatus.NORMAL;
        StringBuilder buff = new StringBuilder();
        for (int index = 0; index < chars.length; index++) {
            char ch = chars[index];
            char next;
            switch (status) {
                case SINGLE_NOTE:
                    if (ch == '\n' || ch == '\r') {
                        buff.append(' ');
                        status = StatementStatus.NORMAL;
                    }
                    break;
                case MULTI_NOTE:
                    next = (index == chars.length - 1) ? '/' : chars[index + 1];
                    if (ch == '*' && next == '/') {
                        index++;
                        status = StatementStatus.NORMAL;
                    }
                    break;
                case SINGLE_QUOTATION:
                    buff.append(ch);
                    if (ch == '\'') {
                        status = StatementStatus.NORMAL;
                    }
                    break;
                case DOUBLE_QUOTATION:
                    buff.append(ch);
                    if (ch == '"') {
                        status = StatementStatus.NORMAL;
                    }
                    break;
                case NORMAL:
                default:
                    next = (index == chars.length - 1) ? ';' : chars[index + 1];
                    if (ch == '-' && next == '-') {
                        index++;
                        status = StatementStatus.SINGLE_NOTE;
                    } else if (ch == '/' && next == '*') {
                        index++;
                        status = StatementStatus.MULTI_NOTE;
                    } else if (ch == '\'') {
                        buff.append(ch);
                        status = StatementStatus.SINGLE_QUOTATION;
                    } else if (ch == '"') {
                        buff.append(ch);
                        status = StatementStatus.DOUBLE_QUOTATION;
                    } else if (ch == ';') {
                        String statement = buff.toString().trim();
                        if (!"".equals(statement)) {
                            statements.add(statement);
                        }
                        buff = new StringBuilder();
                    } else if (ch == '\n' || ch == '\r') {
                        buff.append(' ');
                    } else {
                        buff.append(ch);
                    }
                    break;
            }
        }
        String statement = buff.toString().trim();
        if (!"".equals(statement)) {
            statements.add(statement);
        }

        String[] stmts = new String[statements.size()];
        statements.toArray(stmts);
        return stmts;
    }

    /**
     * 根据DataMap构造查询条件
     *
     * @param map where parameter
     * @return where sql
     */
    public static String getWhereCondition(Map map) {
        if (map == null || map.size() == 0) {
            return "";
        }
        StringBuilder where = new StringBuilder();
        where.append(" where ");
        boolean isFirst = true;
        for (Map.Entry entry : map.entrySet()) {
            if (isFirst) {
                isFirst = false;
            } else {
                where.append(" and ");
            }
            where.append(entry.getKey()).append("=?");
        }
        return where.toString();
    }

    public static Object normaliseValue(String klass, Object currVal) throws Exception {
        if (currVal == null) {
            return null;
        }
        TypeNormaliser tn = TypeNormaliserFactory.getNormaliser(klass);
        return tn == null ? currVal : tn.normalise(currVal);
    }

    /**
     * 构建h2数据库表table的唯一键脚本
     *
     * @param table  指定表
     * @param fields 索引字段
     * @return unique index
     */
    public static String buildH2Unique(String table, String... fields) {
        if (fields == null || fields.length == 0) {
            return "";
        }
        return "ALTER TABLE " + table + "  ADD CONSTRAINT " + indexName(table, fields, true) +
            join(" UNIQUE(", ",", fields, "); ");
    }

    private static String indexName(String table, String[] fields, boolean isUniq) {
        String uniName = (isUniq ? "UNI" : "INX") + join("_" + table + "_", "_", fields, "").toUpperCase();
        uniName = uniName.substring(0, min(uniName.length(), 20)) + "_" + uniName.hashCode();
        return uniName.replace('-', '_');
    }

    /**
     * 构建h2数据库表table的普通索引脚本
     *
     * @param table  指定表
     * @param fields 索引字段
     * @return index sql
     */
    public static String buildH2Index(String table, String... fields) {
        if (fields == null || fields.length == 0) {
            return "";
        }
        return "CREATE INDEX " + indexName(table, fields, false) +
            join(" ON " + table + "(", ",", fields, "); ");
    }
}