kotlin mysql数据库_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? {

val conn = getConnection(dataSource) ?: return null

val result = ArrayList()

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? {

val conn = getConnection(dataSource) ?: return null

val result = ArrayList()

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 {

return arrayOf("TABLE", "VIEW")

}

fun ddl(table: String, fields: List?): 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)

}