文档章节

数据迁移

东门非树
 东门非树
发布于 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
南京
私信 提问
存储总量达20T的MySQL实例,如何完成迁移?

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

偶素浅小浅
2016/11/02
2
0
虚拟化学习笔记-KVM虚拟化跨机迁移原理

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

z奶油面包
2018/02/28
0
0
重置EntityFramework数据迁移到洁净状态

前言 翻译一篇有关EF数据迁移的文章,以备日后所用,文章若有翻译不当的地方请指出,将就点看,废话少说,看话题。【注意】:文章非一字一句的翻译,就重要的问题进行解释并解决。 话题引入 ...

jeffcky
2016/01/18
0
0
一张图学会数据库迁云最佳路径

摘要: 我们以基于Oracle数据库的应用系统上云为例,如何根据实际需求,及不同的应用特征,去选择合适的上云解决方案?看懂了以下这张图,就能找到最适合你的应用系统总体的迁移上云路径。 ...

阿里云云栖社区
2018/01/11
0
0
困扰当前数据中心管理的三大难题

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

问题终结者
2018/08/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

操作数据库表

/* DML:数据库操作语言 主要对表中的数据库进行 增删改 ****增:插入一条记录 insert into 表名 (列名1,列名2..) values (值1,值2..) 注意: 1.列名可以在表中选择一列或者几列 2.后面的值 必须...

stars永恒
15分钟前
1
0
你真的了解 volatile 关键字吗?

volatile关键字经常在并发编程中使用,其特性是保证可见性以及有序性,但是关于volatile的使用仍然要小心,这需要明白volatile关键字的特性及实现的原理,这也是本篇文章的主要内容。 一、J...

Henrykin
15分钟前
1
0
条码插件TBarCode Office系列教程二(Word Add-In篇)

TBarCode Office是一款适用于Microsoft Word 2007、2010和2013的条码插件,通过此插件可以轻松的在您的文档中嵌入代码。此系列教程旨在介绍TBarCode Office的常见问题及解答,帮助大家学习使...

ymy_666666
15分钟前
1
0
折腾Java设计模式之命令模式

博客原文地址 折腾Java设计模式之命令模式 命令模式 wiki上的描述 Encapsulate a request as an object, thereby allowing for the parameterization of clients with different requests, ......

大萌小路
16分钟前
2
0
微服务架构:基于微服务和Docker容器技术的PaaS云平台架构设计

基于微服务架构和Docker容器技术的PaaS云平台建设目标是给我们的开发人员提供一套服务快速开发、部署、运维管理、持续开发持续集成的流程。平台提供基础设施、中间件、数据服务、云服务器等资...

编程SHA
17分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部