SpringBoot + Spring JPAでNativeクエリを使う

##SpringBootでクエリ検索を試します。

1.構成

```
sample-jpa02
│  build.gradle
└─src
    └─main
        ├─java
        │  └─com
        │      └─sample
        │            HeloController.java
        │            QEmployee.java
        │            QEmployeeRepository.java
        │            SamplePrj005Jpa02Application.java
        │            ServletInitializer.java
        └─resources
            │  application.properties
            ├─static
            └─templates
                  index.html
```

2.依存関係

```build.gradle
dependencies {
	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')
	runtime('org.springframework.boot:spring-boot-devtools')
	runtime('mysql:mysql-connector-java')
	compileOnly('org.projectlombok:lombok')
	providedRuntime('org.springframework.boot:spring-boot-starter-tomcat')
	testCompile('org.springframework.boot:spring-boot-starter-test')
}
```

3.ＤＢ接続情報の設定

```
spring.datasource.url=jdbc:mysql://localhost:3306/sampledb
spring.datasource.username=testuser
spring.datasource.password=testuser
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=update
```

4.テーブル
CREATE TABLE `m_emp` (
  `empno` bigint(20) NOT NULL AUTO_INCREMENT,
  `empname` varchar(255) DEFAULT NULL,
  `departmentid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

insert into sampledb.m_emp
(empname,departmentid)
values
('従業員A','10101001')
,('従業員B','10101001')
,('従業員C','10101002')
,('従業員D','10101003')

CREATE TABLE `m_department` (
  `departmentid` varchar(10) NOT NULL,
  `departmentname` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`departmentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into sampledb.m_department
(departmentid,departmentname)
values
 ('10101001','所属P')
,('10101002','所属Q')
,('10101003','所属R')

5.Entity

```QEmployee.java
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class QEmployee {
	@Id
	@Column(name="empno")
	private long id;
	private String empname;
	private String departmentid;
	private String departmentname;
// setter getter
}
```

6.Repository

```QEmployeeRepository.java
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface QEmployeeRepository extends JpaRepository<QEmployee, Long>  {
	public static final String _querybase
	 = "SELECT"
	 + "  emp.empno"
	 + " ,emp.empname"
	 + " ,emp.departmentid"
	 + " ,dep.departmentname"
	 + "  FROM"
	 + "  sampledb.m_emp emp"
	 + "  LEFT JOIN"
	 + "  sampledb.m_department dep"
	 + "  ON emp.departmentid = dep.departmentid"
	 ;
	public static final String _query = _querybase
			 + "  ORDER BY"
			 + "  emp.departmentid,emp.empno";
	public static final String _query2 = _querybase
			 + "  WHERE"
			 + "  emp.departmentid = :departmentid"
			 + "  ORDER BY"
			 + "  emp.departmentid,emp.empno";
	@Query(value = _query, nativeQuery = true)
	List<QEmployee> findAll();
	@Query(value = _query2, nativeQuery = true)
	List<QEmployee> findByDepartmentId(@Param("departmentid") String departmentid);
}
```

7.Controller

```HeloController.java
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.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

@Controller
public class HeloController {
	@Autowired
	QEmployeeRepository empRepository;
	@RequestMapping(value = "/", method = RequestMethod.GET)
    public String index(Model model) {
		//画面に表示する従業員データを取得します。
		List<QEmployee> emplist=empRepository.findAll();
        model.addAttribute("emplist", emplist);
        return "index";
    }
	@RequestMapping(value = "/department", method = RequestMethod.GET)
    public String departmentsearch(@RequestParam("departmentid") String departmentid, Model model) {
		//画面に表示する従業員データ（所属='10101001'）を取得します。
		List<QEmployee> emplist=empRepository.findByDepartmentId(departmentid);
        model.addAttribute("emplist", emplist);
        return "index";
    }
}
```
8.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>
        <h1 th:text="${title}"></h1>
        <form th:action="@{/department}" th:method="get">
        	<label>所属コード：</label>
	        <input type="text" th:name="departmentid"/>
	        <button type="submit">検索</button>
        </form>
        <table>
			<tr th:each="emp : ${emplist}" th:object="${emp}">
			    <td th:text="*{id}"></td>
			    <td th:text="*{empname}"></td>
			    <td th:text="*{departmentid}"></td>
			    <td th:text="*{departmentname}"></td>
			</tr>
        </table>
	</body>
</html>
```
9.Springbootを実行してＵＲＬにアクセスします。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/246741/80fbad10-894b-be0c-6295-8412efd826cf.png)

所属コードを指定して検索してみます

![image.png](https://qiita-image-store.s3.amazonaws.com/0/246741/0243b056-5b2a-d61b-cb25-66b311f7aaee.png)

SpringBootなら、ＤＢ検索も簡単に実装できます。