调用DM FLDR JNI导出数据库文件

客官°小女子只卖身不卖艺 2022-08-28 12:44 249阅读 0赞

有任何问题请到技术社区反馈。

24小时免费服务热线：400 991 6599

达梦技术社区：https://eco.dameng.com

# 1. 介绍 #

java通过 jni 接口调用dmfldr快速装载接口导数据。

开发环境：CentOS7+DM8(Linux)

# 2. 准备工作 #

## 2.1 java项目引入jar ##

引入jar com.dameng.floader.jar、 DmJdbcDriver18.jar

我采用的方法是将jar包放到lib目录下.pom文件配置引入路径

<dependency>
          <groupId>com.dm</groupId>
          <artifactId>DmJdbcDriver18</artifactId>
          <version>18</version>
          <scope>system</scope>
          <systemPath>${project.basedir}/lib/DmJdbcDriver18.jar</systemPath>
        </dependency>
        <dependency>
          <groupId>com.dm</groupId>
          <artifactId>Floader</artifactId>
          <version>1.0</version>
          <scope>system</scope>
          <systemPath>${project.basedir}/lib/com.dameng.floader.jar</systemPath>
        </dependency>

![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_20_color_FFFFFF_t_70_g_se_x_16]

## 2.2 java项目中引入dll ##

程序在编译的过程中需要调用DM的一些lib动态链接库文件(运行java项目时需要这些dll)

我采用的方式是在启动类的VM options参数 配置DLL的路径

**DM调用的dll在达梦安装目录的三个目录里面都需要引入**

// 我的dm安装目录是 D:\damba\dm8 ，需要自行修改称自己的目录
    -Djava.library.path=D:\damba\dm8\drivers;D:\damba\dm8\drivers\logmnr;D:\damba\dm8\bin -Dfile.encoding=UTF-8

![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_20_color_FFFFFF_t_70_g_se_x_16 1]

## 2.3 创建表 ##

-- 创建表
    CREATE TABLE "SYSDBA"."TEST"
    (
    "C1" INT,
    "C2" INT,
    "C3" DATE) STORAGE(ON "MAIN", CLUSTERBTR) ;
    -- 插入数据
    INSERT INTO test VALUES(1, 1 , '2021-09-15');
    INSERT INTO test VALUES(2, 2 , '2021-09-16');
    INSERT INTO test VALUES(3, 3 , '2021-09-17');
    INSERT INTO test VALUES(4, null , '2021-09-18');

# 3. 代码实战 #

## 3.1 导出所有数据 ##

需求：导出表的所有数据

### 3.1.1 linux ###

*  新建 import\_start.ctl 文件（新建目录： /dm8/ctl/insert\_test）

LOAD DATA
    INFILE '/dm8/ctl/insert_test/test.txt' 
    INTO TABLE test
    FIELDS '|'

*  在dm8的安装目录bin目录下运行dmfldr脚本

./dmfldr userid=SYSDBA/SYSDBA@192.168.60.137:5236 control=\'/dm8/ctl/insert_test/import_start.ctl\' mode=\'out\'

*  执行成功后会生成test.txt文件

![请添加图片描述][8c6605d2255847ff8b810ba78a25f834.png]

### 3.1.2 java ###

package com.dm.demo.jni;
    import com.dameng.floader.Instance;
    import com.dameng.floader.Properties;
    
    import java.io.UnsupportedEncodingException;
    
    /** * @author xgt(小光头) * @version 1.0 * @date 2021-9-18 11:02 */
    public class Import_start { 
        public static void main(String[] args)
        { 
    
            //System.out.println(System.getProperty("java.library.path"));
            Instance instance = new Instance();
            try
            { 
                //初始化
                if (instance.allocInstance())
                { 
                    instance.setAttribute(Properties.FLDR_ATTR_SERVER, "192.168.60.137");
                    instance.setAttribute(Properties.FLDR_ATTR_PORT, "5236");
                    instance.setAttribute(Properties.FLDR_ATTR_UID, "SYSDBA");
                    instance.setAttribute(Properties.FLDR_ATTR_PWD, "SYSDBA");
                    instance.initializeInstance(null, null, null, "\"SYSDBA\".\"TEST\"");
                    String attribute = instance.getAttribute(Properties.FLDR_ATTR_DATA_CHAR_SET);
    
                    String ctl = "OPTIONS\r\n" +
                            "(\r\n" +
                            "mode='out'\r\n" +
                            ")\r\n" +
                            "Load\r\n" +
                            "infile 'e:\\fldr_out.txt'\r\n" +
                            "Append\r\n" +
                            "into table \"SYSDBA\".\"TEST\"\r\n" +
                            "FIELDS '|'";
                    //导出的ctrl内容
                    instance.setControl(ctl);
                }
                else { 
                    try { 
                        byte[] bytes = instance.getErrorMsg().getBytes("utf-8");
                        System.out.println(new String(bytes, "utf-8"));
                    } catch (UnsupportedEncodingException e) { 
                        e.printStackTrace();
                    }
                }
            }catch (Exception e){ 
                System.out.println(e);
            }
            finally
            { 
                //释放
                instance.unitialize();
                instance.free();
            }
        }
    }

