package com.cdel.jdbc;
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.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class HandExpData {
public static String table = "";
// sqlserver
public static String sqlserverDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static String url = "jdbc:sqlserver://192.168.181.3:1433;databaseName=g12e";
public static String username = "sa";
public static String password = "cailiqiang";
// oracle
public static String oracleDriver = "oracle.jdbc.driver.OracleDriver";
public static String oracleurl = "jdbc:oracle:thin:@192.168.190.245:1521:cdel";
public static String ousername = "dbog12e";
public static String opassword = "g12e123456";
public static Connection con = null;
public static PreparedStatement sm = null;
public static ResultSet rs = null;
public static Connection oraclecon = null;
public static PreparedStatement oraclesm = null;
public void getConnection() {
try {
Class.forName(oracleDriver);
oraclecon = DriverManager.getConnection(oracleurl,ousername,opassword);
} catch (Exception e) {
}
}
/**
* param:table name
* @param args
*/
public static void main(String[] args) {
//修改
table = "FEE_USER_ACCOUNT";
String key = "ACCUNTID";
long beginTime = System.currentTimeMillis();
System.out.println(table + "。。。。。。。。。。导入开始。。。。。。。。。。。。");
converttableDate(table,key);
System.out.println(table + "。。。。。。。。。。导入结束。。。。。。。。。。。。");
long endTime = System.currentTimeMillis();
System.out.println(table + "。。。。。。。。。。导数据用时。。。。。。。。。。。。" + (endTime - beginTime)/1000 + "秒");
}
@SuppressWarnings("unused")
public static void converttableDate(String table,String key) {
int auto = 1;
try {
//查询sql
String sqlCount = "select count(*) count from " + table;
Class.forName(sqlserverDriver);
Connection conCount = DriverManager.getConnection(url, username, password);
Statement smCount = conCount.createStatement();
ResultSet rsCount = smCount.executeQuery(sqlCount);
int rowCount = 0;
while(rsCount.next()){
rowCount = rsCount.getInt(1);
}
//设置分页
int tableRowCount = rowCount;
int pageSize = 100;
int maxPage = (tableRowCount % pageSize) == 0 ? tableRowCount/pageSize:(tableRowCount/pageSize)+1;
System.out.println("maxPage = " + maxPage);
for(int currentPage = 1 ; currentPage < maxPage + 1 ; currentPage++ ){
int beginRow = (currentPage - 1) * pageSize + 1;
int endRow = currentPage * pageSize;
//查询sql
String sqlTable = "select * from (select a.*, row_number() over(order by " + key + " asc) as pageNum from " + table + " as a ) as mytable where pageNum between " + beginRow + " and " + endRow;
Class.forName(sqlserverDriver);
con = DriverManager.getConnection(url,username, password);
sm = con.prepareStatement(sqlTable);
rs = sm.executeQuery();
ResultSetMetaData rsmd1 = rs.getMetaData();
//oracle
String values= "insert into "+table +"(" ;
for(int k=1;k<=rsmd1.getColumnCount();k++){
values = values + (rsmd1.getColumnName(k)+",");
}
values = values.substring(0, values.length()-9);
values = values +") values (";
for(int k =1;k<=rsmd1.getColumnCount();k++){
values = values + ("?,");
}
values = values.substring(0, values.length()-3);
values = values +")";
System.out.println(values);
String insert = values;
Class.forName(oracleDriver);
oraclecon = DriverManager.getConnection(oracleurl, ousername,opassword);
oraclesm = oraclecon.prepareStatement(insert);
while (rs.next()) {
auto ++;
for(int k=1;k<rsmd1.getColumnCount();k++){
if(k == rsmd1.getColumnCount()){
oraclesm.setInt(k, auto);
continue;
}
if(rsmd1.getColumnTypeName(k).indexOf("int")!=-1){
oraclesm.setInt(k, rs.getInt(k));
}
if(rsmd1.getColumnTypeName(k).indexOf("varchar")!=-1){
oraclesm.setString(k, rs.getString(k));
}
if(rsmd1.getColumnTypeName(k).indexOf("text")!=-1){
oraclesm.setString(k, rs.getString(k));
}
//////////////////////
if(rsmd1.getColumnTypeName(k).indexOf("numeric")!=-1){
oraclesm.setInt(k, rs.getInt(k));
}
/*if(rsmd1.getColumnTypeName(k).indexOf("numeric")!=-1){
oraclesm.setDouble(k, rs.getDouble(k));
}*/
if(rsmd1.getColumnTypeName(k).indexOf("float")!=-1){
oraclesm.setFloat(k, rs.getFloat(k));
}
if(rsmd1.getColumnTypeName(k).indexOf("nchar")!=-1){
oraclesm.setString(k, rs.getString(k));
}
//////////////////////
if(rsmd1.getColumnTypeName(k).indexOf("nvarchar")!=-1){
oraclesm.setString(k, rs.getString(k));
}
if(rsmd1.getColumnTypeName(k).indexOf("decimal")!=-1){
oraclesm.setDouble(k, rs.getDouble(k));
}
}
oraclesm.addBatch();
}
oraclesm.executeBatch();
}
}catch (Exception e) {
System.out.println("========记录=========" + auto );
System.out.println(e.getMessage());
}finally{
try {
if (oraclesm != null) {
oraclesm.close();
oraclesm = null;
}
if (oraclecon != null) {
oraclecon.close();
oraclecon = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
评论13
最新资源