Spring JPA Repositories Generic FIND WITH Dandelion DatatableCriterias

Question

I found samples on how to implement ajax server-side processing. On the controller the code looks like this.

@RequestMapping(value = "/persons")
public @ResponseBody
DatatablesResponse<Person> findAllForDataTablesFullSpring(@DatatablesParams DatatablesCriterias criterias) {
    DataSet<Person> dataSet = personService.findPersonsWithDatatablesCriterias(criterias);
    return DatatablesResponse.build(dataSet, criterias);
}

On the service layer

    public DataSet<Person> findPersonsWithDatatablesCriterias(DatatablesCriterias criterias) {

    List<Person> persons = personRepository.findPersonWithDatatablesCriterias(criterias);
    Long count = personRepository.getTotalCount();
    Long countFiltered = personRepository.getFilteredCount(criterias);

    return new DataSet<Person>(persons, count, countFiltered);
   }

On the data access layer

@Override
public List<Person> findPersonWithDatatablesCriterias(DatatablesCriterias criterias) {

    StringBuilder queryBuilder = new StringBuilder("SELECT p FROM Person p");

    /**
     * Step 1: global and individual column filtering
     */
    queryBuilder.append(PersonRepositoryUtils.getFilterQuery(criterias));

    /**
     * Step 2: sorting
     */
    if (criterias.hasOneSortedColumn()) {

        List<String> orderParams = new ArrayList<String>();
        queryBuilder.append(" ORDER BY ");
        for (ColumnDef columnDef : criterias.getSortingColumnDefs()) {
            orderParams.add("p." + columnDef.getName() + " " + columnDef.getSortDirection());
        }

        Iterator<String> itr2 = orderParams.iterator();
        while (itr2.hasNext()) {
            queryBuilder.append(itr2.next());
            if (itr2.hasNext()) {
                queryBuilder.append(" , ");
            }
        }
    }

    TypedQuery<Person> query = entityManager.createQuery(queryBuilder.toString(), Person.class);

    /**
     * Step 3: paging
     */
    query.setFirstResult(criterias.getDisplayStart());
    query.setMaxResults(criterias.getDisplaySize());

    return query.getResultList();
}

/**
 * <p>
 * Query used to return the number of filtered persons.
 * 
 * @param criterias
 *            The DataTables criterias used to filter the persons.
 *            (maxResult, filtering, paging, ...)
 * @return the number of filtered persons.
 */
    @Override
public Long getFilteredCount(DatatablesCriterias criterias) {

    StringBuilder queryBuilder = new StringBuilder("SELECT p FROM Person p");

    queryBuilder.append(PersonRepositoryUtils.getFilterQuery(criterias));

    Query query = entityManager.createQuery(queryBuilder.toString());
    return Long.parseLong(String.valueOf(query.getResultList().size()));
}

/**
 * @return the total count of persons.
 */
    @Override
public Long getTotalCount() {
    Query query = entityManager.createQuery("SELECT COUNT(p) FROM Person p");
    return (Long) query.getSingleResult();
}

My problem with this is I need to repeat coding the data access layer which is completely the same for all of my entities. Now, im thinking of having a GENERIC implementation. Can someone give me solution for this. It would be a great help for anyone using Dandelion Datatables plugin.

As you are using Spring MVC, have you considered using the Spring Data module. This removes the need to write a lot of the boiler plate code around common CRUD operations and has support for paging etc. out of the box. http://docs.spring.io/spring-data/jpa/docs/1.8.0.RELEASE/reference/html/ — Alan Hay, May 21 '15 at 08:35

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Since, recently I worked on same kind of task, I can give a couple of suggestions. First of all you should definitely take a look at Spring Data since that part of Spring framework provides you with everything you would need for database access level. You will need to create a @Repository that extends JpaRepository, and then from here querying your database. So, for example, getTotalCount could be would be substituted with findAll() method, that returns a List of all entities, or even simpler, with count() method. Then, data with pagination could be retrieved with findAll(Pageable pageable) where you just need to pass new PageRequest(criterias.getStart(), criterias.getLength()) as a parameter. Sorting option is also available in Spring Data via Sort class and it works pretty neat with Dandelion DatatablesCriterias and ColumnDef classes. For more complex queries you could use @Query and @Param annotations. So, for more specific info regarding spring data it's better to ask new questions.

Spring JPA Repositories Generic FIND WITH Dandelion DatatableCriterias

1 Answers1

Linked