用java导出数据库所有表信息，以及自己写的拼凑建表语句程序

绝地灬酷狼 2022-06-16 01:26 94阅读 0赞

由于公司数据库没有进行分库分表，导出上亿数据量时，内存溢出已经解决但是存在速度很慢的问题，在不允许动表情况下还未找到解决办法。

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'";//单表测试sql
    		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);
    			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";
    						// 生成cvs
    						SSRS 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_01
    						createDir(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 block
    						e.printStackTrace();
    					}
    
    				} else {
    					// 生成cvs
    					SSRS 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_01
    					createDir(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";
    					// 生成cvs
    					SSRS 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();
    			// 查找表格列是否可以为空，表名和是否可以为空存入map
    			String 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);
    
    			}
    			// 查找表格主键 主键列名存入list
    			String 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 block
    				e.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 block
    				e.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=1
    				for (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 block
    					e.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=1
    				for (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 block
    					e.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);
    	}
    }