Why String type requires Hibernate JPQL type

Question

I am using Play! Framework for a small application. In the model, I have the following query:

public static ApplicationUser getByUserName(String userName) { return ApplicationUser.find("SELECT u FROM ApplicationUser u WHERE u.userName = ?", userName).first(); } 

This works fine with DB H2 memory, but when I use Postgres, I get the following error:

 22:57:10,371 WARN ~ SQL Error: 0, SQLState: 42883 22:57:10,371 ERROR ~ ERROR: operator does not exist: character varying = bytea Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 167 

When I specify the parameter as:

 ApplicationUser.find("SELECT u FROM ApplicationUser u WHERE u.userName = CAST(? AS string)", userName).first() 

Then it works. But why is this necessary. Maybe this is a Hibernate bug?

Update: I downgraded the game from 1.2.4 to 1.2.3, and now it works. I think the problem is that the jdbc driver was sent by postgres.

Update II . The problem is still not resolved. I again get the same error for the request:

 ApplicationRole.find("byName", name).first(); 

Error:

 JPAQueryException occured : Error while executing query SELECT u FROM ApplicationUser u WHERE u.userName = ?: ERROR: operator does not exist: character varying = bytea Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. 

In application.conf , I have:

jpa.dialect = org.hibernate.dialect.PostgreSQLDialect

No one has this error?

Answer 1

Try to replace? (? 1), as in:

 public static ApplicationUser getByUserName(String userName) { return ApplicationUser.find("SELECT u FROM ApplicationUser u WHERE u.userName = (?1)", userName).first(); } 

I have a similar request and it works fine. I assume that the userName field in ApplicationUser is of type String (just in case!)

Also, as Dominic suggested, check that your jpa.dialect is set to auto-detect or to PosgreSQL.

Answer 2

Have you enabled postgresql database dialogs in conf / application.conf?

jpa.dialect = org.hibernate.dialect.PostgreSQLDialect

Answer 3

I have the same problem, 1.2.4.

 User.find("byEmail", email).first() 

gets the same casting error as you, so I had to replace it (thanks Pere)

 User.find("email = (?1)", email).first() 

Interesting that

 User.find("byEmailAndPassword", email, password).first() 

still working ...