Paging and sorting with Spring Data JPA and Querydsl

By | December 1, 2016 | 2,113 Views

1. Introduction.

Retrieved data with paging and storing is a basic feature of any application. This can be handled by database side (page and sort in a stored procedure, or native SQL query), and Spring Data JPA via use QueryDSL or Specifications. The note will present how to implement QueryDSL with Optional in Java 8.

2. Problems

Purpose is to filter and sort a stock company data as below:

stock-company-data
Figure 1: A stock company data (Assume IPO date).

The first implementation used JPA repository and PageRequest.
StockCompanyRepository.java

package com.mycompany.app.repository;

public interface StockCompanyRepository extends JpaRepository<StockCompanyData, Long> {
@Query(value = "Select s from StockCompanyData s where s.symbol in :symbols " +
            "and s.stockExchangePrefix in :stockExchangePrefixs " +
            "and s.companyName LIKE CONCAT('%', :companyName, '%') " +
            "and s.ipoDate >= :ipoDate " +
            "and s.activated = :activated " )

    Page<StockCompanyData> findStockCompanyByParams(
            @Param("symbols") Set<String> symbols,
            @Param("stockExchangePrefixs") Set<String> stockExchangePrefixs,
            @Param("companyName") String companyName,
            @Param("ipoDate") DateTime ipoDate,
            @Param("activated") boolean activated,
            Pageable pageable);
}

StockCompanyServiceImpl.java

public Page<StockCompanyData> searchByJPARepository(Set<String> symbols,
                                                             Set<String> stockExchangePrefixs,
                                                             String companyName,
                                                             DateTime ipoDate,
                                                             boolean activated,
                                                             String sortType, String sortProperty,
                                                             int page, int limit){
        PageRequest pageRequest = getPageRequest(sortType, sortProperty, page, limit);

        Page<StockCompanyData> stockCompanyDataPage = stockCompanyRepository
                .findStockCompanyByParams(symbols,stockExchangePrefixs,companyName,ipoDate,activated, pageRequest);

        return stockCompanyDataPage;
    }
    /** Define page request with paging and sorting. **/
    public PageRequest getPageRequest(String sortType, String sortProperty, int page, int pageSize) {
        Sort.Direction direction = ASC.toString().equalsIgnoreCase(sortType)? ASC : DESC;

        return new PageRequest(page, pageSize, new Sort(new Sort.Order(direction, sortProperty)));
    }

There are two issues: Dealing with multi if-else conditions in case a user finds not all properties ,and complicated queries like nested query.

3. Solution with Querydsl.

The solution employed Optional to resolve multi if-else conditions as well as Predicate for nested queries.

  • Noticeable that using field java.util.Optional type is problematic if a class needs to be serializable, which java.util.Optional is not. This leads to Problem deserializing JSON payload with `Optional` fields. Solution is jackson-datatype-jdk8 which has become part of Jackson Java 8 Modules as of Jackson 2.8.5

4. Implementation with the stock company data example.

4.1 Project structure.

stock-company-project-structure
Figure 2: Stock Company project structure.

4.2 Domain implementation.

GenerationType.SEQUENCE strategy is to improve performance. More details should be discussed in other topic.
java/com/mycompany/app/domain/StockCompanyData.java

package com.mycompany.app.domain;

import javax.persistence.*;

import com.mycompany.app.utils.AbstractEntity;
import com.mycompany.app.utils.DomainConstants;
import com.mycompany.app.utils.Identifiable;
import org.hibernate.annotations.Type;
import org.joda.time.DateTime;

@Entity
@Table(name="stock_company_data")
public class StockCompanyData extends AbstractEntity implements 
                    Identifiable, DomainConstants, Activatable{
	@Id
	@Column(name="unique_id")
	@GeneratedValue(strategy= GenerationType.SEQUENCE)
	private Long uniqueId;

    @Column(name="symbol" )
    private String symbol;

    @Column(name="company_name")
    private String companyName;

    @Column(name="stock_exchange_prefix")
    private String stockExchangePrefix;

	@Column(name = "initial_public_offering_date")
	@Type(type = COMMON_DATE_TYPE)
	private DateTime ipoDate;

	@Column(name = ACTIVATED_FIELD_NAME)
	private boolean activated;

	// Add Getters and Setters
}

