JPA one-to-many filtering

Question

We are nesting several entities. However upon retrieving we only want to get those entities which are active.

@Entity
public class System {
  @Id
  @Column(name = "ID")
  private Integer id;

  @OneToMany(mappedBy = "system")
  private Set<Systemproperty> systempropertys;
}

@Entity
public class Systemproperty {
  @Id
  @Column(name = "ID")
  private Integer id;

  @Id
  @Column(name = "ACTIVE")
  private Integer active;
}

When requesting the Systemproperties I only want to get the properties that are active (active = 1).

Searching around I found some hibernate annotations and the possibility to use subqueries. However both don't really work for me. Even though we are currently using hibernate I'm considering to replace it with Eclipselink, because we currently have to use eager loading and we are likely to run into performance problems with that. The subqueries don't really work well, because we are nesting several levels.

Eclipselink seems to have a @Customizer annotation that could work, however it seems to follow a different concept then the hibernate @FilterDef annotation and would cause additional overhead when switching.

The @JoinColumn doesn't seem to allow further filtering. Is there a standard JPA way to solve this problem?

Slightly OT, but are you sure that `active` should be part of `Systemproperty` ID? — Adam Dyga, Nov 09 '12 at 10:26
@AdamDyga I would kick the old Systemsproperties out, however that would violate some foreign keys. — Udo Held, Nov 09 '12 at 10:27
@AleksandrM I'd prefer a none hibernate dependent solution as mentioned above. — Udo Held, Nov 09 '12 at 10:28
@AleksandrM `@Filter` works if get the entity directly (eg. `EntityManager.find()` ). It's not applied when you get the entities via a `Set` or `Collection` that is mapped with JPA/Hibernate — Adam Dyga, Nov 09 '12 at 10:37
If you fetch your `System` entity and filter is applied to `Systempropertys` set then `Systempropertys` should be filtered. — Aleksandr M, Nov 09 '12 at 10:42

score 30 · Answer 1 · edited Jan 01 '20 at 17:00

30

Another hibernate way of doing it with @Where:

@Entity
public class System {
  @Id
  @Column(name = "ID")
  private Integer id;

  @OneToMany(mappedBy = "system")
  @Where(clause = "active = true")
  private Set<Systemproperty> systempropertys;
}

@Entity
public class Systemproperty {
  @Id
  @Column(name = "ID")
  private Integer id;

  @Id
  @Column(name = "ACTIVE")
  private Integer active;
}

edited Jan 01 '20 at 17:00

Meena Chaudhary

9,909
16
60
94

answered Apr 05 '17 at 18:50

danial

4,058
2
32
39

2

You have saved my day. – Vishvesh Phadnis Oct 05 '19 at 15:24
What if some of properties in systempropertys do not exist in SystemProperty class. – Ram Aug 04 '20 at 06:18

score 7 · Accepted Answer · answered Nov 09 '12 at 10:50

AFAIK there is no portable JPA-based way to do this. A clean, however a little bit inefficient, solution would be to do everything on Java-side and create a getter getActiveSystemproperties() that manually iterates over mapped systempropertys and returns an immutable set of active properties.

score 2 · Answer 3 · answered Mar 20 '14 at 07:53

I needed to solve similar problem in EclipseLink. I used special SessionCustomizer and changed mapping condition for OneToMany annotation. Maybee Hibernate has something similar.

Add customizer to persistence unit:

props.put(PersistenceUnitProperties.SESSION_CUSTOMIZER,MySessionCustomizer.class.getName());
EntityManagerFactory factory = new PersistenceProvider().createEntityManagerFactory(pu.getPersistenceUnitName(), props);

Fragment of customizer:

public class MySessionCustomizer implements SessionCustomizer {
    public void customize(Session session) throws Exception {
        final Map<?, ?> descs = session.getDescriptors();
    if (descs != null)
    {
      // This code assumes single table per descriptor!
      for (final Object descObj : descs.values())
      {
        final ClassDescriptor desc = (ClassDescriptor) descObj;
        final Class<?> sourceClass = desc.getJavaClass();

        for (DatabaseMapping mapping : desc.getMappings())
        {
          if (mapping instanceof OneToManyMapping)
          {
            final OneToManyMapping collectionMapping = ((OneToManyMapping) mapping);

            // create default foreign key condition (nescessary):
            final DatabaseField sourceField = mapping.getSourceKeyFields().get(0);
            final DatabaseField targetField = mapping.getTargetForeignKeyFields().get(0);
            final Expression defaultFkExpression =  new ExpressionBuilder(mapping.getReferenceClass()).getParameter(sourceField).equal(eb.getField(targetField));

            // add filter condition "additionalExpression"
            final Expression finalExpression = defaultFkExpression.and(additionalExpression);

             // SET default foreign key condition and filter condition to mapping
             mapping.setSelectionCriteria(finalExpression);
          }
        }
      }
    }
    }
}

score 1 · Answer 4 · answered Sep 05 '13 at 09:36

1

There is no JPA way, but you can try Hibernate filetrs: http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#objectstate-filters

answered Sep 05 '13 at 09:36

hurtledown

673
6
18

JPA one-to-many filtering

4 Answers4

Linked