How to make a select statement on a list contained within an object in spring?

Question

How do you make a select statement or filter a List that is nested within an entity in spring? I have an object that looks like this...

@Entity
@Table(name = "employee")
public class Employee {

...

    @OneToMany(mappedBy = "_employee", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE)
    @JsonManagedReference
    Set<Deal> _deals;

    @OneToMany(mappedBy = "_employee", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE)
    @JsonManagedReference //This is simply to avoid a stackoverflow error according to this link http://stackoverflow.com/questions/3325387/infinite-recursion-with-jackson-json-and-hibernate-jpa-issue
    Set<Recommendation> _recommendations;


    @OneToMany(mappedBy = "_employee", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE)
    @JsonManagedReference //This is simply to avoid a stackoverflow error according to this link http://stackoverflow.com/questions/3325387/infinite-recursion-with-jackson-json-and-hibernate-jpa-issue
    Set<Event> _events;

  public Employee() {
    }

//getters and setters

....

I get employees with a repository that is accessed by a service class.

The repository looks like this.

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

    public Employee getEmployeeById(Long _id);

    public Employee getEmployeeBy_username(String username);

}

So bascially when I get an employee by its id, it returns the above lists. When an employee is retrieved I need to do a select statement or filter in some way _deals, _recommendations and _events. So that only those who have the boolean attribute _active=true returned. As it is now, all deals recommendations and events are returned whether they are active or not. How do I filter or select from these lists only active objects?

so how are you currently selecting your entities? JPQL, CriteriaApi, Spring Data repository, native sql ... there are dozens of options and so far you only showed the entity — Jens Schauder, Jan 15 '17 at 15:46
I use a spring data repository, I have edited the comment to show how an employee is retrieved. — slipperypete, Jan 15 '17 at 16:24

score 1 · Accepted Answer · answered Jan 15 '17 at 22:27

1

You almost always select a single Entity type per query, and preferably you would do the filtering in the database. If you want the Deals, Recommendations and Events belonging to a specific Employee, I would normally put these methods in the Repository belonging to entity type I'm trying to load, it could look like this:

@Repository
public interface DealRepository extends JpaRepository<Deal, Long> {

    @Query("select d from Deal d where d.active= true and d.employee.id = :employeeId")
    List<Deal> findActiveDeals(@Param("employeeId") long employeeId);
}

answered Jan 15 '17 at 22:27

Klaus Groenbaek

4,820
2
15
30

Ok so in this case for example I select the employee separate and then select the deals recommendations and events (all seperate too) which are active for that employee? – slipperypete Jan 15 '17 at 22:41
If you already have the employeeId and don't need the data in the Employee table, you don't need to select him first. If you need data from the Employee, you should probably change the collection FetchType to Lazy, since you will otherwise load ALL the Deals, Recommendation, Events when you load the Employee. – Klaus Groenbaek Jan 15 '17 at 22:50
I got this kind of working, but then I got a bit confused. If you suggest "You almost always select a single Entity type per query, and preferably you would do the filtering in the database", would it be better to select deals, recommendations seperately, and then not have the @OneToMany relationships? – slipperypete Jan 16 '17 at 14:56
Databases use foreign keys to model relations, and as such it is the ManyToOne that defined the relation in JPA. Any relationship can be seen from two sides, OneToMany is the other side of this bi-directional relation OneToMany it is optional, although I would always recommend that you have it. My OneToMany are always lazy loaded, and you typically don't iterate it unless you loop over the entire collection. In you case you need to filter the collection, and this is best done by the database, and therefore you load it using the filtered entity's repository. – Klaus Groenbaek Jan 16 '17 at 16:38
Thanks. I figured it out, my problem was I did not have a good understanding of lazy loading, I read about it solved the issue, and now it seems to be working right. Thanks again. – slipperypete Jan 16 '17 at 18:22

How to make a select statement on a list contained within an object in spring?

1 Answers1