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

Kotlin 开发者社区

1233356-85557eb302fca71c.jpg

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

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

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

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

评论

暂无评论