Dynamically generate queries with Spring Data JPA (multi-word search) (paging support)

I want to perform AND search of multiple words separated by spaces like Google search with JPA. It's like "change of job in Tokyo". I also want to support paging.

After researching various things, I arrived at the Specification, so I will leave a memorandum of the implementation method. There are some rough edges, so just for reference.

Entity

@Data
@Entity
@Table(name="account")
public class AccountEntity implements Serializable{

	/**
	 *Serial version UID
	 */
	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name="id")
	private Integer id;

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

	@Column(name="age")
	private Integer age;
}

Repository It inherits from JpaSpecificationExecutor.

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

@Repository
public interface AccountRepository extends JpaRepository<AccountEntity, Integer>, JpaSpecificationExecutor<AccountEntity> {
	Page<AccountEntity> findAll(Pageable pageable);
}

Specification

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;


@Component
public class AccountSpecification {
    /**
     *Search for accounts that include the specified characters in the user name.
     */
    public Specification<AccountEntity> nameLike(String name) {

   	//Anonymous class
        return new Specification<AccountEntity>() {
        	//CriteriaAPI
            @Override
            public Predicate toPredicate(Root<AccountEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            	// 「name LIKE '%name%'"Add
            	return cb.like(root.get("name"), "%" + name + "%");
            }
        };
    }
    /**
     *Search for accounts that include the specified character in their age.
     */
    public Specification<AccountEntity> ageEqual(String age) {

        return new Specification<AccountEntity>() {
        	//CriteriaAPI
            @Override
            public Predicate toPredicate(Root<AccountEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

            	//Check if numerical conversion is possible
            	try {
            		Integer.parseInt(age);

            		// 「age = 'age'"Add
            		return cb.equal(root.get("age"), age);

            	} catch (NumberFormatException e) {
            		return null;
            	}
            }
        };
    }
}

Connect with AND clause / OR clause

You can connect with and () and or () here as well, but it may be easier to understand if you use the where () or and () or () method in the service class.

            	cb.like(root.get("name"), "%" + name + "%");
            	cb.and(cb.equal(root.get("age"), age));
            	return cb.or(cb.equal(root.get("age"), age));

Service Call the where () and and () methods with the findAll argument to add a Where () or And clause.

1 word search version


@Service
public class AccountService {

	@Autowired
	AccountRepository repository;

	@Autowired
	AccountSpecification accountSpecification;

	public List<AccountEntity> searchAccount(String keyWords, Pageable pageable){

		//Deleted full-width and half-width spaces before and after
		String trimedkeyWords = keyWords.strip();

		//Separate with full-width space and half-width space
		String[] keyWordArray = trimedkeyWords.split("[  ]", 0);

		// 「Select * From account」 + 「Where name LIKE '%keyWordArray[0]%'」
		return repository.findAll(Specification
				.where(accountSpecification.ageEqual(keyWordArray[0])), pageable);
	}
}

When you want to put it in () of Where clause or And clause like "Select * From account Where (name ='name' OR age ='age') AND (name ='name' OR age ='age')" Narrows the And/OR method inside the Where/And method.

Multi-word search version


import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

@Service
public class AccountService {

	@Autowired
	AccountRepository repository;

	@Autowired
	AccountSpecification accountSpecification;

	public Page<AccountEntity> findAll(Pageable pageable) {
		return repository.findAll(pageable);
	}

	public Page<AccountEntity> searchAccount(String keyWords, Pageable pageable){

		//Deleted full-width and half-width spaces before and after
		String trimedkeyWords = keyWords.strip();

		//Separate with full-width space and half-width space
		String[] keyWordArray = trimedkeyWords.split("[  ]", 0);

		//todo The null check in isBlank here is not working. null becomes false.
		//Search all if null or empty string Add Where clause if 1 word Add And clause if 2 or more words.
		if(keyWordArray.length == 1 && StringUtils.isBlank(keyWordArray[0])) {
			return repository.findAll(pageable);

		}else if(keyWordArray.length == 1) {
			// 「Select * From account Where (name LIKE '%keyWordArray[0]%' OR age = '%keyWordArray[0]%')
			return repository.findAll(Specification
					.where(accountSpecification.nameLike(keyWordArray[0])
					.or(accountSpecification.ageEqual(keyWordArray[0]))), pageable);

		}else {
			Specification<AccountEntity> specification =
					Specification.where(accountSpecification.nameLike(keyWordArray[0])
							.or(accountSpecification.ageEqual(keyWordArray[0])));

			// 「Select * From account Where(name LIKE '%keyWordArray[0]%' OR age = '%keyWordArray[0]%') AND(name LIKE '%keyWordArray[i]%' OR age = '%keyWordArray[i]%')AND ・ ・ ・
			for(int i = 1; i < keyWordArray.length; i++) {
				specification = specification.and(accountSpecification.nameLike(keyWordArray[i])
						.or(accountSpecification.ageEqual(keyWordArray[i])));
			}
			return repository.findAll(specification, pageable);
		}
	}
}

Controller

@Controller
public class AccountController {
	//For judgment of full display or search
	boolean isAllOrSearch;

	@Autowired
	AccountService accountService;

	//View all accounts
	@GetMapping("/hello")
	public String getHello( @PageableDefault(page=0, size=2)Pageable pageable, Model model) {
		isAllOrSearch = true;
		Page<AccountEntity> accountAll = accountService.findAll(pageable);

		model.addAttribute("isAllOrSearch", isAllOrSearch);
		model.addAttribute("page", accountAll);
		model.addAttribute("accountAll", accountAll.getContent());
		return "hello";
	}

