spring-data-jpa-datatables

This project is an extension of the Spring Data JPA project to ease its use with jQuery plugin DataTables with server-side processing enabled.

This will allow you to handle the Ajax requests sent by DataTables for each draw of the information on the page (i.e. when paging, ordering, searching, etc.) from Spring @RestController.

For a MongoDB counterpart, please see spring-data-mongodb-datatables.

Example:

@RestController
public class UserRestController {

  @Autowired
  private UserRepository userRepository;

  @RequestMapping(value = "/data/users", method = RequestMethod.GET)
  public DataTablesOutput<User> getUsers(@Valid DataTablesInput input) {
    return userRepository.findAll(input);
  }
}

Contents

• Maven dependency
• Getting started
• Step 1 - Enable the use of the DataTablesRepository factory
• Step 2 - Create a new entity
• Step 3 - Extend the DataTablesRepository interface
• Step 4 - Use the repository in your controllers
• Step 5 - On the client-side, create a new DataTable object
• Step 6 - Fix the serialization / deserialization of the query parameters
• API
• How to
• Apply filters
• Manage non-searchable fields
• Limit the exposed attributes of the entities
• Search on a rendered column
• Use with the SearchPanes extension
• Handle @OneToMany and @ManyToMany relationships
• Search for a specific value in a column
• Examples of additional specification
• Specific date
• Range of integers
• Range of dates
• Troubleshooting

Maven dependency

<dependency>
  <groupId>com.github.darrachequesne</groupId>
  <artifactId>spring-data-jpa-datatables</artifactId>
  <version>8.0.0</version>
</dependency>

Compatibility with Spring Boot:

Back to top.

Getting started

Please see the sample project for a complete example.

Step 1 - Enable the use of the DataTablesRepository factory

With either

@Configuration
@EnableJpaRepositories(repositoryFactoryBeanClass = DataTablesRepositoryFactoryBean.class)
public class DataTablesConfiguration {}

or its XML counterpart

<jpa:repositories factory-class="org.springframework.data.jpa.datatables.repository.DataTablesRepositoryFactoryBean" />

You can restrict the scope of the factory with @EnableJpaRepositories(repositoryFactoryBeanClass = DataTablesRepositoryFactoryBean.class, basePackages = "my.package.for.datatables.repositories"). In that case, only the repositories in the given package will be instantiated as DataTablesRepositoryImpl on run.

@Configuration
@EnableJpaRepositories(basePackages = "my.default.package")
public class DefaultJpaConfiguration {}

@Configuration
@EnableJpaRepositories(repositoryFactoryBeanClass = DataTablesRepositoryFactoryBean.class, basePackages = "my.package.for.datatables.repositories")
public class DataTablesConfiguration {}

Step 2 - Create a new entity

@Entity
public class User {

  private Integer id;

  private String mail;

  @ManyToOne
  @JoinColumn(name = "id_address")
  private Address address;

}

Step 3 - Extend the DataTablesRepository interface

public interface UserRepository extends DataTablesRepository<User, Integer> {}

The DataTablesRepository interface extends both PagingAndSortingRepository and JpaSpecificationExecutor.

Step 4 - Use the repository in your controllers

@RestController
@RequiredArgsConstructor
public class MyController {
  private final UserRepository userRepository;

  @RequestMapping(value = "/data/users", method = RequestMethod.GET)
  public DataTablesOutput<User> getUsers(@Valid DataTablesInput input) {
    return userRepository.findAll(input);
  }
}

Step 5 - On the client-side, create a new DataTable object

