文档章节

数据迁移

东门非树
 东门非树
发布于 2014/11/13 10:40
字数 862
阅读 62
收藏 4

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);
   }
   }
 }
 }

 

© 著作权归作者所有

共有 人打赏支持
东门非树
粉丝 0
博文 11
码字总数 4040
作品 0
南京
上云迁移-海量数据迁移解决方案

摘要:传统数据存储在线下数据中心,存在成本高、运维难、性能难保障等等多方面的问题。阿里云提供闪电立方、OSS/NASImport、混合云存储阵列、镜像回源、302跳转、伪源站等六大解决方案为企业...

黄小凡
05/08
0
0
虚拟化学习笔记-KVM虚拟化跨机迁移原理

版权声明:转载请注明出处:http://blog.csdn.net/dajitui2024 https://blog.csdn.net/dajitui2024/article/details/79396689 跨机迁移存在网络中断时间长的问题; 跨存储类型的场景下如何进行...

z奶油面包
02/28
0
0
困扰当前数据中心管理的三大难题

导读 当企业发展到一定程度,或者之前的机房不能满足现在的数据中心使用时,企业会对数据中心进行迁移。那么在数据中心进行迁移的时候会遇到哪些风险呢?针对这些风险我们应该做出怎样的措施来...

问题终结者
08/18
0
0
存储总量达20T的MySQL实例,如何完成迁移?

版权声明:本文由王亮原创文章,转载请注明出处: 文章原文链接:https://www.qcloud.com/community/article/122 来源:腾云阁 https://www.qcloud.com/community 王亮,腾讯云高级工程师。2...

偶素浅小浅
2016/11/02
2
0
laravel 数据库迁移报错解决

创建数据迁移 使用 Artisan 命令行的 命令创建一个迁移:(在命令行模式下使用) 所有的迁移都被存放在 文件夹下,文件以时间戳命名以方便Laravel框架按时间来界定这些文件顺序. 您可以在创建迁...

ericSM
2015/07/03
0
0

没有更多内容

加载失败,请刷新页面

加载更多

工作日志 ,城市ip地址整理

INSERT INTO sSELECT a.city,b.startip,b.endip FROM cityinfo a JOIN t_ip bON b.cheng LIKE CONCAT("%" ,a.city ,"%")ORDER BY a.cityHAVING COUNT(a.city)<=5ORD......

haifei2017
14分钟前
3
0
cdn是否可以抵御ddos的攻击

随着近年来来网络技术的不断进步,cdn不仅可以简单的用做网站加速,还能够更好的保护网站不被攻击。cdn在相关节点中成功的建立动态加速机制以及智能沉于等机制,能够帮助网站流量访问分配到每...

上树的熊
20分钟前
2
0
Oracle修改字符集

1.cmd下,cd到oracle数据库软件的服务器端 如:D:\app\Administrator\product\11.2.0\dbhome_1\BIN 2.输入set ORACLE_SID=你想进入的数据库的那个sid 3.输入 sqlplus /nolog 4.将数据库启动到...

lyle_luo
27分钟前
1
0
Xamarin Essentials教程打开文件

Xamarin Essentials教程打开文件 FileSystem类的OpenAppPackageFileAsync()方法可以用来打开App包中特定的文件,其语法形式如下: public static System.Threading.Tasks.Task<System.IO.St...

大学霸
38分钟前
1
0
Redis应用之分布式锁(set)

Redis应用之分布式锁(set) 在单机应用的场景下,我们常使用的锁主要是synchronized与Lock;但是在分布式横行的大环境下,显然仅仅这两种锁已经无法满足我们的需求; 需求:秒杀场景下,有若干...

GMarshal
51分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部