Announcement Announcement Module
Collapse
No announcement yet.
How to return a page of results from a typed query ? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to return a page of results from a typed query ?

    Hello,

    I have a custom repository with a TypedQuery that is supposed to return a Page of results and not a List of results.

    But I can't see how I can just extract a page from the results.

    Here is the method implementation:
    Code:
        @Override
        @Transactional(readOnly = true)
        public Page<ElearningSubscription> searchWithDistinctUserAccounts(String searchTerm, Pageable page) {
             String sqlStatement = "SELECT es FROM ElearningSubscription es, UserAccount ua WHERE es.userAccount.id = ua.id AND (LOWER(ua.firstname) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(ua.lastname) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(ua.email) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR ua.id = :searchTerm";
             if (searchTerm.contains(" ")) {
                 String[] bits = searchTerm.split(" ");
                 String firstname = bits[0];
                 String lastname = bits[1];
                 sqlStatement += " OR (LOWER(ua.firstname) LIKE LOWER(CONCAT('%', " + firstname + ", '%')) AND LOWER(ua.lastname) LIKE LOWER(CONCAT('%', " + lastname + ", '%')))";
             }
             sqlStatement += " GROUP BY ua.id ORDER BY ua.firstname, ua.lastname, es.subscriptionDate DESC";
             
             TypedQuery<ElearningSubscription> query = elearningSubscriptionRepository.getEntityManager().createQuery(sqlStatement, ElearningSubscription.class);
    
             query.setParameter("searchTerm", searchTerm);
             
    //         Page<ElearningSubscription> elearningSubscriptions = query.getResultList();
             
    //         return elearningSubscriptions;
             return null;
        }
    I guess I should be able to make use of the passed in page method argument somewhere in the query creation.

    Any clue ?

    Kind Regards,

  • #2
    I had the same problem as you.

    Here is solution:

    Code:
    Page<ElearningSubscription> elearningSubscriptions = PageImpl<ElearningSubscription>(query.getResultList());

    Comment


    • #3
      Hey,

      can i use the same query in struts??

      Comment


      • #4
        Thanks Paulek. But how to you hand the page number, number per page and total, back to the controller ?

        The way you do it, your returned page is always the first one ? What about all the pagination information the controller needs to send the client ?

        Comment


        • #5
          I'm trying with this approach, but I wonder if it will fly...

          The trouble is, there are two calls to get the result list.

          Is this efficient ?

          Code:
              public Page<ElearningSubscription> search(String searchTerm, Pageable page) {
                   String sqlStatement = "SELECT es FROM ElearningSubscription es, UserAccount ua WHERE es.userAccount.id = ua.id AND (LOWER(ua.firstname) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(ua.lastname) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(ua.email) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR ua.id = :searchTerm";
                   if (searchTerm.contains(" ")) {
                       String[] bits = searchTerm.split(" ");
                       String firstname = bits[0];
                       String lastname = bits[1];
                       sqlStatement += " OR (LOWER(ua.firstname) LIKE LOWER(CONCAT('%', " + firstname + ", '%')) AND LOWER(ua.lastname) LIKE LOWER(CONCAT('%', " + lastname + ", '%')))";
                   }
                   sqlStatement += " ORDER BY ua.firstname, ua.lastname, es.subscriptionDate DESC";
                   
                   TypedQuery<ElearningSubscription> query = elearningSubscriptionRepository.getEntityManager().createQuery(sqlStatement, ElearningSubscription.class);
          
                   query.setParameter("searchTerm", searchTerm);
                   
                   List<ElearningSubscription> resultList = query.getResultList();
                   long total = resultList.size();
                   query.setFirstResult(page.getOffset());
                   query.setMaxResults(page.getPageSize());
                   resultList = query.getResultList();
                   Page<ElearningSubscription> elearningSubscriptions = new PageImpl<ElearningSubscription>(resultList, page, total);
          
                   return elearningSubscriptions;
              }

          Comment


          • #6
            Hi Stephane,

            Because your query is not very complex (and it is "dynamic"), I think that it is better to implement that query by using either the JPA Criteria API or Querydsl. Also, if you do this, it is quite easy to paginate the results of your query.

            Do you have some special reason for using JPQL?

            Also, it is not a good idea to concatenate the search terms inside the query. Instead, you should use either named parameters or ordinal parameters.
            Last edited by Loke; Aug 20th, 2013, 01:08 PM.

            Comment


            • #7
              My Controller looks like (I need PAgeable for jqGrid):

              Code:
              PageRequest pageRequest = null;
              
              if (sort != null) {
              	pageRequest = new PageRequest(page - 1, rows, sort);
              } else {
              	pageRequest = new PageRequest(page - 1, rows);
              }
              
              if (search == true) {
              	return getFilteredGrid(filters, pageRequest, locale);
              }
              
              Page<Tournament> tournamentPage = tournamentService.findAllByPage(pageRequest);
              
              Grid<Tournament> grid = new Grid<Tournament>();
              grid.setCurrentPage(tournamentPage.getNumber() + 1);
              grid.setTotalPages(tournamentPage.getTotalPages());
              grid.setTotalRecords(tournamentPage.getTotalElements());
              grid.setData(Lists.newArrayList(tournamentPage.iterator()));
              
              return grid;
              and DaoImpl:

              Code:
              criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
              
              TypedQuery<Tournament> query = entityManager.createQuery(criteriaQuery);
              query.setFirstResult(pageable.getOffset());
              query.setMaxResults(pageable.getPageSize());
              
              Page<Tournament> page = new PageImpl<Tournament>(query.getResultList());
              
              return page;
              PageRequest on controller side is Pageable on DAO Implementation side.

              I hope it answers your question.

              Comment


              • #8
                Hello Loke,

                I will try with the JPA Criteria API and the QueryDsl one and see how I fare. I have no real reason not to try them in fact.

                Thanks for the suggestion !

                Comment


                • #9
                  Hello Paulek,

                  Thanks for the code share. I still wonder where you set the members of the tournamentPage object, like the number, totalPages and totalElements for example...

                  Comment

                  Working...
                  X