We are using PostgreSQl 9.4, and Hibernate 5.1 which seems to be somewhat the latest versions. I know Postgres 9.5 is out, but 9.4 doesn't seem so old. We use JPA with Hibernate 5.1 and create HQL Queries.
We have a field in the database which is a bigint. This is a bitmask for us, stored as an integer. In the java code, this works great, but when we want to check for bitmasks, it doesn't work.
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper -
ERROR: function bitwise_and(bigint, integer) does not exist
Hint: No function matches the given name and argument types.
You might need to add explicit type casts.
I'm looking at the generated hibernate sql and I can see that we have something like:
bitwise_and(mytable_.actionbitmask, 2)>0
So, I'd like to get the raw SQL to work first, and then I can tweak my HQL query to make that work also.
Now, I have searched Google, and all the results come back with: - can't be done in HQL, have to use regular sql But most of these are very old comments, and since Postgres is 9.4, Hibernate is 5.1, I'm thinking surely by now we can figure this out with some examples.
Unfortunately, even the Postgres 9.4 bit operations don't even show bitwise_and at all, the documentation just sucks there.
Should I convert my decimals/longs to binaries of the same length in the sql string before I execute the HQL?
Any help would be much appreciated. Thanks!
Based on what I saw on the internet and various google searches, there is a may to take the standard class 'PostgreSQLDialect' and we extended it to our own class:
public class MyPostgreSQLDialect extends PostgreSQLDialect
We then added code in here which created a special 'bitwise_and' function.
I had to change the database dialect in my Spring application context file, and re-ran my HQL query.
I just didn't know this was the case, because until previously we weren't doing bitwise checks at all. I did not know we had this Dialect extension until just recently.
But this solved our problem. Thanks!