alumnus system-前期数据处理(二)

我知道捣鼓来捣鼓去挺麻烦的,但是,我真的想不到更好的办法,只好这样了...

一:数据库中处理数据:

数据库中当前最原始的表是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文件的时候是用'|'分开的,所以导入的时候要用|

最后完成了,哈哈