Hibernate @ManyToMany not uses joinTable and produces Cartesian instead of inner join

Question

Colleagues, could you please to advise me with Hibernate issues when the relationship @ManyToMany. Project Entity:

    @Entity
    @Table(name = "PROJECT")
    @NamedQueries( {
        @NamedQuery(name = "Projects.employeesByExternal", query = "SELECT P FROM Project P INNER JOIN Employee E ON E.external=:external" )
    } )
    public class Project extends AbstractIdentified
    {
        @Column(name = "NAME") private String name;
        @Column(name = "EXT") private Boolean external;

        @ManyToMany
        @JoinTable( name = "EMPLOYEE_PROJECTS",
        joinColumns = { @JoinColumn(name = "PROJECT_ID") },
        inverseJoinColumns = { @JoinColumn(name = "EMPLOYEE_ID") } )
        private Set<Employee> employees;
    ... 
    // getters/setters here
    }

Employee entity:

    @Entity
    @Table(name = "EMPLOYEE")
    public class Employee extends AbstractIdentified
    {
        @ManyToMany(mappedBy = "employees")
        private Set<Project> projects;
        ....
        // other fields and getters/setters here
    }

When the application starts it runs init.sql:

insert into employee (id,status,ext) values (1,'CORP',true);
insert into employee (id,status,ext) values (2,'CORP',false);
insert into employee (id,status,ext) values (3, 'EXT',true);
insert into project (id,name) values (1,'project1');
insert into project (id,name) values (2,'project2');
insert into project (id,name) values (3,'project3');
insert into employee_projects (employee_id, project_id) values (1,1);
insert into employee_projects (employee_id, project_id) values (2,2);
insert into employee_projects (employee_id, project_id) values (3,3);

In DAO I prepared DAO method to select Project where employee (at least one) has external=false:

public List<Project> getProjectsByExternal( boolean isExternal )
{
    TypedQuery<Project> typedQuery = entityManager.createNamedQuery( "Projects.employeesByExternal", Project.class );
    typedQuery.setParameter( "external", isExternal );
    return typedQuery.getResultList();
}

Now I invoke the method as followed:

List<Project> list = projectDao.getProjectsByExternal(false);

And I expect to get only one Project#2{name='project2'}, but the result is 3 projects:

01:03:49 INFO  vez.Starter - Project#1{name='project1'}
01:03:49 INFO  vez.Starter - Project#2{name='project2'}
01:03:49 INFO  vez.Starter - Project#3{name='project3'}

When I check what SQL been generated by Hibernate (property hibernate.show_sql = true) I noticed that Hibernate creates join tables "PROJECT" and "EMPLOYEE" without using intermediate table "EMPLOYEE_PROJECTS" (see annotation @JoinTable in Project class). There is SQL generated:

01:03:49 DEBUG org.hibernate.SQL - 
    select
        project0_.ID as ID1_3_,
        project0_.NAME as NAME2_3_ 
    from
        PROJECT project0_ 
    inner join
        EMPLOYEE employee1_ 
            on ( employee1_.EXT=?)

Actually, this means that one Employee has been selected and multiplied for all Projects. I puzzled because this the Cartesian but not inner join I expected. Please advise what I've done wrong? How can I use NativeQuery to select only Projects with one/many of Employees has external attribute value=false? pom.xml:

<spring.version>5.0.5.RELEASE</spring.version>
<hibername.version>5.3.0.CR2</hibername.version>

score 2 · Answer 1 · answered May 11 '18 at 11:22

2

The problem comes from your JPQL query:

SELECT P FROM Project P INNER JOIN Employee E ON E.external=:external

You should change it to:

SELECT P 
FROM Project P 
INNER JOIN P.employees E 
Where E.external=:external

answered May 11 '18 at 11:22

Vlad Mihalcea

142,745
71
566
911

thank you. I changed JPQL query accordingly to: `SELECT P FROM Project P INNER JOIN Employee E WHERE E.external=:external`. There is odd, but Hibernate generates wrong sql query again: `15:24:24 DEBUG org.hibernate.SQL - select project0_.ID as ID1_3_, project0_.NAME as NAME2_3_ from PROJECT project0_ inner join EMPLOYEE employee1_ on where employee1_.EXT=?`. Therefore a SQLGrammarException `java.sql.SQLSyntaxErrorException: Syntax error: Encountered "where" at line 1, column 116`. Hibernate bug? – vzateychuk May 11 '18 at 12:34
1

Your JPQL still doesn't match the one I wrote. Use the p.employees alias. – Vlad Mihalcea May 11 '18 at 12:37

Hibernate @ManyToMany not uses joinTable and produces Cartesian instead of inner join

1 Answers1