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;
}
}
};
}
}
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}"><<lead</span>
<a th:if="${!page.isFirst}"
th:href="@{/hello(page = 0)}">
<<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>></span> <a th:if="${!page.isLast}"
th:href="@{/hello(page = ${page.totalPages - 1})}">
last>> </a></li>
</ul>
</div>
<div th:if="${!isAllOrSearch}">
<ul>
<li style="display: inline;"><span th:if="${page.isFirst}"><<lead</span>
<a th:if="${!page.isFirst}"
th:href="@{'/search?keyWords=' + ${keyWords} + '&page=0'}">
<<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>></span> <a th:if="${!page.isLast}"
th:href="@{'/search?keyWords=' + ${keyWords} + '&page=' + ${page.totalPages - 1}}">
last>> </a></li>
</ul>
</div>
</body>
</html>
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)
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
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