DataBaseUtils

package com.ccsoft.freemarker.codegenerator.utils;

import com.ccsoft.freemarker.codegenerator.entity.DataBase;
import com.ccsoft.freemarker.codegenerator.entity.DataTypeEnum;
import com.ccsoft.freemarker.codegenerator.entity.Field;
import com.ccsoft.freemarker.codegenerator.entity.Table;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author chanchaw
 * @create 2022-09-21 12:38
 */
public class DataBaseUtils {

    public static List<String> getTableNameList(DataBase dataBase) throws SQLException, ClassNotFoundException {
        Connection connection = getConnection(dataBase);
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});

        List<String> ret = new ArrayList<>();
        while(tables.next()){
            String tableName = tables.getString("TABLE_NAME");
            ret.add(tableName);
        }

        tables.close();
        connection.close();
        return ret;
    }
    // 根据数据库参数创建并返回 Connection
    public static Connection getConnection(DataBase dataBase) throws ClassNotFoundException, SQLException {
        Properties props = new Properties();
        props.put("remarksReporting","true");//获取数据库备注信息
        props.put("user",dataBase.getUserName());
        props.put("password",dataBase.getPassWord());

        Class.forName(dataBase.getDriver());
        return DriverManager.getConnection(dataBase.getUrl(),props);
    }

    public static List<String> getDbNameList(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet catalogs = metaData.getCatalogs();

        List<String> dbList = new ArrayList<>();
        while(catalogs.next()){
            String dbName = catalogs.getString(1);
            dbList.add(dbName);
        }

        catalogs.close();
        // 父级方法传入的对象 connection 在子方法中不关闭
        // 如有需要在后续逻辑中自己手动关闭
        // - 方法内部只处理自己创建的对象
//        connection.close();
        return dbList;
    }

    public static List<String> getDbNameList(DataBase dataBase) throws SQLException, ClassNotFoundException {
        Connection connection = getConnection(dataBase);
        List<String> dbNameList = getDbNameList(connection);
        connection.close();
        return dbNameList;
    }

    public static List<String> getSchemas(DataBase dataBase) throws SQLException, ClassNotFoundException {
        Connection connection = getConnection(dataBase);
        List<String> dbNameList = getDbNameList(connection);
        connection.close();
        return dbNameList;
    }

    // 获取指定数据库下的所有表以及字段信息
    public static List<Table> getTables(DataBase dataBase) throws SQLException, ClassNotFoundException {
        Connection connection = getConnection(dataBase);
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});

        List<Table> ret = new ArrayList<>();
        while(tables.next()){
            String tableName = tables.getString("TABLE_NAME");// DB中表名
            String className = getClassName(tableName);// 根据表名转换为实体类名称
            String remarks = tables.getString("REMARKS");// 获取表注释

            // tableName 中有中文时调用方法 metaData.getPrimaryKeys 会报错
            ResultSet primaryKeys = null;
            try{
                primaryKeys = metaData.getPrimaryKeys(null, null, tableName);// 可能是多字段组合主键
            }catch (Exception e){
                continue;
            }

            String pkString = "";
            while(primaryKeys.next()){
                String keyFieldName = primaryKeys.getString("COLUMN_NAME");
                pkString += keyFieldName;
            }

            // 获取表的所有字段
            // 这里调用的表名有中文可能也会报错
            ResultSet columns = null;
            try {
                columns = metaData.getColumns(null, null, tableName, null);
            }catch (Exception e){
                continue;
            }

            List<Field> fields = new ArrayList<>();
            while(columns.next()){
                Field field = new Field();

                String fieldName = columns.getString("COLUMN_NAME");
                String attributeName = StringUtils.getAttributeName(fieldName);
                field.setFieldName(fieldName);
                field.setAttributeName(attributeName);

                String dbType = columns.getString("TYPE_NAME");
//                String javaType = PropertiesUtils.kvMap.get(dbType);
                String javaType = DataTypeEnum.get8Key(dbType).getJava();
                String mybatisType = DataTypeEnum.get8Key(dbType).getMybatis();
                field.setDbType(dbType);
                field.setJavaType(javaType);
                field.setMybatisType(mybatisType);

                String remarks1 = columns.getString("REMARKS");
                field.setRemarks(remarks1);
                Boolean primaryKey = false;
                if(StringUtils.contains(fieldName,pkString.split(","))){
                    primaryKey = true;
                }
                field.setPrimaryKey(primaryKey);
                fields.add(field);
            }
            columns.close();// 主要关闭资源

            // 构建 Table 对象，并加入返回用的集合
            Table table = new Table();
            table.setTableName(tableName);
            table.setModelClassName(className);
            table.setRemarks(remarks);
            table.setPrimaryKeyFieldName(pkString);
            table.setFields(fields);
            ret.add(table);
        }

        // 要关闭两个资源
        tables.close();
        connection.close();
        return ret;
    }

    // 将表名移除前缀后缀返回实体类名称
    private static String getClassName(String tableName){
        tableName = removePrefix(tableName);
        tableName = removeSuffix(tableName);
        return StringUtils.getClassName(tableName);
    }

    private static String removePrefix(String str){
        String tablePrefixes = PropertiesUtils.kvMap.get("tablePrefixes");
        if(tablePrefixes == null || tablePrefixes.length()<=0) return str;

        String[] prefixs = tablePrefixes.split(",");
        if(prefixs == null || prefixs.length<=0) return str;

        for (String symbol:prefixs){
            str = removePrefix(str,symbol,false);// 移除前缀时不忽略大小写
        }

        return str;
    }

    private static String removeSuffix(String str){
        String tableSuffixes = PropertiesUtils.kvMap.get("tableSuffixes");
        if(tableSuffixes == null || tableSuffixes.length()<=0) return str;

        String[] suffixes = tableSuffixes.split(",");
        if(suffixes == null || suffixes.length<=0) return str;

        for (String symbol:suffixes){
            str = removeSuffix(str,symbol,false);// 移除时不忽略大小写
        }

        return str;
    }

    //======================= 下面开始是工具方法 ===========================

    public static String removePrefix(String str,String prefix,boolean ignoreCase) {
        if(str == null) return null;
        if(prefix == null || prefix.length()<=0) return str;
        if(ignoreCase) {
            if(str.toLowerCase().startsWith(prefix.toLowerCase())) {
                return str.substring(prefix.length());
            }
        }else {
            if(str.startsWith(prefix)) {
                return str.substring(prefix.length());
            }
        }
        return str;
    }

    /**
     * 移除字符串末尾的后缀字符串
     * @param str 原始字符串
     * @param suffix 后缀字符串
     * @param ignoreCase true表示忽略大小写
     * @return 返回移除后缀之后的字符串
     */
    public static String removeSuffix(String str,String suffix,boolean ignoreCase) {
        if(str == null) return null;
        if(suffix == null || suffix.length()<=0) return str;
        if(ignoreCase) {
            if(str.toLowerCase().endsWith(suffix.toLowerCase())) {
                return str.substring(0,str.length()-suffix.length());
            }
        }else {
            if(str.endsWith(suffix)) {
                return str.substring(0,str.length()-suffix.length());
            }
        }
        return str;
    }
    //======================= 以上是工具方法 ===========================

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
//        DataBase dataBase = new DataBase("mysql","localhost","3306","multds");
//        dataBase.setUserName("root");
//        dataBase.setPassWord("chanchaw");
//        List<Table> tables = getTables(dataBase);
//        System.out.println(tables);

    }

}