从一个数据库导入到另一个数据库中
package cn.dao;
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.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 org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.logicalcobwebs.proxool.configuration.JAXPConfigurator;
/**
* yicha
* Dec 11, 2008
*/
public class DataSql {
private Logger logger=Logger.getLogger(DataSql.class);
private Connection mysqlConn=null;
private Connection conn = null;
private Statement stat = null;
private Statement mysqlStat=null;
/**
* jdbc链接
* @return
*/
public Connection getConn(){
String url="jdbc:microsoft:sqlserver://192.168.0.214:1433;DatabaseName=unionfee";
String user="unionfee";
String password="unionfee";
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
conn=DriverManager.getConnection(url, user, password);
}catch(Exception e){
logger.error("Connecter=>getConnection error: "+e);
}
return conn;
}
/**
* mysql 的jdbc链接
* @return
*/
public Connection getMysqlConn(){
String url="jdbc:mysql://192.168.0.225/unionfee?useUnicode=true&characterEncoding=utf8";
String user="root";
String password="root";
try{
Class.forName("com.mysql.jdbc.Driver");
mysqlConn=DriverManager.getConnection(url, user, password);
}catch(Exception e){
logger.error("Connecter=>getConnection error: "+e);
}
return mysqlConn;
}
public Statement getStatement(){
try{
conn = this.getConn();
if(conn!=null)stat = conn.createStatement();
}catch(Exception e){
logger.error("Connecter=>getConnection error: "+e);
}
return stat;
}
public void close(){
try{
if(stat!=null)stat.close();
stat= null;
}catch(Exception e){
logger.error("Connecter=>close statement error: "+e);
}
try{
if(conn!=null)conn.close();
conn = null;
}catch(Exception e){
logger.error("Connecter=>close connection error: "+e);
}
}
public ArrayList getTableData(String tableName) {
// String tableName="t_ad_union_user";
String sql="select * from "+tableName;
HashMap hashMap=null;
ArrayList result=new ArrayList();
ResultSet rs;
try {
rs = this.getStatement().executeQuery(sql);
//使用元数据获取一个表字段的总数
ResultSetMetaData rsmd=rs.getMetaData();
int coulum=rsmd.getColumnCount();
while(rs.next()){
hashMap=new HashMap();
// String[] array=new String[coulum];
for(int i=0;i<coulum;i++){
String columName=rsmd.getColumnName(i+1);
hashMap.put(columName, rs.getString(i+1));
// logger.info(columName);
// array[i]=rs.getString(i+1);
//在数据库中可以插入日期，int,float,double,varchar
// int columType=rsmd.getColumnType(i+1);
// if(columType==java.sql.Types.INTEGER ){
// rs.getInt(i+1);
// }else if(columType==java.sql.Types.BIGINT){
// rs.getInt(i+1);
// }else if(columType==java.sql.Types.DATE){
// rs.getString(i+1);
// }
// else{
// rs.getString(i+1);
// }
}
// result.add(array);
result.add(hashMap);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
public String[] DataToSql(ArrayList al,String tableName){
// String tableName="t_ad_union_user";
String[] result=new String[al.size()];
String sql="";
for(int i=0;i<al.size();i++){
String fieldSql="";
String valueSql="";
HashMap data=(HashMap)al.get(i);
Iterator it=data.keySet().iterator();
while(it.hasNext()){
String field=it.next().toString();
String value=data.get(field).toString();
fieldSql+=field+",";
valueSql+="'"+value+"',";
// logger.info(field);
}
//去除最后字段的最后一个,符号，去除值的最后两个',符号
fieldSql=fieldSql.substring(0, fieldSql.length()-1);
valueSql=valueSql.substring(0, valueSql.length()-1);
sql="insert into "+tableName+"("+fieldSql+") "+" values("+valueSql+");";
// logger.info(sql);
// logger.info(fieldSql);
result[i]=sql;
}
return result;
}
/**
* 写入到文件中
* @param str
* @param path
*/
public void stringWriteToTxt(String tableName){
String path="c:\\"+tableName+".txt";
String[] content=this.DataToSql(this.getTableData(tableName), tableName);
BufferedWriter bw=null;
String str=null;
try {
bw=new BufferedWriter(new FileWriter(path));
for(int i=0;i<content.length;i++){
str=content[i]+"\n";
bw.write(str);
}
System.out.println("写文件成功！"+path);
} catch (IOException e) {
System.out.println("写文件时有错误！"+path);
e.printStackTrace();
}finally{
if(bw!=null){
try {
bw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
public void getTableData1(String tableName) {
// String tableName="t_ad_union_user";
String sql="select * from "+tableName;
String mysqlSql="select * from "+tableName;
ResultSet rs=null;
ResultSet mysqlRs=null;
String path="c:\\"+tableName+".txt";
BufferedWriter bw=null;
mysqlConn=this.getMysqlConn();
int count=0;
long oldTime=System.currentTimeMillis();
try {
// bw=new BufferedWriter(new FileWriter(path));
rs = this.getStatement().executeQuery(sql);
//使用元数据获取一个表字段的总数
ResultSetMetaData rsmd=rs.getMetaData();
int coulum=rsmd.getColumnCount();
while(rs.next()){
String fieldSql="";
String valueSql="";
for(int i=0;i<coulum;i++){//获取mssql的字段
String columName=rsmd.getColumnName(i+1);
String field=columName;
String value=rs.getString(i+1);
if(value!=null && !value.equalsIgnoreCase("NULL")){
fieldSql+=field+",";
// valueSql+="'"+value.replaceAll("\'", "\\\\'")+"',";
valueSql+="'"+value.replaceAll("\\\\'", "\\\\\\\\'")+"',";
}
}
//生成sql语句
fieldSql=fieldSql.substring(0, fieldSql.length()-1);
valueSql=valueSql.substring(0, valueSql.length()-1);
sql="insert into "+tableName+"("+fieldSql+") "+" values("+valueSql+");";
mysqlStat.executeUpdate(sql);
// if(mysqlConn.createStatement().executeUpdate(sql)>0){
// logger.info((count++)+"success");
// }else{
// logger.info("false");
// }
//把sql语句写到文件中
// bw.write(sql+"\n");
// logger.info(sql);
}
} catch (Exception e) {
logger.error("SQLException有错误！"+sql,e);
} finally{
try {
if(mysqlStat!=null) mysqlStat.close();
if(rs!=null) rs.close();
if(bw!=null) bw.close();
if(mysqlConn!=null) mysqlConn.close();
if(conn!=null) conn.close();
} catch (Exception ex) {
logger.error("关闭conn时出错", ex);
}
}
long now=System.currentTimeMillis();
logger.info("生成完毕,共花费了"+(now-oldTime)/1000+"s时间");
}
/**
* 数据库转移到mysql上，mysqlTableName上的字段必须和msFields的字段一一对应。
* @param mysqlTableName 目标数据库表
* @param tableName 源数据库表
* @param msFields tableName表中的字段
*/
public void getTableData2(String mysqlTableName,String tableName,String[] msFields) {
// String tableName="t_ad_union_user";
String sql="select * from "+tableName;
String mysqlSql="select * from "+mysqlTableName;
ResultSet rs=null;
ResultSet mysqlRs=null;
String path="c:\\"+mysqlTableName+".txt";
BufferedWriter bw=null;
mysqlConn=this.getMysqlConn();
int count=0;
long oldTime=System.currentTimeMillis();
try {
// bw=new BufferedWriter(new FileWriter(path));
mysqlStat=mysqlConn.createStatement();
mysqlRs=mysqlStat.executeQuery(mysqlSql);
ResultSetMetaData mysqlRsmd=mysqlRs.getMetaData();
// String mysqlField="";
// int mysqlColum=mysqlRsmd.getColumnCount();
// for(int i=0;i<mysqlColum;i++){//mysql数据库的字段
// mysqlField+=mysqlRsmd.getColumnName(i+1)+",";
// }
// mysqlField=mysqlField.substring(0, mysqlField.length()-1);
rs = this.getStatement().executeQuery(sql);
//使用元数据获取一个表字段的总数
ResultSetMetaData rsmd=rs.getMetaData();
int coulum=rsmd.getColumnCount();
// logger.info("mysqlColum:"+mysqlColum+":msFields:"+msFields.length);
while(rs.next()){
String mysqlField="";
String valueSql="";
for(int i=0;i<coulum;i++){//获取mssql的字段
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(value!=null && !value.equalsIgnoreCase("NULL")){
valueSql+="'"+value.replaceAll("\\'", "\\\\'")+"',";//mssql值
mysqlField+=mysqlRsmd.getColumnName(i+1)+",";//mysql数据库的字段
}
}
}
}
//生成sql语句
valueSql=valueSql.substring(0, valueSql.length()-1);
mysqlField=mysqlField.substring(0, mysqlField.length()-1);
sql="insert into "+mysqlTableName+"("+mysqlField+") "+" values("+valueSql+");";
// mysqlStat.executeUpdate(sql);
if(mysqlStat.executeUpdate(sql)>0){
logger.info((count++)+"success");
}else{
logger.info("false");
}
// logger.info(mysqlField);
// logger.info(valueSql);
//把sql语句写到文件中
// bw.write(sql+"\n");
}
} catch (Exception e) {
logger.error("SQLException有错误！"+sql,e);
} finally{
try {
if(mysqlStat!=null) mysqlStat.close();
if(rs!=null) rs.close();
if(bw!=null) bw.close();
if(mysqlConn!=null) mysqlConn.close();
if(conn!=null) conn.close();
} catch (Exception ex) {
logger.error("关闭conn时出错", ex);
}
}
long now=System.currentTimeMillis();
logger.info("生成完毕,共花费了"+(now-oldTime)/1000+"s时间");
}
public static void main(String[] args){
// String tableName="LeaveWords";
// String tableName="SearchPrice";
// String tableName="t_site_indexed";
// String tableName="Declaration";
// String tableName="Bulletin";
// String tableName="t_login_info";
// String tableName="ss_ipnetlist";
String mysqlTableName="t_ad_union_basicData";
String msTableName="t_ad_union_basicData";
String[] msFields={"ID","createDate","statDate","alltype","uid","posid",
"type","remoteIp","isMobile"};
// String mysqlTableName="t_ad_union_userTotal_final";
// String msTableName="t_ad_union_userTotal_final";
// String[] msFields={"ID","createDate","statDate","uid","posId","clickNum",
// "showNum","clickNumOne","clickNumTwo","ipNum","aa","aa2","price"};
// String mysqlTableName="t_ad_union_userTotal";
// String msTableName="t_ad_union_userTotal";
// String[] msFields={"ID","createDate","statDate","uid","posId","clickNum",
// "showNum","clickNumOne","clickNumTwo","ipNum","aa","aa2","price"};
// String mysqlTableName="t_ad_union_kouliang";
// String msTableName="t_ad_union_kouliang";
// String[] msFields={"ID","statDate","assignRatio","deductRatio","incomeTotal","assignRatioInit",
// "deductRatioInit","incomeTotalInit","ispast","incomeTotalFinal"};
// String mysqlTableName="t_ad_union_ip_config";
// String msTableName="t_ad_union_ip_config";
// String[] msFields={"ID","userid","minQuery","maxQuery","minValue","maxValue","minTrans",
// "maxTrans"};
// String mysqlTableName="t_ad_union_cpc_config";
// String msTableName="t_ad_union_cpc_config";
// String[] msFields={"ID","userid","minCpc","maxCpc","value","beginDate","endDate"};
// String mysqlTableName="t_ad_union_adver_cpc";
// String msTableName="t_ad_union_adver_cpc";
// String[] msFields={"ID","statDate","adverName","clickNum"};
// String mysqlTableName="t_ad_union_adTotal_ip";
// String msTableName="t_ad_union_adTotal_ip";
// String[] msFields={"ID","createDate","statDate","allType","showNum","clickNum","remoteIp"};
// String mysqlTableName="t_ad_union_adTotal_allType";
// String msTableName="t_ad_union_adTotal_allType";
// String[] msFields={"ID","createDate","statDate","allType","showNum","clickNum","remoteIp"};
// String mysqlTableName="t_ad_union_ader_config";
// String msTableName="t_ad_union_ader_config";
// String[] msFields={"ID","adName","minCpa","maxCpa","value"};
// String mysqlTableName="t_ad_union_ad_income";
// String msTableName="t_ad_union_ad_income";
// String[] msFields={"ID","createDate","statDate","cpa","price","income","username"};
// String mysqlTableName="t_UnionPayMonthReport";
// String msTableName="UnionPayMonthReport";
// String[] msFields={"ID","createDate","statDate","payTime","userid","feeValue","validTotal",
// "checkStatus","payStatus","remark","confirmStatus"};
// String mysqlTableName="t_Union_UserPayMonth";
// String msTableName="Union_UserPayMonth";
// String[] msFields={"ID","createDate","statDate","userid","feeValue","validTotal",
// "checkStatus","remark","advalue"};
// String mysqlTableName="t_UnionSiteMonthReport";
// String msTableName="UnionSiteMonthReport";
// String[] msFields={"ID","createDate","statDate","sid","feeValue","validTotal"};
// String mysqlTableName="t_UnionSiteMonthReportDetail";
// String msTableName="UnionSiteMonthReportDetail";
// String[] msFields={"ID","validTotal","sid","statDate","searchType","userid","price"};
// String mysqlTableName="t_TopSites";
// String msTableName="t_TopSites";
// String[] msFields={"ID","easyName","fullName","url","sid","topKey","typeKey","orderNo","showSite"};
// String mysqlTableName="t_LeaveWords";
// String msTableName="LeaveWords";
// String[] msFields={"ID","CreateTime","Content","HasReply","ReplyContent","Email","userid",
// "ua","url","ip","uid"};
// String mysqlTableName="t_SearchPrice";
// String msTableName="SearchPrice";
// String[] msFields={"ID","MinDate","MaxDate","MinScale","MaxScale","SearchType","price","sid",
// "userid","remark"};
// String mysqlTableName="t_site_indexed";
// String msTableName="t_site_indexed";
// String[] msFields={"ID","CreateDate","reviewedDate","domainAddr","email","status","reviewed"};
// String mysqlTableName="t_Declaration";
// String msTableName="Declaration";
// String[] msFields={"id","CreateTime","title","content","dposition","userid"};
// String mysqlTableName="t_login_info";
// String msTableName="t_login_info";
// String[] msFields={"id","userid","username","loginDate","ip","loginType"};
// String mysqlTableName="t_ipnetlist";
// String msTableName="ss_ipnetlist";
// String[] msFields={"ip","ip2","createdt","netname"};
// String mysqlTableName="t_ss_sqlconfig";
// String msTableName="ss_sqlconfig";
// String[] msFields={"ID","keyword","sqlUrl","title","roles","team","isurl"};
// String mysqlTableName="t_ss_managetree";
// String msTableName="ss_managetree";
// String[] msFields={"ID","title","pid","orderId",};
// String mysqlTableName="t_ss_operator";
// String msTableName="ss_operator";
// String[] msFields={"ID","UserName","Password","Attribute","remark"};
// String mysqlTableName="t_unionsite";
// String msTableName="unionsite";
// String[] msFields={"SID","CreateTime","UpdateTime","userid","DomainAddr","DomainTitle","BottomLink",
// "BottomTitle"};
// String mysqlTableName="t_unionuser";
// String msTableName="unionuser";
// String[] msFields={"UID","CreateTime","UpdateTime","userid","password","teleNo","mobileNo",
// "IDCard","BankType","BankPlace","BankCardNo","QQ","FullName","Email","LinkFrom",
// "LastIp","UA","msn","isOpen"};
DataSql ds=new DataSql();
ds.getTableData2(mysqlTableName,msTableName,msFields);
}
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
