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.動作確認

![image.png](https://qiita-image-store.s3.amazonaws.com/0/246741/afede32f-9222-29fd-77d9-dc2655004a50.png)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/246741/0b69afa5-e246-268a-8097-a09e1576f1ea.png)