Support for domain:

package com.mycompany.app.utils;
public interface DomainConstants {
    String COMMON_DATE_TYPE = "org.jadira.usertype.dateandtime.joda.PersistentDateTime";
}

4.2 Repository implementation.

The repository extends from QueryDslPredicateExecutor interface.
java/com/mycompany/app/repository/StockCompanyRepository.java

package com.mycompany.app.repository;

import com.mycompany.app.domain.StockCompanyData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;

public interface StockCompanyRepository extends JpaRepository<StockCompanyData, Long>, 
QueryDslPredicateExecutor<StockCompanyData> {

}

Create Search Parameters for Stock Company Data using BooleanExpression and Optional.

package com.mycompany.app.repository;

import com.mycompany.app.api.dto.StockCompanySearchParamsDTO;
import com.mysema.query.types.expr.BooleanExpression;
import org.joda.time.LocalDate;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

import java.util.Arrays;
import java.util.HashSet;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Stream;

import static com.mycompany.app.domain.QStockCompanyData.stockCompanyData;
import static org.springframework.data.domain.Sort.Direction.ASC;
import static org.springframework.data.domain.Sort.Direction.DESC;

/**
 * Created by Khiem on 11/28/2016.
 * Create Search Parameters for Stock Company Data.
 */
public class StockCompanySearchParams {

    private final Optional<Set<String>> symbols;
    private final Optional<Set<String>> stockExchangePrefixs;
    private final Optional<String> companyName;
    private final Optional<LocalDate> ipoDate;
    private final boolean activated;
    private final int offset;
    private final int limit;
    private final String DEFAULT_SORT_PROPERTY = "symbol";

    private final Set<String> sortedParams = new HashSet<>(
            Arrays.asList("symbol", "companyName",
                    "stockExchangePrefix", "ipoDate", "activated"));

    /**
     * Create constructor
     **/
    public StockCompanySearchParams(final Optional<Set<String>> symbols,
                                    final Optional<Set<String>> stockExchangePrefixs,
                                    final Optional<String> companyName,
                                    final Optional<LocalDate> ipoDate,
                                    final boolean activated,
                                    final int offset, final int limit) {
        this.symbols = symbols;
        this.stockExchangePrefixs = stockExchangePrefixs;
        this.companyName = companyName;
        this.ipoDate = ipoDate;
        this.activated = activated;
        this.offset = offset;
        this.limit = limit;
    }

    /**
     * Assume that final expression consists of two sub expressions.
     **/
    public Optional<BooleanExpression> getCommonCriteria() {
        // Create the first expression group.
        Optional<BooleanExpression> andExpression = Stream.of(
                symbols.map(stockCompanyData.symbol::in),
                stockExchangePrefixs.map(stockCompanyData.stockExchangePrefix::in),
                ipoDate.map(LocalDate::toDateTimeAtStartOfDay)
                        .map(stockCompanyData.ipoDate::goe),
                Optional.of(activated).filter(b -> !b)
                        .map(o -> stockCompanyData.activated.eq(true))
        )
                .filter(Optional::isPresent).map(Optional::get)
                .reduce(BooleanExpression::and);

        // Create the second expression group.
        Optional<BooleanExpression> likeExpression = Stream.of(
                companyName.map((str) -> stockCompanyData.companyName.like("%" + str + "%"))

        )
                .filter(Optional::isPresent).map(Optional::get)
                .reduce(BooleanExpression::and);

        //  Able to join the two expressions
        return Stream.of(andExpression, likeExpression)
                .filter(Optional::isPresent)
                .map(Optional::get)
                .reduce(BooleanExpression::and);

    }

    /**
     * Define page request with paging and sorting.
     **/
    public PageRequest getPageRequest(String sortType, String sortProperty) {
        // Validate sort properties. Including returned default
        // value is "symbol" if sort property is null
        sortProperty = sortedParams.contains(sortProperty) 
                       ? sortProperty : DEFAULT_SORT_PROPERTY;
        Sort.Direction direction = ASC.toString().equalsIgnoreCase(sortType) ? ASC : DESC;

        int page = offset > 0 ? offset / limit : 0;

        return new PageRequest(page, limit, 
                           new Sort(new Sort.Order(direction, sortProperty)));
    }

