A tiny Migration script

最新推荐文章于 2025-09-23 23:27:16 发布

最新推荐文章于 2025-09-23 23:27:16 发布 · 113 阅读

文章标签：

#Oracle #SQL #Groovy #JDBC

未分类专栏收录该内容

64 篇文章

订阅专栏

本文介绍了一个使用Groovy语言实现的数据库迁移脚本，该脚本从原始Oracle数据库中读取数据，并将其插入到目标数据库中对应的表里。迁移的数据包括收费项目和项目信息。

import groovy.sql.Sql

class Migration {
    static getSqlInstance(String orig_or_dest) {
        Sql.newInstance(
            "jdbc:oracle:thin:@asapp:1521:appdb",
            orig_or_dest == 'orig' ? "usr1" : 'usr2',
            orig_or_dest == 'orig' ? "pwd1" : 'pwd2',
            "oracle.jdbc.driver.OracleDriver")
    }

    static migrateChargeItems() {
        def dataset = []
        getSqlInstance('orig').eachRow("select * from A_CHARGETREE"){ row ->
            dataset << [row.id, row.name, row.description, row.parentId, row.leaf]
        }
        dataset.each{item ->
            getSqlInstance('dest').execute("insert into CHARGE_ITEM (id, name, description, parent_id, leaf, version, deleted) values (${item[0]}, ${item[1]}, ${item[2]}, ${item[3]}, ${item[4]?:0}, 1, 0)")
        }
    }

    static migrateProjects() {
        def dataset = []
        getSqlInstance('orig').eachRow("select * from A_PROJECT"){ row ->
            dataset << [row.id, row.name, row.description]
        }
        dataset.each{item ->
            getSqlInstance('dest').execute("insert into PROJECT (id, name, description, version, deleted) values (${item[0]}, ${item[1]}, ${item[2]?:item[1]}, 1, 0)")
        }
    }

    static void main(String[] args) {
        Migration.class.classLoader.rootLoader.addURL( new URL("file:///d:/workspace/timesheet-migration/lib/ojdbc6.jar") )