SpringBoot+MySQL+JPA后端数据库搭建

本文实现了SpringBoot项目对本地MySQL的连接，并通过JPA实现了对数据的CRUD操作的化简

创建SpringBoot项目有问题？看这里：Eclipse创建Maven+SpringBoot项目

1. 在pom.xml添加dependencies

<!-- JPA dependency -->
<dependency>    
    <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- JDBC dependency -->
<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- MySQL dependency -->
<dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <scope>runtime</scope>
       <version>8.0.15</version>
</dependency>

其中，如果version填写自己相对应的mysql版本，如果是8以上，不填的话运行时会报错

Unable to load authentication plugin 'caching_sha2_password'.

2. application properties添加database配置参数

#---------------------------------------------
# DATA PROPERTIES
#---------------------------------------------
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/purchase_analytics
spring.datasource.username = root
spring.datasource.password = 1996S05y22u!
spring.jpa.hibernate.ddl-auto = update

# Specify the DBMS
spring.jpa.database = MYSQL
# Show or not log for each sql query
spring.jpa.show-sql = true
# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto = update
# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
# stripped before adding them to the entity manager)
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

spring.jpa.hibernate.ddl-auto的几个属性：

• create： 每次加载hibernate时都会删除上一次的生成的表，然后根据你的model类再重新来生成新表，哪怕两次没有任何改变也要这样执行，这就是导致数据库表数据丢失的一个重要原因
• create-drop ： 每次加载hibernate时根据model类生成表，但是sessionFactory一关闭,表就自动删除
• update： 最常用的属性，第一次加载hibernate时根据model类会自动建立起表的结构（前提是先建立好数据库），以后加载hibernate时根据model类自动更新表结构，即使表结构改变了也不会删除以前的行
• validate ： 每次加载hibernate时，会校验数据与数据库的字段类型是否相同，不同会报错
  参考文献：jpa的hibernate.ddl-auto的几个属性值区别

spring.jpa.hibernate.naming-strategy的几个属性：

• DefaultNamingStrategy： 直接映射，不会做过多的处理（前提没有设置@Table，@Column等属性的时候）。如果有@Column则以@Column为准
• ImprovedNamingStrategy： 表名，字段为小写，当有大写字母的时候会转换为分隔符号“_”
  参考文献：hibernate不同版本的命名策略

如果MySQL还没有设置过时区，这时build项目可能会出现MySQL时区错误。

springboojava.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more spec

有两个解决方式：

1. 调整数据库时区，在mysql里输入SET GLOBAL time_zone = '+8:00'
2. 关闭mysql和时区相关的配置，在application.properties里修改配置成：

spring.datasource.url = jdbc:mysql://localhost:3306/mysql?useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai

在实际机器运行中时区信息还是重要的，推荐第一种方法
参考文献

3. 架构简述

structure of src/main/java
src/main/java/entity存放每一种存入数据库的Object的Model，Entity可以视为对象的单体格式
src/main/java/repository存放基于JPA接口优化的DML(Data Manipulation Languages)，Repository用来操作对应的表中的Entity
src/main/java/service存放更复杂的对数据库或者相应表的操作，如生成特殊ID，批量备份，存取和更新
src/main/java/controller以及其他Handlers通过调用Entity，Repository和Service里的方法对数据进行操作
下面的例子我们编写一个有Id, firstName, lastName, birthDate四个变量的命名为testu的User Table

4. 编写Entity Class

注：JPA Repository中find方法的命名是基于Entity的变量名而非表中变量名的，所以给变量命名时最好不要加“_”，不然可能会给JPA造成混乱，导致报错
一个Entity中可以套另一个Entity，复杂的业务逻辑里经常把一个Entity的Key封装成另一个Entity

package com.pde_staff.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import java.io.Serializable;
import java.util.Date;

@Entity
@Table(name = "testu")
public class User implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;

        @Id
        @GeneratedValue
        private long id;
    
        @Column(name = "first_name", nullable = false)
        private String firstName;
    
        @Column(name = "last_name", nullable = false)
        private String lastName;
    
        @Column(name = "birth_date", nullable = false)
        private Date birthDate;
    
    public User() {
        firstName = "";
        lastName = "";
        birthDate = new Date();
    }
    
    public User(String my_firstName, String my_lastName, Date my_birthday) {
        firstName = my_firstName;
        lastName = my_lastName;
        birthDate = my_birthday;
    }
    
    public long getId() {
        return id;
    }
    
    public void setId(long my_id) {
        id = my_id;
    }
    
    public String getFirstName() {
        return firstName;
    }
    
    public void setFirstName(String s) {
        firstName = s;
    }
    
    public String getLastName() {
        return lastName;
    }
    
    public void setLastName(String s) {
        lastName = s;
    }
    
    public Date getBirthDate() {
        return birthDate;
    }
    
    public void setBirthDate(Date d) {
        birthDate = d;
    }
}

