Search code examples
mysqlsqlhibernatemany-to-manyhql

HQL many to many between 3 tables

I have 3 tables :

User ->>- many to many ->>- Userapp ->>- many to many ->>- Application

User have :

  • userId

  • userName

UserApp :

  • userId

  • applicationId

Applicaiton :

  • applicationId

  • applicaitonName

I didn't succeed in creating a HQL query which returns every Application of one specific user.

My HQL :

select a.userId, a.userName from Application b join b.userId a where b.userId = 1

Simplify query i would like to do : from Application WHERE Userapp.userID = 1

could you help me please :) ?

EDIT :

My tools :

  • Netbean 8.x

  • Hibernate plugin

Second error : org.hibernate.hql.internal.ast.QuerySyntaxException: Userapp is not mapped

When i create hibernate mapping files and POJOs from database it creates me 2 objects : User and Application. But not the associative table "Userapp"

My hibernate.reveng.xml : 

<hibernate-reverse-engineering>
  <schema-selection match-catalog="allin"/>
  <table-filter match-name="user"/>
  <table-filter match-name="application"/>
  <table-filter match-name="userapp"/>
</hibernate-reverse-engineering>

Regards

Solution

  • I think your query should be like this:

    SELECT a.applicaitonName
FROM User u
    LEFT JOIN UserApp ua ON u.userId= ua.userId
    LEFT JOIN Application a On ua.applicationId= a.applicationId
WHERE
    u.userName = ?

    or

    SELECT a.applicaitonName
FROM UserApp ua
    LEFT JOIN Application a On ua.applicationId= a.applicationId
WHERE
    ua.userId = ?