 Named parameters in Native SQL [message #1700794] |
Tue, 07 July 2015 09:15 |
|
A little background first:
Our website uses Hibernate as it's JPA provider, but we've run into several issues with it, one of which they actively refuse to fix out of pure arrogance, even when we gave them a good patch for it. The other one is a performance issue that can be greatly improved by passing arrays as parameters, which I've read rumours is supported in EclipseLink. I first tried implementing a Hibernate UserType as they've specified and it turns out they never read them into the Map that looks-up parameter types during binding, even though it's loaded from their hibernate.cfg.xml. In other words, they're only pretending to support custom user types, but unless you're writing-out the full manual loading of their configuration, there's no way to do it.
After a day of trying to fix all the relation annotations that EclipseLink finds unacceptable in our project (which only uses native SQL and EntityManager.find for selects) I finally got it running and came to discover you don't support named parameters in native SQL. That was really disappointing, because a few months ago we migrated all our queries to named parameters, because positioning ones were too difficult to track and caused more bugs when we had to add more criterias.
My questions are these:
- Are named parameters for native SQL not supported by JPA specifications or because they are considered low priority?
- Would you accept a patch that supports them?
- Can we really use arrays and/or lists as parameters in queries like this "SELECT * FROM some_table WHERE my_column_a IN ? AND my_column_b NOT IN ?"
- If yes to above, would it still work if the first one is an array of integers and the second is an array of strings?
- Would it work if it's in native SQL and the column types cannot be resolved due to query complexity?
edit: I found the answers to some of my questions by now:
"JPA 2.1 Specifications"
Only positional parameter binding and positional access to result items may be portably used for native queries, except for stored procedure queries for which named parameters have been defined
I underscored the relevant part to my second question.
For the third question, I tested all possibilities and the answer is "No". However I learned the array-friendly syntax (at least in PostgreSQL) is actually "my_column_a = ANY(?) AND my_column_b <> ALL(?)", which the IN and NOT IN are converted to internally, but it still does not work in EclipseLink. I managed to modify Hibernate for myself to use them at least for parameters. Not for extracting results.
[Updated on: Wed, 22 July 2015 14:37] Report message to a moderator |
|
|
Powered by
FUDForum. Page generated in 0.03522 seconds
] 
Search
Help
Register
Login
Home