    public static Builder builder() {
        return new Builder();
    }

    /**
     * Initiate stock company search parameters.
     **/
    public static StockCompanySearchParams from(
            final StockCompanySearchParamsDTO searchParamsDTO) {
        final StockCompanySearchParams.Builder builder = StockCompanySearchParams.builder()
                .withSymbols(searchParamsDTO.getSymbols())
                .withStockExchangePrefixs(searchParamsDTO.getStockExchangePrefixs())
                .withCompanyName(searchParamsDTO.getCompanyName())
                .withIPODate(searchParamsDTO.getIpoDate())
                .withActivated(searchParamsDTO.getActivated());

        searchParamsDTO.getLimit().ifPresent(builder::withLimit);
        searchParamsDTO.getOffset().ifPresent(builder::withOffset);

        return builder.build();
    }

    /**
     * Create builder class.
     **/
    public static class Builder {
        private Optional<Set<String>> symbols = Optional.empty();
        private Optional<Set<String>> stockExchangePrefixs = Optional.empty();
        private Optional<String> companyName = Optional.empty();
        private Optional<LocalDate> ipoDate = Optional.empty();
        private boolean activated;
        private int offset = 0;
        private int limit = 10;

        public Builder withSymbols(final Optional<Set<String>> symbols) {
            this.symbols = symbols;
            return this;
        }

        public Builder withStockExchangePrefixs(
                final Optional<Set<String>> stockExchangePrefixs) {
            this.stockExchangePrefixs = stockExchangePrefixs;
            return this;
        }

        public Builder withCompanyName(final Optional<String> companyName) {
            this.companyName = companyName;
            return this;
        }

        public Builder withIPODate(final Optional<LocalDate> ipoDate) {
            this.ipoDate = ipoDate;
            return this;
        }

        public Builder withActivated(final boolean activated) {
            this.activated = activated;
            return this;
        }

        public Builder withLimit(final int limit) {
            this.limit = limit;
            return this;
        }

        public Builder withOffset(final int offset) {
            this.offset = offset;
            return this;
        }

        public StockCompanySearchParams build() {
            return new StockCompanySearchParams(
                    symbols, stockExchangePrefixs, companyName, 
                    ipoDate, activated, offset, limit);
        }
    }
}

4.3 Service implementation.

java/com/mycompany/app/service/StockCompanyServiceImpl.java

package com.mycompany.app.service;

import com.mycompany.app.api.dto.StockCompanyDTO;
import com.mycompany.app.api.dto.StockCompanySearchParamsDTO;
import com.mycompany.app.api.dto.StockCompanySearchResultDTO;
import com.mycompany.app.domain.StockCompanyData;
import com.mycompany.app.repository.StockCompanyRepository;
import com.mycompany.app.repository.StockCompanySearchParams;
import com.mycompany.app.service.mapper.StockCompanyMapper;
import com.mysema.query.types.expr.BooleanExpression;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Optional;

@Service
@Transactional
public class StockCompanyServiceImpl implements StockCompanyService {

    private static final Logger LOGGER = LoggerFactory
                              .getLogger(StockCompanyServiceImpl.class);
    private final StockCompanyMapper stockCompanyMapper;
    private final StockCompanyRepository stockCompanyRepository;

    @Autowired
    public StockCompanyServiceImpl(final StockCompanyRepository stockCompanyRepository,
                                   final StockCompanyMapper stockCompanyMapper) {
        this.stockCompanyRepository = stockCompanyRepository;
        this.stockCompanyMapper = stockCompanyMapper;
    }

