Valhalla Legends Forums Archive | Java Programming | JDBC for idiots?

AuthorMessageTime
Camel
I've been working on a new battle.net bot - essentially, a ground-up port of my old bot - that uses JDBC to access a MySQL database.

I have a (singleton) Database class with the responsibility of generating <java.sql.ResultSet>s for various types of queries. Here's a simple example:
[code] public ResultSet getTriviaLeaders() throws SQLException {
return createStatement().executeQuery("SELECT * FROM `account` WHERE `trivia_correct` > 0 ORDER BY `trivia_correct` DESC LIMIT 10");
}[/code]
A slightly more complex function granted by the same class:
[code] public ResultSet getMail(long accountID) throws SQLException {
PreparedStatement ps = prepareStatement("SELECT M.`id`, M.`sent`, A.`name`, M.`read`, M.`message` FROM `mail` AS M JOIN `account` AS A ON (A.`id` = M.`from`) WHERE M.`to`=? ORDER BY M.`id` ASC");
ps.setLong(1, accountID);
return ps.executeQuery();
}[/code]

I directly reference these <ResultSet>s directly from my application code to streamline the source. I often use <ResultSet>s to do UPDATEs and DELETEs. Now, my problem is that the compatible JDBC drivers are limited to those that support ResultSet updates and deletes. I'm using MySQL, so it's no issue there, but to get the bot in to the hands of the average user, I need to figure out how to make setup as simple as a few clicks.

Are there any JDBC drivers that meet these requirements and also are able to store data to a file? I've looked in to ODBC->CSV, but that's out becuase it's hugely non-SQL compliant, and doesn't support modification of any kind. SQLite is out because the only driver I could find for it was less SQL-compliant than the ODBC/CSV path. DB2 sounds promising, but I haven't ever used it as a storage medium before.
August 6, 2007, 6:01 AM
Camel
I answered my own question - Apache Derby seems to be the best balance of features and weight. The JAR is 2MB, and it supports everything I need. Has a few strange quirks to it, but nothing I couldn't work out in a day. Its string comparison functions are case sensitive, unlike MySQL.
August 9, 2007, 6:28 AM
unTactical
Why not just include a sql script that sets up the databases on installation/first-run?
August 30, 2007, 7:20 PM
Camel
I have one of those; it' isn't a solution to this problem. Did you read my post? I was asking what JDBC drivers support updateRow and deleteRow.

http://bnubot.googlecode.com/svn/trunk/BNUBot/schema.mysql
http://bnubot.googlecode.com/svn/trunk/BNUBot/schema.derby
August 31, 2007, 7:11 PM
unTactical
I must have missed that part :)
September 6, 2007, 4:39 PM

Search