Client Oriented Dynamic Search Query Supporting Multiple Tables in Spring

The main motive of this article to have a simple and common data search logic that applies to almost every table and is client-oriented.

Ashish Lohia

CORE ·

Apr. 21, 20 · Analysis

Likes (3)

Comment

Save

20.9K Views

Backdrop

To begin with, this an example primarily written in Springboot to leverage the benefits of Spring Data JPA. The main motive of this article to have a simple and common data search logic that applies to almost every table and is client-oriented. This article is heavily inspired by one from Eugen Paraschiv, I recommend going through his tutorials to learn Spring professionally.

Pre-Requisites for Getting Started

Java 8 is installed.
Any Java IDE (preferably STS or IntelliJ IDEA).
Basic understanding of Java and Spring-based web development along with Spring Data JPA.

I used Spring Initializer to add all the dependencies and create a blank working project with all my configurations. I used Maven as project build type and Java 8 as language, though this part is up to your choice as long as it is supported by spring. Below are my required dependencies which can easily be added from spring initializer.

I am using H2 but any other database with JPA support should work well. I have also used Lombok to generate some general java code snippets on compile-time, I highly recommend by can be skipped, but don't forget to install it in your IDE else the compilation errors will keep coming.

Spring boot starters

Below is my project structure, it is just like any other Spring Boot Project.
jpa-search

Let's start with the entities since our main goal is to search tables we need to create some entities based on the tables.

      Java 
    
 
 
       
     
 
       
 
      
 
       
 
      
 
      
 
     
xxxxxxxxxx
 
           
 
          
 
           
 
          

               
           
@Entity
@Getter
@Setter
@NoArgsConstructor
public class Avenger {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    private String firstName;
    private String lastName;
    private String alias;
    private int age;
    private BigDecimal powerRating;
    private boolean seniorMembers;
    private Instant joiningDate = Instant.now();
    public Avenger(String firstName, String lastName, String alias, int age, BigDecimal powerRating,
            boolean seniorMembers) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.alias = alias;
        this.age = age;
        this.powerRating = powerRating;
        this.seniorMembers = seniorMembers;
    }
}
@Entity
@Getter
@Setter
@NoArgsConstructor
public class JusticeLeaguer {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    private String firstName;
    private String lastName;
    private String alias;
    private int age;
    private BigDecimal powerRating;
    private boolean seniorMembers;
    private Instant joiningDate = Instant.now();
    public JusticeLeaguer(String firstName, String lastName, String alias, int age, BigDecimal powerRating,
            boolean seniorMembers) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.alias = alias;
        this.age = age;
        this.powerRating = powerRating;
        this.seniorMembers = seniorMembers;
    }
}
 
       
 
      

As we can see we have two tables of Superheroes belonging to two different universes (yes I like Superheroes, grow up :p). I won't go much into the details of creating the entities as these are pretty straightforward, still would be glad to resolve any doubts. Now, we will try to create a dynamic query generator using the JPA Specification and javax Criteria package classes.

The next step is to create repositories for the above entities.

     Java 
   
          x 
         
@Repository
public interface AvengerRepository extends CrudRepository<Avenger, Long>, JpaSpecificationExecutor<Avenger>{
}
@Repository
public interface JusticeLeaguerRepository extends CrudRepository<JusticeLeaguer, Long>, JpaSpecificationExecutor<JusticeLeaguer>{
}

As we can see, we extended two interfaces:

CrudRepository

As the name suggests this Repository provides CRUD utilities on the table.

JpaSpecificationExecutor

This interface is needed to pass the specifications created by the CriteriaBuiler to filter the datasets along with paging and sorting which we will see later as well.

Now we are ready to pass certain parameters to our repository methods to get desired results. But, our main objective was to create a query engine where the query generation logic will completely generic and applicable for most of the tables. Let's take a step back and come up with an approach.

Let's create a POJO which will all the details we need to create one specification.

     Java 
   
x
 
