Accessing data with MySQL笔记

饮水思源：https://spring.io/guides/gs/accessing-data-mysql/

①通过https://start.spring.io/创建项目，添加Spring Web, Spring Data JPA, 和MySQL Driver这三个依赖。

②创建数据库

打开命令行工具登陆一个可以创建用户的用户：

D:\Temp>mysql -uxkfx -p

创建一个新的数据库：

create database db_example;

创建一个新的用户：

create user 'springuser'@'%' identified by 'ThePassword';

查看是否创建成功：

mysql> SELECT user,host FROM mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| springuser    | %         |
| xkfx          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

把新数据库的所有权限都赋予新用户：

grant all on db_example.* to 'springuser'@'%';

③编辑application.properties

Spring Data JPA底层默认用hibernate实现。

需要对数据源以及hibernate的一些行为进行配置：

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/db_example
spring.datasource.username=springuser
spring.datasource.password=ThePassword
spring.datasource.driver-class-name =com.mysql.cj.jdbc.Driver
#spring.jpa.show-sql: true

关于spring.jpa.hibernate.ddl-auto属性，原文有较详细说明。

④创建实体（@Entity）模型：

package com.example.accessingdatamysql;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity // This tells Hibernate to make a table out of this class
public class User {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Integer id;

  private String name;

  private String email;

  public Integer getId() {
    return id;
  }

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

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }
}

hibernate将自动把实体翻译成一张表。

⑤创建Repository

您需要创建保存用户记录的存储库：

package com.example.accessingdatamysql;

import org.springframework.data.repository.CrudRepository;

import com.example.accessingdatamysql.User;

// This will be AUTO IMPLEMENTED by Spring into a Bean called userRepository
// CRUD refers Create, Read, Update, Delete

public interface UserRepository extends CrudRepository<User, Integer> {

}

Spring在一个具有相同名称的bean中自动实现了这个存储库接口（大小写发生了变化）——它叫userRepository

⑥创建一个控制器

您需要创建一个控制器来处理对应用程序的HTTP请求，如下所示：

package com.example.accessingdatamysql;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller // This means that this class is a Controller
@RequestMapping(path="/demo") // This means URL's start with /demo (after Application path)
public class MainController {
  @Autowired // This means to get the bean called userRepository
         // Which is auto-generated by Spring, we will use it to handle the data
  private UserRepository userRepository;

  @PostMapping(path="/add") // Map ONLY POST Requests
  public @ResponseBody String addNewUser (@RequestParam String name
      , @RequestParam String email) {
    // @ResponseBody means the returned String is the response, not a view name
    // @RequestParam means it is a parameter from the GET or POST request

    User n = new User();
    n.setName(name);
    n.setEmail(email);
    userRepository.save(n);
    return "Saved";
  }

  @GetMapping(path="/all")
  public @ResponseBody Iterable<User> getAllUsers() {
    // This returns a JSON or XML with the users
    return userRepository.findAll();
  }
}

⑦程序入口已经自动创建了，所以直接打包运行程序：

$ mvn package

$ java -jar target/accessingdatamysql-0.0.1-SNAPSHOT.jar

打包过程顺利，但是运行过程却出现了异常。我发现数据库中已经多了hibernate_sequence和user这两张表。

错误日志主要信息：

ERROR 12568 --- [ main] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to execute isValid() for connection, configure connection test query (com.mysql.jdbc.Connection.isValid(I)Z).

WARN 12568 --- [ main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is java.lang.AbstractMethodError: com.mysql.jdbc.Connection.isValid(I)Z

对BUG进行复现发现，数据库中的表是在执行mvn package时创建的，而非执行java命令时创建的。

该BUG已解决，且已更新到上文（2022年3月13日），详见：https://www.cnblogs.com/xkxf/p/15999655.html

⑧手动测试。

通过curl指令POST一些数据到数据库：

curl localhost:8080/demo/add -d name=xkfx -d email=1223830128309@qq.com   

可以通过curl，也可以通过浏览器访问http://localhost:8080/demo/all去查看数据：

[{"id":1,"name":"xkfx","email":"1223830128309@qq.com"},
{"id":2,"name":"xk3123fx","email":"1223830128309@qq.com"},
{"id":3,"name":"xk3123fx","email":"1223830128309@qq.com"},
{"id":4,"name":"xk3123fx","email":"1223830128123309@qq.com"},
{"id":5,"name":"xk3123123fx","email":"1223830128123309@qq.com"}]

也可以去数据库验证。

 

⑨一些安全性的改变。详见原文。