SpringBoot/SpringCloud初探九（数据库）

演示采用MySQL，数据库安装见初探一（安装篇），切记先创建一个数据库，名为hello（在yml文件体现数据库名称）

演示架构

可通过http://localhost:8899/insert?id=1&title=demo&author=shiweizhiyan测试插入操作，该访问会触发一系列调用，最终访问到数据库。其它操作见controller文件。操作之后，可借助数据库可视化工具查看操作情况。

pom.xml
引入依赖：mybatis-spring-boot-starter、mysql-connector-java、spring-boot-starter-jdbc

<?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.6.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.abcd</groupId>
    <artifactId>Database</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Database</name>
    <description>Database</description>
    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>2021.0.0</spring-cloud.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
        </dependency>

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

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
         </dependency>

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

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

</project>

application.yml

schema-locations指定sql脚本的位置

mysql://localhost:3306//hello表示访问mysql中数据库名为hello的数据库

3306是数据库的端口，与mysql安装文件的my.ini里面port配置值保持一致。

server:
  port: 8899

eureka:
  client:
    service-url:
      defaultZone: http://localhost:8761/eureka/

spring:
  application:
    name: database
  datasource:
    url: jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
    username: root
    password: 123456
    driver-class-name:  com.mysql.cj.jdbc.Driver
  sql:
    init:
      schema-locations:
        - classpath:./schema.sql
      mode: always

数据库SQL语言脚本

存放位置：src/main/resources/schema.sql

注意新版本中，book/id/title...这些通通不需要双引号

schema.sql内容：

DROP TABLE IF EXISTS book;
CREATE TABLE book(
    id int(11) NOT NULL,
    title varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
    author varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
    PRIMARY KEY(id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

java

调用关系：controller --> service --> mapper --> entity

Application代码

@EnableEurekaClient
@SpringBootApplication
public class DatabaseApplication {

    public static void main(String[] args) {
        SpringApplication.run(DatabaseApplication.class, args);
    }

}

Controller代码

@RestController
public class BookController {

    @Autowired
    private BookService bookService;

    @RequestMapping(value = "/hello")
    public String hello(){
        return bookService.hello();
    }

    @RequestMapping(value = "/insert")
    public String insert(int id, String title, String author){
        return bookService.insert(id, title, author);
    }

    @RequestMapping(value = "/delete")
    public String delete(int id){
        return bookService.delete(id);
    }

    @RequestMapping(value = "/update")
    public String update(String title, int id){
        return bookService.update(title, id);
    }

    @RequestMapping(value = "/select")
    public Object select(String title){
        return bookService.select(title);
    }
}

Service代码

@Service
public class BookService {

    @Autowired
    private BookMapper bookMapper;

    public String hello(){
        return "hello world";
    }

    public String insert(int id, String title, String author){

        Book book = new Book();
        book.setId(id);
        book.setTitle(title);
        book.setAuthor(author);

        System.out.println("id: " + id + ", title: " + title + ", author: " + author);
        bookMapper.insert(book);
        return "INSERTED";
    }

    public String delete(int id){
        bookMapper.delete(id);
        return "DELETED";
    }

    public String update(String title, int id){
        bookMapper.update(title, id);
        return "UPDATED";
    }

    public List<Book> select(String title){
        return bookMapper.select(title);
    }
}

Mapper(DAO)代码

@Mapper
public interface BookMapper {

    @Results(id = "bookWords",
            value = {
                    @Result(property = "id", column = "id"),
                    @Result(property = "title", column = "title"),
                    @Result(property = "author", column = "author")
            }
    )

    @Insert("INSERT INTO BOOK (id,title,author) value (#{id},#{title},#{author})")
    void insert(Book book);

    @Delete("DELETE FROM BOOK WHERE id=#{title}")
    void delete(int id);

    @Update("UPDATE BOOK SET title=#{title} WHERE ID=#{id}")
    void update(String title, int id);

    @Select("SELECT * FROM BOOK WHERE TITLE=#{title}")
    List<Book> select(String title);

Entity代码

public class Book {

    private int id;
    private String title;
    private String author;

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

    public int getId(){
        return this.id;
    }

    public void setTitle(String title){
        this.title = title;
    }

    public String getTitle(){
        return this.title;
    }

    public void setAuthor(String author){
        this.author = author;
    }

    public String getAuthor(){
        return this.author;
    }
}