-
Notifications
You must be signed in to change notification settings - Fork 82
/
InformationSchemaRepository.java
97 lines (84 loc) · 3.98 KB
/
InformationSchemaRepository.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
package org.blackdread.sqltojava.repository;
import org.blackdread.sqltojava.pojo.ColumnInformation;
import org.blackdread.sqltojava.pojo.TableInformation;
import org.blackdread.sqltojava.pojo.TableRelationInformation;
import org.blackdread.sqltojava.jooq.InformationSchema;
import org.blackdread.sqltojava.pojo.ColumnInformation;
import org.blackdread.sqltojava.pojo.TableInformation;
import org.blackdread.sqltojava.pojo.TableRelationInformation;
import org.jooq.DSLContext;
import org.jooq.Record4;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.List;
import static org.blackdread.sqltojava.jooq.InformationSchema.INFORMATION_SCHEMA;
import static org.blackdread.sqltojava.jooq.tables.KeyColumnUsage.KEY_COLUMN_USAGE;
/**
* <p>Created on 2018/2/8.</p>
*
* @author Yoann CAPLAIN
*/
@Repository
public class InformationSchemaRepository {
private static final Logger log = LoggerFactory.getLogger(InformationSchemaRepository.class);
private final DSLContext create;
@Autowired
public InformationSchemaRepository(final DSLContext create) {
this.create = create;
}
public List<TableRelationInformation> getAllTableRelationInformation(final String dbName) {
/*
SELECT CONCAT(table_name) AS table_name, CONCAT(column_name) AS column_name, CONCAT(referenced_table_name)
AS referenced_table_name, CONCAT(referenced_column_name) AS referenced_column_name
FROM INFORMATION_SCHEMA.key_column_usage WHERE referenced_table_schema = '" . DB_NAME . "'
AND referenced_table_name IS NOT NULL ORDER BY table_name, column_name
*/
return create.select(
KEY_COLUMN_USAGE.TABLE_NAME,
KEY_COLUMN_USAGE.COLUMN_NAME,
KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,
KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME)
.from(InformationSchema.INFORMATION_SCHEMA.KEY_COLUMN_USAGE)
.where(KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA.eq(dbName)
.and(KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME.isNotNull()))
.orderBy(KEY_COLUMN_USAGE.TABLE_NAME, KEY_COLUMN_USAGE.COLUMN_NAME)
.fetch()
.map(this::map);
}
public List<ColumnInformation> getFullColumnInformationOfTable(final String dbName, final String tableName) {
return create.resultQuery("SHOW FULL COLUMNS FROM " + dbName + "." + tableName)
// .bind(1, tableName)
.fetch()
.map(r -> new ColumnInformation(
(String) r.get("Field"),
(String) r.get("Type"),
(String) r.get("Collation"),
(String) r.get("Null"),
(String) r.get("Key"),
(String) r.get("Default"),
(String) r.get("Extra"),
(String) r.get("Comment")));
}
public List<TableInformation> getAllTableInformation(final String dbName) {
return create.select(
InformationSchema.INFORMATION_SCHEMA.TABLES.TABLE_NAME,
InformationSchema.INFORMATION_SCHEMA.TABLES.TABLE_COMMENT)
.from(InformationSchema.INFORMATION_SCHEMA.TABLES)
.where(InformationSchema.INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA.eq(dbName))
.fetch()
.map(r -> new TableInformation(r.value1(), r.value2()));
}
public List<String> getAllTableName(final String dbName) {
return create.select(
InformationSchema.INFORMATION_SCHEMA.TABLES.TABLE_NAME)
.from(InformationSchema.INFORMATION_SCHEMA.TABLES)
.where(InformationSchema.INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA.eq(dbName))
.fetch()
.getValues(InformationSchema.INFORMATION_SCHEMA.TABLES.TABLE_NAME);
}
private TableRelationInformation map(final Record4<String, String, String, String> r) {
return new TableRelationInformation(r.value1(), r.value2(), r.value3(), r.value4());
}
}