本文详细介绍了在Eclipse/MyEclipse环境下处理数据库数据，包括去除重复项、整合信息并将其导出到TXT文件，最后导入MySQL数据库的完整流程。
我知道捣鼓来捣鼓去挺麻烦的,但是,我真的想不到更好的办法,只好这样了...
一:数据库中处理数据:
数据库中当前最原始的表是data,接下来就是把data中重复的部分分离开:
create table tp select * from data group by name,xueyuan; //将以name和xueyuan为一组的数据查出来放到一个新表中.之后将data中多余的部分去掉,或者是说新建一个新表,取data和tp的差集:
create table difset select * from data where id not in (select id from tp);
这个因为数据太多,运行了老长时间呢,哈哈~
最后得到三个表:
data:所有的数据,有重复的
tp:name和xueyuan为一组的数据,没有重复的.
difset:多余的重复xueyuan和name的表,为了将有用信息放入tp里面进行整合的表,重复性不定.
二:eclipse/myeclipse中的工作:
主要是想把数据放到两个list中,通过遍历的方式将difsetlist中有用的信息放到tplist中,然后将最终的文件输出到txt文档中,最后再导入mysql数据库.
连接数据库中,刚开始想开两个connection,但是总是报错,后来发现,是因为"多个rs不能交叉使用",最后才想到了上面的方法,下面记录下my process:
1:GetData.java:(为啥没有删除那些注释,因为那些记录了我解决问题的整个过程,所以,嘻嘻~)
package com.xingyun.pi;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mysql.jdbc.Connection;
public class GetData {
public static Statement getStmt(){
Statement stmt;
try{
Class.forName("com.mysql.jdbc.Driver");//加载mysql jdbc驱动程序
}catch(Exception e){
System.out.println("Error loading mysql driver");
}
try{
Connection connect = (Connection) DriverManager.getConnection( "jdbc:mysql://localhost:3306/test?user=root&password=111111&useUnicode=true&characterEncoding=utf-8" );
stmt = connect.createStatement();
return stmt;
}catch(Exception e){
System.out.println("Error getting data");
}
return null;
}
}
2:DealWithData.java:
package com.xingyun.pi;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class DealWithData {
public static void main(String args[]) throws Exception{
Statement stmt = GetData.getStmt();
Statement stmtdif = GetData.getStmt();
ResultSet tprs = stmt.executeQuery("select * from tp");
ResultSet difsetrs = stmtdif.executeQuery("select * from difset");
List<String[]> tplist = new ArrayList<String[]>();
List<String[]> difsetlist = new ArrayList<String[]>();
while(tprs.next()){
String name = tprs.getString("name");
String gender = tprs.getString("gender");
String birth = tprs.getString("birth");
String jiguan = tprs.getString("jiguan");
String shouji = tprs.getString("shouji");
String email = tprs.getString("email");
String dianhua = tprs.getString("dianhua");
String xiantongxun = tprs.getString("xiantongxun");
String youbian = tprs.getString("youbian");
String xueli = tprs.getString("xueli");
String xueyuan = tprs.getString("xueyuan");
String xibie = tprs.getString("xibie");
String major = tprs.getString("major");
String ruxuetime = tprs.getString("ruxuetime");
String gotime = tprs.getString("gotime");
String gongzuochengshi = tprs.getString("gongzuochengshi");
String daiwei = tprs.getString("daiwei");
String danweidizhi = tprs.getString("danweidizhi");
String danweiyoubian = tprs.getString("danweiyoubian");
String gongzuodianhua = tprs.getString("gongzuodianhua");
String gongzuochuanzhen = tprs.getString("gongzuochuanzhen");
String danweixingzhi = tprs.getString("danweixingzhi");
String zhiwu = tprs.getString("zhiwu");
String zhicheng = tprs.getString("zhicheng");
String zhiji = tprs.getString("zhiji");
String beizhu1 = tprs.getString("beizhu1");
String beizhu2 = tprs.getString("beizhu2");
String url = tprs.getString("url");
String firstname = tprs.getString("first_name");
String lastname = tprs.getString("last_name");
String[] str = new String[]{name,gender,birth,jiguan,shouji,email,dianhua,xiantongxun,youbian,xueli,
xueyuan,xibie,major,ruxuetime,gotime,gongzuochengshi,daiwei,danweidizhi,danweiyoubian,
gongzuodianhua,gongzuochuanzhen,danweixingzhi,zhiwu,zhicheng,zhiji,beizhu1,beizhu2,url,
firstname,lastname};
tplist.add(str);
}
while(difsetrs.next()){
String name = difsetrs.getString("name");
String gender = difsetrs.getString("gender");
String birth = difsetrs.getString("birth");
String jiguan = difsetrs.getString("jiguan");
String shouji = difsetrs.getString("shouji");
String email = difsetrs.getString("email");
String dianhua = difsetrs.getString("dianhua");
String xiantongxun = difsetrs.getString("xiantongxun");
String youbian = difsetrs.getString("youbian");
String xueli = difsetrs.getString("xueli");
String xueyuan = difsetrs.getString("xueyuan");
String xibie = difsetrs.getString("xibie");
String major = difsetrs.getString("major");
String ruxuetime = difsetrs.getString("ruxuetime");
String gotime = difsetrs.getString("gotime");
String gongzuochengshi = difsetrs.getString("gongzuochengshi");
String daiwei = difsetrs.getString("daiwei");
String danweidizhi = difsetrs.getString("danweidizhi");
String danweiyoubian = difsetrs.getString("danweiyoubian");
String gongzuodianhua = difsetrs.getString("gongzuodianhua");
String gongzuochuanzhen = difsetrs.getString("gongzuochuanzhen");
String danweixingzhi = difsetrs.getString("danweixingzhi");
String zhiwu = difsetrs.getString("zhiwu");
String zhicheng = difsetrs.getString("zhicheng");
String zhiji = difsetrs.getString("zhiji");
String beizhu1 = difsetrs.getString("beizhu1");
String beizhu2 = difsetrs.getString("beizhu2");
String url = difsetrs.getString("url");
String firstname = difsetrs.getString("first_name");
String lastname = difsetrs.getString("last_name");
String[] str = new String[]{name,gender,birth,jiguan,shouji,email,dianhua,xiantongxun,youbian,xueli,
xueyuan,xibie,major,ruxuetime,gotime,gongzuochengshi,daiwei,danweidizhi,danweiyoubian,
gongzuodianhua,gongzuochuanzhen,danweixingzhi,zhiwu,zhicheng,zhiji,beizhu1,beizhu2,url,
firstname,lastname};
difsetlist.add(str);
}
//遍历tplist
for(int i = 0; i < tplist.size(); i++){
String name = tplist.get(i)[0];
// String gender = tplist.get(i)[1];
// String birth = tplist.get(i)[2];
// String jiguan = tplist.get(i)[3];
// String shouji = tplist.get(i)[4];
// String email = tplist.get(i)[5];
// String dianhua = tplist.get(i)[6];
// String xiantongxun = tplist.get(i)[7];
// String youbian = tplist.get(i)[8];
// String xueli = tplist.get(i)[9];
String xueyuan = tplist.get(i)[10];
// String xibie = tplist.get(i)[11];
// String major = tplist.get(i)[12];
// String ruxuetime = tplist.get(i)[13];
// String gotime = tplist.get(i)[14];
// String gongzuochengshi = tplist.get(i)[15];
// String daiwei = tplist.get(i)[16];
// String danweidizhi = tplist.get(i)[17];
// String danweiyoubian = tplist.get(i)[18];
// String gongzuodianhua = tplist.get(i)[19];
// String gongzuochuanzhen = tplist.get(i)[20];
// String danweixingzhi = tplist.get(i)[21];
// String zhiwu = tplist.get(i)[22];
// String zhicheng = tplist.get(i)[23];
// String zhiji = tplist.get(i)[24];
// String beizhu1 = tplist.get(i)[25];
// String beizhu2 = tplist.get(i)[26];
// String url = tplist.get(i)[27];
// String firstname = tplist.get(i)[28];
// String lastname = tplist.get(i)[29];
for(int j = 0; j < difsetlist.size(); j++){
if(difsetlist.get(j)[10]!=null && difsetlist.get(j)[0]!=null){
if(difsetlist.get(j)[1]!=null && tplist.get(i)[1]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[1] = difsetlist.get(j)[1];
}
if(difsetlist.get(j)[2]!=null && tplist.get(i)[2]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[2] = difsetlist.get(j)[2];
}
if(difsetlist.get(j)[3]!=null && tplist.get(i)[3]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[3] = difsetlist.get(j)[3];
}
if(difsetlist.get(j)[4]!=null && tplist.get(i)[4]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[4] = difsetlist.get(j)[4];
}
if(difsetlist.get(j)[5]!=null && tplist.get(i)[5]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[5] = difsetlist.get(j)[5];
}
if(difsetlist.get(j)[6]!=null && tplist.get(i)[6]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[6] = difsetlist.get(j)[6];
}
if(difsetlist.get(j)[7]!=null && tplist.get(i)[7]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[7] = difsetlist.get(j)[7];
}
if(difsetlist.get(j)[8]!=null && tplist.get(i)[8]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[8] = difsetlist.get(j)[8];
}
if(difsetlist.get(j)[9]!=null && tplist.get(i)[9]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[9] = difsetlist.get(j)[9];
}
if(difsetlist.get(j)[29]!=null && tplist.get(i)[29]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[29] = difsetlist.get(j)[29];
}
if(difsetlist.get(j)[11]!=null && tplist.get(i)[11]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[11] = difsetlist.get(j)[11];
}
if(difsetlist.get(j)[12]!=null && tplist.get(i)[12]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[12] = difsetlist.get(j)[12];
}
if(difsetlist.get(j)[13]!=null && tplist.get(i)[13]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[13] = difsetlist.get(j)[13];
}
if(difsetlist.get(j)[14]!=null && tplist.get(i)[14]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[14] = difsetlist.get(j)[14];
}
if(difsetlist.get(j)[15]!=null && tplist.get(i)[15]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[15] = difsetlist.get(j)[15];
}
if(difsetlist.get(j)[16]!=null && tplist.get(i)[16]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[16] = difsetlist.get(j)[16];
}
if(difsetlist.get(j)[17]!=null && tplist.get(i)[17]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[17] = difsetlist.get(j)[17];
}
if(difsetlist.get(j)[18]!=null && tplist.get(i)[18]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[18] = difsetlist.get(j)[18];
}
if(difsetlist.get(j)[19]!=null && tplist.get(i)[19]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[19] = difsetlist.get(j)[19];
}
if(difsetlist.get(j)[20]!=null && tplist.get(i)[20]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[20] = difsetlist.get(j)[20];
}
if(difsetlist.get(j)[21]!=null && tplist.get(i)[21]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[21] = difsetlist.get(j)[21];
}
if(difsetlist.get(j)[22]!=null && tplist.get(i)[22]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[22] = difsetlist.get(j)[22];
}
if(difsetlist.get(j)[23]!=null && tplist.get(i)[23]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[23] = difsetlist.get(j)[23];
}
if(difsetlist.get(j)[24]!=null && tplist.get(i)[24]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[24] = difsetlist.get(j)[24];
}
if(difsetlist.get(j)[25]!=null && tplist.get(i)[25]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[25] = difsetlist.get(j)[25];
}
if(difsetlist.get(j)[26]!=null && tplist.get(i)[26]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[26] = difsetlist.get(j)[26];
}
if(difsetlist.get(j)[27]!=null && tplist.get(i)[27]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[27] = difsetlist.get(j)[27];
}
if(difsetlist.get(j)[28]!=null && tplist.get(i)[28]==null
&& difsetlist.get(j)[10].equals(xueyuan) && difsetlist.get(j)[0].equals(name)){
tplist.get(i)[28] = difsetlist.get(j)[28];
}
}
}
}
// Statement fstm = GetData.getStmt();
//遍历difsetlists
for(int i = 0; i < tplist.size(); i++){
String name = tplist.get(i)[0];
String gender = tplist.get(i)[1];
String birth = tplist.get(i)[2];
String jiguan = tplist.get(i)[3];
String shouji = tplist.get(i)[4];
String email = tplist.get(i)[5];
String dianhua = tplist.get(i)[6];
String xiantongxun = tplist.get(i)[7];
String youbian = tplist.get(i)[8];
String xueli = tplist.get(i)[9];
String xueyuan = tplist.get(i)[10];
String xibie = tplist.get(i)[11];
String major = tplist.get(i)[12];
String ruxuetime = tplist.get(i)[13];
String gotime = tplist.get(i)[14];
String gongzuochengshi = tplist.get(i)[15];
String daiwei = tplist.get(i)[16];
String danweidizhi = tplist.get(i)[17];
String danweiyoubian = tplist.get(i)[18];
String gongzuodianhua = tplist.get(i)[19];
String gongzuochuanzhen = tplist.get(i)[20];
String danweixingzhi = tplist.get(i)[21];
String zhiwu = tplist.get(i)[22];
String zhicheng = tplist.get(i)[23];
String zhiji = tplist.get(i)[24];
String beizhu1 = tplist.get(i)[25];
String beizhu2 = tplist.get(i)[26];
String url = tplist.get(i)[27];
String firstname = tplist.get(i)[28];
String lastname = tplist.get(i)[29];
File f = new File("D:\\data3.txt");
try{
BufferedOutputStream bos =
new BufferedOutputStream(new FileOutputStream(f,true));
String info = name+"|"+gender+"|"+birth+"|"+jiguan+"|"+shouji+"|"+email+"|"+dianhua+"|"+xiantongxun+"|"+youbian
+"|"+xueli+"|"+xueyuan+"|"+xibie+"|"+major+"|"+ruxuetime+"|"+gotime+"|"+gongzuochengshi+"|"+
daiwei+"|"+danweidizhi+"|"+danweiyoubian+"|"+gongzuodianhua+"|"+gongzuochuanzhen+"|"+danweixingzhi+"|"+
zhiwu+"|"+zhicheng+"|"+zhiji+"|"+beizhu1+"|"+beizhu2+"|"+url+"|"+firstname+"|"+lastname+"\r\n" ;
bos.write(info.getBytes(),0,info.getBytes().length);
bos.flush();
bos.close();
info="";
}catch(IOException e){
System.out.println(e);
}
// System.out.println(name+","+gender+","+birth+","+jiguan+","+shouji+","+email+","+dianhua+","+xiantongxun+","+youbian
// +","+xueli+","+xueyuan+","+xibie+","+major+","+ruxuetime+","+gotime+","+gongzuochengshi+","+
// daiwei+","+danweidizhi+","+danweiyoubian+","+gongzuodianhua+","+gongzuochuanzhen+","+danweixingzhi+","+
// zhiwu+","+zhicheng+","+zhiji+","+beizhu1+","+beizhu2+","+url+","+firstname+","+lastname);
// stmt.execute("insert into fdata(name,gender,birth,jiguan,shouji,email,dianhua,xiantongxun,youbian,xueli," +
// "xueyuan,xibie,major,ruxuetime,gotime,gongzuochengshi,daiwei,danweidizhi,danweiyoubian,gongzuodianhua," +
// "gongzuochuanzhen,danweixingzhi,zhiwu,zhicheng,zhiji,beizhu1,beizhu2,url,firstname,lastname " +
// " values("+name+","+gender+","+birth+","+jiguan+","+shouji+","+email+","
// +dianhua+","+xiantongxun+","+youbian+","+xueli+","+xueyuan+","+xibie+","+major+","+ruxuetime+","+
// gotime+","+gongzuochengshi+","+daiwei+","+danweidizhi+","+danweiyoubian+","+gongzuodianhua+
// ","+gongzuochuanzhen+","+danweixingzhi+","
// +zhiwu+","+zhicheng+","+zhiji+","+beizhu1+","+beizhu2+","+url+","+firstname+","+lastname);
// stmt.execute("insert into haha(name) " +
// " values("+name+")");
}
/**
* 放入Map中遍历,有点麻烦
*/
// while(tprs.next()){
// Map<String,String> map = new HashMap<String,String>();
// Map<Map<String,String>,String> damap = new HashMap<Map<String,String>,String>();
// String name = tprs.getString("name");
// String college = tprs.getString("college");
// String work = tprs.getString("work");
// map.put(name, college);
// damap.put(map, work);
// tplist.add(damap);
// }
//
// while(difsetrs.next()){
// Map<String,String> map = new HashMap<String,String>();
// Map<Map<String,String>,String> damap = new HashMap<Map<String,String>,String>();
// String name = difsetrs.getString("name");
// String college = difsetrs.getString("college");
// String work = difsetrs.getString("work");
// map.put(name, college);
// damap.put(map, work);
// difsetlist.add(damap);
// }
//
// for(int i = 0;i < tplist.size(); i++){
//// System.out.println(tplist.get(i).get(0).indexOf(1));
// Map<Map<String, String>, String> commap = tplist.get(i);
// Set<Entry<Map<String, String>, String>> set = commap.entrySet();
// Iterator<Entry<Map<String, String>, String>> it = set.iterator();
// while (it.hasNext()){
// Map.Entry mapentry = (Map.Entry)it.next();
// System.out.println(mapentry.getKey());
// }
// System.out.println(tplist.get(i));
// }
// System.out.println("1111111111");
// for(int i = 0;i < difsetlist.size(); i++){
// System.out.println(difsetlist.get(i));
// }
/**
* 多个rs不能交叉使用
*/
// try {
// ResultSet tprs = stmt.executeQuery("select * from tp");
// while (tprs.next()){
// ResultSet difsetrs = stmtdif.executeQuery("select * from difset");
// String name = tprs.getString("name");
// String college = tprs.getString("college");
//
// while(difsetrs.next()){
// String names = difsetrs.getString("name");
// String colleges = difsetrs.getString("college");
// if(name.equals(names) && college.equals(colleges)){
// String work = difsetrs.getString("work");
// if(work != null){
// System.out.println(difsetrs.getString(4));
//// stmt.execute("update tp set work ='" + work +"' where name ='ningning'");
//// System.out.println(111);
// stmt.execute("update tp set work ='" + work +"' where name ='" +names+"'"+
// " and college ='"+colleges+"'");
//// tprs.updateString(4, difsetrs.getString(4));
// }
// System.out.println(name);
// }
// }
//// System.out.println(rs.getString("name"));
//// System.out.println(rs.getString(2));
// }
// } catch (SQLException e) {
// e.printStackTrace();
// }
}
}
三:导入数据库:
通过上面的方法,经过5,6分钟的时间,已经将整理好的数据放到了data.txt里面,接下来就是怎样将txt数据导入到mysql中:
当然,导入之前要新建一个一样结构的表:
create table finedata select * from tp where 1=2;
之后导入数据:
load data local infile 'D:\data.txt' into table alumnus fields terminated by '|'; //输入到txt文件的时候是用'|'分开的,所以导入的时候要用|最后完成了,哈哈
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
