package com.goldwind.ipark.base.util;
import com.alibaba.fastjson.JSONObject;
import lombok.Getter;
import lombok.Setter;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class TableForeignKeyReferenceUtils {
private static String url = "jdbc:mysql://127.0.0.1:3306/xxx?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&&&useSSL=false&tinyInt1isBit=false&allowMultiQueries=true";
private static String user = "xxx";
private static String password = "xxx";
private static String driverclass = "com.mysql.jdbc.Driver";
private static final Pattern pattern_CREATE_TABLE = Pattern.compile( "CREATE TABLE `([0-9a-zA-Z_]{1,})`" );
private static final Pattern pattern_REFERENCES = Pattern.compile( "REFERENCES `([0-9a-zA-Z_]{1,})`" );
@Getter
@Setter
static class TableInfo{
private String tableName;
private List<TableInfo> subTableInfos;
}
/**
* 获取连接
* @return 连接
*/
public static Connection getConnection(){
Connection connection = null;
try {
Class.forName(driverclass);
connection = DriverManager.getConnection(url,user,password);
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}return connection;
}
public static List<String> queryTableNames() throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("SHOW TABLES");
List<String> tableNames = new ArrayList<>();
while( resultSet.next() ){
String tableName = resultSet.getString(1);
System.out.println( tableName );
tableNames.add( tableName );
}
return tableNames;
}catch ( Exception e ){
e.printStackTrace();
}finally {
close( resultSet,statement,connection );
}
return null;
}
public static void main(String[] args) throws SQLException {
List<String> tableNames = queryTableNames();
for( String tableName:tableNames ){
List<String> referencesTableNames = queryReferencesTableNames( tableName );
System.out.println( "表 " + tableName + " 引用了表 " + JSONObject.toJSONString( referencesTableNames ));
}
}
private static List<String> queryReferencesTableNames(String tableName) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("SHOW CREATE TABLE " + tableName);
List<String> referencesTableNames = new ArrayList<>();
if( resultSet.next() ){
String sql_createTable = resultSet.getString(2);
Matcher matcher = pattern_REFERENCES.matcher( sql_createTable );
while( matcher.find() ){
String referencesTableName = matcher.group(1);
referencesTableNames.add( referencesTableName );
}
}
return referencesTableNames;
}catch ( Exception e ){
e.printStackTrace();
}finally {
close( resultSet,statement,connection );
}
return null;
}
/**
* 关闭查询连接
* @param resultSet
* @param statement
* @param connection
*/
public static void close(ResultSet resultSet,Statement statement, Connection connection){
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
表 blob_triggers 引用了表 ["triggers"]
表 calendars 引用了表 []
表 cron_triggers 引用了表 ["triggers"]
表 fired_triggers 引用了表 []
表 job_details 引用了表 []
表 locks 引用了表 []
表 paused_trigger_grps 引用了表 []
表 scheduler_state 引用了表 []
表 simple_triggers 引用了表 ["triggers"]
表 simprop_triggers 引用了表 ["triggers"]
表 triggers 引用了表 ["job_details"]