X-Git-Url: http://git.phpeclipse.com diff --git a/archive/net.sourceforge.phpeclipse.wiki/src/net/sourceforge/phpeclipse/wiki/sql/WikipediaDB.java b/archive/net.sourceforge.phpeclipse.wiki/src/net/sourceforge/phpeclipse/wiki/sql/WikipediaDB.java new file mode 100644 index 0000000..204d563 --- /dev/null +++ b/archive/net.sourceforge.phpeclipse.wiki/src/net/sourceforge/phpeclipse/wiki/sql/WikipediaDB.java @@ -0,0 +1,205 @@ +package net.sourceforge.phpeclipse.wiki.sql; + +import java.io.BufferedReader; +import java.io.FileNotFoundException; +import java.io.FileReader; +import java.io.IOException; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; + +import net.sourceforge.phpeclipse.wiki.editor.LineTokenizer; + +public class WikipediaDB { + + public static void dump(ResultSet rs) throws SQLException { + + // the order of the rows in a cursor + // are implementation dependent unless you use the SQL ORDER statement + ResultSetMetaData meta = rs.getMetaData(); + int colmax = meta.getColumnCount(); + int i; + Object o = null; + + // the result set is a cursor into the data. You can only + // point to one row at a time + // assume we are pointing to BEFORE the first row + // rs.next() points to next row and returns true + // or false if there is no next row, which breaks the loop + for (; rs.next();) { + for (i = 0; i < colmax; ++i) { + o = rs.getObject(i + 1); // Is SQL the first column is indexed + // with 1 not 0 + System.out.print(o.toString() + " "); + } + + System.out.println(" "); + } + } + + public static ArrayList getResultAsString(ResultSet rs) throws SQLException { + ArrayList list = new ArrayList(); + int maxProposals = 500; + // the order of the rows in a cursor + // are implementation dependent unless you use the SQL ORDER statement + ResultSetMetaData meta = rs.getMetaData(); + int colmax = meta.getColumnCount(); + int i; + Object o = null; + + // the result set is a cursor into the data. You can only + // point to one row at a time + // assume we are pointing to BEFORE the first row + // rs.next() points to next row and returns true + // or false if there is no next row, which breaks the loop + for (; rs.next();) { + for (i = 0; i < colmax; ++i) { + o = rs.getObject(i + 1); // Is SQL the first column is indexed + // with 1 not 0 + list.add(o.toString()); + maxProposals--; + if (maxProposals <= 0) { + return list; + } + } + } + return list; + } + + public static void main(String[] args) { + WikipediaDB db = null; + + try { + db = new WikipediaDB(); + } catch (Exception ex1) { + ex1.printStackTrace(); // could not start db + return; // bye bye + } + + try { + // do a query + ArrayList list = db.queryPrefix("Programming:PHP"); + // db.query("SELECT * FROM cur WHERE cur_title like 'Programming:PHP%'"); // WHERE num_col < 250"); + for (int i = 0; i < list.size(); i++) { + System.out.println(list.get(i).toString()); + } + // at end of program + db.shutdown(); + } catch (SQLException ex3) { + ex3.printStackTrace(); + } + } + +// private static void readFile(WikipediaDB db, String filename) { +// FileReader fileReader; +// try { +// BufferedReader bufferedReader = new BufferedReader(new FileReader(filename)); +// // String line; +// LineTokenizer lineTokenizer = new LineTokenizer(); +// StringBuffer line = new StringBuffer(1024); +// while (lineTokenizer.getToken(line, bufferedReader)) { +// if (line.length() == 0) { +// // this should not happen +// } else { +// // try { +// // db.update("INSERT INTO wp_titles(title) VALUES('" + line + "')"); +// System.out.println(line); +// line.delete(0, line.length()); +// // addLine(line); +// // } catch (SQLException ex3) { +// //// ex3.printStackTrace(); +// // } +// } +// } +// bufferedReader.close(); +// } catch (FileNotFoundException e) { +// // ignore this +// // TODO DialogBox which asks the user if she/he likes to build new index? +// } catch (IOException e) { +// // TODO Auto-generated catch block +// e.printStackTrace(); +// } +// } + + Connection conn; + + PreparedStatement fGetPrefixTitles; + + public WikipediaDB() throws Exception // note more general exception + { + + // Load the Database Engine JDBC driver + // mysql-connector.jar should be in the class path or made part of the current jar + Class.forName("com.mysql.jdbc.Driver"); + + // connect to the database. This will load the db files and start the + // database if it is not alread running. + // db_file_name_prefix is used to open or create files that hold the state + // of the db. + // It can contain directory names relative to the + // current working directory + conn = DriverManager.getConnection("jdbc:mysql://localhost/wikidb", // filenames + "root", // username + ""); // password + fGetPrefixTitles = conn.prepareStatement("SELECT cur_title FROM cur WHERE LOWER( cur_title ) like ?"); + } + + //use for SQL commands CREATE and SELECT + public synchronized void query(String expression) throws SQLException { + + Statement st = null; + ResultSet rs = null; + + st = conn.createStatement(); // statement objects can be reused with + // repeated calls to execute but we + // choose to make a new one each time + rs = st.executeQuery(expression); // run the query + + // do something with the result set. + dump(rs); + st.close(); // NOTE!! if you close a statement the associated ResultSet is + // closed too + // so you should copy the contents to some other object. + // the result set is invalidated also if you recycle an Statement + // and try to execute some other query before the result set has been + // completely examined. + } + + public synchronized ArrayList queryPrefix(String prefix) throws SQLException { + fGetPrefixTitles.setString(1, prefix.toLowerCase() + '%'); + ResultSet rs = null; + rs = fGetPrefixTitles.executeQuery(); // run the query + // do something with the result set. + return getResultAsString(rs); + // st.close(); // NOTE!! if you close a statement the associated ResultSet is + } + + public void shutdown() throws SQLException { + + conn.close(); // if there are no other open connection + // db writes out to files and shuts down + // this happens anyway at garbage collection + // when program ends + } + + //use for SQL commands DROP and INSERT and UPDATE + public synchronized void update(String expression) throws SQLException { + + Statement st = null; + + st = conn.createStatement(); // statements + + int i = st.executeUpdate(expression); // run the query + + if (i == -1) { + System.out.println("db error : " + expression); + } + + st.close(); + } +} \ No newline at end of file