Does Hibernate/JPA CriteriaQuery trim function generate the wrong SQL for PostgreSQL?
Please note that this is a cross posting from this
SO questionI'm trying to use the Criteria Query trim() function to strip the leading zero's of a field in the database. The relevant part of the criteria query is:
Code:
final Expression<String> _code = cmm.get(CodeMasterMapping_.code);
builder.equal(builder.trim(Trimspec.LEADING, Character.valueOf('0'), _code), productCode)));
The SQL generated by this snippet is:
Code:
trim(LEADING 0 from codemaster3_.comm_code)=?)
This leads to the following exception from Postgres:
Quote:
PSQLException: ERROR: function pg_catalog.ltrim(character varying, integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
I then changed the zero '0' to a character, thinking that maybe postgres is interpreting the zero '0' as an integer, hence triggering the exception.
Changing the character to be removed to an 'A':
Code:
builder.equal(builder.trim(Trimspec.LEADING, Character.valueOf('A'), _code), productCode)));
but that generates this SQL:
Code:
trim(LEADING A from codemaster3_.comm_code)=?)
and the following SQL exception:
Quote:
PSQLException: ERROR: column "a" does not exist
According to the Postgres docs for
9.5 the trim function take these parameters:
Code:
trim([leading | trailing | both] [characters] from string)
and the given example is:
Code:
trim(both 'x' from 'xTomxx') --> 'Tom'
I have tested this on the following combinations with the exact same results
Postgres 9.5.5, Wildfly 9.0.2.Final with Hibernate Core 4.3.10.Final
Postgres 9.5.5, Wildfly 10.1.0.Final with Hibernate Core 5.0.10.Final
Please let me know if you need more code for the Criteria Query.
Is there something wrong with my use of the CriteriaBuilder.trim() function or should Hibernate generate SQL that use single quotes for the trim character?
Login
FAQ
Search
