首页 > 代码库 > 数据迁移
数据迁移
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 =http://www.mamicode.com/(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 = http://www.mamicode.com/null;
for (int j = 0; j < msFields.length; j++) {
if (columName.equalsIgnoreCase(msFields[j])) {
value = http://www.mamicode.com/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);
}
}
}
}
数据迁移