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

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)
}