SQLHelper

A common need in plugins is storing data. Oftentimes, this can be done through config files. But other times, you need a database. We all know that working with Java's standard SQL library is a pain, which is why SQLHelper is here to make things easier for you.

To start with, you can easily open a connection to a database using SQLHelper by calling one of the static open methods. For example, if you wanted to open a connection to a SQLite database in your plugin's data folder, you could do so like this:

Connection connection = SQLHelper.openSQLite(pluginHere.getDataFolder().toPath().resolve("database.db"));

But once you have a Connection, working with SQL is still annoying. That's why you can wrap the Connection with a SQLHelper:

SQLHelper sql = new SQLHelper(connection);

And once you've done that, you can easily execute SQL commands and queries. To execute a command, simply call the execute method like so:

sql.execute("CREATE TABLE IF NOT EXISTS people (age INT, firstName STRING, lastName STRING);");

This will make the SQL call and return nothing. If you need to make a call that uses prepared statement fields, you can do that by calling the same method and passing more arguments:

sql.execute("UPDATE people SET age=? WHERE firstName=?;", 15, "JOHN");

And now, this will create a PreparedStatement, set the fields to the passed values in order, and execute the command.

If the underlying Connection throws a SQLException, it will be propagated as a runtime exception rather than a checked exception, so you're still given the same safety and can choose to catch it if you want to.

If you want to query a single column in a single row, you can call the querySingleResult method:

int age = sql.querySingleResult("SELECT age FROM people WHERE firstName=?;", "JOHN");

And you can also get a List of any supported type by calling queryResultList:

List<Integer> ages = sql.queryResultList("SELECT age FROM people;");

Lastly, you can get a Results object by calling queryResults:

Results results = sql.queryResults("SELECT * FROM people;");

A Results object is just a wrapped ResultSet with some handy methods. Its get method is generic, and will infer the return type, similarly to querySingleResult. It calls next() automatically once, meaning you can skip the call and immediately start getting data from it. If you need to ensure it's not empty, you can call isEmpty(), which will tell you if the initial call to next() returned false. Since it calls next() immediately, you can't do what you would normally do with a ResultSet:

ResultSet results; //Assume this variable is assigned a value
while (results.next()) {
	int i = results.getInt(1);
}

Instead, you should use the forEach method:

Results results; //Assume this variable is assigned a value
results.forEach(r -> {
	int i = r.get(1);
	//Call getString if it's a String column, otherwise it could return the wrong type (if the column is STRING type but returns a value parsable to an int)
	String s = r.getString(2);
});

And just like a ResultSet, once you're done with it, you should call close(). The same applies to a SQLHelper; calling close() on either of them will close the underlying object they wrap.

Read about SQL Caches here.