I precise that I am a french student in 1st year of Java Developper. And I'm pretty sure my question title isn't even correct regarding what i'm trying to do. Then I hope the following explaination will be clearer!
I'm developing a little multi-module app using: Spring Boot, Spring security, Hibernate, Spring Data, Spring MVC and Thymeleaf.
Here is my repo
I'm currently working on a method that will handle a multi-criteria request. I'm using Querydsl.
Here is the involved piece of the domain:
The mother entity
@Entity
@Table(name="element")
@Inheritance(strategy = InheritanceType.JOINED)
@Getter
@Setter
@NoArgsConstructor
@ToString
@EqualsAndHashCode
public abstract class Element {
//----------ATTRIBUTES----------
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@NotNull
@Size(min=4,max=50)
private String name;
private LocalDateTime createDate;
private LocalDateTime updateDate;
@OneToMany/*(mappedBy = "element")*/
@JoinColumn(name = "element_id")
private List< Comment > comments;
//..
The child entities
/**
* Bean used to define a book or a document that contains one or many climbing areas
*/
@Entity
@Table(name="atlas")
@PrimaryKeyJoinColumn(name = "element_id")
@Getter
@Setter
@NoArgsConstructor
@ToString( exclude = {"areas" , "bookingRequests" , "user"})
public class Atlas extends Element {
//----------ATTRIBUTES----------
@ManyToMany
@JoinTable(
name = "areas_in_atlases",
joinColumns = { @JoinColumn(name = "atlas_id") },
inverseJoinColumns = { @JoinColumn(name = "area_id") } )
private List< Area > areas;
private String scale;
private String country;
private String region;
private String department;
private boolean available;
@ManyToOne
@JoinColumn(name = "user_id")
private User user;
@OneToMany(mappedBy = "atlas", cascade = CascadeType.ALL)
private List<BookingRequest> bookingRequests;
//..
/**
* Bean used to define an area with one or many climbing crag
*/
@Entity
@Table(name="area")
@PrimaryKeyJoinColumn(name = "element_id")
@Getter
@Setter
@NoArgsConstructor
@ToString
@EqualsAndHashCode( callSuper = true )
public class Area extends Element {
//----------ATTRIBUTES----------
@ManyToMany(mappedBy = "areas")
private List< Atlas > atlases;
private Atlas atlas;
@OneToMany/*(mappedBy = "area", fetch = FetchType.LAZY)*/
@JoinColumn(name = "area_id")
private List< Crag > crags;
private int approachDuration;
private String locality;
@OneToMany/*(mappedBy = "area", fetch = FetchType.LAZY)*/
@JoinColumn(name = "area_id")
private List< Parking > parking;
private String rockType;
//..
The first part of my multi-criteria method is working (not very elegant I guess..):
@Override
public Page<Atlas> searchAtlasByNameAndCountryAndRegionAndDepartment( int page, int size, String name, String country, String region, String department ){
QAtlas qAtlas = QAtlas.atlas;
ArrayList< Predicate > predicates = new ArrayList<>();
List<Atlas> atlases;
if( !name.equals( "" ) ){
predicates.add( qAtlas.name.containsIgnoreCase(name) );
}
if( !country.equals( "" ) ){
predicates.add( qAtlas.country.containsIgnoreCase(country) );
}
if( !region.equals( "" ) ){
predicates.add( qAtlas.region.containsIgnoreCase(region) );
}
if( !department.equals( "" ) ){
predicates.add( qAtlas.department.containsIgnoreCase(department) );
}
BooleanBuilder booleanBuilder = new BooleanBuilder();
for ( Predicate predicate: predicates ) {
booleanBuilder.and(predicate);
}
atlases = ( List< Atlas > ) getDaoFactory().getAtlasRepository().findAll( booleanBuilder );
long total = ( long ) atlases.size( );
return new PageImpl(atlases, PageRequest.of( page, size ), total );
}
But I'm stucked with the method that will search in Area, using fields that are ONLY in Atlas, like Country.
@Override
public Page< Area > searchAreaByNameAndCountryAndRegionAndDepartment( int page, int size, String name, String country, String region, String department ){
QAtlas qAtlas = QAtlas.atlas;
QArea qArea = QArea.area;
final JPAQueryFactory queryFactory = new JPAQueryFactory(em);
List<Area> areas = queryFactory.selectFrom(qArea)
.innerJoin(qAtlas)
.where(
qArea.name.containsIgnoreCase(name),
qAtlas.country.containsIgnoreCase(country),
qAtlas.department.containsIgnoreCase(department),
qAtlas.region.containsIgnoreCase(region)
)
.fetch();
long total = ( long ) areas.size( );
return new PageImpl(areas, PageRequest.of( page, size ), total );
}
Currently the console says:
ervlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'atlas.areas' [select area
from org.thibaut.wheretoclimb.model.entity.Area area
inner join atlas.areas as area
where lower(area.name) like ?1 escape '!' and lower(atlas.country) like ?2 escape '!' and lower(atlas.department) like ?1 escape '!' and lower(atlas.region) like ?1 escape '!']] with root cause
org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'atlas.areas' [select area
from org.thibaut.wheretoclimb.model.entity.Area area
inner join atlas.areas as area
where lower(area.name) like ?1 escape '!' and lower(atlas.country) like ?2 escape '!' and lower(atlas.department) like ?1 escape '!' and lower(atlas.region) like ?1 escape '!']
Does anyone could help me with that query?
Thank you very much for your time guys!
THE SOLUTION:
public Page< Area > searchAreaByNameAndCountryAndRegionAndDepartment( int page, int size, String name, String country, String region, String department ){
QAtlas qAtlas = QAtlas.atlas;
QArea qArea = QArea.area;
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
BooleanBuilder booleanBuilder = new BooleanBuilder();
if( ! name.equals( "" ) ){
booleanBuilder.and( qArea.name.containsIgnoreCase(name) );
}
if( ! country.equals( "" ) ){
booleanBuilder.and( qAtlas.country.containsIgnoreCase(country) );
}
if( ! region.equals( "" ) ){
booleanBuilder.and( qAtlas.region.containsIgnoreCase(region) );
}
if( ! department.equals( "" ) ){
booleanBuilder.and( qAtlas.department.containsIgnoreCase(department) );
}
List<Area> areas = queryFactory.from(qAtlas)
.innerJoin(qAtlas.areas, qArea)
.where(booleanBuilder)
.select(qArea)
.fetch();
long total = ( long ) areas.size( );
return new PageImpl(areas, PageRequest.of( page, size ), total );
}
Thank you very much for your time guys!