HQL select query on @ManyToMany mapped entity produces invalid SQL syntax

Question

There are a bunch of questions here about queries on HQL many-to-many mapped tables but I have looked quite a bit and have not seen this one.

I have two tables in a many-to-many relationship with the source and destination tables mapped as entities and the @ManyToMany relationship defined between them, configured as is commonly recommended.

The twist is that I am trying to query on these tables so that I can get the values for both the source and destination keys in a single query. The reason is for performance, we have deeply nested hierarchical data and to get all of the related data via vanilla Hibernate results in dozens or hundreds of queries which doesn't perform adequately. Instead I intend on fetching all of the data at once and mapping it in code.

Here are sample class definitions:

@Entity
public class This {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long id;

    @Column
    protected Long otherId;

    @ManyToMany
    @JoinTable(joinColumns = {@JoinColumn(name = "this_id")},
        inverseJoinColumns = {@JoinColumn(name = "that_id")})
    private List<That> thats;

    // getters and setters
}

and

@Entity
public class That {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "name", unique = true)
    private String name;

    // getters and setters
}

The HQL is this:

select t.id,t.thats from This t where t.otherId=13

And here is the SQL that gets produced:

select
    this0_.id as col_0_0_,
    . as col_1_0_,
    that2_.id as id1_59_,
    that2_.name as name2_59_ 
from
    this this0_ 
inner join
    this_thats thats1_ 
        on this0_.id=thats1_.this_id 
inner join
    that that2_ 
        on thats1_.that_id=that2_.id 
where
    this0_.other_id=13

As you can see, the 2nd column in the select clause is invalid, it looks as though it is trying to create a column but does not use a valid column name or alias and somewhat surprisingly produces a SQL statement of invalid syntax that fails when it is executed. I have stepped through the SQL generation code and turned on trace logging but can't figure out what it's trying to do or how to fix it.

If I delete that 2nd column, this query runs fine and returns exactly the data I'm looking for. As an alternative I've considered implementing this in native SQL, but I'd like to avoid this and stick with HQL if possible for consistency across the rest of the project.

Another option I tried was select t,t.thats .... This did produce a valid query, but, not shown in this example, it issued a lot more queries for other relationships that are eagerly fetched from the This entity which also kills the performance. I really need to fetch just the single key column from the source table.

The really minimal query that would return the data that I need would not have to hit the That table at all, it would be only on the This table and the implicit, unmapped this_thats table which is created and managed by JPA but is not mapped to an entity. I tried mapping this to an entity so that I could query on it but Hibernate complained on startup that this table was mapped twice. If there is some other way to get at this table and query it directly I'd be happy to do that.

This is with hibernate-jpa-2.1 which I know is an old version but this is a legacy system in production. If upgrading the version of JPA would help I'd consider it but would like to understand the root cause first if possible.

Answer 1

I figured out the answer, which I'm posting to help anyone else who runs into this. The join needs to be explicit in the HQL like this:

select t.id,th from This t join t.thats th where t.otherId=13

Apparently Hibernate can't figure out the correct select statement without this.