How to mask key words in HQL query?

Hibernate ORM
1

I have a table named constraint. As one can guess, constraint is a SQL keyword, so no surprise so far. Unfortunately, when I rely on Hibernate to build the query for a simple find, Hibernate won’t mask the keyword, thus resulting in an error:

 o.h.e.j.s.SqlExceptionHelper  :  SQL Error: 42001, SQLState: 42001

Now, here’s the repository containing the query method in question:

public interface ConstraintRepository extends JpaRepository<Constraint, Long> {

    List<Constraint> findAllByChallengeChallengeId(Long challengeId);

}

Hibernate then builds the following SQL statement:

    select
        c1_0.constraint_id,
        c1_0.challenge_id,
        c1_0.content,
        c1_0.display_order 
    from
        constraint c1_0 
    where
        c1_0.challenge_id=?

That this won’t function is obvious and the following error is thrown:

Syntax error in SQL statement "select c1_0.constraint_id,c1_0.challenge_id,c1_0.content,c1_0.display_order from [*]constraint c1_0 where c1_0.challenge_id=?"; expected "identifier"; SQL statement:
select c1_0.constraint_id,c1_0.challenge_id,c1_0.content,c1_0.display_order from constraint c1_0 where c1_0.challenge_id=? [42001-224]

I could get around that problem by using native SQL:

@Query(value = """
        SELECT constraint_id, challenge_id, content, display_order
        FROM `constraint`
        WHERE challenge_id = :challengeId
        """, nativeQuery = true)
List<Constraint> findAllByChallengeId(@Param("challengeId") Long challengeId);

But I’d rather stick with HQL where possible.

So, is there a way to tell Hibernate to mask keywords, or is this a feature, or is it bug?
Cheers, and many thanks for any clarification.

2

Hibernate supports SQL quoted identifiers for both table and column names. You can simply annotate your entity with @Table(name="`constraint`") and your queries should work.

1 Like
3

i have similar issue while running junit tests using HSQLDB server queries are not working of hibernate . Migrating from Hibernate 3.1.3 to 5.6.15.FINAL and using hibernate.cfg.xml and hbm.xml for entities .

4

@ mbladel Thank you for that hint, works like charm. No excuse why I didn’t come up with that obvious solution :wink: