Spring Data MongoDB for DataTables

Spring Data MongoDB extension to work with the great jQuery plug-in DataTables (http://datatables.net/)

License

License

Categories

Categories

MongoDB Data Databases
GroupId

GroupId

com.github.darrachequesne
ArtifactId

ArtifactId

spring-data-mongodb-datatables
Last Version

Last Version

1.0.3
Release Date

Release Date

Type

Type

jar
Description

Description

Spring Data MongoDB for DataTables
Spring Data MongoDB extension to work with the great jQuery plug-in DataTables (http://datatables.net/)
Project URL

Project URL

https://github.com/darrachequesne/spring-data-mongodb-datatables
Source Code Management

Source Code Management

https://github.com/darrachequesne/spring-data-mongodb-datatables

Download spring-data-mongodb-datatables

How to add to project

<!-- https://jarcasting.com/artifacts/com.github.darrachequesne/spring-data-mongodb-datatables/ -->
<dependency>
    <groupId>com.github.darrachequesne</groupId>
    <artifactId>spring-data-mongodb-datatables</artifactId>
    <version>1.0.3</version>
</dependency>
// https://jarcasting.com/artifacts/com.github.darrachequesne/spring-data-mongodb-datatables/
implementation 'com.github.darrachequesne:spring-data-mongodb-datatables:1.0.3'
// https://jarcasting.com/artifacts/com.github.darrachequesne/spring-data-mongodb-datatables/
implementation ("com.github.darrachequesne:spring-data-mongodb-datatables:1.0.3")
'com.github.darrachequesne:spring-data-mongodb-datatables:jar:1.0.3'
<dependency org="com.github.darrachequesne" name="spring-data-mongodb-datatables" rev="1.0.3">
  <artifact name="spring-data-mongodb-datatables" type="jar" />
</dependency>
@Grapes(
@Grab(group='com.github.darrachequesne', module='spring-data-mongodb-datatables', version='1.0.3')
)
libraryDependencies += "com.github.darrachequesne" % "spring-data-mongodb-datatables" % "1.0.3"
[com.github.darrachequesne/spring-data-mongodb-datatables "1.0.3"]

Dependencies

compile (3)

Group / Artifact Type Version
org.springframework.data : spring-data-mongodb jar
com.fasterxml.jackson.core : jackson-databind jar
javax.validation : validation-api jar

provided (1)

Group / Artifact Type Version
org.projectlombok : lombok jar

test (4)

Group / Artifact Type Version
junit : junit jar
org.assertj : assertj-core jar
org.springframework : spring-test jar
ch.qos.logback : logback-classic jar

Project Modules

There are no modules declared in this project.

Build Status Maven Central

spring-data-mongodb-datatables

This project is an extension of the Spring Data MongoDB 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 its JPA counterpart, please see spring-data-jpa-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);
  }
}

Example

Contents

Maven dependency

<dependency>
  <groupId>com.github.darrachequesne</groupId>
  <artifactId>spring-data-mongodb-datatables</artifactId>
  <version>1.0.3</version>
</dependency>

Back to top.

Getting started

Please see the sample project for a complete example.

1. Enable the use of DataTablesRepository factory

With either

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

or its XML counterpart

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

You can restrict the scope of the factory with @EnableMongoRepositories(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
@EnableMongoRepositories(basePackages = "my.default.package")
public class DefaultConfiguration {}

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

2. Create a new entity

@Entity
public class User {

  private Integer id;

  private String mail;

  private Address address;

}

3. Extend the DataTablesRepository interface

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

4. 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 || '';
      }
    }]
  });
}

5. 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:

It overrides jQuery data serialization to allow Spring MVC to correctly map input parameters (by changing column[0][data] into column[0].data in request payload)

  • retrieve data with 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:

@RequestMapping(value = "/data/users", method = RequestMethod.POST)
public DataTablesOutput<User> getUsers(@Valid @RequestBody DataTablesInput input) {
  return userRepository.findAll(input);
}
  • manually serialize the query parameters
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:

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

DataTablesOutput<T> findAll(DataTablesInput input, Criteria additionalCriteria,
		Criteria preFilteringCriteria);

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

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

@RestController
public class UserRestController {

  @Autowired
  private UserRepository userRepository;

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

  // or with some preprocessing
  @JsonView(DataTablesOutput.View.class)
  @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)
  @JsonView(DataTablesOutput.View.class)
  @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

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 MongoDB query

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

Manage non-searchable fields

If you have a column that does not match an attribute on the server-side (for example, an 'Edit' button), you'll have to set the searchable and orderable attributes to false.

$(document).ready(function() {
  var table = $('table#sample').DataTable({
    'ajax' : '/data/users',
    'serverSide' : true,
    columns : [{
      data: 'id'
    }, {
      data: 'mail'
    }, {
      searchable: false,
      orderable: false
    }]
  });
}

Limit the exposed attributes of the entities

There are several ways to restrict the attributes of the entities on the server-side:

  • using DTO
@RestController
public class UserRestController {

  @Autowired
  private UserRepository userRepository;

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

  @JsonView(DataTablesOutput.View.class)
  private Integer id;

  // ignored
  private String mail;

}

@RestController
public class UserRestController {

  @Autowired
  private UserRepository userRepository;

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

  private Integer id;

  @JsonIgnore
  private String mail;

}

Back to top.

Troubleshooting

  • Invalid property 'columns[0][data]' of bean class [org.springframework.data.jpa.datatables.mapping.DataTablesInput]

Please see here.

  • java.lang.IllegalArgumentException: Unable to locate Attribute with the the given name ...

It seems you have a column with a data attribute that does not match the attribute of the @Entity on the server-side.

Please see here.

Back to top.

Versions

Version
1.0.3
1.0.2
1.0.1
1.0.0