    @Override
    public StockCompanySearchResultDTO searchByQueryDSL(
            final StockCompanySearchParamsDTO searchParamsDTO,
            String sortType, String sortProperty) {
        // Initiate search params data.
        StockCompanySearchParams searchParams = StockCompanySearchParams.from(searchParamsDTO);
        // Create search criteria expression.
        final Optional<BooleanExpression> criteria = searchParams.getCommonCriteria();
        // Create pageRequest with sorting as well paging.
        final PageRequest pageRequest = searchParams.getPageRequest(sortType, sortProperty);

        LOGGER.info("Search StockCompanyData with criteria {}", 
                   searchParams.getCommonCriteria().toString());
        final Page<StockCompanyData> stockCompanyDataPage = criteria.map(
                c -> stockCompanyRepository.findAll(c, pageRequest))
                .orElseGet(() -> stockCompanyRepository.findAll(pageRequest));

        // Mapper stockCompanyData object to StockCompanyDTO object.
        List<StockCompanyDTO> stockCompanyDTOs = stockCompanyMapper.
                toStockCompanyDTOList(stockCompanyDataPage.getContent());

        // Return search result DTO.
        final StockCompanySearchResultDTO results = 
                                     new StockCompanySearchResultDTO(stockCompanyDTOs);
        results.setLimit(stockCompanyDataPage.getSize());
        results.setOffset(stockCompanyDataPage.getNumber());
        results.setTotal(stockCompanyDataPage.getTotalElements());

        return results;
    }
}

4.4 REST implementation.

java/com/mycompany/app/rest/StockCompanyResourceImpl.java

package com.mycompany.app.rest;

import com.mycompany.app.api.StockCompanyResource;
import com.mycompany.app.api.dto.StockCompanySearchParamsDTO;
import com.mycompany.app.api.dto.StockCompanySearchResultDTO;
import com.mycompany.app.service.StockCompanyService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import static org.springframework.http.MediaType.APPLICATION_JSON_VALUE;

/**
 * Created by Khiem on 11/29/2016.
 * Provide resource implementation.
 */
@RestController
@RequestMapping("/api/company")
public class StockCompanyResourceImpl implements StockCompanyResource {

    private static final Logger LOG = LoggerFactory.getLogger(StockCompanyResourceImpl.class);

    @Autowired
    private StockCompanyService stockCompanyService;

    /**
     * Important:
     * POST instead of a GET is used here to workaround the Tomcat and HAProxy header buffer recommended limits of 8-16k.
     *
     * @see <a href="https://cbonte.github.io/haproxy-dconv/configuration-1.5.html#tune.bufsize">HAProxy configuration</a>
     * @see <a href="https://tomcat.apache.org/tomcat-8.0-doc/config/http.html">Tomcat configuration</a>
     */
    @RequestMapping(value = "/search", method = RequestMethod.POST, 
            consumes = APPLICATION_JSON_VALUE, produces = APPLICATION_JSON_VALUE)
    @ResponseStatus(HttpStatus.OK)
    @Override
    public StockCompanySearchResultDTO findStockCompany(
            @RequestBody StockCompanySearchParamsDTO companySearchParamsDTO,
            @RequestParam(value = "sortType", required = false) String sortType,
            @RequestParam(value = "sortProperty", required = false) String sortProperty) {
        LOG.info("Finding Stock Companies {}", companySearchParamsDTO);
        return stockCompanyService
               .searchByQueryDSL(companySearchParamsDTO, sortType, sortProperty);
    }
}

JSON payload used Optional as below:
java/com/mycompany/app/dto/StockCompanySearchParamsDTO.java

package com.mycompany.app.api.dto;

import com.fasterxml.jackson.annotation.*;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.joda.time.LocalDate;

import java.util.Optional;
import java.util.Set;

import static java.util.Optional.empty;

/**
 * Define Search Params DTO.
 */
@JsonIgnoreProperties(ignoreUnknown = true)
public class StockCompanySearchParamsDTO {
    private Optional<Set<String>> symbols = empty();
    private Optional<Set<String>> stockExchangePrefixs = empty();
    private Optional<String> companyName = empty();
    private Optional<LocalDate> ipoDate = empty();
    private Boolean activated;
    private Optional<Integer> offset = empty();
    private Optional<Integer> limit = empty();

