JDBC实现生成指定连接的数据库的建表语句（根据外键排序）和根据建表语句创建指定名称的数据库

在采用分库方案实现多租户的时候可能会遇到需要程序根据指定库的表结构创建一个新库的场景，比较麻烦的一点是需要根据外键关系来建立表，这个功能使用JDBC的API实现并不复杂。

package copyright.hang.shu.daily.util;

import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.StringReader;
import java.sql.*;
import java.util.LinkedHashSet;
import java.util.Set;

public class CreateTablesLikeTemplateUtil {
    private static Logger logger= LoggerFactory.getLogger(CreateTablesLikeTemplateUtil.class);

    private CreateTablesLikeTemplateUtil() throws Exception {
        throw new Exception();
    }

    public static void createTablesLikeTemplate(Connection connection,String templateSchemaName,String newSchemaName) throws SQLException {
        Statement statement=connection.createStatement();
        statement.executeUpdate("CREATE SCHEMA "+newSchemaName);
        connection.setCatalog(newSchemaName);
        StringReader reader= new StringReader(getCreateTablesSQL(connection,templateSchemaName).toString());
        ScriptRunner scriptRunner=new ScriptRunner(connection);
        scriptRunner.runScript(reader);

        /*这里注意，如果你的connection是从一个数据源当中获取的，那么在你使用完毕过后应当主动将connection释放掉，如果是从逻辑数据源当中获取的，
        这一步操作就是为connection打标，但是一定要做这个操作，如果不做这个操作，你每次建表的时候都会从数据源当中获取一个新的connection，最终
        导致connection耗光阻塞当前线程*/
        connection.close();
    }

    private static StringBuilder getCreateTablesSQL(Connection connection,String schemaName) throws SQLException {
        DatabaseMetaData databaseMetaData=connection.getMetaData();
        ResultSet tablesNameResultSet=databaseMetaData.getTables(schemaName,schemaName,"%", new String[] { "TABLE" });
        LinkedHashSet<String> tablesNameSet=new LinkedHashSet<>();
        tablesNameResultSet.first();

        do{
            String tableName=tablesNameResultSet.getString("TABLE_NAME");
            if(!tablesNameSet.contains(tableName)){
                ResultSet tableForeignKey=databaseMetaData.getImportedKeys(schemaName,schemaName,tableName);
                checkImportedKeys(tableForeignKey,tablesNameSet,databaseMetaData,schemaName);
                tablesNameSet.add(tableName);
            }
        }while (tablesNameResultSet.next());


        StringBuilder SQLStringBuilder=new StringBuilder();

        tablesNameSet.forEach(tableName -> {
            try {
                SQLStringBuilder.append("DROP TABLE IF EXISTS")
                        .append(" ")
                        .append(addApostropheAround(tableName))
                        .append(";")
                        .append("\n");

                ResultSet createTableResultSet=connection.createStatement().executeQuery("SHOW CREATE TABLE".concat(" ").concat(tableName));
                while (createTableResultSet.next()){
                    SQLStringBuilder.append(createTableResultSet.getString("Create Table"))
                            .append(";")
                            .append("\n")
                            .append("\n");
                }
            } catch (SQLException e) {
                logger.error("Get create tables SQL fail");
            }
        });
        return SQLStringBuilder;
    }

    private static void checkImportedKeys(ResultSet tableForeignKey,Set<String> set,DatabaseMetaData databaseMetaData,String schemaName) throws SQLException {
        while (tableForeignKey.next()){
            if(!set.contains(tableForeignKey.getString("PKTABLE_NAME"))){
                addTablesNameToTablesNameSet(set,databaseMetaData,schemaName,tableForeignKey.getString("PKTABLE_NAME"));
            }
        }
    }

    private static void addTablesNameToTablesNameSet(Set<String> set, DatabaseMetaData databaseMetaData, String schemaName, String tableName) throws SQLException{
        ResultSet tableForeignKey=databaseMetaData.getImportedKeys(schemaName,schemaName,tableName);
        checkImportedKeys(tableForeignKey,set,databaseMetaData,tableName);
        set.add(tableName);
    }

    private static String addApostropheAround(String s){
        return "`"+s+"`";
    }
}