JPQL EclipseLink Pagination

Question

I am having trouble implementing pagination in a JavaEE web application with EclipseLink.

The target entity:

@Entity
@JsonIdentityInfo(generator=JSOGGenerator.class)
@NamedQueries({
    @NamedQuery(
        name = Project.QUERY_FIND_FOR_ADMIN,
        query = "SELECT DISTINCT p " +
                "FROM Project p " +
                "LEFT JOIN FETCH p.documents " +
                "LEFT JOIN FETCH p.projectManager " +
                "LEFT JOIN FETCH p.watchingUsers " +
                "LEFT JOIN FETCH p.users " +
                "LEFT JOIN FETCH p.scheme " +
                "ORDER BY p.id",
        hints = {
            @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.documents.states")
//            ,@QueryHint(name = QueryHints.JDBC_FETCH_SIZE, value = Project.ITEMS_PER_PAGE) // does not change anything
//            ,@QueryHint(name = QueryHints.JDBC_MAX_ROWS, value = Project.ITEMS_PER_PAGE) // results in a LIMIT statement
        }
    )
})
public class Project extends BaseEntity {

    public static final String ITEMS_PER_PAGE = "" + 2;

    ...
}

A BaseDAO

public abstract class BaseEntityDAO<Entity> extends AbstractDAO {

  ...

    protected List<Entity> executeQuery(String queryName, Map<String, ?> parameters, final int firstResult) {
        TypedQuery<Entity> query = prepareQuery(queryName, parameters);
        query.setFirstResult(firstResult);
        query.setMaxResults(Integer.parseInt(Project.ITEMS_PER_PAGE));
        return query.getResultList();
    }

}

This results in the following queries:

// Logging
[2016-09-11T13:16:26.141+0200] [glassfish 4.1] [INFO] [] [] [tid: _ThreadID=28 _ThreadName=Thread-8] [timeMillis: 1473592586141] [levelValue: 800] [[
  page: 1]]
// Logging
[2016-09-11T13:16:26.141+0200] [glassfish 4.1] [INFO] [] [] [tid: _ThreadID=28 _ThreadName=Thread-8] [timeMillis: 1473592586141] [levelValue: 800] [[
  first row: 0]]

[2016-09-11T13:16:26.143+0200] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session./file:/Users/timtoheus/NetBeansProjects/discanno/target/swan-2.0/WEB-INF/classes/_SwanPU.sql] [tid: _ThreadID=32 _ThreadName=http-listener-1(5)] [timeMillis: 1473592586143] [levelValue: 500] [[
  SELECT DISTINCT t1.ID, t1.Name, t1.CREATOR_ID, t1.COLORSCHEME_ID, t0.ID, t0.Name, t0.TOKENIZATIONLANG, t0.Scheme FROM SCHEME t1 LEFT OUTER JOIN PROJECT t0 ON (t0.Scheme = t1.ID)]]

[2016-09-11T13:16:26.144+0200] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session./file:/Users/timtoheus/NetBeansProjects/discanno/target/swan-2.0/WEB-INF/classes/_SwanPU.sql] [tid: _ThreadID=28 _ThreadName=http-listener-1(1)] [timeMillis: 1473592586144] [levelValue: 500] [[
  SELECT DISTINCT t1.ID AS a1, t1.Name AS a2, t1.TOKENIZATIONLANG AS a3, t1.Scheme AS a4, t0.ID AS a5, t0.Name AS a6, t0.Text AS a7, 
  t0.project_fk AS a8, t2.ID AS a9, t2.Completed AS a10, t2.LastEdit AS a11, t2.document_fk AS a12, t2.user_fk AS a13, 
  t3.ID AS a14, t3.CreateDate AS a15, t3.EMail AS a16, t3.Lastname AS a17, t3.Password AS a18, t3.Prename AS a19, t3.Role AS a20, 
  t3.session AS a21, t4.ID AS a22, t4.CreateDate AS a23, t4.EMail AS a24, t4.Lastname AS a25, t4.Password AS a26, t4.Prename AS a27, 
  t4.Role AS a28, t4.session AS a29, t5.ID AS a30, t5.CreateDate AS a31, t5.EMail AS a32, t5.Lastname AS a33, t5.Password AS a34, 
  t5.Prename AS a35, t5.Role AS a36, t5.session AS a37, t6.ID AS a38, t6.Name AS a39, t6.CREATOR_ID AS a40, t6.COLORSCHEME_ID AS a41 F
  ROM PROJECT t1 LEFT OUTER JOIN DOCUMENT t0 ON (t0.project_fk = t1.ID) 
  LEFT OUTER JOIN STATE t2 ON (t2.document_fk = t0.ID) 
  LEFT OUTER JOIN (PROJECTS_MANAGER t7 JOIN Users t3 ON (t3.ID = t7.MANAGER_ID)) ON (t7.PROJECT_ID = t1.ID) 
  LEFT OUTER JOIN (PROJECTS_WATCHINGUSERS t8 JOIN Users t4 ON (t4.ID = t8.WATCHINGUSER_ID)) ON (t8.PROJECT_ID = t1.ID) 
  LEFT OUTER JOIN (USERS_PROJECTS t9 JOIN Users t5 ON (t5.ID = t9.USERS_ID)) ON (t9.PROJECT_ID = t1.ID) 
  LEFT OUTER JOIN SCHEME t6 ON (t6.ID = t1.Scheme) 
  ORDER BY t1.ID 
  LIMIT ? OFFSET ?
    bind => [2, 0]]]
// Logging
[2016-09-11T13:16:28.885+0200] [glassfish 4.1] [INFO] [] [] [tid: _ThreadID=28 _ThreadName=Thread-8] [timeMillis: 1473592588885] [levelValue: 800] [[
  number of results: 1]]

As you can see the query makes use of LIMIT and OFFSET and binds the desired parameters properly, but always returns the same result no matter what the page/ OFFSET parameter is. It always returns 'Project1' but there are in total 6 projects in the database (pgAdmin and IntelliJ can proof that). Also changing the ORDER BY to name/ id or increasing 'Project.ITEMS_PER_PAGE' does not change anything. The desired behavior should return the number of rows as declared as 'Project.ITEMS_PER_PAGE' and not always one. I want to avoid writing a native SQL query.

As listed here PostgreSQL should be supported by EclipseLink: https://www.eclipse.org/eclipselink/documentation/2.6/concepts/app_tl_ext001.htm

The expressions 'LIMIT', 'FETCH FIRST ROWS' and 'OFFSET' are not supported by JPQL. I also tried some QueryHints as stated above. The weird thing is that if I execute the query manually in Postgres it properly returns the desired amount of results.

I hope somebody can give me a hint.

Environment: GlassFish 4.1, PostgreSQL ~9.3, EclipseLink 2.6.2

Chris · Accepted Answer · 2016-09-12T19:36:35.213

1

Try removing your left outer joins - they really don't work with pagination, as pagination options are at the row level, while outer joins over ManyToMany relationships increase the number of rows that are required to fully read an entity. This is likely causing your entity to span multiple rows, so even though you are asking for the next 'entity' you are getting only the next row.. which happens to be for the same first entity.

Try using batch reads on ManyToMany and OneToMany relationships when using pagination instead, using a query hint or an annotation on the mapping

edited Sep 12 '16 at 19:36

answered Sep 12 '16 at 15:25

Chris

20,138
2
29
43

Ahhh, interesting! Thank you! I will try that. – Rooky Sep 12 '16 at 18:48

JPQL EclipseLink Pagination

1 Answers1