数据迁移-jdbc

1.jdbc工具类

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Properties;

public class PropertyUtils {

	public static Properties getProp(String propName){
		  Properties prop = new Properties();     
	        try{
	            //读取属性文件a.properties
	            InputStream in = new BufferedInputStream (new FileInputStream(propName));
	            prop.load(in);     ///加载属性列表
	           /* Iterator<String> it=prop.stringPropertyNames().iterator();
	            while(it.hasNext()){
	                String key=it.next();
	                System.out.println(key+":"+prop.getProperty(key));
	            }*/  //迭代获取key及value
	            in.close();
	        }
	        catch(Exception e){
	            e.printStackTrace();
	        }
			return prop;
	}
}

2.单表迁移方法

package com.huateng.flowsharp;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class 单表数据迁移 {

	private static final Logger logger = LoggerFactory.getLogger(单表数据迁移.class);

	private static Map<String, Object> table_Map = new LinkedHashMap<String, Object>();

	private static String table;

	private static String id;

	public static void main(String[] args) throws SQLException {
		table = args[0];
		id = args[1];
		logger.info("表名为：" + table + " id为" + id + "数据迁移开始");

		Connection conn = getConnection("zx_jdbc.properties");
		loadData(conn);
		Connection conn1 = getConnection("jc_jdbc.properties");
		writeData(conn1);

		logger.info("表名为：" + table + " id为" + id + "数据迁移结束");
	}

	public static void loadData(Connection conn) throws SQLException {
		logger.info("reading is start...");

		String sql = "SELECT * from " + table + " where id=?";
		PreparedStatement prestat = conn.prepareStatement(sql);
		prestat.setString(1, id);
		ResultSet result = prestat.executeQuery();
		ResultSetMetaData metaDatat = result.getMetaData();
		int columnCount = metaDatat.getColumnCount();

		while (result.next()) {
			for (int i = 1; i <= columnCount; i++) {
				String key = metaDatat.getColumnLabel(i);

				if (Types.VARCHAR == metaDatat.getColumnType(i)) {
					String value = result.getString(i);
					table_Map.put(key, value);
				}
				if (Types.NUMERIC == metaDatat.getColumnType(i)) {
					BigDecimal value = result.getBigDecimal(i);
					table_Map.put(key, value);
				}
			}
		}

		logger.info("reading is end...");
		conn.close();
	}

	public static void writeData(Connection conn) throws SQLException {
		logger.info("writing is start...");

		String sql = "insert into " + table + " (";

		Set<String> set = table_Map.keySet();
		for (String key : set) {
			sql += key + ",";
		}
		sql = sql.substring(0, sql.length() - 1);
		sql += ") values (";

		for (String key : set) {
			if (table_Map.get(key) instanceof String) {
				sql += "'" + table_Map.get(key) + "'" + ",";
			} else if (table_Map.get(key) instanceof BigDecimal) {
				sql += table_Map.get(key) + ",";
			} else {
				sql += null + ",";
			}
			sql = sql.trim();
		}
		sql = sql.substring(0, sql.length() - 1) + ")";
		PreparedStatement prestat = conn.prepareStatement(sql);
		prestat.execute();
		logger.info("writing is end...");
		conn.close();
	}

	public static Connection getConnection(String jdbc) throws SQLException {
		Properties prop = PropertyUtils.getProp("src/test/java/config/" + jdbc);
		try {
			Class.forName(prop.getProperty("classDriver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		String url = prop.getProperty("url");
		String username = prop.getProperty("username");
		String passwd = prop.getProperty("passwd");
		return DriverManager.getConnection(url, username, passwd);
	}
}

3.多表数据迁移

package com.huateng.flowsharp;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 执行完后将trunct环境卖出申请记账和买入申请记账迁移到本地jc环境
 * @author lichuang
 *
 */
public class 多表数据迁移 {
	
	private static final Logger logger = LoggerFactory.getLogger(多表数据迁移.class);
	
	
	private static String[] strArr = {"CPES_CLICK_DEAL_CONTRACT","CES_QUOTE_DEAL","CES_QUOTE_INFO"
			                ,"CPES_CLICK_DEAL_DETAILS","DPC_DRAFT_INFO","HTES_DRAFT_INFO"};
	
	
	private static Map<String,Object> contract_Map = new LinkedHashMap<String, Object>();
	private static Map<String,Object> deal_Map = new LinkedHashMap<String, Object>();
	private static Map<String,Object> quote_MAP = new LinkedHashMap<String, Object>();
	private static Map<String,Object> details_Map = new LinkedHashMap<String, Object>();
	private static Map<String,Object> draft_Map = new LinkedHashMap<String, Object>();
	private static Map<String,Object> htestDraft_Map = new LinkedHashMap<String, Object>();
	
	private static String contractId = null;

	public static void main(String[] args) throws SQLException {
		
		logger.info("批次id为"+args[0]+"开始克隆");
		contractId = args[0];
		Connection conn = getConnection("zx_jdbc.properties");
		loadData(conn);
		Connection conn1 = getConnection("jc_jdbc.properties");
		writeData(conn1);
	}
	
	//读取数据
	public static void loadData(Connection conn) throws SQLException{
		logger.info("reading is start...");
		PreparedStatement prestat= null;
		
		Map map = null;
		for(String str:strArr){
			String sql = null;
			if(str.equals(strArr[0])){
				map = contract_Map;
				prestat= conn.prepareStatement("SELECT * from CPES_CLICK_DEAL_CONTRACT c where c.ID =?");
						
			}else if(str.equals(strArr[1])){
				map = deal_Map;
				prestat= conn.prepareStatement("SELECT * from CES_QUOTE_DEAL d where d.BUSS_CONTRACT_ID =?");
			}else if(str.equals(strArr[2])){
				map = quote_MAP;
				prestat= conn.prepareStatement("SELECT * from CES_QUOTE_INFO q where q.DEAL_ID in (SELECT d.ID from CES_QUOTE_DEAL d where d.BUSS_CONTRACT_ID =?)");
			}else if(str.equals(strArr[3])){
				map = details_Map;
				prestat= conn.prepareStatement("SELECT * from NBMS_ZX.CPES_CLICK_DEAL_DETAILS e where e.CONTRACT_ID =?");
			}else if(str.equals(strArr[4])){
				map = draft_Map;
				prestat= conn.prepareStatement("SELECT * from DPC_DRAFT_INFO d where d.ID in (SELECT e.DPC_DRAFT_ID from NBMS_ZX.CPES_CLICK_DEAL_DETAILS e where e.CONTRACT_ID =?)");
			}else if(str.equals(strArr[5])){
				map = htestDraft_Map;
				prestat= conn.prepareStatement("SELECT * from HTES_DRAFT_INFO h where h.DRAFT_NUMBER = (SELECT d.DRAFT_NUMBER from DPC_DRAFT_INFO d where d.ID in (SELECT e.DPC_DRAFT_ID from NBMS_ZX.CPES_CLICK_DEAL_DETAILS e where e.CONTRACT_ID =?))");
			}
			prestat.setString(1, contractId);
			
			ResultSet result = prestat.executeQuery();;
			ResultSetMetaData metaDatat = result.getMetaData();
			int columnCount = metaDatat.getColumnCount();
			
			while(result.next()){
				for (int i = 1; i <=columnCount; i++) {
					String key =metaDatat.getColumnLabel(i);

					if(Types.VARCHAR==metaDatat.getColumnType(i)){
						String value = result.getString(i);
						map.put(key, value);
					}if(Types.NUMERIC==metaDatat.getColumnType(i)){
						BigDecimal value = result.getBigDecimal(i);
						map.put(key, value);
					}
				}
			}
		}
		
		conn.close();
		logger.info("reading is end...");
	}
	
	//写数据
	public static void writeData(Connection conn) throws SQLException{
        logger.info("writing is start...");
	PreparedStatement prestat= null;
		
		Map map = null;
		for(String str:strArr){
			String sql = null;
			if(str.equals(strArr[0])){
				sql = "insert into CPES_CLICK_DEAL_CONTRACT (";
				map = contract_Map;
				
			}else if(str.equals(strArr[1])){
				sql = "insert into CES_QUOTE_DEAL (";
				map = deal_Map;
			}else if(str.equals(strArr[2])){
				sql = "insert into CES_QUOTE_INFO (";
				map = quote_MAP;
			}else if(str.equals(strArr[3])){
				sql = "insert into CPES_CLICK_DEAL_DETAILS (";
				map = details_Map;
			}else if(str.equals(strArr[4])){
				sql = "insert into DPC_DRAFT_INFO (";
				map = draft_Map;
			}else if(str.equals(strArr[5])){
				sql = "insert into HTES_DRAFT_INFO (";
				map = htestDraft_Map;
			}
			Set<String> set = map.keySet();
			for(String key:set){
				sql+=key+",";
			}
			sql=sql.substring(0,sql.length()-1);
			sql+=") values (";
			
			for(String key:set){
				 if (map.get(key) instanceof String) {
					 sql+="'"+map.get(key)+"'"+",";
				}else if(map.get(key) instanceof BigDecimal){
					sql+=map.get(key)+",";
				}else{
					sql+=null+",";
				}
				 sql = sql.trim();
			}
			sql = sql.substring(0, sql.length()-1)+")";
			prestat= conn.prepareStatement(sql);
			prestat.execute();
		}

		logger.info("writing is end...");
		conn.close();
	}
	
	//flowsharp/src/test/java/config/jc_jdbc.properties
	//获取jdbc连接
	public static Connection getConnection(String jdbc) throws SQLException {
		Properties prop = PropertyUtils.getProp("src/test/java/config/"+jdbc);
		try {
			Class.forName(prop.getProperty("classDriver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		String url = prop.getProperty("url");
		String username = prop.getProperty("username");
		String passwd = prop.getProperty("passwd");
		return DriverManager.getConnection(url,username,passwd);
	}
	
}

4.包含blob大字段数据迁移

package com.huateng.flowsharp;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.huateng.flowsharp.inner.entity.Process;

public class Blob处理类 {
	
	static String []str;
	static Blob blob ;

	public static void main(String[] args) throws Exception {
		
		
		String []str = {"f109f9d0bec44ef1a1ae21159862cfd4"};
				for (String processId:str) {
					writeDate(processId);
				}
		
	}
	
	public static void writeDate(String processId) throws Exception {
		com.huateng.flowsharp.inner.entity.Process obj =null;
		try {
			Connection conn = getConnection();
			Statement stat = conn.createStatement();
			String sql = "SELECT * from FLOWSHARP_ZX.TBL_SERPROCESS s where s.PROCESS_ID = "+"'"+processId+"'";
			stat.execute(sql);
			ResultSet result = stat.getResultSet();
			getData(result);
		
		
			//ByteArrayOutputStream outStream = new ByteArrayOutputStream();
			
			//FileOutputStream output = new FileOutputStream(new File("C:/Users/lichuang/Desktop/test.txt"));
			
			
			/*InputStream in = blob.getBinaryStream();
			byte []data =new byte[4096];
			int count = -1;
			while((count = in.read(data,0,4096))!=-1){
				//output.write(data, 0, count);
				outStream.write(data, 0, count);
				
			}
			output.flush();
			output.close();*/
			//String content= new String(outStream.toByteArray(),"UTF8");
			//String content = new String(blob.getBytes((long)1, (int)blob.length()));
	
			  byte[] btArr =blob.getBytes((long)1, (int)blob.length());
			  ByteArrayInputStream bi = new ByteArrayInputStream(btArr);
		      ObjectInputStream oi = new ObjectInputStream(bi);
		      Process obj1 = (Process) oi.readObject();
		      bi.close();
		      oi.close();

			
			result.close();
			stat.close();
			conn.close();
			
	
			Connection conn1 = getConnection1();
			Statement stat1 = conn1.createStatement();
			String sql1 = "insert INTO FLOWSHARP_JC.TBL_SERPROCESS(TBL_SERPROCESS.PROCESS_ID, TBL_SERPROCESS.PROCESS_NAME, TBL_SERPROCESS.BIZ_ID, TBL_SERPROCESS.NODE_ID, TBL_SERPROCESS.NODE_NAME, TBL_SERPROCESS.PROCESS_BYTE, TBL_SERPROCESS.START_DATE, TBL_SERPROCESS.END_DATE, TBL_SERPROCESS.STARTED_USER, TBL_SERPROCESS.MODEL_ID, TBL_SERPROCESS.SUPER_PROCESS_ID, TBL_SERPROCESS.RUN_FLAG) VALUES"+
			"("+"'"+str[0]+"'"+","
				    +"'"+str[1]+"'"+","
				    +"'"+str[2]+"'"+","
				    +"'"+str[3]+"'"+","
				    +"'"+str[4]+"'"+","
				    +"empty_blob()"+","
				    +"'"+str[5]+"'"+","
				   
				    +"'"+str[7]+"'"+","
				    +"'"+str[8]+"'"+","
				    +"'"+str[9]+"'"+","
				    +"'"+str[10]+"'"+","
				    +"'"+str[11]+"'"+")";
			stat1.execute(sql1);
			
			ResultSet rs =stat1.executeQuery("select * from FLOWSHARP_JC.TBL_SERPROCESS s where s.PROCESS_ID  = "+"'"+processId+"'"+"for update");
			if(rs.next()){
				Blob b =rs.getBlob(6);
				
				   ByteArrayOutputStream bo = new ByteArrayOutputStream();
				      ObjectOutputStream oo = new ObjectOutputStream(bo);
				      oo.writeObject(obj1);
				      byte[]bytes = bo.toByteArray();
				      bo.close();
				      oo.close();
				      
				BufferedOutputStream os = new BufferedOutputStream(b.setBinaryStream(0));
				os.write(bytes);
				os.flush();
				os.close();
				stat1.close();
				conn1.close();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void getData(ResultSet result) throws Exception{
		ResultSetMetaData metaDatat = result.getMetaData();
		int columnCount = metaDatat.getColumnCount();
	   
		for (int i = 1; i <=columnCount; i++) {
			if(i>1) System.out.print(",");
			System.out.print(metaDatat.getColumnLabel(i));
		}
		System.out.println();
		
		
		while(result.next()){
			str = new String[columnCount];
			for (int i = 1; i <=columnCount; i++) {
				if(i>1) System.out.print(",");
				  if("PROCESS_BYTE".equals(metaDatat.getColumnLabel(i))){
					   blob =result.getBlob(i);
				  }else{
					  str[i-1]=result.getString(i);
				  }
			}
			System.out.println();
		}
	}
	
	
	public static Connection getConnection() throws SQLException{
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String url = "jdbc:oracle:thin:@ip:port/service";
		String username = "用户名1";
		String passwd = "密码1";
		
		return DriverManager.getConnection(url,username,passwd);
	}
	
	public static Connection getConnection1() throws SQLException{

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String url = "jdbc:oracle:thin:@ip:port/service";
		String username = "用户名2";
		String passwd = "密码2";
		
		return DriverManager.getConnection(url,username,passwd);
	}
}