SpringbootのSpecificationで動的クエリ

SpringbootのSpecificationを使って動的クエリを試してみます。

1.環境

eclipse 4.7.2
java 8
database postgreSQL10
build.gradle

    compile('org.springframework.boot:spring-boot-starter-data-jpa')
    compile('org.springframework.boot:spring-boot-starter-thymeleaf')
    compile('org.springframework.boot:spring-boot-starter-web')
    compile('org.apache.commons:commons-lang3:3.7')
    compile('com.fasterxml.jackson.datatype:jackson-datatype-jsr310')
    compile('org.modelmapper.extensions:modelmapper-spring:1.1.2')
    runtime('org.springframework.boot:spring-boot-devtools')
    runtime('org.postgresql:postgresql')
    compileOnly('org.projectlombok:lombok')
    providedRuntime('org.springframework.boot:spring-boot-starter-tomcat')
    testCompile('org.springframework.boot:spring-boot-starter-test')

2.Entity

Employee.java

import java.time.LocalDate;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import org.springframework.format.annotation.DateTimeFormat;
import lombok.Data;
@Entity
@Data
public class Employee {
    @Id
    /** 従業員コード */
    @Column (name="username")
    private String username;

    /** 従業員名 */
    @Column (name="empname")
    private String empname;

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

    /** ログインパスワード */
    @Column (name="password")
    private String password;

    /** パスワード有効期限 */
    @Column (name="passwordlimit")
    @DateTimeFormat(pattern = "yyyy/MM/dd")
    private LocalDate passwordlimit;

    /** 役割 */
    @Column (name="userrole")
    private int userrole;
}

3.Repository

JpaSpecificationExecutorを継承します。
(1)usernameIn
usernameはin句で複数指定可能な検索とします。
(2)empnameContains
empnameはlike検索にします。

EmployeeRepository.java

import java.util.List;
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.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.util.StringUtils;

public interface EmployeeRepository extends JpaRepository<Employee, String>, JpaSpecificationExecutor<Employee> {
    public class EmployeeSpecifications {
        public static Specification<Employee> empnameContains(final String empname) {
            return StringUtils.isEmpty(empname) ? null : new Specification<Employee>() {
                @Override
                public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                    return cb.like(root.get("empname"), "%" + empname + "%");
                }
            };
        }
        public static Specification<Employee> usernameIn(final List<String> usernames) {
            return StringUtils.isEmpty(usernames) ? null : new Specification<Employee>() {
                @Override
                public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                    return cb.in(root.get("username")).value(usernames);
                }
            };
        }
    }
}

4.Service

EmployeeService.java

import static com.stone.sample.EmployeeRepository.EmployeeSpecifications.*;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
@Service
public class EmployeeService {
    @Autowired
    EmployeeRepository repository;
    public List<Employee> findEmployees(List<String> username, String empname) {
        return repository.findAll(Specification
            .where(usernameIn(username))
            .and(empnameContains(empname))
        );
    }
}

5.Controller

SpecificationController.java

import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
@Controller
public class SpecificationController {
    @Autowired
    EmployeeService employeeService;
      @RequestMapping(value="/", method = RequestMethod.GET)
      public ModelAndView index() {
        ModelAndView mv=new ModelAndView();
        mv.setViewName("index");
        mv.addObject("form",new SearchForm());
        List<Employee> emps = employeeService.findUsers(null, null);
        mv.addObject("emplist",emps);
        return mv;
      }
      @RequestMapping(value="/search", method = RequestMethod.POST)
      public ModelAndView search(@ModelAttribute SearchForm form, Model model) {
        List<String> usernames = new ArrayList<String>();
        if (!form.getUsername1().isEmpty()) usernames.add(form.getUsername1());
        if (!form.getUsername2().isEmpty()) usernames.add(form.getUsername2());
        if (!form.getUsername3().isEmpty()) usernames.add(form.getUsername3());
        List<Employee> emps = employeeService.findUsers(usernames, form.getEmpname());
        ModelAndView mv=new ModelAndView();
        mv.setViewName("index");
        mv.addObject("form",form);
        mv.addObject("emplist",emps);
        return mv;
      }
}

6.html

index.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
    <head>
        <title>Springboot</title>
        <meta charset="utf-8" />
    </head>
    <body>
      <h3>Specifinationで動的検索</h3>
      <form th:action="@{/search}" th:object="${form}" method="post">
        <div>
        <label>ユーザID</label>
        <input type="text" th:field="*{username1}" />
        <input type="text" th:field="*{username2}" />
        <input type="text" th:field="*{username3}" />
        </div>
        <div>
        <label>ユーザ名</label>
        <input type="text" th:field="*{empname}" />
        </div>
        <input type="submit" value="検索"/>
      </form>
        <table border="1">
        <tr>
        <th>username</th>
        <th>empname</th>
        </tr>
        <tr th:each="emp : ${emplist}">
            <td th:text="${emp.username}"></td>
            <td th:text="${emp.empname}"></td>
        </tr>
        </table>    
    </body>
</html>

7.動作確認