Volodymyr Gubarkov

One method for easier JDBC

January 2025

When you work with database in Java using JDBC you often see the code like this:

    try (PreparedStatement statement =
        connection.prepareStatement(
            "SELECT * "
                + "FROM employee "
                + "WHERE (first_name LIKE ? " // 1
                + "    OR last_name LIKE ?) " // 2
                + "  AND department = ? "     // 3
                + "  AND position = ? "       // 4
                + "  AND seniority IN (?, ?) "// 5, 6
                + "  AND speciality = ? "     // 7
                + "  AND salary BETWEEN ? AND ? " // 8, 9
                + "  AND hire_date >= DATE_SUB(NOW(), INTERVAL ? YEAR) " // 10
                + "LIMIT ? OFFSET ?" // 11, 12
        )) {

      statement.setString(1, name);
      statement.setString(2, name);
      statement.setString(3, department);
      statement.setString(4, title);
      statement.setString(5, Seniority.MIDDLE);
      statement.setString(6, Seniority.SENIOR);
      statement.setString(7, speciality);
      statement.setDouble(8, salaryFrom);
      statement.setDouble(9, salaryTo);
      statement.setInt(10, yearsInCompany);
      statement.setInt(11, pageSize);
      statement.setInt(12, pageSize * (pageNo-1));

      try (ResultSet results = statement.executeQuery()) {
        while (results.next()) {
          // processing records
        }
      }

This is a good example. Often it’s just:

    try (PreparedStatement statement =
      connection.prepareStatement(
        "SELECT * FROM employee WHERE (first_name LIKE ? OR last_name LIKE ?) AND department = ? AND position = ? AND seniority IN (?, ?) AND speciality = ? AND salary BETWEEN ? AND ? AND hire_date >= DATE_SUB(NOW(), INTERVAL ? YEAR) LIMIT ? OFFSET ?")) {

      statement.setString(1, name);
      statement.setString(2, name);
      statement.setString(3, department);
      statement.setString(4, title);
      statement.setString(5, Seniority.MIDDLE);
      statement.setString(6, Seniority.SENIOR);
      statement.setString(7, speciality);
      statement.setDouble(8, salaryFrom);
      statement.setDouble(9, salaryTo);
      statement.setInt(10, yearsInCompany);
      statement.setInt(11, pageSize);
      statement.setInt(12, pageSize * (pageNo - 1));
    
      // ...
    }

☝ Good luck matching numbers to parameter placeholders!

Wouldn’t it be great if there was a method to use the SQL arguments inline in the SQL query while still avoiding SQL injections?

Well, there is such method!

What you need is this simple helper class:

public class SqlArgs {
  private final List<Object> args = new ArrayList<>();

  public String arg(Object v) {
    args.add(v);
    return "?";
  }

  public String list(Object... vv) {
    if (vv.length == 0) {
      // because `IN ()` gives a syntax error in SQL
      throw new IllegalArgumentException();
    }
    Collections.addAll(args, vv);
    return "(" + ",?".repeat(vv.length).substring(1) + ")";
  }

  public void setArgs(PreparedStatement statement) throws SQLException {
    int idx = 0;
    for (Object arg : args) {
      statement.setObject(++idx, arg);
    }
  }
}

And now voilà:

    SqlArgs $ = new SqlArgs();

    try (PreparedStatement statement =
        connection.prepareStatement(
            "SELECT * "
                + "FROM employee "
                + "WHERE (first_name LIKE " + $.arg(name)
                + "    OR last_name LIKE " + $.arg(name) + ")"
                + "  AND department = " + $.arg(department)
                + "  AND position = " + $.arg(title)
                + "  AND seniority IN " + $.list(Seniority.MIDDLE, Seniority.SENIOR)
                + "  AND speciality = " + $.arg(speciality)
                + "  AND salary BETWEEN " + $.arg(salaryFrom) + " AND " + $.arg(salaryTo)
                + "  AND hire_date >= DATE_SUB(NOW(), INTERVAL " + $.arg(yearsInCompany) + " YEAR)"
                + "  LIMIT " + $.arg(pageSize) + " OFFSET " + $.arg(pageSize * (pageNo-1))
        )) {

      $.setArgs(statement); // just one line instead of tedious statement.set*() calls!

      // processing records
    }

How cool is that? 😃

But you have more! It’s as easy with this approach to construct dynamic SQL:

  connection.prepareStatement(
    "SELECT * "
      + "FROM employee "
      + "WHERE 1=1 "
      + (name       != null ? " AND (first_name LIKE " + $.arg(name) + " OR last_name LIKE " + $.arg(name) + ")" : "")
      + (department != null ? " AND department = " + $.arg(department) : "")
      + (title      != null ? " AND position = " + $.arg(title) : "")
      + (seniority  != null ? " AND seniority IN " + $.list(seniority) : "")
      + (speciality != null ? " AND speciality = " + $.arg(speciality) : "")
      + (salaryFrom > 0     ? " AND salary >= " + $.arg(salaryFrom) : "")
      + (salaryTo   > 0     ? " AND salary <= " + $.arg(salaryTo) : "")
      + (yearsInCompany > 0 ? " AND hire_date >= DATE_SUB(NOW(), INTERVAL " + $.arg(yearsInCompany) + " YEAR)" : "")
      + " LIMIT " + $.arg(pageSize) + " OFFSET " + $.arg(pageSize * (pageNo-1))
    )

You can find the code and tests of the approach in this GitHub repo.

But you can use ORM instead!

Please let me quote my comment on the orange website:

My take on this is that it’s not always the best idea to abstract-out SQL. You see, the SQL itself is too valuable abstraction, and also a very “wide” one. Any attempt to hide it behind another abstraction layer will face these problems:

• need to learn secondary API which still doesn’t cover the whole scope of SQL
• abstraction which is guaranteed to leak, because any time you’ll need to optimize - you’ll need to start reason in terms of SQL and try to force the ORM produce SQL you need.
• performance
• deceptive simplicity, when it’s super-easy to start on simple examples, but it’s getting increasingly hard as you go. But at the point you realize it doesn’t work (well) - you already produced tons of code which business will disallow you to simply rewrite

(knowledge based on my own hard experiences)

---

If you noticed a typo or have other feedback, please email me at xonixx@gmail.com