1 package net.sourceforge.phpeclipse.wiki.sql;
3 import java.io.BufferedReader;
4 import java.io.FileNotFoundException;
5 import java.io.FileReader;
6 import java.io.IOException;
7 import java.sql.Connection;
8 import java.sql.DriverManager;
9 import java.sql.PreparedStatement;
10 import java.sql.ResultSet;
11 import java.sql.ResultSetMetaData;
12 import java.sql.SQLException;
13 import java.sql.Statement;
14 import java.util.ArrayList;
16 import net.sourceforge.phpeclipse.wiki.editor.LineTokenizer;
18 public class WikipediaDB {
20 public static void dump(ResultSet rs) throws SQLException {
22 // the order of the rows in a cursor
23 // are implementation dependent unless you use the SQL ORDER statement
24 ResultSetMetaData meta = rs.getMetaData();
25 int colmax = meta.getColumnCount();
29 // the result set is a cursor into the data. You can only
30 // point to one row at a time
31 // assume we are pointing to BEFORE the first row
32 // rs.next() points to next row and returns true
33 // or false if there is no next row, which breaks the loop
35 for (i = 0; i < colmax; ++i) {
36 o = rs.getObject(i + 1); // Is SQL the first column is indexed
38 System.out.print(o.toString() + " ");
41 System.out.println(" ");
45 public static ArrayList getResultAsString(ResultSet rs) throws SQLException {
46 ArrayList list = new ArrayList();
47 int maxProposals = 500;
48 // the order of the rows in a cursor
49 // are implementation dependent unless you use the SQL ORDER statement
50 ResultSetMetaData meta = rs.getMetaData();
51 int colmax = meta.getColumnCount();
55 // the result set is a cursor into the data. You can only
56 // point to one row at a time
57 // assume we are pointing to BEFORE the first row
58 // rs.next() points to next row and returns true
59 // or false if there is no next row, which breaks the loop
61 for (i = 0; i < colmax; ++i) {
62 o = rs.getObject(i + 1); // Is SQL the first column is indexed
64 list.add(o.toString());
66 if (maxProposals <= 0) {
74 public static void main(String[] args) {
75 WikipediaDB db = null;
78 db = new WikipediaDB();
79 } catch (Exception ex1) {
80 ex1.printStackTrace(); // could not start db
86 ArrayList list = db.queryPrefix("Programming:PHP");
87 // db.query("SELECT * FROM cur WHERE cur_title like 'Programming:PHP%'"); // WHERE num_col < 250");
88 for (int i = 0; i < list.size(); i++) {
89 System.out.println(list.get(i).toString());
93 } catch (SQLException ex3) {
94 ex3.printStackTrace();
98 // private static void readFile(WikipediaDB db, String filename) {
99 // FileReader fileReader;
101 // BufferedReader bufferedReader = new BufferedReader(new FileReader(filename));
103 // LineTokenizer lineTokenizer = new LineTokenizer();
104 // StringBuffer line = new StringBuffer(1024);
105 // while (lineTokenizer.getToken(line, bufferedReader)) {
106 // if (line.length() == 0) {
107 // // this should not happen
110 // // db.update("INSERT INTO wp_titles(title) VALUES('" + line + "')");
111 // System.out.println(line);
112 // line.delete(0, line.length());
114 // // } catch (SQLException ex3) {
115 // //// ex3.printStackTrace();
119 // bufferedReader.close();
120 // } catch (FileNotFoundException e) {
122 // // TODO DialogBox which asks the user if she/he likes to build new index?
123 // } catch (IOException e) {
124 // // TODO Auto-generated catch block
125 // e.printStackTrace();
131 PreparedStatement fGetPrefixTitles;
133 public WikipediaDB() throws Exception // note more general exception
136 // Load the Database Engine JDBC driver
137 // mysql-connector.jar should be in the class path or made part of the current jar
138 Class.forName("com.mysql.jdbc.Driver");
140 // connect to the database. This will load the db files and start the
141 // database if it is not alread running.
142 // db_file_name_prefix is used to open or create files that hold the state
144 // It can contain directory names relative to the
145 // current working directory
146 conn = DriverManager.getConnection("jdbc:mysql://localhost/wikidb", // filenames
149 fGetPrefixTitles = conn.prepareStatement("SELECT cur_title FROM cur WHERE LOWER( cur_title ) like ?");
152 //use for SQL commands CREATE and SELECT
153 public synchronized void query(String expression) throws SQLException {
158 st = conn.createStatement(); // statement objects can be reused with
159 // repeated calls to execute but we
160 // choose to make a new one each time
161 rs = st.executeQuery(expression); // run the query
163 // do something with the result set.
165 st.close(); // NOTE!! if you close a statement the associated ResultSet is
167 // so you should copy the contents to some other object.
168 // the result set is invalidated also if you recycle an Statement
169 // and try to execute some other query before the result set has been
170 // completely examined.
173 public synchronized ArrayList queryPrefix(String prefix) throws SQLException {
174 fGetPrefixTitles.setString(1, prefix.toLowerCase() + '%');
176 rs = fGetPrefixTitles.executeQuery(); // run the query
177 // do something with the result set.
178 return getResultAsString(rs);
179 // st.close(); // NOTE!! if you close a statement the associated ResultSet is
182 public void shutdown() throws SQLException {
184 conn.close(); // if there are no other open connection
185 // db writes out to files and shuts down
186 // this happens anyway at garbage collection
190 //use for SQL commands DROP and INSERT and UPDATE
191 public synchronized void update(String expression) throws SQLException {
195 st = conn.createStatement(); // statements
197 int i = st.executeUpdate(expression); // run the query
200 System.out.println("db error : " + expression);