Problem when trying to Join two tables with CriteriaQuery in JPA

Viewed 106

I am using JPA's Criteria API to implement a search function for my SpringBoot application. For now, at the beginning I am trying to search jobs, which are objects of class JobEntity, which is shown below:

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
public class JobEntity {
@Id
@GeneratedValue(generator = "uuid")
private UUID uuid;

@Column(nullable = false)
private String jobTitle;

@Column(nullable = false)
private String jobDescription;

@ManyToMany(cascade = {CascadeType.MERGE}, fetch=FetchType.EAGER)
@Column(nullable = false)
private List<TechnologyEntity> technology = new ArrayList<>();

@ManyToMany(cascade = {CascadeType.MERGE}, fetch=FetchType.LAZY)
@Column(nullable = false)
private List<ProgrammingLanguageEntity> programmingLanguages = new ArrayList<>();

@Enumerated(EnumType.STRING)
@Column(nullable = false)
private JobMobilityEnum jobMobility;

@ElementCollection(targetClass= JobTypeEnum.class)
@Enumerated(EnumType.STRING)
@Column(nullable = false)
Collection<JobTypeEnum> jobTypes;

private Integer paymentMin;
private Integer paymentMax;

@OneToOne(cascade = CascadeType.ALL)
private JobContactEntity jobContactEntity;

@OneToOne
private UserEntity createdByUser;

private LocalDate createdAt;

@OneToOne
private UserEntity updatedByUser;

private LocalDate updatedAt;

@ManyToOne
private CompanyEntity companyEntity;

@OneToMany
private List<UserEntity> applicant = new ArrayList<>();

@Enumerated(EnumType.STRING)
@Column(nullable = false)
private JobCreatorEnum jobCreator;
}

I also created an entity for searching of jobs, which contains the criteria by which jobs are searched into the DB:

@Data
public class JobSearchEntity {
private String jobTitle;
private String jobDescription;
private List<String> technology;
private List<String> programmingLanguage;
private List<String> jobMobility;
private List<String> jobType;
private Integer paymentMin;
private Integer paymentMax;
}

This object is passed as parameter to the service's method that realizes the search. I noticed that when I try to search for jobs by their basic types (title, description, salary min and max) everything works fine, the data are selected well from the DB and shown at the console. However there is a need to filter jobs by their technologies and programming languages, and for that a JOIN with the tables with which Job is in relation must be done. Below is the code for searching by technology name:

    @Override
    public List<JobEntity> searchJobs() {
    log.info("JobServiceImpl -> searchJobs");
    List<JobEntity> foundJobs;
    JobSearchEntity searchCriteria = new JobSearchEntity();
    //cautam un job dupa o parte din descrierea sa
    List<String> techs = new ArrayList(); 
    techs.add("react");
    searchCriteria.setTechnology(techs);
    EntityManager entityManager = this.entityManagerFactory.createEntityManager();
    try {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<JobEntity> criteriaQuery = criteriaBuilder.createQuery(JobEntity.class);
        Root<JobEntity> jobs = criteriaQuery.from(JobEntity.class);
        Join<JobEntity, TechnologyEntity> technologies = jobs.join("technology", JoinType.INNER);
        //Join<JobEntity, ProgrammingLanguageEntity> progrLangs = jobs.join("programmingLanguages", JoinType.INNER);
        
        // make param list
        List<Predicate> predicates = new ArrayList<>();
        if (searchCriteria.getTechnology() != null && searchCriteria.getTechnology().size()>0) 
        {
            predicates.add(jobs.get("technology").in(searchCriteria.getTechnology()));
        }

However when I run it the following error is thrown by the application:

"timestamp": "2022-03-29T07:53:12.855+00:00",
"status": 500,
"error": "Internal Server Error",
"trace": "java.lang.IllegalArgumentException: Parameter value [react] did not match expected type [java.util.Collection (n/a)]\r\n\tat org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:54)\r\n\tat

Does someone is good and tell me what am I doing wrong in that selection by technology code? I'm sending as parameter an object of type ArrayList, which IS of type Collection (since it implements the interface). So why is that error thrown then?

1 Answers

Problem:

As per your JobEntity declaration. the technology field declaration is as following

@ManyToMany(cascade = {CascadeType.MERGE}, fetch=FetchType.EAGER)
@Column(nullable = false)
private List<TechnologyEntity> technology = new ArrayList<>();

So technology is a list of TechnologyEntity (a separate entity).

The log you are getting, is complaining that, you are not comparing along comparable types ( TechnologyEntity in List ).

Solution:

I presume, you have a name property of type String inside TechnologyEntity entity.

Then inside the checking update accordingly

predicates.add(jobs.get("technology").get("name").in(searchCriteria.getTechnology())); // Notice the additional `.get("name")`

Then it should work.

Related