使用SpringBoot + Spring JPA来使用原生查询

我将在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

依存关系

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. 数据库表格
创建表格 m_emp (
empno bigint(20) NOT NULL AUTO_INCREMENT,
empname varchar(255) DEFAULT NULL,
departmentid varchar(10) DEFAULT NULL,
PRIMARY KEY (empno)
) 使用 InnoDB 引擎，自动递增起始值为 5，字符集为 utf8。

将以下内容插入到sampledb.m_emp表中：
(empname, departmentid)
values
(‘従業員A’, ‘10101001’)
,(‘従業員B’, ‘10101001’)
,(‘従業員C’, ‘10101002’)
,(‘従業員D’, ‘10101003’)

创建表m_department(
departmentid varchar(10) NOT NULL,
departmentname varchar(128) DEFAULT NULL,
PRIMARY KEY (departmentid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入到sampledb.m_department表中（departmentid，departmentname）的值为
(‘10101001′,’所属P’)
,(‘10101002′,’所属Q’)
,(‘10101003′,’所属R’)

5. 实体

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. 存储库

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. 控制器

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，您可以轻松地实现数据库搜索。