Kotlin (Java) 获取 mysql 数据库的所有表，表的所有字段，注释，字段类型_kotlin 数据库表

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource
import org.slf4j.LoggerFactory
import org.springframework.stereotype.Service
import java.sql.*
import java.util.*
import javax.sql.DataSource

@Service
class Mysql2OdpsService {
    /**
     * 生成 ODPS DDL 语句
     */
    fun generateddl(table: String, dataSource: MysqlDataSource): String? {
        val conn = getConnection(dataSource) ?: return null
        val fields = getTableFields(table, dataSource)
        return ddl(table, fields)
    }

    /**
     * 获取数据库全部表
     */
    fun getAllTables(dataSource: MysqlDataSource): List<String>? {
        val conn = getConnection(dataSource) ?: return null
        val result = ArrayList<String>()
        var rs: ResultSet? = null
        try {
            conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
            val meta = conn.metaData
            //目录名称, 数据库名, 表名称, 表类型
            rs = meta.getTables(catalog(), dataSource.databaseName, tableNamePattern(), types())
            while (rs!!.next()) {
                result.add(rs.getString("TABLE_NAME"))
            }
        } catch (e: Exception) {
            logger.error("获取数据库全部表:", e)
        } finally {
            close(conn, null, rs)
        }
        return result
    }

    /**
     * 获取数据库表所包含的字段
     */
    fun getTableFields(table: String, dataSource: MysqlDataSource): List<FieldInfo>? {
        val conn = getConnection(dataSource) ?: return null
        val result = ArrayList<FieldInfo>()
        var rs: ResultSet? = null
        try {
            val meta = conn.metaData
            rs = meta.getColumns(catalog(), dataSource.databaseName, table, null)
            while (rs.next()) {
                val fieldInfo = FieldInfo(
                    rs.getString("COLUMN_NAME"),
                    rs.getString("REMARKS"),
                    rs.getString("TYPE_NAME")
                )
                result.add(fieldInfo)
            }
        } catch (e: Exception) {
            logger.error("获取数据库表所包含的字段：", e)
        } finally {
            close(conn, null, rs)
        }
        return result
    }

    data class FieldInfo(var fieldName: String, var comment: String, var type: String)

    fun getConnection(dataSource: DataSource): Connection? {
        var conn: Connection? = null
        try {
            conn = dataSource.connection
        } catch (e: SQLException) {
            logger.error("数据库连接失败", e)
        }
        return conn
    }

    /**
     * 关闭(释放)资源
     *
     * @param conn Connection
     * @param ps   PreparedStatement
     * @param rs   ResultSet
     */
    fun close(conn: Connection?, ps: Statement? = null, rs: ResultSet? = null) {
        var conn = conn
        var ps = ps
        var rs = rs
        //关闭ResultSet
        if (rs != null) {
            try {
                rs.close()
            } catch (e: SQLException) {
                rs = null
            }
        }
        //关闭PreparedStatement
        if (ps != null) {
            try {
                ps.close()
            } catch (e: SQLException) {
                ps = null
            }
        }
        //关闭Connection
        if (conn != null) {
            try {
                conn.close()
            } catch (e: SQLException) {
                conn = null
            }
        }
    }

    /**
     * a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
     */
    fun catalog(): String? {
        return null
    }

    /**
     * a table name pattern; must match the table name as it is stored in the database
     */
    fun tableNamePattern(): String {
        return "%"
    }

    /**
     * a list of table types, which must be from the list of table types returned from [DatabaseMetaData],to include; null returns all types
     */
    fun types(): Array<String> {
        return arrayOf("TABLE", "VIEW")
    }

    fun ddl(table: String, fields: List<FieldInfo>?): String {
        var fieldLines = StringBuilder()
        fields?.forEachIndexed { index, fieldInfo ->
            if (index == 0) {
                val line = "${fieldInfo.fieldName}               STRING COMMENT '${fieldInfo.comment}'"
                fieldLines.append("\n")
                fieldLines.append(line)
                fieldLines.append("\n")
            } else {
                val line = ",${fieldInfo.fieldName}               STRING COMMENT '${fieldInfo.comment}'"
                fieldLines.append(line)
                fieldLines.append("\n")
            }
        }
        return """
CREATE TABLE IF NOT EXISTS $table(
$fieldLines 
)
COMMENT '' PARTITIONED BY
(
  pt STRING COMMENT '时间分区键-yyyymmdd'
)
LIFECYCLE 750;
""".trimIndent()
    }

    val logger = LoggerFactory.getLogger(this.javaClass)
}
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165