## 3.2 导出指定列的数据 ##

需求：导出表的某一些指定列并将显示

### 3.2.1 linux ###

*  新建 import\_targe\_data.ctl 文件（新建目录： /dm8/ctl/insert\_test）

OPTIONS
    (
    SQL ='select C1 from SYSDBA.TEST'
    )
    LOAD DATA
    INFILE '/dm8/ctl/insert_test/test.txt'
    INTO TABLE test
    FIELDS '|'
    (
    C1 TERMINATED BY ' '
    )

*  在dm8的安装目录bin目录下运行dmfldr脚本

./dmfldr userid=SYSDBA/SYSDBA@192.168.60.137:5236 control=\'/dm8/ctl/insert_test/import_targe_data.ctl\' mode=\'out\'

![请添加图片描述][f7f4a0a3b098446087c6063e572a46ce.png]

### 3.2.2 java ###

package com.dm.demo.jni;
    import com.dameng.floader.Instance;
    import com.dameng.floader.Properties;
    
    import java.io.UnsupportedEncodingException;
    
    /** * @author 高垣 * @version 1.0 * @date 2021-9-18 11:02 */
    public class Import_targe_data { 
        public static void main(String[] args)
        { 
    
            //System.out.println(System.getProperty("java.library.path"));
            Instance instance = new Instance();
            try
            { 
                //初始化
                if (instance.allocInstance())
                { 
                    instance.setAttribute(Properties.FLDR_ATTR_SERVER, "192.168.60.137");
                    instance.setAttribute(Properties.FLDR_ATTR_PORT, "5236");
                    instance.setAttribute(Properties.FLDR_ATTR_UID, "SYSDBA");
                    instance.setAttribute(Properties.FLDR_ATTR_PWD, "SYSDBA");
    
                    instance.initializeInstance(null, null, null, "\"SYSDBA\".\"TEST\"");
                    String attribute = instance.getAttribute(Properties.FLDR_ATTR_DATA_CHAR_SET);
    
                    String ctl = "OPTIONS\r\n" +
                            "(\r\n" +
                            "mode='out'\r\n" +
                            "SQL='select C1 from SYSDBA.TEST'\r\n" +
                            ")\r\n" +
                            "Load\r\n" +
                            "infile 'e:\\fldr_out.txt'\r\n" +
                            "Append\r\n" +
                            "into table \"SYSDBA\".\"TEST\"\r\n" +
                            "FIELDS '|'\n" +
                            " (\n" +
                            " C1 TERMINATED BY ' '\n" +
                            " )";
    
                    //导出的ctrl内容
                    instance.setControl(ctl);
                }
                else { 
                    try { 
                        byte[] bytes = instance.getErrorMsg().getBytes("utf-8");
                        System.out.println(new String(bytes, "utf-8"));
                    } catch (UnsupportedEncodingException e) { 
                        e.printStackTrace();
                    }
    
    
                }
            }catch (Exception e){ 
                System.out.println(e);
            }
            finally
            { 
                //释放
                instance.unitialize();
                instance.free();
            }
        }
    }

## 3.3 导出指定列并修改NULL值 ##

需求：导出指定表的列并将显示NULL的字段修改为其他值

**温馨提示：因为在CTL文件中编写语句，存在双引号冲突的问题，使用函数时采用双单引号形式**

### 3.3.1 linux ###

*  新建 import\_targe\_func\_data.ctl 文件（新建目录： /dm8/ctl/insert\_test）