    @JsonCreator
    public StockCompanySearchParamsDTO(
            @JsonProperty("symbols") Optional<Set<String>> symbols,
            @JsonProperty("stockExchangePrefixs") Optional<Set<String>> stockExchangePrefixs,
            @JsonProperty("ipoDate")Optional<LocalDate> ipoDate,
            @JsonProperty("companyName")Optional<String> companyName,
            @JsonProperty("activated")Boolean activated,
            @JsonProperty("limit")Optional<Integer> limit,
            @JsonProperty("offset")Optional<Integer> offset) {
        this.symbols = symbols;
        this.limit = limit;
        this.offset = offset;
        this.ipoDate = ipoDate;
        this.companyName = companyName;
        this.stockExchangePrefixs = stockExchangePrefixs;
        this.activated = activated;
    }
    @Override
    public String toString() {
        return ReflectionToStringBuilder.toString(this);
    }
    // Add Setters and Getters

4.5 Resource configuration.

src/main/resources/config/liquibase/db-changelog.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">

    <changeSet id="1" author="khiem.truong">
        <createTable tableName="stock_company_data">
            <column name="unique_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="symbol" type="varchar(255)">
                <constraints nullable="false"/>
            </column>
            <column name="company_name" type="varchar(255)">
                <constraints nullable="false"/>
            </column>
            <column name="stock_exchange_prefix" type="varchar(255)"/>
            <column name="initial_public_offering_date" type="timestamp"/>
            <column name="activated" type = "boolean" />
        </createTable>
        
        <loadData tableName="stock_company_data"
                  encoding="UTF-8"
                  file="config/liquibase/stock_company_data_db.csv"
                  separator=";">
        </loadData>
    </changeSet>

</databaseChangeLog>

4.6 Dependency.

Query DSL dependency and plugin for generate resource.

<dependencies>
<!-- querydsl -->
		<dependency>
			<groupId>com.mysema.querydsl</groupId>
			<artifactId>querydsl-core</artifactId>
			<version>3.6.5</version>
		</dependency>

		<dependency>
			<groupId>com.mysema.querydsl</groupId>
			<artifactId>querydsl-apt</artifactId>
			<version>3.6.5</version>
		</dependency>
		<dependency>
			<groupId>com.mysema.querydsl</groupId>
			<artifactId>querydsl-jpa</artifactId>
			<version>3.6.5</version>
		</dependency>
</dependencies>
<build>
		<plugins>
			<!-- MUST add to use generate QueryDSL -->
			<plugin>
				<groupId>com.mysema.maven</groupId>
				<artifactId>maven-apt-plugin</artifactId>
				<version>1.0.4</version>
				<executions>
					<execution>
						<phase>generate-sources</phase>
						<goals>
							<goal>process</goal>
						</goals>
						<configuration>
							<!-- Specifies the directory in which the query types are generated -->
							<outputDirectory>target/generated-sources/querydsl</outputDirectory>
							<!-- States that the APT code generator should look for JPA annotations -->
							<processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>

4.7 Make the application executable.

src/main/java/com/mycompany/app/transaction/Application.java

package com.mycompany.app;

import ch.qos.logback.classic.LoggerContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import java.io.IOException;

@Configuration
@ComponentScan
@EnableAutoConfiguration
@EnableJpaRepositories
public class Application {
    @Autowired
    private Environment env;

    private static final Logger LOGGER = LoggerFactory.getLogger(Application.class);

