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 
I’m not much familiar with H2 at all actually. I may have seen it as an options in some plugins, but know nothing of it. I’d think if someone were willing to do a post about proper queries, it may be best to start out with different database types (how they work, how they’re accessed, and the pros/cons vs other libraries). Maybe even cover some libraries used to access various types. I think I’d read it. Maybe even a good thing to bookmark for later if I needed to pick up SQL or database stuff again.
Maybe not about proper queries but I might do a post about how to use the different databases available in Java. For the most part they can all be used with JDBC in a similar syntax shown in my code example further up in this thread.
1 Like
Maybe if someone else wrote about the rest they could just link to your post about that then XD
One thing I really love about H2 is the way they describe their grammar.
(the ubiquitous select statement is here)
There’s just a really nice flow to it. Also, H2 is a self-contained package. It stores local database files. It can be pretty fussy about threads and locks though, there are at least 4 threads per connection.
1 Like
We mainly designed jOOQ for the needs of corporate environments, such as banking, logistics, insurance, supply chain management systems and the likes. Our customers value jOOQ for the performance it offers compared to popular ORMs, as you’re in full control of all the generated SQL. It does perform a little worse than JDBC, of course, as there is a certain overhead because of:
- SQL queries being assembled as ASTs (Abstract Syntax Trees) in Java memory
- No support for primitive types in results (because of Java generics)
- Eager fetching of results by default (you can explicitly lazy-fetch your results, though)
If you have any concrete questions, we’re more than happy to help you on the jOOQ User Group (https://groups.google.com/d/forum/jooq-user)
Cheers,
Lukas from jOOQ
1 Like
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.
Not yet, but we’re working on more DDL support.
Cheers,
Lukas from jOOQ
1 Like
Thanks for the explanation