	//Account search
	@GetMapping("/search")
	public String getName(@RequestParam("keyWords")String keyWords, Model model, @PageableDefault(page = 0, size=2)Pageable pageable) {
		isAllOrSearch = false;
		Page<AccountEntity> accountAll = accountService.searchAccount(keyWords, pageable);

		model.addAttribute("keyWords", keyWords);
		model.addAttribute("isAllOrSearch", isAllOrSearch);
		model.addAttribute("page", accountAll);
		model.addAttribute("accountAll", accountAll.getContent());

		return "hello";
	}
}

HTML I have written almost the same pagenation for full account display and search. Null check in service class doesn't work.

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="/search" method="get">
		<input type="text" name="keyWords">
		<input type="submit">
	</form>

	<table>
		<tbody>
			<tr>
				<th>ID</th>
				<th>name</th>
				<th>age</th>
			</tr>
			<tr th:each="account : ${accountAll}">
				<td th:text="${account.id}">id</td>
				<td th:text="${account.name}">name</td>
				<td th:text="${account.age}">age</td>
			</tr>
		</tbody>
	</table>
	<!--Pagination-->
		<div th:if="${isAllOrSearch}">
			<ul>
				<li style="display: inline;"><span th:if="${page.isFirst}">&lt;&lt;lead</span>
					<a th:if="${!page.isFirst}"
					th:href="@{/hello(page = 0)}">
						&lt;&lt;lead</a></li>
				<li style="display: inline; margin-left: 10px;"><span th:if="${page.isFirst}">Before</span>
					<a th:if="${!page.isFirst}"
					th:href="@{/hello(page = ${page.number} - 1)}">
Before</a></li>
				<li th:if="${!page.empty}" th:each="i : ${#numbers.sequence(0, page.totalPages - 1)}"
					style="display: inline; margin-left: 10px;"><span
					th:if="${i} == ${page.number}" th:text="${i + 1}">1</span> <a
					th:if="${i} != ${page.number}"
					th:href="@{/hello(page = ${i})}"> <span
						th:text="${i+1}">1</span></a></li>
				<li style="display: inline; margin-left: 10px;"><span
					th:if="${page.isLast}">Next</span> <a th:if="${!page.isLast}"
					th:href="@{/hello(page = (${page.number} + 1))}">
Next</a></li>
				<li style="display: inline; margin-left: 10px;"><span
					th:if="${page.last}">last&gt;&gt;</span> <a th:if="${!page.isLast}"
					th:href="@{/hello(page = ${page.totalPages - 1})}">
last&gt;&gt; </a></li>
			</ul>
		</div>

		<div th:if="${!isAllOrSearch}">
			<ul>
				<li style="display: inline;"><span th:if="${page.isFirst}">&lt;&lt;lead</span>
					<a th:if="${!page.isFirst}"
					th:href="@{'/search?keyWords=' + ${keyWords} + '&page=0'}">
						&lt;&lt;lead</a></li>
				<li style="display: inline; margin-left: 10px;"><span th:if="${page.isFirst}">Before</span>
					<a th:if="${!page.isFirst}"
					th:href="@{'/search?keyWords=' + ${keyWords} + '&page=' + ${page.number - 1}}">
Before</a></li>
				<li th:if="${!page.empty}" th:each="i : ${#numbers.sequence(0, page.totalPages - 1)}"
					style="display: inline; margin-left: 10px;"><span
					th:if="${i} == ${page.number}" th:text="${i + 1}">1</span> <a
					th:if="${i} != ${page.number}"
					th:href="@{'/search?keyWords=' + ${keyWords} + '&page=' + ${i}}"> <span
						th:text="${i+1}">1</span></a></li>
				<li style="display: inline; margin-left: 10px;"><span
					th:if="${page.isLast}">Next</span> <a th:if="${!page.isLast}"
					th:href="@{'/search?keyWords=' + ${keyWords} + '&page=' + ${page.number + 1}}">
Next</a></li>
				<li style="display: inline; margin-left: 10px;"><span
					th:if="${page.last}">last&gt;&gt;</span> <a th:if="${!page.isLast}"
					th:href="@{'/search?keyWords=' + ${keyWords} + '&page=' + ${page.totalPages - 1}}">
last&gt;&gt; </a></li>
			</ul>
		</div>
</body>
</html>

Specification pattern

A Specification pattern is a design pattern that aims to meet specifications. For example, when searching for human resources, the conditions and candidates are separated. Normally, it is implemented by IF statement or SQL Where clause.

In the specification pattern Use the and () and or () methods to add individual condition judgment objects.

In this example, the source of condition judgment is in Entity, and conditions are added by and and or methods. (I'm sorry if I made a mistake)

reference

Easy dynamic query with Spring Data JPA Specification [JPA] Dynamically set conditions for DB search Refine search by multiple keywords in JPA Specification Specification pattern: A means of expressing complex business rules

At the end

Recently, I often touched Spring Boot and JPA and looked into the contents of the interface. It would be nice to know the design patterns to understand the abstract code. There seems to be a masterpiece of Java design patterns, so let's buy it.

Recommended Posts

Dynamically generate queries with Spring Data JPA (multi-word search) (paging support)
OR search with Spring Data Jpa Specification
Sort by Spring Data JPA (with compound key sort)
Creating a common repository with Spring Data JPA
A memorandum when trying Spring Data JPA with STS
I tried to get started with Spring Data JPA
Spring Boot Introductory Guide I tried [Accessing Data with JPA]
Generate barcode with Spring Boot
[spring] Let's use Spring Data JPA
See the behavior of entity update with Spring Boot + Spring Data JPA
Creating REST APIs with Spring JPA Data with REST and Lombok incredibly easy.
[How to install Spring Data Jpa]
Spring Data JPA SQL log output
Implementation method for multi-data source with Spring boot (Mybatis and Spring Data JPA)