@Data
public class SearchCriteria {
    private boolean clauseAnd;
    private SearchParamTypeEnum type;
    private String key;
    private String operation;
    private String value;
    
    public SearchCriteria(String clause, String type, String key, String operation, String value) {
        this.clauseAnd = StringUtils.isEmpty(clause);
        this.type = SearchParamTypeEnum.valueOfType(type);
        this.key = key;
        this.operation = operation;
        this.value = value;
    }
}

Let's see what each field signifies:

ClauseAnd: This denotes whether the upcoming specification is an AND clause or an OR clause.
Type: This is an enum field that identifies which type of column we filtering on. It might be Long, String, Instant, Boolean, etc.
Key: This is the name of the field, give special care on naming your variable and the same need to passed in the query.
Operation: This is the type of query operation we need to do. e.g >, <, =, in etc.
Value: The value of the field we want to query on.

This bean will now be converted to the corresponding specification.

     Java 
   
 
 
      
    
 
      
 
     
 
      
 
     
 
     
 
    
x
 
          
 
         
 
          
 
         

              
          
@NoArgsConstructor
@AllArgsConstructor
public class SearchSpecification<T> implements Specification<T> {
    private static final long serialVersionUID = -0x2733DE2E86ED0B65L;
    private SearchCriteria criteria;
    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        switch (criteria.getType()) {
        case NUM_PARAM: {
            if (criteria.getOperation().equals(G_T))
                return criteriaBuilder.gt(root.get(criteria.getKey()), new BigDecimal(criteria.getValue()));
            if (criteria.getOperation().equals(L_T))
                return criteriaBuilder.lt(root.get(criteria.getKey()), new BigDecimal(criteria.getValue()));
            if (criteria.getOperation().equals(EQUALS))
                return criteriaBuilder.equal(root.get(criteria.getKey()), new BigDecimal(criteria.getValue()));
            if (criteria.getOperation().equals(NOT_EQUALS))
                return criteriaBuilder.notEqual(root.get(criteria.getKey()), new BigDecimal(criteria.getValue()));
            if (criteria.getOperation().equals(G_T_EQUALS))
                return criteriaBuilder.ge(root.get(criteria.getKey()), new BigDecimal(criteria.getValue()));
            if (criteria.getOperation().equals(L_T_EQUALS))
                return criteriaBuilder.le(root.get(criteria.getKey()), new BigDecimal(criteria.getValue()));
            if (criteria.getOperation().equals(IN)) {
                In<Object> in = criteriaBuilder.in(root.get(criteria.getKey()));
                for (String str : criteria.getValue().split(COMMA))
                    in.value(new BigDecimal(str));
                return in;
            }
        }
            return null;
        case DATE_TIME_PARAM: {
            if (criteria.getOperation().equals(G_T))
                return criteriaBuilder.greaterThan(root.get(criteria.getKey()), parse(criteria.getValue()));
            if (criteria.getOperation().equals(L_T))
                return criteriaBuilder.lessThan(root.get(criteria.getKey()), parse(criteria.getValue()));
            if (criteria.getOperation().equals(EQUALS))
                return criteriaBuilder.equal(root.get(criteria.getKey()), parse(criteria.getValue()));
            if (criteria.getOperation().equals(NOT_EQUALS))
                return criteriaBuilder.notEqual(root.get(criteria.getKey()), parse(criteria.getValue()));
            if (criteria.getOperation().equals(G_T_EQUALS))
                return criteriaBuilder.greaterThanOrEqualTo(root.get(criteria.getKey()), parse(criteria.getValue()));
            if (criteria.getOperation().equals(L_T_EQUALS))
                return criteriaBuilder.lessThanOrEqualTo(root.get(criteria.getKey()), parse(criteria.getValue()));
            if (criteria.getOperation().equals(IN)) {
                In<Object> in = criteriaBuilder.in(root.get(criteria.getKey()));
                for (String str : criteria.getValue().split(COMMA))
                    in.value(parse(str));
                return in;
            }
        }
            return null;
        case STRING_PARAM: {
            if (criteria.getOperation().equals(EQUALS))
                return criteriaBuilder.like(root.<String>get(criteria.getKey()),
                        format(LIKE_PRE_POST, criteria.getValue()));
            if (criteria.getOperation().equals(NOT_EQUALS))
                return criteriaBuilder.notLike(root.<String>get(criteria.getKey()),
                        format(LIKE_PRE_POST, criteria.getValue()));
            if (!criteria.getOperation().equals(IN))
                return null;
            In<Object> in = criteriaBuilder.in(root.get(criteria.getKey()));
            for (String str : criteria.getValue().split(COMMA))
                in.value(str);
            return in;
        }
        case BOOLEAN_PARAM:
            return criteriaBuilder.equal(root.get(criteria.getKey()), parseBoolean(criteria.getValue()));
        default:
            return null;
        }
    }
}
 
      
 
     

