0

Is there a way to convert given query from @Query annotation to Query DSL?
The idea is to search for countries by few first characters of name.
While using Hibernate 5.5.6.Final, this query was fine, but today I tried Hibernate 6.0.0.Alpha9 and it does not like my query at all.

@Query(
    "SELECT DISTINCT c " +
    "FROM Country c " +
    "LEFT JOIN FETCH c.localizations loc " +
    "WHERE " +
        "KEY( loc ) LIKE 'NAME%' " +
        "AND " +
       "LOWER( loc ) LIKE LOWER( :name ) "
)
List<Country> findByFirstChars( @Param(value = "name") String name );

Country entity:

@Getter @Setter
class Country {
    // map entry key can be "NAME1", "NAME2", "NOTE1", "NOTE2"...
    // while map entry value is country name or some note
    private Map<String, String> localizations;
    ...more properties
}

Q classess are generated by Maven APT plugin, querydsl-apt and querydsl-jpa.

This is what I want:

List<Country> findByFirstChars( String name ) {
    return new JPAQueryFactory( entityManager )
                .selectFrom( QCountry.country )
                .where( ??? )
                .fetch();
}

EDIT: Another try which failed

List<Country> findByFirstChars(String name) {
    QCountry country = QCountry.country;

    BooleanExpression whereClause1 =
        Expressions.stringTemplate("KEY({0})",
            country.localizations).like(KEY_PREFIX + "%");

    BooleanExpression whereClause2 =
        Expressions.stringTemplate("LOWER({0})",
            country.localizations).likeIgnoreCase(name);

    return 
        new JPAQueryFactory(this.entityManager)
            .selectFrom(country)
            .distinct()
            .leftJoin(country.localizations).fetchJoin()
            .where(                 
                whereClause1.and(whereClause2)
            )
            .fetch();
}

It generates almost the same HQL as original one:

select distinct country
from Country country
  left join fetch country.localizations.data
where KEY( country.localizations.data ) like ?1 escape '!'
    and lower(LOWER( country.localizations.data )) like ?2

but I get an error: Parameter value [cro] did not match expected type [java.util.Map (n/a)]

If I use VALUE() in whereClause2 the HQL is:

select distinct country
from Country country
  left join fetch country.localizations.data
where KEY( country.localizations.data ) like ?1 escape '!'
   and lower(VALUE( country.localizations.data )) like ?2 

and also I get an error: SQL Error: 1242, SQLState: 21000 - Subquery returns more than 1 row

horvoje
  • 643
  • 6
  • 21
  • 1
    Hibernate 6.Alpha is a heavy work in progress of a major rework of Hibernate. Don't assume your query is faulty because it doesn't work on Hibernate 6 yet. Instead, report it as an issue there. – Jan-Willem Gmelig Meyling Aug 26 '21 at 14:57
  • @Jan-WillemGmeligMeyling Tried as you said and no way to log in into their Jira. Some error pops either using my Google account or email login. Two days in a row. https://community.atlassian.com/t5/Jira-questions/Trying-to-report-a-bug-in-Hibernate/qaq-p/1789387 – horvoje Aug 27 '21 at 18:55
  • Let's continue the discussion on your discourse question and hopefully you can post a reproducer for this there soon :) https://discourse.hibernate.org/t/query-not-working-anymore/5646/4 – Christian Beikov Sep 06 '21 at 13:09

0 Answers0