5. 编写Repository Interface (Not class!)

JPA已经帮我们实现了findAll()和deleteById()等方法，还可以通过方法名称自动生成对应的SQL进行搜索，故我们只需要根据变量名编写简单的Retrieve和Delete方法就可以了

简单CRUD与JPA方法的对应关系

• Create ---> repo.save(entity) with a new Id
• Retrieve ---> repo.findAll() / repo.findByColumn(column)
• Update ---> repo.save(entity) with an existing Id
• Delete ---> repo.deleteById(Id) / repo.deleteByColumn(column)

package com.pde_staff.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import com.pde_staff.entity.User;

public interface UserRepository extends JpaRepository<User,Long> {

    User findById(long id);
    
    List<User> findByBirthDate(Date d);
    
    List<User> findByFirstNameAndLastName(String fn, String ln);
    
    List<User> findByFirstNameAndLastNameAndBirthDate(String fn, String ln, Date d);

    @Transactional
        // must use transactional tag for delete with variables
    void deleteByFirstNameAndLastName(String fn, String ln);
    
}

当想用一个Entity中包含的另一个Entity中的变量查询的时候，必须要写出那个variable的完整路径

// for example，User is included in another Entity class ClubMember
public class ClubMember {
    private User baseInfo;
    ............
}
// and we want to find a ClubMember by his lastName
public interface ClubMemberRepository extends JpaRepository<ClubMember,Long> {
    List<ClubMember> findByBaseInfoLastName(String lastName);
}

也可以自定义Query来查询，JPA默认的是HQL

@Query("select u from testu u where u.first_name = ?1")
List<User> findByFirstName(String name);

当然我们可以选择写更顺手的SQL

@Query("select * from testu u where u.first_name = ?1", nativeQuery = true)
 List<User> findByFirstName(String name);

JPA按类计数SQL宝藏Tips

@Query(value = "SELECT YEAR(birth_date) AS year, COUNT(id) as num FROM testu GROUP BY YEAR(birth_date)", nativeQuery = true)
List<Object[]> countByBirthYear();

自定义Query返回的自定义变量默认储存在List<Object[]>里，如果变量不复杂，可以直接拿出来使用

//比如这样直接压进ResponseBody
@ResponseBody
@RequestMapping(value="/countByBirthYear", method=RequestMethod.POST)
public Map<String, Integer> countByBirthYear(){
    List<Object[]> summary = userRepository.countByBirthYear();
    Map<String, Integer> res = new HashMap<>();
    for(int i = 0; i<summary.size(); i++) {
              Object[] ans = summary.get(i);
              String year = ans[0].toString();
              int count = Integer.parseInt(ans[1].toString());
              res.put(year, count);
    }
    return res;
}

如果复杂的话，还是应该封装进自定义的新回复实体里，参考JPA 自定义返回字段映射

6. 编写Unit Test Case

pom.xml注入test的依赖（可能要去掉旧的JUnit依赖）

<!-- Tester dependency -->
<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
</dependency>

右键src/test/java -> New -> Other -> JUnit -> JUnit Test Case
按照要求填写TestCase的名称，第一次创建的时候应该还要创建一个新的package

package com.pde_staff.test;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.pde_staff.entity.User;
import com.pde_staff.repository.UserRepository;

