MySQL with JDBC

As part of integrating Lucene with my Ruby on Rails project, I needed to get MySQL working with Java on my server. Following are the summarized steps I took to make my first query:

1. Download MySQL Connector J
2. After downloading, extract the archive

tar -zxvf mysql-connector-java-3.1.12.tar.gz

3. Copy the jar file to your class path. If using bash, modify .bashrc and place the class path there. Remember to export the classpath so it's available to your current session.

4. Create a connect function (The following is a crude example. Modify before usage). Also ensure you have a property named con of type Connection defined.

public static void Connect()
throws Exception {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception E) {
System.err.println("Unable to load MySQL driver");
E.printStackTrace();
}
try {
String url = "jdbc:mysql://127.0.0.1/db";
con = DriverManager.getConnection(url, "hosting", "flores0050");
}
catch (SQLException E) {
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}
}

Then in your app do something like:

try { Connect(); }
catch (Exception e){ System.out.println("ERROR in connection" + e.toString() ); }

Statement statement = con.createStatement();
ResultSet RS = statement.executeQuery ("SELECT * FROM " + " table1 , table2 WHERE table1.id = table2.foreign_id " );
while (RS.next()) {
// ...
//RS.getString("id")
//RS.getString(1)
}
con.close();
RS.close();
statement.close();

Some errors:
If you get the error:

SQLException: No suitable driver
SQLState: 08001
VendorError: 0

Then make sure you have the right database settings (URL) and that the driver (connector J) is in your class path)

If you get the error:

SQLException: Before start of result set

Then check out my post on my Ruby on Rails blog. Basically you need to make sure you are calling the first() or next() function before using your retrieved result set.

Other notes:
- Instead of relying on MySQL to provide Full Text Searches for your application, upgrade to a more robust (and fast) Full Text search solution like Lucene. I have always wondered why something like Lucene can't be integrated with MySQL (may be with a special engine).
- My wife has a new blog where she comments about everything/anything she wants to talk about.
- If using Lucene in Action Book by Erik Hatcher, make sure you are not using deprecated code (the book has great info but seems to be full of deprecated code)
- Markus has a post on event and table logging test
- Sheeri has a cool post about MySQL inheritance that I would like to check out in near future.