Hibernate native query selecting entities with joined many-to-many association

Question

I’m not used to ask for help but I’m really stuck for a few days. No way to find the same issue on Stackoverflow or Google...

I have two entities Event and Artist with a many-to-many relationship. The join table is EventArtists.

Event <-- EventArtists --> Artist

Artist

@Entity
public class Artist {
@Id
@GeneratedValue
private Long idArtist;
private String name;
private String description;
@ManyToMany(mappedBy = "artists")
private Set<Event> events = new HashSet<Event>();

Event

@Entity
public class Event {
@Id
@GeneratedValue
private Long idEvent;
private String name;
private String description;
@ManyToMany(cascade = {CascadeType.ALL}, fetch = FetchType.LAZY)
@JoinTable(name="EventArtists",
        joinColumns=
        @JoinColumn(name="idEvent"),
        inverseJoinColumns=
        @JoinColumn(name="idArtist")
)
private Set<Artist> artists = new HashSet<Artist>();

I’m trying to fetch an event and its associated artists using a native query. According to the documentation, I’m using addEntity and addJoin. Since the two entities are mapped to the same column names (id, name, description), I also use aliases.

Here is my request:

NativeQuery nativeQuery = session.createNativeQuery("SELECT {e.*}, {a.*} "+
      "FROM Event e "+
      "LEFT JOIN EventArtists ea ON e.idEvent = ea.idEvent "+
      "LEFT JOIN Artist a ON a.idArtist = ea.idArtist "+
      "WHERE e.idEvent = :id");
nativeQuery.addEntity("e", Event.class)
         .addJoin("a", "e.artists")
         .setParameter("id", 1);
nativeQuery.getResultList();

nativeQuery.getResultList() throws the following exception :

org.h2.jdbc.JdbcSQLException: Column "A.IDEVENT" not found

Indeed, Hibernate doesn’t generate correctly the request. It looks for columns "a.idEvent as idEvent1_2_0__, a.idArtist as idArtist2_2_0__", which are EventArtists's columns.

We get:

SELECT e.idEvent as idEvent1_1_0_, e.description as descript2_1_0_, e.name as name3_1_0_, a.idEvent as idEvent1_2_0__, a.idArtist as idArtist2_2_0__, a.idArtist as idArtist1_0_1_, a.description as descript2_0_1_, a.name as name3_0_1_ FROM Event e LEFT JOIN EventArtists ea ON e.idEvent = ea.idEvent LEFT JOIN Artist a ON a.idArtist = ea.idArtist WHERE e.idEvent = ?

instead of

SELECT e.idEvent as idEvent1_1_0_, e.description as descript2_1_0_, e.name as name3_1_0_, a.idArtist as idArtist1_0_1_, a.description as descript2_0_1_, a.name as name3_0_1_ FROM Event e LEFT JOIN EventArtists ea ON e.idEvent = ea.idEvent LEFT JOIN Artist a ON a.idArtist = ea.idArtist WHERE e.idEvent = ?

Here is a complete TestCase: https://github.com/volvotrax/hibernate/
The HQL query works perfectly but I need to use a native query since the where clause is much more complicated and relies on a database functionality.

"FROM Event e LEFT JOIN FETCH e.artists WHERE e.idEvent = :id"

Renaming column names to avoid using aliases is a solution but it’s not possible.
I'm using Hibernate 5.2

Your assistance will be very much appreciated. Thank you

Hibernate native query selecting entities with joined many-to-many association

0 Answers0