As we can see we are implementing the Specification interface and overriding its method to return a specification based on the values of type and operation which we get from the SearchCriteria object. The values are coming from a Constant interface.

     Java 
   
xxxxxxxxxx
 
public interface Constants {
    Pattern SEARCH_QUERY_PATTERN = Pattern.compile("(OR-)?(N-|S-|D-|B-)(\\w+?)(=|<|>|<=|>=|#|!=)(\"([^\"]+)\")");
    String EMPTY = "";
    String DOUBLE_QUOTES = "\"";
    String COMMA = ",";
    String COLON = ":";
    String LIKE_PRE_POST = "%%%s%%";
    String EQUALS = "=";
    String L_T = "<";
    String NOT_EQUALS = "!=";
    String G_T = ">";
    String L_T_EQUALS = "<=";
    String G_T_EQUALS = ">=";
    String IN = "#";
    String DEFAULT_PROP = "joiningDate";
    int DEFAULT_PAGE_SIZE = 30;
    int DEFAULT_PAGE_INDEX = 0;
}

Our next step is to combine one or multiple specifications to create one combined query. Let's see how we can do that. Also, we need to have support for both AND and OR.

     Java 
   
xxxxxxxxxx
 
public class SearchSpecificationBuilder<T> {
    private final List<SearchCriteria> params;
    public SearchSpecificationBuilder() {
        params = new ArrayList<>();
    }
    public SearchSpecificationBuilder<T> with(String clause, String type, String key, String operation, String value) {
        params.add(new SearchCriteria(clause, type, key, operation, value));
        return this;
    }
    public Specification<T> build() {
        if (params.isEmpty())
            return null;
        Iterator<SearchCriteria> iterator = params.iterator();
        SearchCriteria criteria = iterator.next();
        Specification<T> result = Specification.where(new SearchSpecification<T>(criteria));
        while (iterator.hasNext()) {
            criteria = iterator.next();
            result = criteria.isClauseAnd() ? result.and(new SearchSpecification<T>(criteria))
                    : result.or(new SearchSpecification<T>(criteria));
        }
        return result;
    }
}

Look at line no. 21, how we are using the clauseAnd field to determine whether we want AND/OR.

The next step is to get the SearchCriteria from the client's end. We can expect a list of SearchCriteria directly or have some sort of query string and parse that String to extract it. This part of logic is up to you. I will go with a query string approach.

Suppose, we create a query string like this:

"S-firstName#\"Tony,Steve\",B-seniorMembers=\"true\",

OR-N-age!=\"30\",D-joiningDate>\"2020-04-05T14:44:51.366Z\""

To parse this, we need to first separate based on a comma and pass through a pattern like this:

"(OR-)?(N-|S-|D-|B-)(\\w+?)(=|<|>|<=|>=|#|!=)(\"([^\"]+)\")"

Using Matcher.group we can extract each field information, pay attention to enclosing the values with double quotes to include special characters. Also, the operations should follow what we have added to our constants.

     Java 
   
