1 import java.io.BufferedReader;
 2 import java.io.File;
 3 import java.io.FileReader;
 4 import java.io.IOException;
 5 import java.io.InputStreamReader;
 6 import java.sql.Connection;
 7 import java.sql.DriverManager;
 8 import java.sql.PreparedStatement;
 9 import java.sql.ResultSet;
10 import java.sql.SQLException;
11 import java.sql.Statement;
12 import java.util.ArrayList;
13 import java.util.List;
14 import org.jdom.JDOMException;
15 
16 public class readTh {
17     public static void main(String args[]) throws IOException, SQLException, JDOMException , JDOMException, IOException{
18         String driver = "com.mysql.jdbc.Driver";
19         String url = "jdbc:mysql://localhost:3306/book"; 
20         String userName = "root";
21         String passWd = "123456";
22         Connection conn = null;
23         try {
24             try {
25                 Class.forName(driver);
26             } catch (ClassNotFoundException e) {
27                 // TODO Auto-generated catch block
28                 e.printStackTrace();
29             }
30             conn = DriverManager.getConnection(url, userName, passWd);
31             
32         } catch (SQLException e) {
33             // TODO Auto-generated catch block
34             e.printStackTrace();
35         }
36         List<String> list = new ArrayList<String>();
37         String sql1 = "select distinct qid from qrel13; ";
38         Statement stm = conn.createStatement();
39         ResultSet rset = stm.executeQuery(sql1);
40         while(rset.next()){
41             list.add(rset.getString(1));
42         }
43         for(String tmp:list){
44             
45             String sql3 = "select count(num) from bsorg13 where bookId in(select bookId from qrel13 where qid="+tmp+")and qid="+tmp+";";
46             Statement stm3 = conn.createStatement();
47             ResultSet rset3 = stm3.executeQuery(sql3);
48             String count1 = null;
49             while(rset3.next()){
50                  count1 = rset3.getString(1);
51             }
52         
53             String sql2 = "select count(num) from bsorg5000 where bookId in(select bookId from qrel13 where qid="+tmp+")and qid="+tmp+";";
54             Statement stm2 = conn.createStatement();
55             ResultSet rset2 = stm2.executeQuery(sql2);
56             while(rset2.next()){
57                 String count2 = rset2.getString(1);
58                 System.out.println(tmp+"    "+count2+"    "+count1);
59             }
60         }    
61         
62         File file = new File("/home/13.topics");
63         BufferedReader bf = new BufferedReader(new FileReader(file));
64         String tmpLine = null;
65         while((tmpLine = bf.readLine())!=null){
66             String[] id = tmpLine.split(" ");
67             String qid = id[0];
68             String q0 = id[1];
69             String bookId = id[2];
70             String sim = id[3];
71             String sql = "insert into qrel13(qid,q0,bookId,sim) values(?,?,?,?)";
72             PreparedStatement ps = conn.prepareStatement(sql);
73             ps.setString(1, qid);
74             ps.setString(2, q0);
75             ps.setString(3, bookId);
76             ps.setString(4, sim);
77             ps.addBatch();
78             ps.execute();
79             ps.close();
80         }
81 
82     }
83 }