数据迁移
2019獨角獸企業重金招聘Python工程師標準>>>
package com.bdjsi.bdto;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
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.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.logicalcobwebs.proxool.configuration.JAXPConfigurator;
public class DataSql {
?private Connection myConn = null;
?private Connection conn = null;
?private Statement stat = null;
?private Statement myStat = null;
?private PreparedStatement prst;
?/**
? * 查找數據庫
? *
? * @return
? */
?public Connection getConn() {
??String url = "jdbc:oracle:thin:@10.1.2.11:1521:orcl";
??String user = "locationuser";
??String password = "jsbdyjy2013";
??try {
???Class.forName("oracle.jdbc.driver.OracleDriver");
???conn = DriverManager.getConnection(url, user, password);
??} catch (Exception e) {
??}
??return conn;
?}
?public Statement getStatement() {
??try {
???conn = this.getConn();
???if (conn != null)
????stat = conn.createStatement();
??} catch (Exception e) {
??}
??return stat;
?}
?public void close() {
??try {
???if (stat != null)
????stat.close();
???stat = null;
??} catch (Exception e) {
???e.printStackTrace();
??}
??try {
???if (conn != null)
????conn.close();
???conn = null;
??} catch (Exception e) {
??}
?}
?/**
? * 插入數據庫
? *
? * @return
? */
?public Connection getMysqlConn() {
??//String url = "jdbc:oracle:thin:@192.168.0.10:1521:bdplat";
??String url = "jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = bdplat)))";
??String user = "locationuser";
??String password = "jsbdyjy2013";
??try {
???Class.forName("oracle.jdbc.driver.OracleDriver");
???myConn = DriverManager.getConnection(url, user, password);
??} catch (Exception e) {
???e.printStackTrace();
??}
??return myConn;
?}
?public Statement getmyStatement() {
??try {
???myConn = this.getConn();
???if (myConn != null)
????stat = myConn.createStatement();
??} catch (Exception e) {
???e.printStackTrace();
??}
??return myStat;
?}
?public void myclose() {
??try {
???if (myStat != null)
????myStat.close();
???myStat = null;
??} catch (Exception e) {
??}
??try {
???if (myConn != null)
????myConn.close();
???myConn = null;
??} catch (Exception e) {
??}
?}
?// 查出所有終端id
?public List<String> QueyTable() {
??String sql = "select id from base_terminalinfo";
??ResultSet rs = null;
??ResultSet mysqlRs = null;
??BufferedWriter bw = null;
??conn = this.getConn();
??int count = 0;
??List<String> list = new ArrayList<String>();
??try {
???stat = conn.createStatement();
???mysqlRs = stat.executeQuery(sql);
???ResultSetMetaData mysqlRsmd = mysqlRs.getMetaData();
???rs = this.getStatement().executeQuery(sql);
???ResultSetMetaData rsmd = rs.getMetaData();
???int coulum = rsmd.getColumnCount();
???while (rs.next()) {
????String s = rs.getString("ID");
????list.add(s);
???}
??} catch (Exception e) {
???e.printStackTrace();
??} finally {
???try {
????if (stat != null)
?????stat.close();
????if (rs != null)
?????rs.close();
????if (bw != null)
?????bw.close();
????if (conn != null)
?????conn.close();
???} catch (Exception ex) {
???}
??}
??return list;
?}
?// 插入數據庫
?public void InsertDB(String tableName, List<String> list) throws SQLException {
??myConn = this.getMysqlConn();
??myConn.setAutoCommit(false);?
???try {
????for (Iterator<String> i = list.iterator(); i.hasNext();) {
????String strRef = i.next();
????System.out.println(strRef);
????prst = this.getMysqlConn().prepareStatement(strRef);
????prst.addBatch();
????try {
????prst.executeBatch();
????}catch (Exception e) {
?????e.printStackTrace();
?????continue;
????}
????}
????myConn.commit();
???} catch (Exception e) {
????e.printStackTrace();
????
???} finally {
????try {
?????if (myStat != null)
??????myStat.close();
?????if (myConn != null)
??????myConn.close();
????} catch (Exception ex) {
?????ex.printStackTrace();
????}
???}
??}
?public List<String> QueryDate(String myTableName,String[] msFields, String sTime) {
??String sql = "select * from " + myTableName + " where time > to_date('"
????+ sTime + "' , 'yyyy-mm-dd hh24:mi:ss')";
??System.out.println("時間段的SQL:" + sql);
??String mysqlSql = "select * from " + myTableName;
??ResultSet rs = null;
??ResultSet mysqlRs = null;
??BufferedWriter bw = null;
??conn = this.getConn();
??int count = 0;
??List<String> list = new ArrayList<String>();
??long oldTime = System.currentTimeMillis();
??try {
???stat = conn.createStatement();
???mysqlRs = stat.executeQuery(mysqlSql);
???ResultSetMetaData mysqlRsmd = mysqlRs.getMetaData();
???rs = this.getStatement().executeQuery(sql);
???// 使用元數據獲取一個表字段的總數
???ResultSetMetaData rsmd = rs.getMetaData();
???int coulum = rsmd.getColumnCount();
???while (rs.next()) {
????String mysqlField = "";
????String valueSql = "";
????for (int i = 0; i < coulum; i++) {
?????String columName = rsmd.getColumnName(i + 1);
?????String value = null;
?????for (int j = 0; j < msFields.length; j++) {
??????if (columName.equalsIgnoreCase(msFields[j])) {
???????value = rs.getString(i + 1);
???????if (columName.equals("TIME")) {
????????if (value != null && !value.equalsIgnoreCase("NULL")) {
?????????valueSql += "to_date('"+ value.replaceAll("\\'", "\\\\'")+ "' , 'yyyy-mm-dd hh24:mi:ss'),";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????} else if (columName.equals("SERVERTIME")) {
????????if (value != null && !value.equalsIgnoreCase("NULL")) {
?????????valueSql += "to_date('"+ value.replaceAll("\\'", "\\\\'")+ "' , 'yyyy-mm-dd hh24:mi:ss'),";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????} else if (columName.equals("ADDRESS")) {
????????if (value != null && !value.equalsIgnoreCase("NULL")) {
?????????valueSql += "'"+ value.replaceAll("\\'", "\\\\'") + "',";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????} else if (columName.equals("S0")) {
????????if (value != null && !value.equalsIgnoreCase("NULL")) {
?????????valueSql += "'"+ value.replaceAll("\\'", "\\\\'")+ ",";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????} else if (columName.equals("S1")) {
????????if (value != null && !value.equalsIgnoreCase("NULL")) {
?????????valueSql += "'"+ value.replaceAll("\\'", "\\\\'")+ "',";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????} else if (columName.equals("S2")) {
????????if (value != null && !value.equalsIgnoreCase("NULL")) {
?????????valueSql += "'"+ value.replaceAll("\\'", "\\\\'")+ "',";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????} else if (columName.equals("S3")) {
????????if (value != null && !value.equalsIgnoreCase("NULL")) {
?????????valueSql += "'"+ value.replaceAll("\\'", "\\\\'")+ "',";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????} else if (columName.equals("S4")) {
????????if (value != null && !value.equalsIgnoreCase("NULL")) {
?????????valueSql += "'"+ value.replaceAll("\\'", "\\\\'")+ "',";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????} else {
????????if (value != null
??????????&& !value.equalsIgnoreCase("NULL")) {
?????????valueSql += Integer.valueOf(value.replaceAll("\\'", "\\\\'")) + ",";
?????????mysqlField += mysqlRsmd.getColumnName(i + 1) + ",";
????????}
???????}
??????}
?????}
????}
????// 生成sql語句
????valueSql = valueSql.substring(0, valueSql.length() - 1);
????mysqlField = mysqlField.substring(0, mysqlField.length() - 1);
????sql = "insert into " + myTableName + "(" + mysqlField + ") "
??????+ " values(" + valueSql + ")";
????list.add(sql);
????// System.out.println(list);
???}
??} catch (Exception e) {
??} finally {
???try {
????if (stat != null)
?????stat.close();
????if (rs != null)
?????rs.close();
????if (bw != null)
?????bw.close();
????if (conn != null)
?????conn.close();
???} catch (Exception ex) {
???}
??}
??return list;
?}
?public static void main(String[] args) throws SQLException {
??String[] msFields = { "TIME", "WARNINGFLAG", "STATUS", "LON", "LAT",
????"VELOCITY", "DIRECTION", "DEM", "SERVERTIME", "ADDRESS", "I0",
????"I1", "I2", "I3", "I4", "I5", "I6", "I7", "I8", "I9", "I10",
????"I11", "I12", "I13", "I14", "D0", "D1", "D2", "D3", "D4", "S0",
????"S1", "S2", "S3", "S4", "ADDITIONAL" };
??String sTime = "2014/11/4 18:17:17";
??DataSql ds = new DataSql();
?? List<String> list1 = ds.QueyTable();
?? System.out.println("表LIST:"+list1);
?? System.out.println("表個數："+list1.size());
//??String sid = "14000625043";
//??String mysqlTableName = "BASE_T" + sid;
//??String msTableName = "BASE_T" + sid;
//??System.out.println(mysqlTableName);
//??List<String> list = ds.QueryDate(mysqlTableName, msTableName, msFields,
//????sTime);
//??System.out.println(list);
//??ds.InsertDB(mysqlTableName, list);
?? for(int i=0;i<list1.size();i++){
?? String sid = list1.get(i);
?? String myTableName ="BASE_T"+sid;
?? String msTableName="BASE_T"+sid;
?? System.out.println("表名："+myTableName);
?? List<String> list =
?? ds.QueryDate(myTableName,msFields,sTime);
?? System.out.println(list);
?? if(list.size()>0){
?? ds.InsertDB(myTableName, list);
?? }
?? }
?}
?}
?
轉載于:https://my.oschina.net/dongmenfeishu/blog/343849
總結
- 上一篇： 关于Spring的IOC和DI
- 下一篇： Oracle E-Business Su