CODEWITHSUNDEEP
jpaeclipselinkjpql
Basil
Basil

Reputation: 648

Query value of JoinColumn with JPQL

I have two JPA entities

public class Job {
  @ManyToOne
  @JoinColumn(name = "service")
  public Service service;

  @Column(name = "queue_time")
  public Long queueTime;

  @Column(name = "run_time")
  public Long runTime;
}

public class Service {
  @Id
  @Column(name = "id")
  public Long id;

  @Column(name = "name")
  public String name;

  @Column(name = "host")
  public String host;
}

Now I want to do some aggregation queries with JPQL:

SELECT job.service.id, AVG(job.queueTime), AVG(job.runTime) FROM Job job GROUP BY job.service.id

The resulting SQL query (I'm using a MySQL database) looks like this:

SELECT t0.id, AVG(t1.queueTime), AVG(t1.runTime) FROM Service t0, Job t1 WHERE (t0.service = t1.id) GROUP BY t0.id

As you can see, JPA translates my JPQL query to a SQL query with a join. This however slows down the query dramatically. The following SQL query executes ~6 time faster and returns the exact same result set:

SELECT t1.service, AVG(t1.queueTime), AVG(t1.runTime) FROM Job t1 GROUP BY t1.service

If I change the JPQL query to

SELECT job.service, AVG(job.queueTime), AVG(job.runTime) FROM Job job GROUP BY job.service

the resulting SQL query looks like this:

SELECT t0.id, t0.name, t0.host AVG(t1.queueTime), AVG(t1.runTime) FROM Service t0, Job t1 WHERE (t0.service = t1.id) GROUP BY t0.id, t0.name, t0.host

Is there a way to write the JPQL which only queries the job table without making a join to the service table?

Upvotes: 1

Views: 7316

Answers (1)

Basil
Basil

Reputation: 648

This question solved the issue for me: How can I retrieve the foreign key from a JPA ManyToOne mapping without hitting the target table?

I took the second solution (b) Use read-only fields for the FKs)

Upvotes: 0

Related Questions