CODEWITHSUNDEEP
javamysqlhibernatespring-data-jpa
Hung Nguyen
Hung Nguyen

Reputation: 63

JPA Criteria API checking if an association collection is a subset of another collection

I have 2 tables: job and language. The relationship between these two tables is m:n, which means a job may require many languages and a language can be required by many jobs. So I have to create a third table called job_language to represent this relationship, it just contains two column job_id (which is an integer) and language_code (such as 'eng' for English or 'jap' for Japanese, etc.). The database i'm using is MySQL.

Now I have to create a REST service using Spring Data JPA to get all jobs that require a set of languages which is a subset of another set of languages from user's inputs. For example, suppose I have 3 jobs with the values in table job_language like below:

--------------------------
| job_id | language_code |
--------------------------
| 1      | 'eng'         |
--------------------------
| 2      | 'eng'         |
--------------------------
| 2      | 'vie'         |
--------------------------
| 3      | 'jap'         |
--------------------------
| 3      | 'eng'         |
--------------------------
| 3      | 'vie'         |
--------------------------

Now user wants to find jobs that require ('eng', 'vie') (this is a list of language_code created from user's input), the query should return two jobs: job with job_id = 1 and job with job_id = 2.

These are my entity classes:

@Entity
public class Job {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToMany
    @JoinTable(name = "job_language", 
        joinColumns = { @JoinColumn(name = "job_id", nullable = false) },
        inverseJoinColumns = { @JoinColumn(name = "language_code", nullable = false) })
    private List<Language> languages;

    // getters and setters here...
}

@Entity
public class Language { 
    @Id
    @Column(columnDefinition = "char", length = 3)
    private String code;

    @Column(length = 100)
    private String name;

    // getters and setters...
}

I have a MySQL query that does exactly this but since JPQL does not allow left joining two unrelated tables, I cannot find a solution using JPA Criteria API. Any suggestions?

This is my working query:

select jl1.job_id
from job_language jl1 left join job_language jl2 on jl1.job_id = jl2.job_id and jl1.language_code = jl2.language_code and jl2.language_code in ('eng', 'vie')
group by jl1.job_id
having count(jl1.language_code) = count(jl2.language_code);

Upvotes: 1

Views: 819

Answers (1)

manish
manish

Reputation: 20135

You can use your query as a native SQL query with a Spring Data repository. As an example:

public interface JobRepository extends JobRepository<Job, Long> {
  @Query(nativeQuery = true
      , value = "SELECT "
              + "  * "
              + "FROM"
              + "  job "
              + "WHERE "
              + "  id IN "
              + "  ( "
              + "  SELECT "
              + "    jl1.job_id "
              + "  FROM "
              + "    job_language jl1 "
              + "  LEFT JOIN "
              + "    job_language jl2 "
              + "  ON "
              + "    jl1.job_id = jl2.job_id "
              + "  AND jl1.language_code = jl2.language_code "
              + "  AND jl2.language_code IN ?1 "
              + "  GROUP BY "
              + "    jl1.job_id "
              + "  HAVING "
              + "    COUNT(jl1.language_code) = COUNT(jl2.language_code) "
              + "  )")
  List<Job> findAllByLanguageCodes(String... languageCodes);
}

The only criterion for using native queries is that Spring Data should be able to translate the return value(s) of a native query into the return type of the annotated method. We take advantage of this by putting a SELECT * FROM job at the top of the query, which allows Spring Data JPA to handle the results nicely since job is mapped to a JPA entity (even if job_location, which really drives the query, is not).

See a working example here.

Upvotes: 1

Related Questions