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.依存関係
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.DB接続情報の設定
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
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
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
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
<!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を実行してURLにアクセスします。
所属コードを指定して検索してみます
SpringBootなら、DB検索も簡単に実装できます。