Announcement Announcement Module
Collapse
No announcement yet.
spring-data-jpa, Eclipselink and sorting problems Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • spring-data-jpa, Eclipselink and sorting problems

    Hi all,

    we have some trouble with spring-data-jpa and EclipseLink and sorting. Maybe this is still a bug related to https://jira.springsource.org/browse/DATAJPA-252 or this is some special behavior of EcpliseLink or we misuse spring-data-jpa - we are stuck.

    To explain the unexpected behavior we created a very simple demo application using
    • spring-data-jpa 1.3.0.RELEASE
    • Java 1.7
    • spring 3.1.4
    • Eclipselink 2.4.1

    We have a simple entity Employee

    Code:
    package de.empulse.spring.data.jpa.model;
    
    import javax.persistence.CascadeType;
    import javax.persistence.Entity;
    import javax.persistence.FetchType;
    import javax.persistence.OneToOne;
    
    import org.springframework.data.jpa.domain.AbstractPersistable;
    
    @Entity
    public class Employee extends AbstractPersistable<Long> {
    
        private static final long serialVersionUID = 5567012294089395902L;
    
        private String firstName;
        private String lastName;
    
        //@JoinFetch(JoinFetchType.OUTER)
        @OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
        private Address address;
    
      ...
    }
    and a simple entity Address
    Code:
    package de.empulse.spring.data.jpa.model;
    
    import javax.persistence.Entity;
    
    import org.springframework.data.jpa.domain.AbstractPersistable;
    
    @Entity
    public class Address extends AbstractPersistable<Long> {
    
        private static final long serialVersionUID = -5631651679143112560L;
    
        private String street;
        private String houseNumber;
        private String zip;
        private String city;
        private String region;
        private String country;
    
    ...
    
    }
    Each Employee may have an Address.

    In a testcase we created 3 Employee entities in the persistence, two have an Address, one not. If we call now something like
    Code:
    /*
     * Get employees sorted by address.street
     */
    Sort addressStreetSort = new Sort("address.street");
    assertEquals("sorting by address.streetname must return all employees", 3, employeeRepository.findAll(addressStreetSort).size());
    the test fails because the repository returns just 2 Employees and not all 3.

    This seems to be caused by the SQL created by spring-data-jpa/Eclipselink
    Code:
    SELECT t1.ID, t1.FIRSTNAME, t1.LASTNAME, t1.ADDRESS_ID FROM ADDRESS t0, EMPLOYEE t1 WHERE (t0.ID = t1.ADDRESS_ID) ORDER BY t0.STREET ASC
    because this expression skips all Employees with null Addresses. In our opinion some LEFT JOIN expression should be created.

    We tried to use EclipseLink specific @JoinFetch(JoinFetchType.OUTER), but this didn't help.

    You can find a Maven project demonstrating the problem in https://bitbucket.org/empulse/spring-data-jpa-sorting

    Is this a bug? Is there a work-around without using JPQL expressions in the model?

    Any help is appreciated and thank you in advance,
    Christoph

  • #2
    Hi,

    we made little progress not using the findAll() method and introducing a new custom find method using JP-QL in the Repository like
    Code:
        @Query("select e from Employee e left outer join e.address address")
        List<Employee> findEmployees(Sort sort);
    In the example project this works, but we dislike this approach because the repository needs to know the inner structure of our entities.

    Additionally in our real life project we use JPA Specifications and paging which seen not to work properly in combination with JP-QL.

    Maybe it is possible to patch
    Code:
    org.springframework.data.jpa.repository.query.QueryUtils
    so LEFT JOINs are used for queries if sorting is needed?

    Cheers,
    Christoph

    Comment

    Working...
    X