欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-21 21:48:16
...
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)
}