JPQL - Query manytomany association (mongodb + jpa + hibernate)

Question

I'm using mongodb, jpa and hibernate ogm and I have problems with a query. I have two entities: User and Entitlement, with a unidirectional ManyToMany association between them, so an Entitlement contains a list of User. I'm trying to get all entitlements whose users field contains a specific user.

@Entity
public class User {
@Id
private String id;

private String name;

private String surname;
//getters and setters...
}

@Entity
public class Entitlement {

@Id
private String id;

@ManyToMany(fetch = FetchType.EAGER, cascade = { CascadeType.PERSIST,
        CascadeType.MERGE, CascadeType.REFRESH, CascadeType.DETACH })
private Set<User> users = new HashSet<User>();

private String name;

//getters and setters...
}

I configured hibernate to store association information in a dedicated document per association. The following is the persistence.xml file

<?xml version="1.0"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
         version="2.0">

<persistence-unit name="org.hibernate.ogm.tutorial.jpa" transaction-type="JTA">
    <!-- Use Hibernate OGM provider: configuration will be transparent -->
    <provider>org.hibernate.ogm.jpa.HibernateOgmPersistence</provider>

    <class>com.example.mongodb.model.User</class>
    <class>com.example.mongodb.model.Entitlement</class>

    <properties>
        <property name="hibernate.transaction.jta.platform"
                  value="org.hibernate.service.jta.platform.internal.JBossStandAloneJtaPlatform" />
        <property name="hibernate.ogm.datastore.provider" value="mongodb" />
        <property name="hibernate.ogm.datastore.host" value="localhost"/>
        <property name="hibernate.ogm.datastore.database" value="mydb"/>
        <property name="hibernate.ogm.datastore.create_database" value="true"/>
        <property name="hibernate.ogm.datastore.document.association_storage" value="ASSOCIATION_DOCUMENT"/>
    </properties>
</persistence-unit>

My objective is to get all entitlements whose "users" field contains a specific user. I tried the following queries:

This query returns all entitlements present in db, without any filters

User u = em.find(User.class, id);//id is the user id that I use to find the entitlements

List<Entitlement> list = em
        .createQuery(
                "select e from Entitlement e fetch all properties where :user MEMBER OF e.users",
                Entitlement.class).setParameter("user", u)
        .getResultList();
return list;

2.

User u = em.find(User.class, id);

HashSet<User> userSet = new HashSet<User>();
userSet.add(u);
List<Entitlement> list = em
        .createQuery(
                "select e from Entitlement e fetch all properties where e.users In (:user)",
                Entitlement.class).setParameter("user", userSet)
        .getResultList();
return list;

With this query I get the following error:

java.lang.UnsupportedOperationException: cannot doAfterTransactionCompletion lookups on collections.

3.

  List<Entitlement> list = em
        .createQuery(
                "select e from Entitlement e inner join fetch e.users u where u.id IN (:id) ",
                Entitlement.class)
        .setParameter("id", Arrays.asList(id)).getResultList();

return list;

This query produces this error:

java.lang.UnsupportedOperationException: Unrecognized property type: org.hibernate.type.SetType(com.example.mongodb.model.Entitlement.users)

None of these work.

"fetch all properties" is invalid JPQL. "JOIN" is hardly likely to work on a database that doesn't do joins. There is a reason why JPA was designed for RDBMS and only RDBMS. — , Oct 13 '17 at 14:23
if it is possible to make association birectional, then you could just `User.getEntitlements()` — pirho, Oct 13 '17 at 14:30
@pirho yes, it sames the right choise. But when I delete an entitlement I want that this entitlement is removed also in the entitlements list in User entity. Is this operation "automatically", or do I explicit it, for example: em.remove(em.contains(entitlement) ? entitlement : em .merge(entitlement)); for (User u : entitlement.getUsers()) { u.getEntitlements().remove(entitlement); em.merge(u); } — Luigi Biasi, Oct 13 '17 at 15:00
maybe this answers your question [How to remove entity with ManyToMany relationship in JPA (and corresponding join table rows)?](https://stackoverflow.com/questions/1082095/how-to-remove-entity-with-manytomany-relationship-in-jpa-and-corresponding-join) — pirho, Oct 13 '17 at 15:06
I have another problem, I'm removing an association between one User and one Entitlement in this way: user.getEntitlements().remove(entitlement); entitlement.getUsers().remove(user); em.merge(user); but after commit and close entity manger, in the Database nothing change and the user continues to have the entitlement. How can I remove the association? Making a find before to commit, the entitlement was removed, but this not reflect to DB. Why? Thanks — Luigi Biasi, Oct 16 '17 at 15:35

JPQL - Query manytomany association (mongodb + jpa + hibernate)

0 Answers0