由于公司数据库没有进行分库分表，导出上亿数据量时，内存溢出已经解决但是存在速度很慢的问题，在不允许动表情况下还未找到解决办法。
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import org.omg.CORBA.PUBLIC_MEMBER;import com.f1j.ss.t;
import com.f1j.util.br;
import com.lowagie.tools.concat_pdf;
import com.sinosoft.lis.pubfun.PubFun;
import com.sinosoft.utility.DBConnPool;
import com.sinosoft.utility.ExeSQL;
import com.sinosoft.utility.SSRS;public class LisTransferBL {private static String path;private final static String[] ss = { "T_POLICY_MAIN", "", "", "", "", "","", "" };private static List<String> bugschema = new ArrayList<String>();private static Map<String, String> bigschema = new HashMap<String, String>();private static Map<String, String> yearschema = new HashMap<String, String>();private static Map<String, String> caoschema = new HashMap<String, String>();;static {// 不能查询的表格String[] s1 = { "RATE121501", "SUPPRISKSCORE240301", "MISETLDATE_2","SMT", "TEMPYJ", "LQGRPCONTDAYDATA", "LL", "LDCODE_2","RATE646_B", "RATE646_C", "TEMPGET", "STMT_SQL","TABLESPACE_TABL", "CONTROL_TABL", "CASHVALUE285","CASHVALUE237", "GETFEE330801", "CASHVALUE284", "RATE290","CASHVALUE290", "CASHVALUE238", "CTRATE331501", "RATE331501","GETFEE331501", "LXUPDATELOG", "STATSQLCONFIG_2009","RATE6601_1", "RATE6601_0", "CONTTEST", "TEMP330501:286707","Rate550806", "PAYTIMES662", "RATE6623","LPEDORESPECIALDATA0520", "RATE662", "LDMENU_BAK20090701","RISKDEATHRATE331601", "RATE6622", "LABANKMISION_S","RISKRATE331901", "FINPREM_YM_TB_01", "RATE6621", "POLNO","INTFDATATRACE_SH", "RATE243", "RATE277", "CASHVALUE239","CASHVALUE240", "RATE245", "RATE244", "TEMPYYL", "POLNO_2","LCGRPEDOR", "RATE2301", "RATE2601", "RATE5201", "RATE550706","RATE550806", "RE230801", "RE230901", "LOBGRPPOSITION","RATE6693", "RATE6692", "RATE6691", "LPCERTIFYINSURED","CASHVALUE208", "RATE208", "CASHVALUE217", "RATE217","LPGRPPOSITION", "CASHVALUE301", "CASHVALUE302","LBASCRIPTIONRULEFACTORY", "LPASCRIPTIONRULEFACTORY","LOBASCRIPTIONRULEFACTORY", "LBASCRIPTIONRULEPARAMS","LPASCRIPTIONRULEPARAMS", "LOBASCRIPTIONRULEPARAMS","LCGRPFEETOACC", "LBRISKZTFEE", "LPRISKZTFEE", "LOBRISKZTFEE","LDDINGHOSPITALB", "LQGRPPREMDATA", "LPRREPORTITEM","GETFEE332601", "GETFEE332501", "CTRATE332501","RISKRATE332501", "RATE304", "CASHVALUE309", "RATE309","CASHVALUE314", "RATE314", "RATE308", "TESTCONFIG","FORMU330501_YEAR", "CZL_TEST", "GETFEE332701", "CTRATE332701","LICRMCONTTRACK", "SUPPRISKSCORE231501B","SUPPRISKSCORE231501A", "RATE231501A", "RATE231501B","SUPPRISKSCORE231502A", "SUPPRISKSCORE231502B","PREMYZ333102A", "PREMYZ333102B", "PREMYZ231502A","PREMYZ231502B", "RATE231502A", "RATE231502B", "RATE333101A","RATE333101B", "RATE333102A", "RATE333102B", "CASHVALUE333601","RATE231601", "RATE333601", "SUPPRISKSCORE231601", "RATE5608","PLANQDWZBRIEF", "RATE561101", "LLCASEAFEHZ1t","LLCASEAFEHZ1s", "RATE551401", "RATE122101_2", "RATE122101_3","插入网点SQL", "LDCODE_DATA", "LLCLAIMUWMAIN_DATA","LLCLAIMUWMDETAIL_DATA", "ADMIN_MOVE_TABLE_YM","LLCASE20161012", "EXPLAIN_INSTANCE", "EXPLAIN_STATEMENT","EXPLAIN_ARGUMENT", "EXPLAIN_OBJECT", "EXPLAIN_OPERATOR","EXPLAIN_PREDICATE", "EXPLAIN_STREAM", "ADVISE_INSTANCE","ADVISE_INDEX", "ADVISE_WORKLOAD", "ADVISE_MQT","ADVISE_PARTITION", "ADVISE_TABLE", "EXPLAIN_ACTUALS","LRCESSLISTBAK" };bugschema = Arrays.asList(s1);// 没有makedate的大表bigschema.put("LCGCUWERROR", "modifydate");bigschema.put("CUST_INFO", "etltime");bigschema.put("LCCUWERROR", "modifydate");bigschema.put("LCGUWERROR", "modifydate");bigschema.put("LCUWERROR", "modifydate");bigschema.put("LDTASKRUNLOG", "executedate");bigschema.put("HISTORY", "outputdate");bigschema.put("LOBCUWERROR", "modifydate");bigschema.put("LOBGCUWERROR", "modifydate");bigschema.put("LOBUWERROR", "modifydate");bigschema.put("LYRETURNFROMBANK", "modifydate");bigschema.put("LCPENOTICEITEM", "modifydate");bigschema.put("LICRMCONTINFO", "modifydate");bigschema.put("TEMPRP", "paydate");bigschema.put("POL_MAIN_BAK", "input_date");bigschema.put("ENDO_FEE_BAK", "app_date");bigschema.put("WFCONTLIST", "activedate");bigschema.put("INTFDATATRACE", "policydate");bigschema.put("WFTRANSLOG", "senddate");bigschema.put("LRDUTY", "modifydate");bigschema.put("ENDO_FEE", "app_date");bigschema.put("TEMP_GOLDELEPHANT", "signdate");bigschema.put("TEMP_GOLDELEPHANT2", "signdate");bigschema.put("CLAIM_MAIN", "app_date");bigschema.put("POL_MAIN", "input_date");bigschema.put("LIDATATRANSRESULT", "accountdate");bigschema.put("FIVOUCHERDATADETAIL", "accountdate");bigschema.put("LIABORIGINALDATA", "paydate");bigschema.put("LLCASEOPTIME", "startdate");bigschema.put("LYRETURNFROMBANKB", "modifydate");bigschema.put("LKELECTRONCONT", "contsenddate");// 只保存年yearschema.put("LFTZXML", "statyear");yearschema.put("LFRLXML", "statyear");yearschema.put("LFEXCELCOLL", "statyear");yearschema.put("LFXMLCOLL", "statyear");// 特殊caoschema.put("T_POLICY_MAIN", "datasign"); // 20100824暂不会处理}// 执行全量导入public boolean submitFull(String starttime, String endtime, String path) {this.path = path;fullupdate(starttime, endtime);return true;}// 生成全量导入public static void fullupdate(String startdate, String enddate) {System.out.println("START FULL!");String full = "";// 获取所有表名// String mSQL =// "select name from sysibm.systables where type='T' and creator='DB2INST1' and name='LCCONT'";//单表测试sqlString mSQL = "select name from sysibm.systables where type='T' and creator='DB2INST1'";SSRS mSSRS = new ExeSQL().execSQL(mSQL);for (int i = 1; i <= mSSRS.getMaxRow(); i++) {String table = mSSRS.GetText(i, 1);try {System.out.println("正在导出：" + table);String sql = "";if (bugschema.contains(table)) {System.out.println("查询不了的表格，跳过");continue;} else if (yearschema.containsKey(table)) {String where = yearschema.get(table);String startyear = startdate.split("-")[0];String endyear = enddate.split("-")[0];sql = "select * from DB2INST1." + table+ " where statyear>='" + startyear+ "' and statyear<'" + endyear + "'";} else if (bigschema.containsKey(table)) {String where = bigschema.get(table);sql = "select * from DB2INST1." + table + " where " + where+ ">='" + startdate + "' and " + where + "<'"+ enddate + "'";} else {String pdsql = "select 1 from sysibm.syscolumns where tbname='"+ table.toUpperCase() + "' and name='MAKEDATE'";SSRS pdSSRS = new ExeSQL().execSQL(pdsql);if (pdSSRS.getMaxRow() != 0) {sql = "select * from DB2INST1." + table+ " where makedate>='" + startdate+ "' and makedate<'" + enddate + "'";} else {sql = "select * from DB2INST1." + table;}}String countsql = "select count(1) from db2inst1." + table;SSRS countSSRS = new ExeSQL().execSQL(countsql);int max = Integer.parseInt(countSSRS.GetText(1, 1));// 区分大小表/** 对于超5w的大表 通过分页每次导出5000，避免导出过多内存溢出 但该功能随着数据量的大小所消耗时间会增加。* 通过使用fetch优化缩短一半时间，但是2亿数据还是很慢，不懂如何继续改进*/if (max >= 50000) {int min = 0;System.out.println("开始分批导出：");while (min != max) {System.out.println("开始分批导出：从第" + min + "行开始导出5000行");String maxsql = "SELECT * FROM "+ "( SELECT B.*, ROWNUMBER() OVER() AS RN FROM ("+ sql + ") AS B" + ")AS A WHERE A.RN >=" + min+ " fetch first 5000 rows only";// 生成cvsSSRS tSSRS = new ExeSQL().execSQL(maxsql);String url = path + "/data/lis/" + table.toLowerCase();full = url + "/full//" + changedate(startdate)+ "_21_30_01" + "-" + changedate(enddate)+ "_21_30_01//"; // 20161001_21_30_01createDir(path + "/data");createDir(path + "/data/lis/");createDir(url);createDir(url + "/full//");createDir(full);writrCVS(tSSRS, full, "data_full.csv");if (min + 5000 >= max) {min = max;} else {min += 5000;}};System.out.println("分批导出完毕，共计" + max);try {createSql(table, full, "schema.sql");} catch (Exception e) {e.printStackTrace();}try {createFile(full + "upload_completed");} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}} else {// 生成cvsSSRS tSSRS = new ExeSQL().execSQL(sql);String url = path + "/data/lis/" + table.toLowerCase();full = url + "/full//" + changedate(startdate)+ "_21_30_01" + "-" + changedate(enddate)+ "_21_30_01//"; // 20161001_21_30_01createDir(path + "/data");createDir(path + "/data/lis/");createDir(url);createDir(url + "/full//");createDir(full);writrCVS(tSSRS, full, "data_full.csv");createSql(table, full, "schema.sql");createFile(full + "upload_completed");}} catch (Exception e) {writeTxt2("导出" + table + "出错", path + "/data/bug.txt");continue;}}System.out.println("END");}// 增量导入public void increaseupdate(String date) {System.out.println("START INCREASE!");String mSQL = "select name from sysibm.systables where type='T' and creator='DB2INST1'";SSRS mSSRS = new ExeSQL().execSQL(mSQL);for (int i = 1; i <= mSSRS.getMaxRow(); i++) {String table = mSSRS.GetText(i, 1);String sql = "";if (bugschema.contains(table)) {System.out.println("查询不了的表格，跳过");continue;} else if (yearschema.containsKey(table)) {String where = yearschema.get(table);String startyear = date.split("-")[0];sql = "select * from " + table + " where statyear=" + startyear+ "";} else if (bigschema.containsKey(table)) {String where = bigschema.get(table);sql = "select * from " + table + " where " + where + "='"+ date + "'";} else {String pdsql = "select 1 from sysibm.syscolumns where tbname='"+ table.toUpperCase() + "' and name='MAKEDATE'";SSRS pdSSRS = new ExeSQL().execSQL(pdsql);if (pdSSRS.getMaxRow() != 0) {sql = "select * from " + table + " where makedate='" + date+ "'";} else {System.out.println("固定表" + table + "不进行增量导入");continue;}}String countsql = "select count(1) from db2inst1." + table;SSRS countSSRS = new ExeSQL().execSQL(countsql);int max = Integer.parseInt(countSSRS.GetText(1, 1));// 区分大小表if (max >= 50000) {int min = 0;String url = "";String full = "";while (min != max) {System.out.println("开始分批导出：从第" + min + "行开始导出5000行");String maxsql = "SELECT * FROM "+ "( SELECT B.*, ROWNUMBER() OVER() AS RN FROM ("+ sql + ") AS B" + ")AS A WHERE A.RN >=" + min+ " fetch first 5000 rows only";// 生成cvsSSRS tSSRS = new ExeSQL().execSQL(maxsql);url = path + "/data/lis/" + table.toLowerCase();full = url + "/incremental//" + changedate(date)+ getCurrentTime() + "//";createDir(path + "/data");createDir(path + "/data/lis/");createDir(url);createDir(url + "/incremental//");createDir(full);writrCVS(tSSRS, full, "data_insert_updated.csv");if (min + 5000 >= max) {min = max;} else {min += 5000;}};System.out.println("分批导出完毕，共计" + max);try {// 创建创表语句，为提升效率改为从全量数据文件夹粘贴// createSql(table, full, "schema.sql");createFile(full + "schema.sql");coby(url+ "/full//20050101_21_30_01-20100101_21_30_01//schema.sql",full + "schema.sql");createFile(full + "upload_completed");} catch (Exception e) {e.printStackTrace();}} else {SSRS tSSRS = new ExeSQL().execSQL(sql);String url = path + "/data/lis/" + table.toLowerCase();String full = url + "/incremental//" + changedate(date)+ getCurrentTime() + "//";createDir(path + "/data");createDir(path + "/data/lis/");createDir(url);createDir(url + "/incremental//");createDir(full);writrCVS(tSSRS, full, "data_insert_updated.csv");try {// 创建创表语句，为提升效率改为从全量数据文件夹粘贴// createSql(table, full, "schema.sql");createFile(full + "schema.sql");coby(url+ "/full//20050101_21_30_01-20100101_21_30_01//schema.sql",full + "schema.sql");createFile(full + "upload_completed");} catch (Exception e) {e.printStackTrace();}}}System.out.println("END");}/** 创建目录 如果存在则不创建*/public static boolean createDir(String destDirName) {File dir = new File(destDirName);if (dir.exists()) {// 判断目录是否存在return true;}if (dir.mkdirs()) {// 创建目标目录System.out.println("创建目录成功！" + destDirName);return true;} else {System.out.println("创建目录失败！");return false;}}/** 创建文档，存在则不创建*/public static boolean createFile(String FileName) throws IOException {File dir = new File(FileName);if (dir.exists()) {// 判断目录是否存在return true;}if (dir.createNewFile()) {// 创建目标目录System.out.println("创建文件成功！" + FileName);return true;} else {System.out.println("创建文件失败！");return false;}}/** 写入cvs 编码：UTF8 以“|”隔开*/public static void writrCVS(SSRS tSSRS, String url, String filename) {System.out.println("START WRITECVS!");FileOutputStream tFileOutputStream;try {createFile(url + filename);tFileOutputStream = new FileOutputStream(url + filename, true);// tFileOutputStream = new FileOutputStream(url + filename);OutputStreamWriter tOutputStreamWriter = new OutputStreamWriter(tFileOutputStream, "utf-8");BufferedWriter mBufferedWriter = new BufferedWriter(tOutputStreamWriter);for (int i = 1; i <= tSSRS.getMaxRow(); i++) {StringBuffer sb = new StringBuffer();for (int j = 1; j <= tSSRS.getMaxCol(); j++) {String info = tSSRS.GetText(i, j);sb.append(info);sb.append("|");}sb.deleteCharAt(sb.lastIndexOf("|"));String string = sb.toString();// System.out.println(string);mBufferedWriter.write(string);mBufferedWriter.newLine();mBufferedWriter.flush();}mBufferedWriter.close();tOutputStreamWriter.close();tFileOutputStream.close();} catch (Exception e) {System.out.println("创建文件失败");writeTxt2("导出" + filename + "出错", path + "/data/bug.txt");}System.out.println("END");}/** 自动生成建表sql 目前如果为空表不确定能否生成TAT 如果生成过，会重写覆盖*/public static void createSql(String table, String url, String name)throws SQLException {Connection conn = null;try {System.out.println("START CREATESQL!");// 用于拼凑字符串StringBuffer sb = new StringBuffer();// 查找表格列是否可以为空，表名和是否可以为空存入mapString tSQL = "select name,nulls from sysibm.syscolumns where tbname='"+ table + "'";SSRS tSSRS = new ExeSQL().execSQL(tSQL);Map<String, String> map = new HashMap<String, String>();for (int i = 1; i <= tSSRS.getMaxRow(); i++) {String value = "";if ("Y".equals(tSSRS.GetText(i, 2))) {value = " ";} else {value = "NOT NULL";}map.put(tSSRS.GetText(i, 1), value);}// 查找表格主键 主键列名存入listString cSQL = "select colname from SYSCAT.KEYCOLUSE where tabname='"+ table + "'";SSRS cSSRS = new ExeSQL().execSQL(cSQL);List<String> list = new ArrayList<String>();if (cSSRS.getMaxRow() == 0) {} else {for (int i = 1; i <= cSSRS.getMaxRow(); i++) {list.add(cSSRS.GetText(i, 1));}}// 为了查找顺序列名的sql，好像必须有数据，未测试空表String sql = "select * from db2inst1." + table+ " fetch first 1 rows only with ur";String columnName = "";String columnTypeName = "";int columnDisplaySize = 0;int max = 0;conn = DBConnPool.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql);ResultSetMetaData data = rs.getMetaData();sb.append("CREATE TABLE " + table + " (");rs.next();for (int i = 1; i <= data.getColumnCount(); i++) {// 获得指定列的列名columnName = data.getColumnName(i);// 获得指定列的数据类型名columnTypeName = data.getColumnTypeName(i);// 在数据库中类型的最大字符个数columnDisplaySize = data.getColumnDisplaySize(i);// 保留位数max = data.getScale(i);// System.out.println(columnName + "," + columnTypeName + ","+// columnDisplaySize);String nulls = map.get(columnName);sb.append(columnName + " ");// 有小数点约定的if ("DECIMAL".equals(columnTypeName)) {sb.append(columnTypeName + "(" + columnDisplaySize + ","+ max + ") " + nulls);} else {sb.append(columnTypeName + "(" + columnDisplaySize + ") "+ nulls);}if (list.contains(columnName)) {sb.append(" " + " primary key,\n");} else {sb.append(" " + ",\n");}}// 去掉末尾多余,sb.deleteCharAt(sb.lastIndexOf(","));sb.append(");");String sqlString = sb.toString();FileOutputStream tFileOutputStream;try {String filename = url + "//" + name;createFile(filename);// tFileOutputStream = new FileOutputStream(filename, true);// 不会覆盖，会续写tFileOutputStream = new FileOutputStream(filename);OutputStreamWriter tOutputStreamWriter = new OutputStreamWriter(tFileOutputStream, "utf-8");BufferedWriter mBufferedWriter = new BufferedWriter(tOutputStreamWriter);mBufferedWriter.write(sqlString);mBufferedWriter.close();tOutputStreamWriter.close();tFileOutputStream.close();} catch (Exception e) {System.out.println("创建文件失败");e.printStackTrace();}rs.close();System.out.println("END");} catch (Exception e) {writeTxt2("创建" + table + "的sql语句出错", path + "/data/bug.txt");} finally {conn.close();}}public static void coby(String inputpath, String outputpath) {byte[] dates = new byte[1024];FileInputStream fis = null;FileOutputStream fos = null;try {fis = new FileInputStream(inputpath);fos = new FileOutputStream(outputpath);long time1 = System.currentTimeMillis();int i = fis.read(dates);while (i != -1) {fos.write(dates, 0, i);i = fis.read(dates);}long time2 = System.currentTimeMillis();long time3 = time2 - time1;System.out.println(time3);} catch (Exception e) {// TODO: handle exception} finally {try {fos.close();fis.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/** 日期格式转换 yyyy-mm-dd to yyyymmdd*/public static String changedate(String date) {String[] s = date.split("-");StringBuffer sb = new StringBuffer();for (String s1 : s) {sb.append(s1);}return sb.toString();}// 输出txt文件，会覆盖之前写的文件public static void writeTxt(String bl, String filepath) {// 确定写出文件的位置try {createFile(filepath);} catch (IOException e1) {e1.printStackTrace();}File file = new File(filepath);// 建立输出字节流PrintWriter pw = null;try {pw = new PrintWriter(file);pw.println(bl);System.out.println("写入成功");} catch (FileNotFoundException e) {e.printStackTrace();} finally {pw.close();}}// 输出TXT 不会覆盖之前文件，会续写public static void writeTxt2(String bl, String filepath) {FileOutputStream tFileOutputStream = null;OutputStreamWriter tOutputStreamWriter = null;BufferedWriter mBufferedWriter = null;try {createFile(filepath);tFileOutputStream = new FileOutputStream(filepath, true);// tFileOutputStream = new FileOutputStream(url + filename);tOutputStreamWriter = new OutputStreamWriter(tFileOutputStream,"utf-8");mBufferedWriter = new BufferedWriter(tOutputStreamWriter);mBufferedWriter.write(bl);mBufferedWriter.newLine();mBufferedWriter.flush();} catch (IOException e) {e.printStackTrace();} finally {try {mBufferedWriter.close();tOutputStreamWriter.close();tFileOutputStream.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}// 检查是否有表格未导出完全public static void checkTxt() {createDir(path + "/data/");createDir(path + "/data/lis/");createDir(path + "/data/lis/bug/");File f1 = new File(path + "/data/lis/bug");File[] f2 = f1.listFiles();BufferedReader br = null;for (File txt : f2) {String filename = txt.getName();if (filename.endsWith(".txt")) {try {br = new BufferedReader(new FileReader(txt));String info = br.readLine();String[] s = info.split("|");String tablename = s[0];String time = s[1];String name = s[2];String bl = s[3];if (bl.indexOf("false") != -1) {String sql = "select * from " + tablename+ " where makedate='" + time + "'";SSRS tSSRS = new ExeSQL().execSQL(sql);String url = path + "/data/lis/"+ tablename.toLowerCase();String full = url + "/incremental//" + name + "//";createDir(path + "/data");createDir(path + "/data/lis/");createDir(url);createDir(url + "/incremental//");createDir(full);writrCVS(tSSRS, full, "data_insert_updated.csv");}} catch (Exception e) {e.printStackTrace();} finally {try {br.close();} catch (IOException e) {e.printStackTrace();}}}}}public static String getCurrentTime() {String time = PubFun.getCurrentTime();String currenttime = time.replaceAll(":", "_");return currenttime;}// 该方法于导出数据库信息无关// 用于生成导入EXCEL时需要的配置文件public static void createxml(String[] tables, String[] confignames) {StringBuffer sb = new StringBuffer();sb.append("<CONFIG>\n");for (String table : tables) {int a = 0;sb.append("<" + confignames[a] + ">\n");sb.append("<COL0></COL0>\n");System.out.println("START CREATEXML!");Connection conn = null;try {// 为了查找顺序列名的sql，好像必须有数据，未测试空表String sql = "select * from db2inst1." + table+ " fetch first 1 rows only with ur";String columnName = "";String columnTypeName = "";int columnDisplaySize = 0;int max = 0;conn = DBConnPool.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql);ResultSetMetaData data = rs.getMetaData();rs.next();int j = 1;// 由于我从第二个列开始需要导入所以i=2，如果表格全部导入为i=1for (int i = 2; i <= data.getColumnCount(); i++) {// 获得指定列的列名columnName = data.getColumnName(i);sb.append("<COL" + j + ">" + columnName + "</COL" + j+ ">\n");j += 1;}sb.append("</" + confignames[a] + ">\n");rs.close();a += 1;} catch (Exception e) {} finally {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}sb.append("</CONFIG>\n");String sqlString = sb.toString();writeTxt2(sqlString, "F:/XFYJ.xml");System.out.println("END");}// 该方法于导出数据库信息无关// 用于生成导入EXCEL时需要的配置文件,只输出到所需列数,最后一列不会取到public static void createxml(String[] tables, String[] confignames,int startcol, int endcol) {StringBuffer sb = new StringBuffer();sb.append("<CONFIG>\n");for (String table : tables) {int a = 0;sb.append("<" + confignames[a] + ">\n");sb.append("<COL0></COL0>\n");System.out.println("START CREATEXML!");Connection conn = null;try {// 为了查找顺序列名的sql，好像必须有数据，未测试空表String sql = "select * from db2inst1." + table+ " fetch first 1 rows only with ur";String columnName = "";String columnTypeName = "";int columnDisplaySize = 0;int max = 0;conn = DBConnPool.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql);ResultSetMetaData data = rs.getMetaData();rs.next();if (endcol > data.getColumnCount()) {endcol = data.getColumnCount();}int j = 1;// 由于我从第二个列开始需要导入所以i=2，如果表格全部导入为i=1for (int i = startcol; i <= endcol; i++) {// 获得指定列的列名columnName = data.getColumnName(i);sb.append("<COL" + j + ">" + columnName + "</COL" + j+ ">\n");j += 1;}sb.append("</" + confignames[a] + ">\n");rs.close();a += 1;} catch (Exception e) {} finally {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}sb.append("</CONFIG>\n");String sqlString = sb.toString();writeTxt2(sqlString, "F:/XFYJ.xml");System.out.println("END");}// 导出特定表的信息public static void createtable(String [] tables) {for (int i = 0; i <tables.length; i++) {String table = tables[i];String sql = "select * from "+table+" where prtno='1130002645147'with ur";SSRS tSSRS = new ExeSQL().execSQL(sql);String url = "F:/data/lis/" + table.toLowerCase();String full = url + "/incremental//" ;createDir( "F:/data");createDir("F:/data/lis/");createDir(url);createDir(full);writrCVS(tSSRS, full, "data.csv");
// try {
// // 创建创表语句，为提升效率改为从全量数据文件夹粘贴
// // createSql(table, full, "schema.sql");
// createFile(full + "schema.sql");
// coby(url
// + "/full//20050101_21_30_01-20100101_21_30_01//schema.sql",
// full + "schema.sql");
// createFile(full + "upload_completed");
// } catch (Exception e) {
//
// e.printStackTrace();
// }}System.out.println("END");}public static void main(String[] args) throws Exception {// 查询所有表// String tSQL =// "SELECT TABNAME FROM SYSCAT.TABLES where type='T' and creator='DB2INST1' ";// select name from sysibm.systables where type='T' and// creator='DB2INST1' ;// LisTransferBL l1=new LisTransferBL();// l1.submitFull("2005-01-01", "2010-01-01", "F://");// System.out.println(PubFun.getCurrentTime());// String mSQL =// "select * from LCCONT where makedate>='2005-01-01' and makedate<='2006-01-01'";// String maxsql="SELECT * FROM "// +"( SELECT B.*, ROWNUMBER() OVER() AS RN FROM"// +mSQL+") AS B"// +")AS A WHERE A.RN BETWEEN 1 AND 10000";// SSRS mSSRS = new ExeSQL().execSQL(mSQL);// String url = "F://"+"/data/lis/" + "lccont";// String full = url + "/incremental//20170410" + "//";// createDir("F://"+"/data");// createDir("F://"+"/data/lis/");// createDir(url);// createDir(url + "/incremental//");// createDir(full);//// writrCVS(mSSRS, full, "data_insert_updated.csv");// try {// //创建创表语句，为提升效率改为从全量数据文件夹粘贴// createSql("LCCONT", full, "schema.sql");////// createFile(full+"upload_completed");// } catch (Exception e) {//// e.printStackTrace();// }// System.out.println(PubFun.getCurrentTime());
// String[] tables = { "XFYJAPPNTINFO","XFYJINSUREDINFO"};
// String[] conf = { "AppntInfo","InsuredInfo" };
// createxml(tables, conf);String [] tables={"LCCONT","LCINSURED","LCAPPNT","LCPOL"};createtable(tables);}
}