Spring Boot Sample 017之spring-boot-data-crudrepository

一、环境

• Idea 2020.1
• JDK 1.8
• maven

二、目的

spring boot整合crudrepository。

gitHub地址：https://github.com/ouyushan/ouyushan-spring-boot-samples

三、步骤

3.1、点击File -> New Project -> Spring Initializer，点击next

3.2、在对应地方修改自己的项目信息

 3.3、选择Web依赖，选中Spring Web、Spring Data JDBC、H2 DataBase。可以选择Spring Boot版本，本次默认为2.2.6，点击Next

3.4、项目结构

四、添加文件

pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>org.ouyushan</groupId>
    <artifactId>spring-boot-data-crudrepository</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-boot-data-crudrepository</name>
    <description>Crudrepository project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

数据库表结构文件schema.sql

CREATE TABLE CUSTOMER (
  ID             INTEGER IDENTITY PRIMARY KEY,
  FIRST_NAME     VARCHAR(30),
  DATE_OF_BIRTH  DATE
);

数据库记录文件data.sql

INSERT INTO CUSTOMER (ID, FIRST_NAME, DATE_OF_BIRTH) values (1, 'Meredith', '1998-07-13');
INSERT INTO CUSTOMER (ID, FIRST_NAME, DATE_OF_BIRTH) values (2, 'Joan', '1982-10-29');

Customer.java

package org.ouyushan.springboot.data.crudrepository.entity;

import org.springframework.data.annotation.Id;

import java.time.LocalDate;

/**
 * @Description:
 * @Author: ouyushan
 * @Email: ouyushan@hotmail.com
 * @Date: 2020/5/7 17:34
 */
public class Customer {

    @Id
    private Long id;

    private String firstName;

    private LocalDate dateOfBirth;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public LocalDate getDateOfBirth() {
        return dateOfBirth;
    }

    public void setDateOfBirth(LocalDate dateOfBirth) {
        this.dateOfBirth = dateOfBirth;
    }
}

CustomerRepository.java

package org.ouyushan.springboot.data.crudrepository.repository;

import org.ouyushan.springboot.data.crudrepository.entity.Customer;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @Description:
 * @Author: ouyushan
 * @Email: ouyushan@hotmail.com
 * @Date: 2020/5/7 17:34
 */
public interface CustomerRepository extends CrudRepository<Customer, Long> {

    @Query("select id, first_name, date_of_birth from customer where upper(first_name) like '%' || upper(:name) || '%' ")
    List<Customer> findByName(@Param("name") String name);

}

CustomerController.java

package org.ouyushan.springboot.data.crudrepository.controller;

import org.ouyushan.springboot.data.crudrepository.entity.Customer;
import org.ouyushan.springboot.data.crudrepository.repository.CustomerRepository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @Description:
 * @Author: ouyushan
 * @Email: ouyushan@hotmail.com
 * @Date: 2020/5/7 17:36
 */
@RestController
public class CustomerController {

    private final CustomerRepository customerRepository;

    // 构造注入
    public CustomerController(CustomerRepository customerRepository) {
        this.customerRepository = customerRepository;
    }

    @GetMapping("/")
    @Transactional(readOnly = true)
    public List<Customer> customers(@RequestParam String name) {
        return this.customerRepository.findByName(name);
    }
}

README.md

# spring-boot-data-crudrepository
springboot中CrudRepository的使用

* 继承CrudRepository   编写Repository 接口类，使用@Query注解编写查询语句
* 在Controller中通过构造函数注入Repository接口

1.Spring Data JPA可以极大地简化JPA的写法，在几乎不用写接口实现的情况下完成对数据的访问和操作。

2.Spring Data通过提供Repository接口来约定数据访问的统一标准。

3.Repository接口下包含一些常用的子接口：

   CrudRepository、PagingAndSortingRepository、JpaRepository

   JpaRepository，它继承自PagingAndSortingRepository，而PagingAndSortingRepository又继承自CrudRepository。
每个都有自己的功能：
   CrudRepository提供CRUD的功能。
   PagingAndSortingRepository提供分页和排序功能
   JpaRepository提供JPA相关的方法，如刷新持久化数据、批量删除。
   由于三者之间的继承关系，所以JpaRepository包含了CrudRepository和PagingAndSortingRepository所有的API。

4.在进行spring boot 项目的开发中，开发者只需要定义自己项目的数据访问接口，然后实现Spring Data提供的这些接口，就可以实现对数据的CRUD操作。
```
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
```

Spring Boot可以自动配置嵌入式H2、HSQL和Derby数据库。
您不需要提供任何连接url,您只需要包含对希望使用的嵌入式数据库的构建依赖项。

默认数据源：HikariCP，其次Tomcat pooling DataSource，再次Commons DBCP2

默认采用h2数据库，在resources中定义以下文件
schema.sql      定义数据表
```
CREATE TABLE CUSTOMER (
  ID             INTEGER IDENTITY PRIMARY KEY,
  FIRST_NAME     VARCHAR(30),
  DATE_OF_BIRTH  DATE
);
```

data.sql    定义数据
```
INSERT INTO CUSTOMER (ID, FIRST_NAME, DATE_OF_BIRTH) values (1, 'Meredith', '1998-07-13');
INSERT INTO CUSTOMER (ID, FIRST_NAME, DATE_OF_BIRTH) values (2, 'Joan', '1982-10-29');
```


http://localhost:8080/?name
```
[
{
"id": 1,
"firstName": "Meredith",
"dateOfBirth": "1998-07-13"
},
{
"id": 2,
"firstName": "Joan",
"dateOfBirth": "1982-10-29"
}
]
```

http://localhost:8080/?name=Joan
```
[{"id":2,"firstName":"Joan","dateOfBirth":"1982-10-29"}]
```

五、测试

http://localhost:8080/?name
[
{
"id": 1,
"firstName": "Meredith",
"dateOfBirth": "1998-07-13"
},
{
"id": 2,
"firstName": "Joan",
"dateOfBirth": "1982-10-29"
}
]

http://localhost:8080/?name=Joan

[{"id":2,"firstName":"Joan","dateOfBirth":"1982-10-29"}]