xxxxxxxxxx
 
    public static <T> Specification<T> createSpec(String query) {
        SearchSpecificationBuilder<T> builder = new SearchSpecificationBuilder<T>();
        for (Matcher matcher = SEARCH_QUERY_PATTERN.matcher(query + COMMA); matcher.find();)
            builder.with(matcher.group(1), matcher.group(2), matcher.group(3), matcher.group(4),
                    matcher.group(5).replaceAll(DOUBLE_QUOTES, EMPTY));
        return builder.build();
    }

Let's give a quick look at the overall search request body.

     Java 
   
xxxxxxxxxx
 
@Data
public class SearchReqDto {
    private int pageIndex = DEFAULT_PAGE_INDEX;
    private int pageSize = DEFAULT_PAGE_SIZE;
    private String query;
    private List<String> sorts = new ArrayList<String>();
}

In the request body apart from having the query we have 3 more fields which will help in providing sorting and pagination. One important thing here is how the sort is a list of String and how it helps in adding sorting. e.g. "sorts":["powerRating","firstName:A"]

Here the default sorting is in Descending order, in case we want ascending we need to append:A. Let's see how it works.

     Java 
   
xxxxxxxxxx
 
    public static List<Sort.Order> getOrders(List<String> sorts, String defaultProp) {
        if (sorts.isEmpty())
            return Arrays.asList(new Sort.Order(DESC, defaultProp));
        List<Sort.Order> orders = new ArrayList<Sort.Order>(sorts.size());
        for (String sort : sorts) {
            String[] split = sort.split(COLON);
            orders.add(new Sort.Order(split.length > 1 ? ASC : DESC, split[0]));
        }
        return orders;
    }

Let's take a look at our service class now and see how we can use our generic code to query two tables with much effort.

     Java 
   
 
 
      
    
 
      
 
     
 
      
 
     
 
     
 
    
x
 
          
 
         
 
          
 
         

              
          
@Service
public class HeroServiceImpl implements HeroService {
    private final AvengerRepository avengerRepository;
    private final JusticeLeaguerRepository justiceLeaguerRepository;
    @Autowired
    public HeroServiceImpl(AvengerRepository avengerRepository, JusticeLeaguerRepository JusticeLeaguerRepository) {
        this.avengerRepository = avengerRepository;
        this.justiceLeaguerRepository = JusticeLeaguerRepository;
    }
    @Override
    public SearchResDto searchMarvel(SearchReqDto reqDto) {
        PageRequest pageRequest = PageRequest.of(reqDto.getPageIndex(), reqDto.getPageSize(),
                by(getOrders(reqDto.getSorts(), DEFAULT_PROP)));
        Page<Avenger> page = avengerRepository.findAll(createSpec(reqDto.getQuery()), pageRequest);
        Function<Avenger, HeroDto> mapper = (hero) -> createCopyObject(hero, HeroDto::new);
        return prepareResponseForSearch(page, mapper);
    }
    @Override
    public SearchResDto searchDc(SearchReqDto reqDto) {
        PageRequest pageRequest = PageRequest.of(reqDto.getPageIndex(), reqDto.getPageSize(),
                by(getOrders(reqDto.getSorts(), DEFAULT_PROP)));
        Page<JusticeLeaguer> page = justiceLeaguerRepository.findAll(createSpec(reqDto.getQuery()), pageRequest);
        Function<JusticeLeaguer, HeroDto> mapper = (hero) -> createCopyObject(hero, HeroDto::new);
        return prepareResponseForSearch(page, mapper);
    }
    // create test data
    @PostConstruct
    public void createTestData() {
        avengerRepository.saveAll(asList(new Avenger("Tony", "Stark", "Iron Man", 40, new BigDecimal(9.8f), true),
                new Avenger("Bruce", "Banners", "Hulk", 35, new BigDecimal(8.2f), true),
                new Avenger("Steve", "Rogers", "Captain America", 32, new BigDecimal(9.9f), true),
                new Avenger("Clint", "Barton", "Hawkeye", 36, new BigDecimal(7.5f), true),
                new Avenger("Sam", "Wilson", "Falcon", 29, new BigDecimal(6.6f), false),
                new Avenger("Peter", "Parker", "Spiderman", 20, new BigDecimal(9), false),
                new Avenger("Nick", "Fury", "Fury", 45, new BigDecimal(8.1f), true),
                new Avenger("Scott", "Lang", "Antman", 37, new BigDecimal(8.3f), false),
                new Avenger("Nathasha", "Romannoff", "Black Widow", 33, new BigDecimal(9.2f), true),
                new Avenger("Thor", "Odinson", "Thor", 1025, new BigDecimal(9.9f), true),
                new Avenger("Wanda", "Maximoff", "Scarlett Witch", 26, new BigDecimal(9), false)));
        justiceLeaguerRepository
                .saveAll(asList(new JusticeLeaguer("Bruce", "Wayne", "BATMAN", 39, new BigDecimal(20), true),
                        new JusticeLeaguer("Clark", "Kent", "Superman", 32, new BigDecimal(10), true),
                        new JusticeLeaguer("Diana", "Prince", "Wonder Woman", 1051, new BigDecimal(10), true),
                        new JusticeLeaguer("Barry", "Allen", "Flash", 26, new BigDecimal(9.9f), true),
                        new JusticeLeaguer("Oliver", "Queen", "Green Arrow", 30, new BigDecimal(9), false),
                        new JusticeLeaguer("Hal", "Jordan", "Green Lantern", 31, new BigDecimal(9.55f), true),
                        new JusticeLeaguer("Jonn", "Jonnz", "Martian Manhunter", 745, new BigDecimal(9.2f), true),
                        new JusticeLeaguer("Billy", "Batson", "SHAZAM", 15, new BigDecimal(9.5f), false),
                        new JusticeLeaguer("Dinah", "Lang", "Black Canary", 28, new BigDecimal(8.5f), false),
                        new JusticeLeaguer("Kara", "Danvers", "Supergirl", 22, new BigDecimal(9.1f), false)));
    }
}
 
      
 
     

