A class for SQLite and MySQL queries

I made this a short long time ago while developing a plugin, got a few headaches at the SQLite part but it generally works.
I posted it for anyone interested in a single class that can handle both SQLite and MySQL.
It wasn't widely tested tough, so if you use it and find anything wrong, please post it so I can update it :}

This still requires you to know MySQL or SQLite syntax for queries, they're kinda the same but if something goes wrong you can always google

Download class: http://forums.bukkit.org/attachments/db-zip.7845/

Usage:

Code:

// for MySQL (plugin, host, database, user, password)
Db db = new Db(this, "localhost", "storage", "root", "");

db.query("CREATE TABLE IF NOT EXISTS `minecraft` (`player` VARCHAR(16) NOT NULL, `money` INT(10) unsigned NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;");

// for SQLite (plugin, file path)
Db db = new Db(this, getDataFolder() + "/storage.db");

db.query("CREATE TABLE IF NOT EXISTS `minecraft` (`player` TEXT PRIMARY KEY, `money` INTEGER)");

// ..

db.query("INSERT INTO `minecraft`(`player`,`money`) VALUES('Digi', 10)");

// ...

ResultSet result = db.query("SELECT `player`,`money` FROM `storage` WHERE `player` = 'Digi'");

String name = db.resultString(result, 1);
int money = db.resultInt(result, 2);

Really useful Digi :3

And totally confusing for anyone who doesn't realize the class needs to be downloaded.
This was posted before the attachments disappeared from view, I've edited and added the link to the attachment

LOL thanks mate xD was looking at it earlier and was like... lol wut?

I've posted this last year, in december, and it's got noticed just now (Getting likes and replies) ? =)

Anyway, feel free to post if there are any issues with it, as I've already mentioned, not widely tested.

And also, be aware of SQL injection (google it if you're not familiar with what it is).

Does that involve using PreparedStatements instead of using straight MySQL queries? So like:

Code:

            PreparedStatement prep = mysql
                    .prepare("INSERT INTO tribedata (name, chief, maker, spawnX, spawnY, spawnZ, world, spawnPitch, spawnYaw, joinMsg, bonusChunks, civilisation) VALUES "
                            + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
            prep.setString(1, tribe.getName());
            prep.setString(2, tribe.getChief());
            prep.setString(3, tribe.getMaker());
            prep.setDouble(4, tribe.getSpawn().x);
            prep.setDouble(5, tribe.getSpawn().y);
            prep.setDouble(6, tribe.getSpawn().z);
            prep.setString(7, tribe.getSpawn().world.getName());
            prep.setFloat(8, tribe.getSpawn().pitch);
            prep.setFloat(9, tribe.getSpawn().yaw);
            prep.setString(10, tribe.getJoinMsg());
            prep.setInt(11, tribe.getBonusChunks());
            prep.setString(12, "None");
            prep.executeUpdate();

As opposed to doing it all in 1 mysql.query("blabla");?

@theguynextdoor
Yeah, this class tough is more for quick queries that you know values are safe... but you can always change it to your liking, I guess I should add a method for safe queries... or something :}

I just want to thank you. I've been searching for months on how to implement SQLite, and feel stupid now that I know how simple it is....lol. I've also been doing massive amounts of work building my own MySQL constructor classes.... This makes everything simple and cuts down alot of work. so... Thank You.

Could I suggest you allow logging in without passwords like this.

Code:

if(password == "")
    url = "jdbc:mysql://" + host + "/" + database + "?user=" + user;
else
    url = "jdbc:mysql://" + host + "/" + database + "?user=" + user + "&password=" + password;

Or if you wanted it shorter you could use a ternary.

Code:

url = "jdbc:mysql://" + host + "/" + database + "?user=" + user + (password == "" ? "" : "&password=" + password);

Im using the ternary and have used the if statement perfectly. Other than that great work and I will be using this in my plugins

I don't think you can compare empty strings with == (apart from non-empty strings which I know for sure you can't compare), have you tested that ?
And also, does the MySQL server reject the connection if you enter the password parameter blank ?

I guess it would be better practice to do password.equals("") but the == does work. It does reject the connection but it looks like it removes the user for some reason. I was getting this error before I added the ternary.

Code:

MySQL Error:
Database query error: Access denied for user ''@'localhost' to database 'elchilen0'
Java Code:
new SQL(this, "localhost", "elchilen0", "elchilen0", "");

Looks like I'll use that for a SQLite db.
But I got a question because I'm pretty new to SQL.

I copied your example and I'm getting this:

Code:

19:31:27  Database result error: ResultSet closed
19:31:27 [INFO] Digi 0

Code:java

 
ResultSet result = db.query("SELECT `player`,`money` FROM `minecraft` WHERE `player` = 'Digi'");
 
String name = db.resultString(result, 1);
int money = db.resultInt(result, 2);
 
System.out.println(name + " " + money);
 

Aside from being more secure, prepared statements tend to execute faster, especially if you're using the same query often, so they should probably always be used, unless you have a good reason not to.

@wesleydeman
Your 'db' object once created and connected to database is alive all the time (almost, however you know java, yh?). Well, it is enough to call it once in your onEnable().

How would I check if there is no result to my query? Like if I did a query to get a player's balance but there is no balance, how would I see that it is null? All that prints to my console is a nasty looking database error if it returns null.

Would a simple: result.wasNull(); do? (Where result is the ResultSet)

Code:

if (result.next()) {
        // here you know that there is at least one record, which is what your question was about
} else {
    // here you do whatever needs to be done when there is no record
}

@weslydeman

Sounds good, however whenever I define the result class in the first place it errors for me cause it querys when it defines it or something. Ill try your code out now but when I try to debug it, the SQL error comes from the definition of result. Not another part of code.

Post your SQL query please.

@wesleydeman
Figured it out, it works fine now. Thank you for your help.

I was passing a null variable to a non-null field without realizing it.

Edit: Another error I am getting is from a custom event I am creating.

I am passing the ID of a sign (the second line) through to my MySQL tables, so when I click a button, I see the target, pass it to a spawn event (custom), listen for it with a custom listener, it spawns a piece of cake 2 blocks above it. Currently when I push the button, after seeing that the target is correctly passed, I get the following error for when I search for my location x,y,z values in my table:[SEVERE] Database result error: Operation not allowed after ResultSet closed

How to close Connection and Statement in that code?
if i use only
base = new Db(this, getDataFolder() + File.separator + "SQLite.db");