import junit.framework.TestCase;

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserTest extends TestCase {
    
    @Autowired
    private UserRepository userRepository;

    @Test
    public void saveTest() {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        User user1 = new User("Harry", "Potter", new Date());
        User user2 = new User("Ronald", "Weasley", new Date());
        userRepository.save(user1);
        userRepository.save(user2);
        String dateString = "1981-02-23";
        Date date = new Date();
        try {
            date= formatter.parse(dateString);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        User user3 = new User("Harry", "Potter", date);
        userRepository.save(user3);
        List<User> users = userRepository.findByFirstNameAndLastName("Harry", "Potter");
        for(User u : users) {
            System.out.println("========= Id: "+ u.getId() + ", firstName:" +u.getFirstName()+", lastName: "+u.getLastName()+", birthday: "+formatter.format(u.getBirthDate()));
        }
    }
}

右键写好的UserTest.java -> Run as -> JUnit Test
Console里可以看到Spring跑了起来......并出现错误

2020-05-11 10:48:18.113  INFO 10719 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.0.12.Final}
2020-05-11 10:48:18.114  INFO 10719 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2020-05-11 10:48:18.116  INFO 10719 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2020-05-11 10:48:18.172  INFO 10719 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Mon May 11 10:48:18 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2020-05-11 10:48:18.918 ERROR 10719 --- [           main] o.a.tomcat.jdbc.pool.ConnectionPool      : Unable to create initial connections of pool.

java.sql.SQLException: Access denied for user ''@'localhost' (using password: YES)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310) ~[tomcat-jdbc-8.5.11.jar:na]
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203) ~[tomcat-jdbc-8.5.11.jar:na]

通过错误信息可以看到我们连接上了数据库，但是存在权限验证失败的问题。按照逻辑把可能出问题的地方检查一下

1. IP和port (一般不会是这里出错，如果这里出错报的应该是连接超时的错误)

// 查看mysql port
mysql> show global variables like "port";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

2. 检查配置模版错误
   网上有一些模版是

spring.datasource.data-username=root
spring.datasource.data-password=123456

但其实应该是

spring.datasource.username=root
spring.datasource.password=123456

另外不要把spring.datasource.username和spring.datasource.name搞混了，用的是前者

3. 检查并重新输入一遍数据库信息（尤其是用户名和密码）
   有时候前后多一个空格就有可能导致错误（虽然亲测等号两边是可以有空格的）
   我就是删掉重新打了一遍就好了

spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/purchase_analytics
spring.datasource.username = root
spring.datasource.password = 123456
spring.jpa.hibernate.ddl-auto = update

如果使用.yml配置文件，记得要给纯数字的username或者password加上引号，不然会被默认为数字类型 yml配置参考

修复之后右键当前Project -> Maven -> Update Project，再重新运行JUnit Test，成功了

2020-05-11 15:37:58.573  INFO 11517 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2020-05-11 15:37:59.245  INFO 11517 --- [           main] com.pde_staff.test.UserTest              : Started UserTest in 5.726 seconds (JVM running for 6.613)
Hibernate: insert into testu (birth_date, first_name, last_name) values (?, ?, ?)
Hibernate: insert into testu (birth_date, first_name, last_name) values (?, ?, ?)
Hibernate: insert into testu (birth_date, first_name, last_name) values (?, ?, ?)
2020-05-11 15:37:59.428  INFO 11517 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select user0_.id as id1_0_, user0_.birth_date as birth_da2_0_, user0_.first_name as first_na3_0_, user0_.last_name as last_nam4_0_ from testu user0_ where user0_.first_name=? and user0_.last_name=?
========= Id: 10, firstName:Harry, lastName: Potter, birthday: 2020-05-11
========= Id: 12, firstName:Harry, lastName: Potter, birthday: 1981-02-23
2020-05-11 15:37:59.629  INFO 11517 --- [       Thread-4] o.s.w.c.s.GenericWebApplicationContext   : Closing org.springframework.web.context.support.GenericWebApplicationContext@2118cddf: startup date [Mon May 11 15:37:54 CST 2020]; root of context hierarchy
2020-05-11 15:37:59.633  INFO 11517 --- [       Thread-4] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'

进入mysql查看新建的表

mysql> describe testu;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| birth_date | datetime     | NO   |     | NULL    |                |
| first_name | varchar(255) | NO   |     | NULL    |                |
| last_name  | varchar(255) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM testu;
+----+---------------------+------------+-----------+
| id | birth_date          | first_name | last_name |
+----+---------------------+------------+-----------+
| 10 | 2020-05-11 02:37:59 | Harry      | Potter    |
| 11 | 2020-05-11 02:37:59 | Ronald     | Weasley   |
| 12 | 1981-02-22 10:00:00 | Harry      | Potter    |
+----+---------------------+------------+-----------+
3 rows in set (0.00 sec)

想知道数据库方法如何被外界调用，戳这里～SpringBoot+Swagger-UI构建API及其文档