Java-将Oracle中某个表的数据导出成数据文件
在做数据开发或者ETL工作中,经常会遇到需要在客户端将Oracle数据库中的某些表的数据导出,提供给其他人员使用的情况。
下面介绍我之前实施的一种方法:(该方法不是最好的办法,但是可以勉强使用,仅供参考)
实现逻辑:
1、编写Java程序(DBToFile.java),读取配置文件中的数据库连接信息,将表中的数据查询出来,并生成文件;
2、通过shell脚本(run-KF00001.sh、run-KF00002.sh)设置Java程序中需要的环境变量信息,并执行java程序;
3、通过启动脚本(run-day.sh),依次执行第2步的多个shell脚本。
一、编写java程序(DBToFile.java)
import java.io.File; import java.io.BufferedWriter; import java.io.FileInputStream; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Map; import java.util.Properties; import java.util.Iterator; import java.util.Map.Entry; public class DBToFile { public final static int PREPARE_OK = 0; public final static int RET_CONF_ERROR = 10; public final static int RET_ENV_ERROR = 15; public final static int RET_RUNTIME_ERROR = 5; public final static String DBTF_SQL = "DBTF_SQL"; public final static String DBTF_OUTPUT_DIR = "DBTF_OUTPUT_DIR"; public final static String DBTF_OUTPUT_FILE = "DBTF_OUTPUT_FILE"; public final static String DBTF_OUTPUT = "DBTF_OUTPUT"; public final static String JDBC_CLASS_NAME = "oracle.jdbc.driver.OracleDriver"; public final static String CONFIG_FILE = "DBToFile.conf"; public final static char SEPARATOR = 0x01; // private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); // public static void main(String[] args) { File outputFile = null; BufferedWriter bw = null; Connection conn = null; Statement stmt = null; ResultSet rs = null; // StringBuffer sb = null; int rowCount = 0; long tt = 0L; Properties props = new Properties(); int ret = loadConf(props); if (PREPARE_OK != ret) { System.exit(ret); } ret = loadEnv(props); if (PREPARE_OK != ret) { System.exit(ret); } showProperties(props); //data transfer try { tt = System.currentTimeMillis(); outputFile = new File(props.getProperty(DBTF_OUTPUT) + ".avl"); bw = new BufferedWriter(new FileWriter(outputFile)); conn = getConn(props); conn.createStatement(); stmt = conn.createStatement(); rs = stmt.executeQuery(props.getProperty(DBTF_SQL)); int count = rs.getMetaData().getColumnCount(); String v = null; while (rs.next()) { sb = new StringBuffer(); for (int i = 1; i <= count; i++) { v = rs.getString(i); if (null == v) { v = ""; } else { v = v.replaceAll("\r|\n", " ").trim(); } sb.append(v); if (i < count) { sb.append(SEPARATOR); } } bw.write(sb.toString()); bw.newLine(); rowCount++; } bw.flush(); tt = System.currentTimeMillis() - tt; writeCheckFile(props.getProperty(DBTF_OUTPUT), props.getProperty(DBTF_OUTPUT_FILE), rowCount, outputFile.length()); log("Output " + rowCount + " rows, " + outputFile.length() + " bytes, within " + tt + " ms."); } catch (IOException ioe) { ioe.printStackTrace(); log("Output File Error"); System.exit(RET_RUNTIME_ERROR); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); log("Java CLASSPATH Error"); System.exit(RET_RUNTIME_ERROR); } catch (SQLException sqle) { sqle.printStackTrace(); log("DB Error"); System.exit(RET_RUNTIME_ERROR); } finally { CloseWriter(bw); closeConn(conn); } } private static void writeCheckFile(String n, String nn, int c, long s) { try { FileWriter writer = new FileWriter(n + ".chk", false); writer.write(nn + "," + c + "," + s); writer.close(); } catch (IOException ioe) { ioe.printStackTrace(); log("Output Check File Error"); } } public static int loadEnv(Properties p) { Map<String, String> env = System.getenv(); String v = env.get(DBTF_SQL); if (null == v || v.length() < 10) { log("Env DBTF_SQL Error"); return RET_ENV_ERROR; } else { p.put(DBTF_SQL, v.trim()); } String d = env.get(DBTF_OUTPUT_DIR); if (null == d || d.length() < 0) { log("Env DBTF_OUTPUT_DIR Error"); return RET_ENV_ERROR; } File f = new File(d); if (!f.exists() || !f.isDirectory()) { log("Env DBTF_OUTPUT_DIR Error"); return RET_ENV_ERROR; } else { String n = env.get(DBTF_OUTPUT_FILE); if (null == n || n.length() < 1) { log("Env DBTF_OUTPUT_FILE Error"); return RET_ENV_ERROR; } else { p.put(DBTF_OUTPUT_DIR, d.trim()); p.put(DBTF_OUTPUT_FILE, n.trim()); p.put(DBTF_OUTPUT, d.trim() + n.trim()); } } return PREPARE_OK; } public static int loadConf(Properties p) { FileInputStream fis = null; String v; try { fis = new FileInputStream(CONFIG_FILE); p.load(fis); fis.close(); v = p.getProperty("DB_URL"); if (null == v || v.length() < 7) { log("Config File DB_URL Error"); return RET_CONF_ERROR; } v = p.getProperty("DB_USERNAME"); if (null == v || v.length() < 1) { log("Config File DB_USERNAME Error"); return RET_CONF_ERROR; } v = p.getProperty("DB_PASSWORD"); if (null == v || v.length() < 4) { log("Config File DB_PASSWORD Error"); return RET_CONF_ERROR; } return PREPARE_OK; } catch (IOException ioe) { ioe.printStackTrace(); log("Config File Error"); return RET_CONF_ERROR; } finally { fis = null; } } public static void CloseWriter(BufferedWriter bw) { try { if (null != bw) { bw.close(); } } catch(IOException e) { e.printStackTrace(); } } public static Connection getConn(Properties p) throws ClassNotFoundException, SQLException { Class.forName(JDBC_CLASS_NAME); return DriverManager.getConnection( p.getProperty("DB_URL"), p.getProperty("DB_USERNAME"), p.getProperty("DB_PASSWORD")); } public static void closeConn(Connection conn) { try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); conn = null; } } private static void showProperties(Properties prop) { Iterator<Entry<Object, Object>> it = prop.entrySet().iterator(); while (it.hasNext()) { Entry<Object, Object> entry = it.next(); log(entry.getKey() + ":" + entry.getValue()); } } private static void log(String s) { String d = sdf.format(new Date()); System.out.println("<" + d + ">" + s); } }
配置文件(DBToFile.conf)
DB_URL=jdbc:oracle:thin:@10.32.8.195:1521:orcl DB_USERNAME=scott DB_PASSWORD=scott LOG_LEVEL=DEBUG
上面的Java程序,需要编译成class文件。
javac DBToFile.java
二、编写运行shell脚本(run-KF00001.sh、run-KF00002.sh)
#!/bin/bash export LANG="zh_CN.UTF-8" export THE_LAST_DAY=`date -d yesterday +%Y%m%d` export BIANHAO="KF00001" export DBTF_SQL="select * from V_COMPLAINT_ALERT_INTERFACE where DATE_STAMP = "$THE_LAST_DAY export DBTF_OUTPUT_DIR="/srv/BigData/bdkfsj/"$THE_LAST_DAY"/" export DBTF_OUTPUT_FILE=$BIANHAO$THE_LAST_DAY export JAVA_HOME=/srv/BigData/bdkfsj/jdk1.7.0_71 export PATH=$JAVA_HOME/bin:$PATH export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/mylib/ojdbc6.jar cd /srv/BigData/bdkfsj/bin/ java DBToFile RET=$? if [ $RET -ne 0 ]; then echo "Generate Data File FAILED, ERROR_ID = $RET" exit 2 else exit 0 fi
备注:1、运行shell脚本的服务器必须要安装jdk,并配置好环境变量;
2、CLASSPATH中需要使用odbc6.jar,这里我是放在 $JAVA_HOME/mylib/ 目录下。
三、编辑启动shell脚本(run-day.sh)
#!/bin/bash export THE_NEW_DIR=/srv/BigData/bdkfsj/`date -d yesterday +%Y%m%d` mkdir $THE_NEW_DIR /srv/BigData/bdkfsj/bin/run-KF00001.sh /srv/BigData/bdkfsj/bin/run-KF00002.sh /srv/BigData/bdkfsj/bin/run-KF00003.sh /srv/BigData/bdkfsj/bin/run-KF00004.sh /srv/BigData/bdkfsj/bin/run-KF00005.sh /srv/BigData/bdkfsj/bin/run-KF00006.sh /srv/BigData/bdkfsj/bin/run-KF00007.sh /srv/BigData/bdkfsj/bin/run-KF00008.sh /srv/BigData/bdkfsj/bin/run-KF00009.sh /srv/BigData/bdkfsj/bin/run-KF00010.sh
四、设置定时作业,每天凌晨1点钟执行导出任务。
crontab -e
0 1 * * * sh /srv/BigData/bdkfsj/bin/run-day.sh
5、预期结果
(1)每天会自动生成一个日期目录,这里是生成前一天的日期。(可以在run-day.sh中修改生成目录的日期)
(2)会自动导出的数据生成到avl文件中,同时生成chk文件(存放文件名,记录数,文件大小)用来做校验,防止数据确实不完整。
(3)可以通过vi命令查看导出的avl数据文件,文件是已^A作为分隔符。
(4)通过cat命令可以查看chk校验文件信息。
文件名为:KF0000120201115,记录数: 44, 文件大小:951
本文来自博客园,作者:业余砖家,转载请注明原文链接:https://www.cnblogs.com/yeyuzhuanjia/p/13985129.html