    @PostConstruct
    public void initApplication() throws IOException {
        LOGGER.info("Running with Spring profile(s) : {}", env.getActiveProfiles());
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @PreDestroy
    public void shutdownLogback(){
        LOGGER.info("Shutting down Logback");
        LoggerContext lCtx = (LoggerContext) LoggerFactory.getILoggerFactory();
        lCtx.stop();
    }

}

Run the application to test the API:

POST http://localhost:8081/api/company/search?sortType=desc&sortProperty=symbol
{
	"symbols":null,
	"stockExchangePrefixs":["NASDAQ","NYSE"],
	"companyName":"co",
	"ipoDate": null,
	"activated":true,
	"offset":0,
	"limit":10
}
Error-Response: HTTP/1.1 400 Bad Request
{
  "timestamp": 1480613393046,
  "status": 400,
  "error": "Bad Request",
  "exception": "org.springframework.http.converter.HttpMessageNotReadableException",
  "message": "Could not read document: Can not deserialize instance of java.util.Optional out of START_ARRAY token\n at [Source: java.io.PushbackInputStream@6d89b974; line: 3, column: 25] (through reference chain: com.mycompany.app.api.dto.StockCompanySearchParamsDTO[\"stockExchangePrefixs\"]); nested exception is com.fasterxml.jackson.databind.JsonMappingException: Can not deserialize instance of java.util.Optional out of START_ARRAY token\n at [Source: java.io.PushbackInputStream@6d89b974; line: 3, column: 25] (through reference chain: com.mycompany.app.api.dto.StockCompanySearchParamsDTO[\"stockExchangePrefixs\"])",
  "path": "/api/company/search"
}

Reason is not deserialize JSON payload with `Optional` fields. Fix by adding dependency as well as bean configure.

<dependency>
			<groupId>com.fasterxml.jackson.datatype</groupId>
			<artifactId>jackson-datatype-jdk8</artifactId>
			<version>2.8.5</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.module</groupId>
			<artifactId>jackson-module-parameter-names</artifactId>
			<version>2.8.5</version>
		</dependency>
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.registerModule(new Jdk8Module());
//objectMapper.findAndRegisterModules();

It worked fine now with JSON respond:

{
  "results": [
    {
      "uniqueId": 3,
      "symbol": "ADP",
      "companyName": "Automatic Data Processing, Inc.",
      "stockExchangePrefix": "NASDAQ",
      "ipoDate": {
        "era": 1,
        "dayOfMonth": 17,
        "dayOfWeek": 1,
        "dayOfYear": 291,
        "year": 2016,
        "weekOfWeekyear": 42,
        "millisOfSecond": 0,
        "secondOfMinute": 17,
        "yearOfCentury": 16,
        "centuryOfEra": 20,
        "weekyear": 2016,
        "yearOfEra": 2016,
        "monthOfYear": 10,
        "minuteOfDay": 1096,
        "secondOfDay": 65777,
        "minuteOfHour": 16,
        "millisOfDay": 65777000,
        "hourOfDay": 18,
        "chronology": {
          "zone": {
            "fixed": false,
            "cachable": false,
            "id": "Asia/Bangkok"
          }
        },
        "zone": {
          "fixed": false,
          "cachable": false,
          "id": "Asia/Bangkok"
        },
        "millis": 1476702977000,
        "afterNow": false,
        "beforeNow": true,
        "equalNow": false
      },
      "activated": true
    },
    ....

But, the ipoDate is type of joda.time so that by default it shows everything. Following steps will customize date time format.

4.8 Jackson Configuration.

src/main/java/com/mycompany/app/configuration/JacksonConfig.java

package com.mycompany.app.configuration;

import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.databind.*;
import com.fasterxml.jackson.datatype.jdk8.Jdk8Module;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import java.io.IOException;

/**
 * Created by Khiem on 11/30/2016.
 * Correctly handle JSR-310 (java 8) Optional/Date with Jackson
 */
@Configuration
public class JacksonConfig {
    public static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormat.forPattern("yyyy-MM-dd");

    /**
     * Bean ObjectMapper is defined with annotation @Primary, to override default configuration.
     * My custom pattern for org.joda.time.DateTime is yyyy-MM-dd
     * @return ObjectMapper
     */
    @Bean
    @Primary
    public ObjectMapper objectMapper() {
        ObjectMapper objectMapper = new ObjectMapper();
        objectMapper.registerModule(new Jdk8Module());

        JavaTimeModule javaTimeModule = new JavaTimeModule();
        javaTimeModule.addSerializer(DateTime.class, new DateTimeSerializer());
        javaTimeModule.addDeserializer(DateTime.class, new DateTimeDeserializer());
        objectMapper.registerModule(javaTimeModule);
        // objectMapper .findAndRegisterModules();

        return objectMapper;
    }

    /**
     * Definitions of serializer and deserializer for all LocalDate classes.
     */
   public static class DateTimeSerializer extends JsonSerializer<DateTime> {
        @Override
        public void serialize(DateTime dateTime, JsonGenerator jsonGenerator, SerializerProvider serializerProvider) throws IOException {
            jsonGenerator.writeString(dateTime.toString(DATE_TIME_FORMATTER));
        }
   }

   public static class DateTimeDeserializer extends JsonDeserializer<DateTime> {
        @Override
        public DateTime deserialize(JsonParser jsonParser, DeserializationContext deserializationContext) throws IOException {
            return DateTime.parse(jsonParser.getValueAsString(),DATE_TIME_FORMATTER);
        }
    }
}

Plug JSR310 dependency as well

<dependency>
			<groupId>com.fasterxml.jackson.datatype</groupId>
			<artifactId>jackson-datatype-jsr310</artifactId>
			<version>2.6.1</version>
</dependency>

Run the API again.

Success-respond: HTTP 1.1 200 OK
{
  "results": [
    {
      "uniqueId": 4,
      "symbol": "CSCO",
      "companyName": "Cisco Systems, Inc.",
      "stockExchangePrefix": "NASDAQ",
      "ipoDate": "2016-10-17",
      "activated": true
    },
    {
      "uniqueId": 15,
      "symbol": "ASX",
      "companyName": "Advanced Semiconductor Engineering, Inc.",
      "stockExchangePrefix": "NYSE",
      "ipoDate": "2000-02-17",
      "activated": true
    },
    {
      "uniqueId": 2,
      "symbol": "AMZN",
      "companyName": "Amazon.com, Inc.",
      "stockExchangePrefix": "NASDAQ",
      "ipoDate": "2000-10-17",
      "activated": true
    },
    {
      "uniqueId": 13,
      "symbol": "ACCO",
      "companyName": "Acco Brands Corporation",
      "stockExchangePrefix": "NYSE",
      "ipoDate": "2002-08-17",
      "activated": true
    }
  ],
  "offset": 0,
  "total": 4,
  "limit": 10
}

6. Summary.

QueryDSL with Optional resolve sorting and paging data for the API. However, the solution do bring some disadvantages.

  • Performance issue: If Relationships mapping types are more complex and not effective, the system do not take advantages of power of the SQL query language and is quite tricky to optimize slow search queries automatically generated by Hibernate.
  • Maintenance issue: It is clear that Database admin do not want to maintain the queries in Java codes. If the relationship mapping , Inheritance, Fetching, and Caching types changed, the Java codes also update and rewrite unit and integration tests. This takes much effort.


References:

  • Querydsl. Retrieved from http://www.querydsl.com/
  • Paging And Sorting Repository. Retrieved from http://www.javaworld.com/article/2078898/open-source-tools/open-source-tools-open-source-java-projects-spring-data.html?page=2
  • Query by Example. Retrieved from http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example
  • Hibernate-criteria-examples. Retrived from https://www.mkyong.com/hibernate/hibernate-criteria-examples/
  • Optional issue. Retrieved from https://github.com/google/FreeBuilder/issues/166
  • Jackson update. Retrieved from https://github.com/FasterXML/jackson
  • Formatting Java Time with Spring Boot using JSON. Retrieved from http://lewandowski.io/2016/02/formatting-java-time-with-spring-boot-using-json/
  • http://www.baeldung.com/rest-api-search-language-spring-data-querydsl

One thought on “Paging and sorting with Spring Data JPA and Querydsl

  1. Khiem

    Update maven dependency as below:

            <dependency>
                <groupId>com.querydsl</groupId>
                <artifactId>querydsl-apt</artifactId>
                <version>4.1.3</version>
                <scope>provided</scope>
            </dependency>
            <dependency>
                <groupId>com.querydsl</groupId>
                <artifactId>querydsl-jpa</artifactId>
                <version>4.1.3</version>
            </dependency>
            <dependency>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-log4j12</artifactId>
                <version>1.6.1</version>
            </dependency>
            <plugin>
                    <groupId>com.mysema.maven</groupId>
                    <artifactId>apt-maven-plugin</artifactId>
                    <version>1.1.3</version>
                    <executions>
                        <execution>
                            <goals>
                                <goal>process</goal>
                            </goals>
                            <configuration>
                                <outputDirectory>target/generated-sources/java</outputDirectory>
                                <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                            </configuration>
                        </execution>
                    </executions>
            </plugin>
    
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *