jambit ToiletPaper #116

SELECT n:m-tables with JPQL Projection

Problem: Several Database Accesses Needed for Join Tables

JPA is a widely used interface for database access and object-relational mapping (ORM). Using the annotations @ManyToMany and @JoinTable, n:m relationships between database tables can be written comfortably, quickly and legibly. But only because of this description, the Join table does not yet have its own Java class, which means that all objects of the Join table’s "left" part must first be retrieved before the "right" part can be aggregated using lazy loading. The information of the Join table can only be used after this step.

Solution: JPQL Projection

Using the keyword NEW and specifying a constructor, JPQL can create one object per record for SELECT queries. This means that table columns are projected onto an object and opens the possibility to obtain the Join table with ONE database access.


A project has several employees and one employee can work in several projects.

public class Project {
    @Id @Column(name = "ID")
    private long id;

    @ManyToMany @JoinTable(name = "PROJECT_EMPLOYEE",
      joinColumns = @JoinColumn(name = "PROJECT_ID", referencedColumnName = "ID"),
      inverseJoinColumns = @JoinColumn(name = "EMPLOYEE_ID", referencedColumnName = "ID"))
    private List<Employee> employees;
public class Employee {
    @Id @Column(name = "ID")
    private long id;

    @ManyToMany(mappedBy = "employees")
    private List<Project> projects;
public class JoinTable {
    private long projectId, employeeId;

    public JoinTable(long projectId, long employeeId) {
        this.projectId = projectId; this.employeeId = employeeId;
// Spring Data
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
public interface ProjectRepository extends JpaRepository<Project, Long> {
    @Query("SELECT NEW com.jambit.toiletpaper.JoinTable(p.id, e.id) FROM Project p " +
            "JOIN p.employees e WHERE p.id = ?1")
    List<JoinTable> getAllJoinTableById(long id);

Further Aspects


Author: Matthias Mair / Senior Software Architect / Business Division Automotive Bavaria

Download Toilet Paper #116: SELECT n:m-tables with JPQL Projection (pdf)

SELECT n:m-Tabellen mit JPQL Projection

Cookie Settings

This website uses cookies to personalize content and ads, provide social media features, and analyze website traffic. In addition, information about your use of the website is shared with social media, advertising, and analytics partners. These partners may merge the information with other data that you have provided to them or that they have collected from you using the services.

For more information, please refer to our privacy policy. There you can also change your cookie settings later on.

contact icon

Contact us now