Took a very short break from JPA and tried working with Spring JDBCTemplate for a small project.
It's a mock version of a complex super optimized service using a lot of custom native sql.
I appreciated the simplicity of JDBCTemplate, and specifically the NamedParameter version.
I found the RowCallbackHandler useful for my case where each row didn't necessarily mean one object, and I was able to reuse the handler across different queries.
One problem I had was attempting to avoid creating new tables, and thus using what I had for columns. One problem was the indexes missing for the searchable columns in where clause.
But another more interesting one was needing to map two numeric columns into a unique numeric key. And the key is limited to a long value. In the end, I was able to combine the two numbers using 32 bit shifting to create the long value, treating them both as ints (AA+BB=AABB). With the issue that it would not work if they were beyond the max int value. Fine for now.
And this ended up working great, being able to encode the column values into a single unique key and translate them back into the separate column values for querying.