Wrong number of TRIM function parameters in Hibernate @Formula

Question

I am trying to make a formula to choose a convenient user profile name. He chooses firstname + '' + lastname if at least one of them is not null and not empty (contains characters without spaces), otherwise he chooses shortname (with the same conditions) and, finally, if shortname is empty or empty , it selects id , is converted to a string.

@Formula("COALESCE(NULLIF(TRIM(BOTH FROM CONCAT(sp.firstname, ' ', sp.lastname)), ''), TRIM(p.shortname), to_char(p.id, 'FM9999999999999999')) " + "FROM socialprofile AS sp " + "JOIN profile AS p ON sp.id=p.id") public String getUserFriendlyName() { return super.getUserFriendlyName(); } 

Result:

 org.hibernate.HibernateException: Unexpected number of trim function operands : 10 

but there are only 3 arguments: BOTH, FROM and the result of CONCAT. Is there any solution to this problem? DB - PostgreSQL 9.1, Hibernate version - 4.2.1. Just in case, here is the table structure:

 Profile bigint id text shortname SocialProfile bigint id text firstname text lastname 

They are linked as a one-to-one relationship using the id field in the SocialProfile extends Profile Java code. In the PgAdmin SQL editor, this query works fine.

Answer 1

I also ran into this problem. It looks like @Formula delegating the Hibernate internal "where template" parser , which does not expect another function to be nested in trim() . This issue was previously reported as HHH-5970 , but they did not do anything with it.

The work was straightforward: don't use trim() this way in @Formula . Instead, use, for example, ltrim(rtrim(..)) or even the DB-specific regular expression function, which replaces "^\s+|\s+$" with "" , depending on the capabilities of your database.

PostgreSQL supports ltrim() and rtrim() , so this should do it for you:

 @Formula("COALESCE(NULLIF(LTRIM(RTRIM(BOTH FROM CONCAT(sp.firstname, ' ', sp.lastname))), '') [...]" 

Answer 2

I also ran into many problems with formula annotation, as Hibernate changed the contents of the formula to be able to add to the original request. This led me to use db representations in these cases instead of the unpredictable Hibernate formula.