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