OPTIONS
    (
    SQL ='select c1,IFNULL(c2,''1'') c2 from test' ) LOAD DATA INFILE '/dm8/ctl/insert_test/test.txt' INTO TABLE test FIELDS '|' ( C1 TERMINATED BY ' ', C2 TERMINATED BY ' '
    )

*  在dm8的安装目录bin目录下运行dmfldr脚本

./dmfldr userid=SYSDBA/SYSDBA@192.168.60.137:5236 control=\'/dm8/ctl/insert_test/import_targe_func_data.ctl\' mode=\'out\' 
    
    
    ./dmfldr userid=SYSDBA/SYSDBA@192.168.60.137:5236 control=\'/dm8/ctl/insert_func/test1/test.ctl\' mode=\'out\' 
    
    
    select C1,replace(V1,chr(47),''\'') V1 from test1'

![请添加图片描述][245fe25e62c74d509186fb9e9b3ffb47.png]

### 3.3.2 java ###

package com.dm.demo.jni;
    import com.dameng.floader.Instance;
    import com.dameng.floader.Properties;
    
    import java.io.UnsupportedEncodingException;
    
    /** * @author 高垣 * @version 1.0 * @date 2021-9-18 11:02 */
    public class TestFloderImport2 { 
        public static void main(String[] args)
        { 
            Instance instance = new Instance();
            try
            { 
                //初始化
                if (instance.allocInstance())
                { 
                    instance.setAttribute(Properties.FLDR_ATTR_SERVER, "192.168.60.137");
                    instance.setAttribute(Properties.FLDR_ATTR_PORT, "5236");
                    instance.setAttribute(Properties.FLDR_ATTR_UID, "SYSDBA");
                    instance.setAttribute(Properties.FLDR_ATTR_PWD, "SYSDBA");
    
                    instance.initializeInstance(null, null, null, "\"SYSDBA\".\"TEST\"");
                    String attribute = instance.getAttribute(Properties.FLDR_ATTR_DATA_CHAR_SET);
    
                    String ctl = "OPTIONS\r\n" +
                            "(\r\n" +
                            "mode='out'\r\n" +
                            "SQL='select c1,IFNULL(c2,''1'') c2 from SYSDBA.TEST'\r\n" +
                            ")\r\n" +
                            "Load\r\n" +
                            "infile 'e:\\fldr_out.txt'\r\n" +
                            "Append\r\n" +
                            "into table \"SYSDBA\".\"TEST\"\r\n" +
                            "FIELDS '|'\n" +
                            " (\n" +
                            " C1 TERMINATED BY ' ',\n" +
                            " C2 TERMINATED BY ' '\n" +
                            " )";
                    //导出的ctrl内容
                    instance.setControl(ctl);
                }
                else { 
                    try { 
                        byte[] bytes = instance.getErrorMsg().getBytes("utf-8");
                        System.out.println(new String(bytes, "utf-8"));
                    } catch (UnsupportedEncodingException e) { 
                        e.printStackTrace();
                    }
                }
            }catch (Exception e){ 
                System.out.println(e);
            }
            finally
            { 
                //释放
                instance.unitialize();
                instance.free();
            }
        }
    }

# =================================== #

下面的测试是另外一张表测试附表结构

CREATE TABLE "SYSDBA"."TEST1"
    (
    "C1" INT,
    "V1" VARCHAR(50)) STORAGE(ON "MAIN", CLUSTERBTR) ;
    
    insert into "SYSDBA"."TEST1"("C1", "V1") VALUES(1, '11/a');
    insert into "SYSDBA"."TEST1"("C1", "V1") VALUES(2, '22/2/2');
    insert into "SYSDBA"."TEST1"("C1", "V1") VALUES(3, '33/3//');

## 3.4 导出数据并使用嵌套函数 ##

OPTIONS
    (
    SQL ='select C1,replace(V1,chr(47),''\'') V1 from test1'
    )
    LOAD DATA
    INFILE '/dm8/ctl/insert_func/test1/test.txt'
    INTO TABLE test1
    FIELDS '|'
    (
    C1 TERMINATED BY ' ',
    V1 TERMINATED BY ' '
    )

[dmdba@localhost bin]$ ./dmfldr userid=SYSDBA/SYSDBA@192.168.60.137:5236 control=\'/dm8/ctl/insert_func/test1/test1.ctl\' mode=\'out\'

## 3.5 导出数据并函数嵌套使用char函数 ##

