I always wondered why in Bukkit, there was such a lack of plugins using H2 or any other lightweight database that can be loaded in memory rather than SQLite. Why do devs prefer SQLite, is there some sort of advantage that I never understood? I’ve always hated it because of the way it handles typing.
i only know how to use sql
1 Like
My best guess would be that most servers only used SQL. Think about it, 99% of server admins have probably never heard of h2 or any database other than SQL. I think it would be nice to have plugins that support better/alternative databases.
1 Like
I would like it if Sponge had any API to make using SQL easier. I’ve never had much luck with that when it came to Java. Doing it via PHP was usually much more preferable to me, but obviously not gonna go sticking PHP in Java and expect that to work, so I’ve just strayed away from trying to use SQL at all in my plugins.
Or interfacing with any remote or flat-file storage of similar natures, I should add, considering the OP XD
1 Like
if you know how to write a MySQL query, you know how to write a H2 query 
More or less, I haven’t even heard of h2 until support for it was added to some plugin that I had on my server.
Are you talking about PDO? Java already has something similar with supports for multiple dbs natively, it’s called JDBC. You prepare a Statement, execute it and get a ResultSet, not very different than PHP. What would you like to see in this API? Something like the Laravel Query Builder maybe?
Are you talking about SQL or SQLite. One is a language, the other is a database.
Maybe too tired, but honestly I’ve no idea. I’ve tried figuring that stuff out on Java several times, and just doesn’t agree with me.
Supporting multiple databases is not as trivial as switching the Connection providers, which is probably why a lot of people didn’t bother with other databases.
SQL varies a lot between different vendors and it’s pretty easy to accidentally write SQL specific to a database flavor.
jOOQ is a nice SQL DSL for Java with support for different flavors. However, it can’t generate table creation statements as far as I know.
4 Likes
// Loads the jdbc driver
Class.forName("com.mysql.jdbc.Driver");
// Setup the connection
String url = "jdbc:mysql://localhost/";
String user = "user";
String pass = "pass";
Connection conn = DriverManager.getConnection(url,user,pass);
// Prepare your statement and execute
Statement stmt = conn.createStatement();
String sql = "SELECT id FROM Users";
ResultSet results = stmt.executeQuery(sql);
while(results.next()){
int id = results.getInt("id");
System.out.println("ID: " + id);
}
1 Like
How does jOOQ fare in memory intensive tasks against optimized queries. I know this is Minecraft, most plugin DBs will have less than a thousand lines, but in a real world environment with a few millions lines, is it efficient?
With some libaries no SQL Code is needed (!), e.g. Ebean, Hibernate, JOOQ, …
Most people are not able to write optimized queries and dbms can optimizes a lot 
jOOQ is mostly just a query builder, so there’s not going to be much impact if you decide to just use it for that purpose.
As far as I know, jOOQ is pretty popular though.
Agreed, writing SQL is kind of like writing “portable” C code. In theory you could just recompile it on any system but the reality on the ground is that a person will have to rewrite some nontrivial portion of it to deal with platform idiosyncrasies. I’ve hopped between SQLite, MySQL, and H2 a fair bit for different tasks, and it’s not pretty. The closest you can come is to use or write an abstraction layer with a class to address each specific case you want to support. I’m pretty sure that’s why noSQL options are gaining popularity. Well, that and code injection.
Well the main reason I use H2 is for it’s compatibility modes with the other RDBMS so that I don’t have to write specific queries for the file-based H2 database and the MySQL daemon. It’s not perfect but I’ve never encountered a use case that wasn’t fixable.
Sad but true, it’s a pity because it’s so useful. Nowadays much of the performance drains comes from badly written algorithms and inefficient I/O.
Maybe you can start a new thread:
Bad Queries vs Good Queries
Or: More eleganr queries - optimize your query
1 Like
I think I’d like to see that actually. I’m quite novice at database queries, only familiar via my web design days.
Part of it is education. I know of H2 but very little about it, its pros or cons over sqlite. Without googling I can’t even tell you if it is self contained binary file like sqlite or if it requires a separate database server 