Better JDBC Prepared Statements?

After using PreparedStatements for a while, you understand how painful it is maintaining SQL queries. Setting values by position is error-prone and tough.

Let's imagine a query that you want to check multiple columns against the same value:

SELECT * FROM Table WHERE a > ? AND b < ? AND c == ?

You will need to have the following code in place:

int THE_VALUE = 12345;
stmt.setInt(1, THE_VALUE);
stmt.setInt(2, THE_VALUE);
stmt.setInt(3, THE_VALUE);

Just imagine that you need to refactor the query such as the new values don't have nothing to do with THE_VALUE. The query could look like this:

SELECT * FROM Table WHERE (a > ? OR ? ) AND (b < ? OR ?) AND c == ?

You'd need to refactor your previous query such as:

int THE_VALUE = 12345;
boolean ANOTHER_VALUE = false;
stmt.setInt(1, THE_VALUE);
stmt.setInt(3, THE_VALUE);
stmt.setInt(5, THE_VALUE);
stmt.setBoolean(2, ANOTHER_VALUE);
stmt.setBoolean(4, ANOTHER_VALUE);

Not sure what you think about, but having to count question marks in a query to make things work never looked very attractive to me. I think we could be smarter than that

So what?

Why not having something like:

SELECT * FROM Table WHERE a > @{startDate} AND b < @{startDate} AND C = @{startDate}

and code like:

int THE_VALUE = 12345;
smartStatement.setInt("startDate", THE_VALUE);

The second approach is not impacted by any refactoring and it's less error prone. So, this small library tackles this problem with a very simplistic approach.

The approach

The library is very compact and simple. It is responsible for parsing the sql query and populating prepared statement values, but nothing more. So, creation of the prepared statement itself is not part of this library. I wanted to avoid over-engineered wrappers for such a small problem

What is not supported?

Do not use parametrized values in the queries. It might break the parsing logic. So something like this will break:

String sql = "SELECT * FROM MY_STRINGS WHERE str = \"a string containing weird stuff: ${else}\" AND id = ${id}";

Use the following instead:

String weird = "a string containing weird stuff: ${else}";
String sql = "SELECT * FROM MY_STRINGS WHERE str = ${weird} AND id = ${id}";
smartStatement.setString(stmt, "weird", weird);
smartStatement.setString(stmt, "id", "WTF");

That's brilliant. Where can I find it?

Check my sicoris-jdbc bickbucket repo . Comment it, fork it, use it. Any feedback is more than welcome.