1import com.mysql.jdbc.jdbc2.optional.MysqlDataSource
2import org.slf4j.LoggerFactory
3import org.springframework.stereotype.Service
4import java.sql.*
5import java.util.*
6import javax.sql.DataSource
7
8@Service
9class Mysql2OdpsService {
10    /**
11     * 生成 ODPS DDL 语句
12     */
13    fun generateddl(table: String, dataSource: MysqlDataSource): String? {
14        val conn = getConnection(dataSource) ?: return null
15        val fields = getTableFields(table, dataSource)
16        return ddl(table, fields)
17    }
18
19    /**
20     * 获取数据库全部表
21     */
22    fun getAllTables(dataSource: MysqlDataSource): List<String>? {
23        val conn = getConnection(dataSource) ?: return null
24        val result = ArrayList<String>()
25        var rs: ResultSet? = null
26        try {
27            conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
28            val meta = conn.metaData
29            //目录名称, 数据库名, 表名称, 表类型
30            rs = meta.getTables(catalog(), dataSource.databaseName, tableNamePattern(), types())
31            while (rs?.next()) {
32                result.add(rs.getString("TABLE_NAME"))
33            }
34        } catch (e: Exception) {
35            logger.error("获取数据库全部表:", e)
36        } finally {
37            close(conn, null, rs)
38        }
39        return result
40    }
41
42    /**
43     * 获取数据库表所包含的字段
44     */
45    fun getTableFields(table: String, dataSource: MysqlDataSource): List<FieldInfo>? {
46        val conn = getConnection(dataSource) ?: return null
47        val result = ArrayList<FieldInfo>()
48        var rs: ResultSet? = null
49        try {
50            val meta = conn.metaData
51            rs = meta.getColumns(catalog(), dataSource.databaseName, table, null)
52            while (rs.next()) {
53                val fieldInfo = FieldInfo(
54                    rs.getString("COLUMN_NAME"),
55                    rs.getString("REMARKS"),
56                    rs.getString("TYPE_NAME")
57                )
58                result.add(fieldInfo)
59            }
60        } catch (e: Exception) {
61            logger.error("获取数据库表所包含的字段：", e)
62        } finally {
63            close(conn, null, rs)
64        }
65        return result
66    }
67
68    data class FieldInfo(var fieldName: String, var comment: String, var type: String)
69
70    fun getConnection(dataSource: DataSource): Connection? {
71        var conn: Connection? = null
72        try {
73            conn = dataSource.connection
74        } catch (e: SQLException) {
75            logger.error("数据库连接失败", e)
76        }
77        return conn
78    }
79
80    /**
81     * 关闭(释放)资源
82     *
83     * @param conn Connection
84     * @param ps   PreparedStatement
85     * @param rs   ResultSet
86     */
87    fun close(conn: Connection?, ps: Statement? = null, rs: ResultSet? = null) {
88        var conn = conn
89        var ps = ps
90        var rs = rs
91        //关闭ResultSet
92        if (rs != null) {
93            try {
94                rs.close()
95            } catch (e: SQLException) {
96                rs = null
97            }
98        }
99        //关闭PreparedStatement
100        if (ps != null) {
101            try {
102                ps.close()
103            } catch (e: SQLException) {
104                ps = null
105            }
106        }
107        //关闭Connection
108        if (conn != null) {
109            try {
110                conn.close()
111            } catch (e: SQLException) {
112                conn = null
113            }
114        }
115    }
116
117    /**
118     * 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
119     */
120    fun catalog(): String? {
121        return null
122    }
123
124    /**
125     * a table name pattern; must match the table name as it is stored in the database
126     */
127    fun tableNamePattern(): String {
128        return "%"
129    }
130
131    /**
132     * a list of table types, which must be from the list of table types returned from [DatabaseMetaData],to include; null returns all types
133     */
134    fun types(): Array<String> {
135        return arrayOf("TABLE", "VIEW")
136    }
137
138    fun ddl(table: String, fields: List<FieldInfo>?): String {
139        var fieldLines = StringBuilder()
140        fields?.forEachIndexed { index, fieldInfo ->
141            if (index == 0) {
142                val line = "${fieldInfo.fieldName}               STRING COMMENT '${fieldInfo.comment}'"
143                fieldLines.append("\n")
144                fieldLines.append(line)
145                fieldLines.append("\n")
146            } else {
147                val line = ",${fieldInfo.fieldName}               STRING COMMENT '${fieldInfo.comment}'"
148                fieldLines.append(line)
149                fieldLines.append("\n")
150            }
151        }
152        return """
153CREATE TABLE IF NOT EXISTS $table(
154$fieldLines 
155)
156COMMENT '' PARTITIONED BY
157(
158  pt STRING COMMENT '时间分区键-yyyymmdd'
159)
160LIFECYCLE 750;
161""".trimIndent()
162    }
163
164    val logger = LoggerFactory.getLogger(this.javaClass)
165}

Kotlin 开发者社区

国内第一Kotlin 开发者社区公众号，主要分享、交流 Kotlin 编程语言、Spring Boot、Android、React.js/Node.js、函数式编程、编程思想等相关主题。

越是喧嚣的世界，越需要宁静的思考。

合抱之木，生于毫末；
九层之台，起于垒土；
千里之行，始于足下。
积土成山，风雨兴焉；
积水成渊，蛟龙生焉；
积善成德，而神明自得，圣心备焉。
故不积跬步，无以至千里；
不积小流，无以成江海。
骐骥一跃，不能十步；
驽马十驾，功在不舍。
锲而舍之，朽木不折；
锲而不舍，金石可镂。
蚓无爪牙之利，筋骨之强，上食埃土，下饮黄泉，用心一也。
蟹六跪而二螯，非蛇鳝之穴无可寄托者，用心躁也。

本文分享 CSDN - 东海陈光剑。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。