• 功能说明

    data_tool

    Groovy脚本:基于表导出Liquibase Groovy脚本,支持批量导出
    数据库对比:对比不同环境中数据库差异,生成xml脚本文件
    数据导出:使用XML模板文件导出数据到Excel文件,支持批量导出
    数据导入:基于Liquibase Groovy脚本更新数据库,导入Excel文件,支持批量导入

    Groovy脚本导出

    groovy

    数据库对比

    compare
    compare_result

    数据导出

    export

    datasource:
        # 开发环境
        dev:
          driver-class-name: com.mysql.jdbc.Driver
          url: ${SPRING_DATASOURCE_URL:jdbc:mysql://dev.hzero.org:3306?useUnicode=true&useSSL=false}
          username: ${SPRING_DATASOURCE_USERNAME:hzero}
          password: ${SPRING_DATASOURCE_PASSWORD:hzero}
        # 测试环境
        tst:
          driver-class-name: oracle.jdbc.driver.OracleDriver
          url: ${SPRING_DATASOURCE_URL:jdbc:oracle:thin:@dev.hzero.org:1521:sid}
          username: ${SPRING_DATASOURCE_USERNAME:hzero}
          password: ${SPRING_DATASOURCE_PASSWORD:hzero}
        # 验收环境
        uat:
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          url: ${SPRING_DATASOURCE_URL:jdbc:sqlserver://dev.hzero.org:1433;}
          username: ${SPRING_DATASOURCE_USERNAME:hzero}
          password: ${SPRING_DATASOURCE_PASSWORD:hzero}
        # 生产环境
        prd:
          driver-class-name: com.mysql.jdbc.Driver
          url: ${SPRING_DATASOURCE_URL:jdbc:mysql://prd.hzero.org:3306?useUnicode=true&useSSL=false}
          username: ${SPRING_DATASOURCE_USERNAME:hzero}
          password: ${SPRING_DATASOURCE_PASSWORD:hzero}
    

    1.基础数据导出

    2.客制化数据导出

    <?xml version="1.0" encoding="UTF-8"?>
    <!--name:服务名,order:排序,description:描述-->
    <service name="" order="" description="">
        <!--name:模块名称,description:描述,fileName:文件名,schema:数据库-->
        <excel name="" description="" fileName="" schema="">
            <!--name:名称,description:页面显示名称和Excel页名称-->
            <sheet name="" description="">
                <!--name:表名,description:描述-->
                <table name="" description="">
                    <!--sql语句,包含需要查询的字段和where条件,主表别名为 t -->
                    <sql>SELECT ...
                    FROM ...
                    WHERE 1=1
                    </sql>
                    <!--主键,默认自增,保持原ID只需加个*号,例如 <id>*tenant_id</id> -->
                    <id></id>
                    <!--引用字段,主键是否被其它表引用-->
                    <cited></cited>
                    <!--唯一性索引,多个用逗号分隔-->
                    <unique></unique>
                    <!--多语言(可配多个),field:多语言列,pkName:关联主键-->
                    <lang>
                        <field></field>
                        <pkName></pkName>
                    </lang>
                    <!--类型(可配多个),field:类型列,type:类型-->
                    <type>
                        <field></field>
                        <type></type>
                    </type>
                    <!--关联(可配多个),field:关联列,sheetName:关联的sheet,tableName:关联的表,columnName:关联的列-->
                    <reference>
                        <field></field>
                        <sheetName></sheetName>
                        <tableName></tableName>
                        <columnName></columnName>
                    </reference>
                </table>
            </sheet>
        </excel>
    </service>
    

    举个栗子:

    xml

    lov

    <table name="hpfm_lov" description="LOV表">
        <sql>SELECT
            lov_id,
            lov_code,
            lov_type_code,
            route_name,
            lov_name,
            description,
            tenant_id,
            parent_lov_code,
            parent_tenant_id,
            custom_sql,
            custom_url,
            value_field,
            display_field,
            must_page_flag,
            enabled_flag,
            translation_sql
            FROM
            hpfm_lov
            WHERE
            enabled_flag = 1
            AND tenant_id = 0
            AND (
            lov_code LIKE 'HPFM.%'
            OR lov_code LIKE 'HIAM.%'
            OR lov_code LIKE 'HOTH.%'
            OR lov_code LIKE 'HADM.%'
            )
        </sql>
        <id>lov_id</id>
        <cited>lov_id</cited>
        <unique>lov_code,tenant_id</unique>
        <lang>
            <field>lov_name</field>
            <pkName>lov_id</pkName>
        </lang>
        <lang>
            <field>description</field>
            <pkName>lov_id</pkName>
        </lang>
    </table>
    
    <table name="hpfm_lov_value" description="LOV独立值集表">
        <sql>SELECT
            lov_value_id,
            lov_id,
            lov_code,
            value,
            meaning,
            description,
            tenant_id,
            tag,
            order_seq,
            parent_value,
            start_date_active,
            end_date_active,
            enabled_flag
            FROM
            hpfm_lov_value
            WHERE
            enabled_flag = 1
            AND tenant_id = 0
            AND (
            lov_code LIKE 'HPFM.%'
            OR lov_code LIKE 'HIAM.%'
            OR lov_code LIKE 'HOTH.%'
            OR lov_code LIKE 'HADM.%')
        </sql>
        <id>lov_value_id</id>
        <unique>lov_id,value,tenant_id,parent_value</unique>
        <lang>
            <field>meaning</field>
            <pkName>lov_value_id</pkName>
        </lang>
        <lang>
            <field>description</field>
            <pkName>lov_value_id</pkName>
        </lang>
        <type>
            <field>start_date_active</field>
            <type>DATE</type>
        </type>
        <type>
            <field>end_date_active</field>
            <type>DATE</type>
        </type>
        <reference>
            <field>lov_id</field>
            <tableName>hpfm_lov</tableName>
            <columnName>lov_id</columnName>
        </reference>
    </table>
    
    <table name="hpfm_lov_view_header" description="值集查询视图头表">
        <sql>SELECT
            view_header_id,
            view_code,
            view_name,
            lov_id,
            tenant_id,
            value_field,
            display_field,
            title,
            width,
            height,
            page_size,
            delay_load_flag,
            children_field_name,
            enabled_flag
            FROM
            hpfm_lov_view_header
            WHERE
            enabled_flag = 1
            AND tenant_id = 0
            AND (
            view_code LIKE 'HPFM.%'
            OR view_code LIKE 'HIAM.%'
            OR view_code LIKE 'HOTH.%'
            OR view_code LIKE 'HADM.%'
            )
        </sql>
        <id>view_header_id</id>
        <cited>view_header_id</cited>
        <unique>view_code,tenant_id</unique>
        <lang>
            <field>view_name</field>
            <pkName>view_header_id</pkName>
        </lang>
        <lang>
            <field>title</field>
            <pkName>view_header_id</pkName>
        </lang>
        <reference>
            <field>lov_id</field>
            <tableName>hpfm_lov</tableName>
            <columnName>lov_id</columnName>
        </reference>
    </table>
    
    <table name="hpfm_lov_view_line" description="值集查询视图行表">
        <sql>SELECT
            view_line_id,
            view_header_id,
            tenant_id,
            lov_id,
            display,
            order_seq,
            field_name,
            query_field_flag,
            table_field_flag,
            table_field_width,
            enabled_flag
            FROM
            hpfm_lov_view_line
            WHERE
            enabled_flag = 1
            AND tenant_id =0
        </sql>
        <id>view_line_id</id>
        <unique>view_header_id,field_name</unique>
        <lang>
            <field>display</field>
            <pkName>view_line_id</pkName>
        </lang>
        <reference>
            <field>view_header_id</field>
            <tableName>hpfm_lov_view_header</tableName>
            <columnName>view_header_id</columnName>
        </reference>
        <reference>
            <field>lov_id</field>
            <tableName>hpfm_lov</tableName>
            <columnName>lov_id</columnName>
        </reference>
    </table>
    
    class XmlUtils{
        /**
         * 需要过滤掉的文件
         */
        private static List<String> SKIP_FILE = new ArrayList<>();
        
        static {
            // 忽略模板文件
            SKIP_FILE.add("template-vacancy.xml");
            SKIP_FILE.add("template-demo.xml");
        }
    }
    

    数据导入

    install

    更新数据库

    导入数据

    data_tree