文档章节

数据迁移

东门非树
 东门非树
发布于 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
南京
私信 提问
虚拟化学习笔记-KVM虚拟化跨机迁移原理

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

z奶油面包
02/28
0
0
上云迁移-海量数据迁移解决方案

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

黄小凡
05/08
0
0
困扰当前数据中心管理的三大难题

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

问题终结者
08/18
0
0
平台运行中,数据库如何平滑升级

一、问题的提出 互联网有很多“数据量较大,并发量较大,业务复杂度较高”的业务场景,其典型系统分层架构如下: (1)上游是业务层biz,实现个性化的业务逻辑 (2)中游是服务层service,封...

张锦飞
09/21
0
0
CVPR 2018 | Spotlight 论文:北京大学计算机研究所提出深度跨媒体知识迁移方法

  机器之心发布   作者:Xin Huang、Yuxin Peng      近日,来自北京大学计算机科学技术研究所的博士生黄鑫和彭宇新教授提出了一种新型的迁移学习方法:深度跨媒体知识迁移(Deep Cr...

机器之心
04/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Mariadb二进制包安装,Apache安装

安装mariadb 下载二进制包并解压 [root@test-a src]# wget https://downloads.mariadb.com/MariaDB/mariadb-10.2.6/bintar-linux-glibc_214-x86_64/mariadb-10.2.6-linux-glibc_214-x86_64.t......

野雪球
今天
3
0
ConcurrentHashMap 高并发性的实现机制

ConcurrentHashMap 的结构分析 为了更好的理解 ConcurrentHashMap 高并发的具体实现,让我们先探索它的结构模型。 ConcurrentHashMap 类中包含两个静态内部类 HashEntry 和 Segment。HashEnt...

TonyStarkSir
今天
3
0
大数据教程(7.4)HDFS的java客户端API(流处理方式)

博主上一篇博客分享了namenode和datanode的工作原理,本章节将继前面的HDFS的java客户端简单API后深度讲述HDFS流处理API。 场景:博主前面的文章介绍过HDFS上存的大文件会成不同的块存储在不...

em_aaron
昨天
4
0
聊聊storm的window trigger

序 本文主要研究一下storm的window trigger WindowTridentProcessor.prepare storm-core-1.2.2-sources.jar!/org/apache/storm/trident/windowing/WindowTridentProcessor.java public v......

go4it
昨天
7
0
CentOS 生产环境配置

初始配置 对于一般配置来说,不需要安装 epel-release 仓库,本文主要在于希望跟随 RHEL 的配置流程,紧跟红帽公司对于服务器的配置说明。 # yum update 安装 centos-release-scl # yum ins...

clin003
昨天
11
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部