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 index 9bbad44..753536c 100644 --- 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 @@ -1,9 +1,5 @@ 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; @@ -14,10 +10,10 @@ import java.sql.Statement; import java.util.ArrayList; import java.util.List; -import net.sourceforge.phpeclipse.wiki.editor.LineTokenizer; import net.sourceforge.phpeclipse.wiki.editor.WikiEditorPlugin; import net.sourceforge.phpeclipse.wiki.internal.ConfigurationManager; import net.sourceforge.phpeclipse.wiki.internal.IConfiguration; +import net.sourceforge.phpeclipse.wiki.preferences.Util; public class WikipediaDB { @@ -75,6 +71,29 @@ public class WikipediaDB { return list; } + public static String getFirstRow(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 + return o.toString(); + } + } + return null; + } + public static void main(String[] args) { WikipediaDB db = null; @@ -87,7 +106,7 @@ public class WikipediaDB { try { // do a query - ArrayList list = db.queryPrefix("Programming:PHP"); + ArrayList list = db.queryPrefixTexts("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()); @@ -130,9 +149,13 @@ public class WikipediaDB { // } // } - Connection conn; + private final Connection conn; + + private final PreparedStatement fGetPrefixTitles; - PreparedStatement fGetPrefixTitles; + private final PreparedStatement fGetPrefixTexts; + + private final PreparedStatement fGetExactText; public WikipediaDB() throws Exception // note more general exception { @@ -141,16 +164,20 @@ public class WikipediaDB { // mysql-connector.jar should be in the class path or made part of the current jar Class.forName("com.mysql.jdbc.Driver"); - // determine the foirst SQL configuration - List allConfigsList = ConfigurationManager.getInstance().getConfigurations(); - ArrayList configsList = new ArrayList(); + // determine the first SQL configuration IConfiguration configuration = null; - for (int i = 0; i < allConfigsList.size(); i++) { - configuration = (IConfiguration) allConfigsList.get(i); - if (configuration.getType().equals(WikiEditorPlugin.WIKIPEDIA_SQL)) { - break; + try { + List allConfigsList = ConfigurationManager.getInstance().getConfigurations(); + ArrayList configsList = new ArrayList(); + for (int i = 0; i < allConfigsList.size(); i++) { + configuration = (IConfiguration) allConfigsList.get(i); + if (configuration.getType().equals(WikiEditorPlugin.WIKIPEDIA_SQL)) { + break; + } + configuration = null; } - configuration = null; + } catch (Throwable th) { + // } // connect to the database. This will load the db files and start the @@ -164,10 +191,12 @@ public class WikipediaDB { } else { // default configuration for XAMPP distribution conn = DriverManager.getConnection("jdbc:mysql://localhost/wikidb", // filenames - "root", // username + "root", // category ""); // password } fGetPrefixTitles = conn.prepareStatement("SELECT cur_title FROM cur WHERE LOWER( cur_title ) like ?"); + fGetPrefixTexts = conn.prepareStatement("SELECT cur_text FROM cur WHERE LOWER( cur_title ) like ?"); + fGetExactText = conn.prepareStatement("SELECT cur_text FROM cur WHERE cur_title = ?"); } //use for SQL commands CREATE and SELECT @@ -191,15 +220,60 @@ public class WikipediaDB { // completely examined. } - public synchronized ArrayList queryPrefix(String prefix) throws SQLException { + public synchronized ArrayList queryPrefixTitle(String prefix) throws SQLException { fGetPrefixTitles.setString(1, prefix.toLowerCase() + '%'); ResultSet rs = null; rs = fGetPrefixTitles.executeQuery(); // run the query // do something with the result set. + ArrayList list = getResultAsString(rs); + if (list != null) { + // convert to editor format + for (int i = 0; i < list.size(); i++) { + list.set(i, Util.db2TitleLink((String) list.get(i))); + } + } + return list; + // st.close(); // NOTE!! if you close a statement the associated ResultSet is + } + + public synchronized ArrayList queryPrefixTexts(String prefix) throws SQLException { + fGetPrefixTexts.setString(1, prefix.toLowerCase() + '%'); + ResultSet rs = null; + rs = fGetPrefixTexts.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 synchronized String queryExactText(String prefix) throws SQLException { + fGetExactText.setString(1, prefix); + ResultSet rs = null; + rs = fGetExactText.executeQuery(); // run the query + // do something with the result set. + return getFirstRow(rs); + // st.close(); // NOTE!! if you close a statement the associated ResultSet is + } + + public static String getExactText(String prefix) { + WikipediaDB db = null; + + try { + db = new WikipediaDB(); + } catch (Exception ex1) { + ex1.printStackTrace(); // could not start db + return null; // bye bye + } + + try { + String text = db.queryExactText(prefix); + db.shutdown(); + return text; + } catch (SQLException ex3) { + ex3.printStackTrace(); + } + return null; + } + public void shutdown() throws SQLException { conn.close(); // if there are no other open connection