OPTIONS
    (
    SQL ='select C1,replace(replace(V1,chr(47),chr(41)),chr(41),chr(39)) V1 from test1'
    )
    LOAD DATA
    INFILE '/dm8/ctl/insert_func/test1/test.txt'
    INTO TABLE test1
    FIELDS '|'
    (
    C1 TERMINATED BY ' ',
    V1 TERMINATED BY ' '
    )

[dmdba@localhost bin]$ ./dmfldr userid=SYSDBA/SYSDBA@192.168.60.137:5236 control=\'/dm8/ctl/insert_func/test1/test2.ctl\' mode=\'out\'

## 3.6 导出数据并函数嵌套+拼接 ##

OPTIONS
    (
    SQL ='select C1,replace(replace(V1,chr(47),chr(41)),chr(41),chr(39)|| chr(42)) V1 from test1'
    )
    LOAD DATA
    INFILE '/dm8/ctl/insert_func/test1/test.txt'
    INTO TABLE test1
    FIELDS '|'
    (
    C1 TERMINATED BY ' ',
    V1 TERMINATED BY ' '
    )

[dmdba@localhost bin]$ ./dmfldr userid=SYSDBA/SYSDBA@192.168.60.137:5236 control=\'/dm8/ctl/insert_func/test1/test3.ctl\' mode=\'out\'

## 3.7 导出数据并函数嵌套+替换+拼接 ##

OPTIONS
    (
    SQL ='select C1,replace(replace(V1,''/'',chr(41)),chr(41),chr(39)|| chr(42)) V1 from test1' ) LOAD DATA INFILE '/dm8/ctl/insert_func/test1/test.txt' INTO TABLE test1 FIELDS '|' ( C1 TERMINATED BY ' ', V1 TERMINATED BY ' '
    )

[dmdba@localhost bin]$ ./dmfldr userid=SYSDBA/SYSDBA@192.168.60.137:5236 control=\'/dm8/ctl/insert_func/test1/test4.ctl\' mode=\'out\'

# 4. 常见问题 #

## 4.1 指定的列与服务器列不一致 ##

![请添加图片描述][30f4b85a387c4f1e996ecf255e7bb084.png]  
编写的ctl文件中 SQL属性的语句执行后的值与FIELDS属性配置的值不一样导致，修改一下就好  
![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_15_color_FFFFFF_t_70_g_se_x_16]

## 4.2 java.lang.UnsatisfiedLinkError ##

报错：Exception in thread “main” java.lang.UnsatisfiedLinkError: no dmutl in java.library.path

![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_20_color_FFFFFF_t_70_g_se_x_16 2]

意思是说：运行项目需要去`java.library.path`中加载`dmutl`dll然后没找到。我的解决方案是在启动类上配置参数去配置这个DLL所在的文件路径

我采用的方式是在启动类的VM options参数 配置DLL的路径

**DM调用的dll在达梦安装目录的三个目录里面都需要引入**

// 我的dm安装目录是 D:\damba\dm8 ，需要自行修改称自己的目录
    -Djava.library.path=D:\damba\dm8\drivers;D:\damba\dm8\drivers\logmnr;D:\damba\dm8\bin -Dfile.encoding=UTF-8

![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_20_color_FFFFFF_t_70_g_se_x_16 3]

## 4.3 项目的资料 ##

链接：https://pan.baidu.com/s/1Gtsbsq\_EM25pzWYuBWpVzg 提取码：4hrp

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/86a12a54a4454cefbd12157465819c43.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/5b2d7d568047460f8a93402f5244340c.png
[8c6605d2255847ff8b810ba78a25f834.png]: /images/20220828/478ae0e315634880b9fbe356dcfc62f9.png
[f7f4a0a3b098446087c6063e572a46ce.png]: /images/20220828/4ff7541187b44b9e89b0d5e4b7e17ff9.png
[245fe25e62c74d509186fb9e9b3ffb47.png]: /images/20220828/5335d122a9604b05a8d9af103dba6420.png
[30f4b85a387c4f1e996ecf255e7bb084.png]: /images/20220828/31179506efb14829aefddd36ca251eba.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_15_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/b1909ae44f614be596532f547c01d1a2.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/6e55bf6e802f42618a9421b512781824.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5YWJ5aS055qEamF2YeaXpeiusA_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/5fae66871bd0409db8683eaeab9e8d70.png