Start using SQL statements in your Java code

Overview

I often write small apps that need minimal database interactions. Like all developers, I need to decide how my application is going to access the database.

Three popular Java options are frameworks like HIbernate, JooQ, or writing custom raw SQL statements.

Disadvantages

Hibernate's disadvantages

  1. Completely takes over your database access and object mapping in an opinionated way.
  2. Harder to tune the queries than it should be. By default Hibernate will fetch too much data from the database for typical use cases. You can tune the queries but doing so defeats the purpose of using Hibernate.
  3. Terrible for any batch processing. This is an extension of #2 above. If you do high volume batch processing, it's better to just write custom SQL queries and tune them for performance.

JooQ's disadvantages

  1. Not a free product. It has a complex pricing scheme.
  2. Although I do pay for a lot of commercial software to help with my productivity, I never pay for libraries or frameworks.

Raw SQL disadvantages

  1. In Java, it's awkward to write SQL strings. You end up writing lots of string concatenation with if-else statements to get it formatted correctly.
  2. No auto mapping the database results to objects.
  3. No SQL dialect selecting. You can't switch automatically from a MySQL database to a DB2 database, for example. I've never had to switch database vendors before but this is a feature most ORM frameworks boast about.

Raw SQL

For the small apps I write I like to use Raw SQL but I hate writing all the code that generates the SQL strings. I find it irritating and writing them makes me irrationally angry.

So what did I do?

I wrote a small library called sql-statement-builder that constructs the SQL strings for you using the builder pattern and a fluent API.

Example SELECT statement

Let's write a raw sql statement using Java strings to select from the user table. We will write this as a prepared statement to help prevent SQL injection attacks.

A typical usage is selecting all columns from a table but the where clause is dynamically generated. You pass in the where columns and values and the sql is built dynamically.

// SNIP
// where columns/values are in whereClause variable
Map<String, Object> whereClause = Map.of("id", 1, "type", "basic");
String sql = "SELECT * FROM USER ";

if (!whereClause.isEmpty()) {
    sql += "WHERE ";

    List<Map.Entry<String, Object>> entryList = whereClause.entrySet().stream().collect(Collectors.toList());
    for (int i = 0; i < entryList.size(); i++) {
        sql += entryList.get(i).getKey() + " = ?";

        if (i < entryList.size() - 1) {
            sql += " AND ";
        }
    }
}

System.out.println(sql);

// OUTPUT:
// SELECT * FROM USER type = ? AND id = ?

This is awkward and too much work to write a single SQL statement.

sql-statement-builder - using fluent Select statement

A fluent API uses an expressive, method-chaining approach. They are good for the builder pattern, which is a pattern that creates complex objects in a clear, concise way.

Here's a select example from sql-statement-builder.

SqlString sqlStatement = new SqlString.SqlStringBuilder()
    .select()								// first specify what operation you want: select, insert, delete, or update
    .column("first_name")		// next put in all the columns you want to select
    .column("last_name")
    .fromTable("users")			// the table you're selecting from
    .where("id", 1)					// zero or more where predicates (predicate means returns true or false)
    .orderBy("last_name")		// zero or more ordering directives
    .build();								// create the selectStatement obejct

// can get a preparedStatment with ? for parameters or statement with the actual parameter values
System.out.println(sqlStatement.getSqlPreparedStatement())

// OUTPUT:
// SELECT FIRST_NAME, LAST_NAME FROM USERS WHERE ID = ? ORDER BY LAST_NAME

System.out.println(sqlStatement.getSqlStatement())

// OUTPUT:
// SELECT FIRST_NAME, LAST_NAME FROM USERS WHERE ID = '1' ORDER BY LAST_NAME

What is sql-statement-builder good for?

  1. Allows you to build sql statements using the way you naturally write code.
  2. You can add where entries one at a time just like you would when parsing a user request.
  3. Easier to read.
  4. Keeps parameters with the SqlString object. Easy to pass those into execute prepared statement.

What are the limitations?

  1. This creates MySQL SQL statements. The sql statements might work on other database, because it's basic SQL but that's not guaranteed.
  2. Only supports SELECT, UPDATE, DELETE, and INSERT.

What about in the corporate world?

For large projects especially when working for large companies, I'll use Hibernate. It's a common framework that's used just about everywhere in the Java world.

No one ever got yelled at for picking Hibernate.

For everything else, I like SQL statements.

Conclusion

Too many developers pick ORM frameworks like HIbernate without considering what the project really needs to access the database.

Don't be afraid to pick straight SQL statements.

Add your thoughts to the discussion

I have a level headed thought I want to share

I have an totally unhinged thought you must know about, RIGHT NOW!