Examine how searchMarvel and searchDc are querying different tables without any specific logic apart from using a specific repository. For the sake of simplicity, I created two API but we can the same API for both cases with some minor conditions and passing another param quite easily. The last method is for generating test data which is again optional.

This is the core logic of creating a dynamic query engine. I have to say this example is to provide an idea and can be added with additional features and remove some as per your requirements. I hope it will provide some useful information.

I have left out the pagination part on purpose since it is nothing new and pretty self-explanatory.

I also have added some helper methods and DTO to create response data.

     Java 
   
x
 
    public static <T, R> SearchResDto prepareResponseForSearch(Page<T> page, Function<T, R> mapper) {
        SearchResDto response = new SearchResDto();
        response.setHeroes(page.stream().map(mapper).collect(toList()));
        response.setPageIndex(page.getNumber());
        response.setTotalPages(page.getTotalPages());
        response.setTotalRecords(page.getTotalElements());
        return response;
    }
    public static <T> T createCopyObject(Object src, Supplier<T> supplier) {
        T dest = supplier.get();
        copyProperties(src, dest);
        return dest;
    }

The full source code can be forked or downloaded from here.

Run this like any Springboot app in IDE or a jar after a build.

Sample request command:

     JSON 
   
xxxxxxxxxx

 curl -X POST \http://localhost:8080/hero/searchDc \-H 'cache-control: no-cache' \   -H 'content-type: application/json' \   -H 'postman-token: bef5c61b-62a0-0d88-b0df-9510f7c472a4' \   -d '{     "pageIndex":0,     "pageSize":5,     "query":"S-firstName#\"Tony,Steve\",B-seniorMembers=\"true\",OR-N-age!=\"30\",D-joiningDate>\"2020-04-05T14:44:51.366Z\"",     "sorts":["powerRating","firstName:A"] }'

Feel free to post suggestions, improvements or queries, will be glad to work on those.

Database Spring Framework

Published at DZone with permission of Ashish Lohia, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

Trending