Be wary of "IN" clause

In my current project, some queries share a pattern of "where category.id in (:categoryids)". To be "DRY", I implemented a routine to extract a set of category ids; then use Hibernate's setParameterList(). Nice and clean... and WRONG.

The problem is that database has limited buffer for parsing sql query so that there is a limitation on the length of the "IN" clause. The above approach works with small set of data but bombs out when data gets larger.

The solution is to place the query for extracting category ids directly in the "IN" clause as subquery. The result is messy with lots of string concatenation, special case handling. But CORRECT.

An example of leaky abstraction and a reminder to test with large data set early on.

Technorati Tags: sql