當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2024/4/19 数据库 30 豆豆

生活随笔收集整理的這篇文章主要介紹了调用DM FLDR JNI导出数据库文件小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

有任何問題請到技術社區反饋。

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>

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

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文件

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\'

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'

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 指定的列與服務器列不一致

編寫的ctl文件中 SQL屬性的語句執行后的值與FIELDS屬性配置的值不一樣導致，修改一下就好

4.2 java.lang.UnsatisfiedLinkError

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

意思是說：運行項目需要去java.library.path中加載dmutldll然后沒找到。我的解決方案是在啟動類上配置參數去配置這個DLL所在的文件路徑