A yellow bird toy with "Kaboom" written on its be

I was debugging a piece of code that threw a Java.lang.OutOfMemoryError. After a short investigation, I spotted that the SQL query the coded executed did not contain a ‘WHERE’ clause causing a nearly full download of a large table, making the process run out of memory.

The fix was simple and quick: add a WHERE clause to the SQL query. Then, as part of preventative maintenance, I thought to check why this query even executed as the parameters were present in the code, ready to be inserted into the SQL statement.

I want the NamedParameterJdbcTemplate to throw an exception like ‘Hey, you should have had two parameters in this query; you have none!’. So I asked ChatGPT for assistance: how can I check beforehand if all parameters provided found their way into the SQL inside the WHERE-clause?

How to check when you run jdbctemplate.query that all NamedParameterSource variables have been filled in?

This is what it came up with:

You can check if all NamedParameterSource variables have been filled in by using the SqlParameterSourceUtils class provided by Spring framework. Here's an example:

1NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
2String sql = "SELECT * FROM my_table WHERE column1 = :param1 AND column2 = :param2";
3MapSqlParameterSource params = new MapSqlParameterSource();
4params.addValue("param1", "value1");
5//params.addValue("param2", "value2"); //Commented out to simulate a missing parameter
7SqlParameterSourceUtils.assertAllParamsSet(params, sql);
8List<MyObject> results = jdbcTemplate.query(sql, params, new MyRowMapper());

In this example, the assertAllParamsSet method will throw an IllegalArgumentException if any named parameter in the SQL query is missing in the params object. This allows you to catch missing parameters before running the query. Note that this method only works for NamedParameterJdbcTemplate and SqlParameterSource objects, but you can adapt it to other types of JDBC templates and parameter sources if needed.

So the ‘money shot’ (Pardon my French) is at line 7. Since I’m looking for a way to check (assert) that all my parameters are used (set), reading this code suggestion made me a happy little nerd.

I quickly discovered that this API call, part of this helper class, does not exist; it never has. After the IDE gave all sorts of ‘not found’ indications, I decided to look up this part of the code, and this is what happened:

Google logo w/"assertAllParamsSet" text: successful search

(sad trombone)

An old programmer joke is that you’re in trouble if there are 0 results when you Google your problem.


In the end, I needed to implement this myself rather than expecting the library to do it for me. ChatGPT suggested a helper class and API call that simply does not exist: it would be a nice addition to the Util, so I might do that.

ChatGPT dreamt of helping me; the bot hallucinated about APIs that would exist in an ideal world, just not in the real world.

Increase Engineer Engagement by measuring what matters