$(document).ready(function() {
  var table = $('table#sample').DataTable({
    ajax : '/data/users',
    serverSide : true,
    columns : [{
      data : 'id'
    }, {
      data : 'mail'
    }, {
      data : 'address.town',
      render: function (data, type, row) {
        return data || '';
      }
    }]
  });
}

Step 6 - Fix the serialization / deserialization of the query parameters

By default, the parameters sent by the plugin cannot be deserialized by Spring MVC and will throw the following exception: InvalidPropertyException: Invalid property 'columns[0][data]' of bean class [org.springframework.data.jpa.datatables.mapping.DataTablesInput].

There are multiple solutions to this issue:

• Solution n°1 - custom serialization
• Solution n°2 - POST requests
• Solution n°3 - manual serialization

Solution n°1 - custom serialization

You need to include the jquery.spring-friendly.js file found at the root of the repository.

<script src="https://github.com/darrachequesne/spring-data-jpa-datatables/raw/v8.0.0/jquery.spring-friendly.js" />

It overrides the default serialization of the HTTP request parameters to allow Spring MVC to correctly map them, by changing column[0][data] into column[0].data in the request payload.

Solution n°2 - POST requests

Client-side:

$('table#sample').DataTable({
  ajax: {
    contentType: 'application/json',
    url: '/data/users',
    type: 'POST',
    data: function(d) {
      return JSON.stringify(d);
    }
  }
})

Server-side:

@RestController
public class MyController {
  @RequestMapping(value = '/data/users', method = RequestMethod.POST)
  public DataTablesOutput<User> getUsers(@Valid @RequestBody DataTablesInput input) {
    return userRepository.findAll(input);
  }
}

Solution n°3 - manual serialization

function flatten(params) {
  params.columns.forEach(function (column, index) {
    params['columns[' + index + '].data'] = column.data;
    params['columns[' + index + '].name'] = column.name;
    params['columns[' + index + '].searchable'] = column.searchable;
    params['columns[' + index + '].orderable'] = column.orderable;
    params['columns[' + index + '].search.regex'] = column.search.regex;
    params['columns[' + index + '].search.value'] = column.search.value;
  });
  delete params.columns;

  params.order.forEach(function (order, index) {
    params['order[' + index + '].column'] = order.column;
    params['order[' + index + '].dir'] = order.dir;
  });
  delete params.order;

  params['search.regex'] = params.search.regex;
  params['search.value'] = params.search.value;
  delete params.search;

  return params;
}

$('table#sample').DataTable({
  'ajax': {
    'url': '/data/users',
    'type': 'GET',
    'data': flatten
  }
})

Back to top.

API

The repositories now expose the following methods:

public interface DataTablesRepository<T, ID extends Serializable> {

    DataTablesOutput<T> findAll(
        DataTablesInput input
    );

    DataTablesOutput<R> findAll(
        DataTablesInput input,
        Function<T, R> converter
    );

    DataTablesOutput<T> findAll(
        DataTablesInput input,
        Specification<T> additionalSpecification
    );

    DataTablesOutput<T> findAll(
        DataTablesInput input,
        Specification<T> additionalSpecification,
        Specification<T> preFilteringSpecification
    );

    DataTablesOutput<R> findAll(
        DataTablesInput input,
        Specification<T> additionalSpecification,
        Specification<T> preFilteringSpecification,
        Function<T, R> converter
    );
}

Note: QueryDSL is also supported, you can simply replace DataTablesRepository with QDataTablesRepository and your repositories will now expose:

public interface QDataTablesRepository<T, ID extends Serializable> {

    DataTablesOutput<T> findAll(
        DataTablesInput input
    );

    DataTablesOutput<R> findAll(
        DataTablesInput input,
        Function<T, R> converter
    );

    DataTablesOutput<T> findAll(
        DataTablesInput input,
        Predicate additionalPredicate
    );

    DataTablesOutput<T> findAll(
        DataTablesInput input,
        Predicate additionalPredicate,
        Predicate preFilteringPredicate
    );

    DataTablesOutput<R> findAll(
        DataTablesInput input,
        Predicate additionalPredicate,
        Predicate preFilteringPredicate,
        Function<T, R> converter
    );
}

Your controllers should be able to handle the parameters sent by DataTables:

@RestController
public class UserRestController {

  @Autowired
  private UserRepository userRepository;

  @RequestMapping(value = "/data/users", method = RequestMethod.GET)
  public DataTablesOutput<User> getUsers(@Valid DataTablesInput input) {
    return userRepository.findAll(input);
  }

  // or with some preprocessing
  @RequestMapping(value = "/data/users", method = RequestMethod.GET)
  public DataTablesOutput<User> getUsers(@Valid DataTablesInput input) {
    ColumnParameter parameter0 = input.getColumns().get(0);
    Specification additionalSpecification = getAdditionalSpecification(parameter0.getSearch().getValue());
    parameter0.getSearch().setValue("");
    return userRepository.findAll(input, additionalSpecification);
  }

  // or with an additional filter allowing to 'hide' data from the client (the filter will be applied on both the count and the data queries, and may impact the recordsTotal in the output)
  @RequestMapping(value = "/data/users", method = RequestMethod.GET)
  public DataTablesOutput<User> getUsers(@Valid DataTablesInput input) {
    return userRepository.findAll(input, null, removeHiddenEntitiesSpecification);
  }
}

The DataTablesInput class maps the fields sent by the client (listed there).

Spring documentation for Specification: https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html

How to

Apply filters

By default, the main search field is applied to all columns.

You can apply specific filter on a column with table.columns(<your column id>).search(<your filter>).draw(); (or table.columns(<your column name>:name)...) (see documentation).

Supported filters:

• Strings (WHERE <column> LIKE %<input>%)
• Booleans
• Array of values (WHERE <column> IN (<input>) where input is something like 'PARAM1+PARAM2+PARAM4')
• NULL values are also supported: 'PARAM1+PARAM3+NULL' becomes WHERE (<column> IN ('PARAM1', 'PARAM3') OR <column> IS NULL) (to actually search for 'NULL' string, please use \NULL)

Also supports paging and sorting.

Example:

{
  "draw": 1,
  "columns": [
    {
      "data": "id",
      "name": "",
      "searchable": true,
      "orderable": true,
      "search": {
        "value": "",
        "regex": false
      }
    },
    {
      "data": "firstName",
      "name": "",
      "searchable": true,
      "orderable": true,
      "search": {
        "value": "",
        "regex": false
      }
    },
    {
      "data": "lastName",
      "name": "",
      "searchable": true,
      "orderable": true,
      "search": {
        "value": "",
        "regex": false
      }
    }
  ],
  "order": [
    {
      "column": 0,
      "dir": "asc"
    }
  ],
  "start": 0,
  "length": 10,
  "search": {
    "value": "john",
    "regex": false
  }
}

is converted into the following SQL (through the Criteria API):

SELECT
    user0_.id AS id1_0_0_,
    user0_.first_name AS first_na3_0_0_,
    user0_.last_name AS last_nam4_0_0_
FROM
    users user0_
WHERE
    user0_.id LIKE "%john%"
    OR user0_.first_name LIKE "%john%"
    OR user0_.last_name LIKE "%john%"
ORDER BY user0_.id ASC
LIMIT 10

**Note