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 开发者社区

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

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

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

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