SpringBoot应用报错'microservices.micro_users'表不存在，无法访问数据库

我创建了一个基础SpringBoot应用，尝试向SQL数据库插入数据，代码如下：

@SpringBootApplication
public class UserServiceApplication implements CommandLineRunner {

   @Autowired
   JdbcTemplate jdbcTemplate;

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

   @Override
   public void run(String... args) throws Exception {
      String sql = "INSERT INTO micro_users (fullname, email, password) values (?, ? ,?)";
      int result = jdbcTemplate.update(sql, "MyName", "hell@gmail.com",  "2021");
      System.out.println("result " + result);
   }
}

我的application.properties配置如下：

spring.datasource.url=jdbc:mysql://localhost:3306/microservices
spring.datasource.username=root
spring.datasource.password=root

application.yml配置如下：

server:
  port: 8081

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/microservices
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
  application:
    name: service
  main:
      allow-bean-definition-overriding: true
      lazy-initialization: true


  jpa:
    hibernate:
      ddl-auto:update
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect

我已创建本地数据库，连接信息如下：

Name: Local instance
Host: localhost
Port: 3306
Login user: root
Current user: root@localhost
Version: 8.0.33

运行应用时收到如下错误：

T10:37:09.815-05:00  INFO 12812 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2023-07-05T10:37:10.102-05:00  INFO 12812 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8081 (http) with context path ''
2023-07-05T10:37:10.115-05:00  WARN 12812 --- [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2023-07-05T10:37:10.130-05:00  INFO 12812 --- [           main] c.b.user.service.UserServiceApplication  : Started UserServiceApplication in 3.897 seconds (process running for 4.33)
2023-07-05T10:37:10.182-05:00  INFO 12812 --- [           main] .s.b.a.l.ConditionEvaluationReportLogger : 
Caused by: java.sql.SQLSyntaxErrorException: Table 'microservices.micro_users' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) ~[mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) ~[mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061) ~[mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009) ~[mysql-connector-j-8.0.33.jar:8.0.33]

请问我哪里配置或操作有误，导致无法访问数据库并出现表不存在的错误？

核心原因 #

错误本质是数据库中不存在micro_users表，而你的Hibernate自动建表功能未生效，直接用JdbcTemplate执行插入语句自然找不到表。具体失效原因有两个：

1. YAML配置语法错误：ddl-auto:update中间缺少空格，YAML对格式敏感，无空格会导致Spring无法识别该配置项，Hibernate默认使用none策略（不做任何表操作）。
2. 缺少JPA实体类：Hibernate的ddl-auto功能需要基于实体类生成表结构，你未创建micro_users对应的实体类，Hibernate不知道要生成什么表。

解决步骤 #

步骤1：修复YAML配置语法 #

修改application.yml中JPA配置，给ddl-auto和update之间添加空格：

jpa:
  hibernate:
    ddl-auto: update  # 补充空格
  show-sql: true
  properties:
    hibernate:
      dialect: org.hibernate.dialect.MySQL8Dialect

步骤2：创建对应JPA实体类 #

在项目中创建User实体类，映射到micro_users表：

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "micro_users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String fullname;
    private String email;
    private String password;

    // JPA要求的无参构造函数
    public User() {}

    // 带参构造函数、getter和setter
    public User(String fullname, String email, String password) {
        this.fullname = fullname;
        this.email = email;
        this.password = password;
    }

    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getFullname() { return fullname; }
    public void setFullname(String fullname) { this.fullname = fullname; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }
}

步骤3：优化配置（可选） #

你同时存在application.properties和application.yml两个配置文件，SpringBoot会优先加载application.yml，为避免混淆，建议保留一个配置文件即可。

步骤4：重新运行应用 #

修复后启动应用，Hibernate会自动在microservices数据库中创建micro_users表，JdbcTemplate的插入语句即可正常执行。

替代方案（手动建表） #

如果不想使用JPA实体类，也可以手动在MySQL中创建表，执行以下SQL语句：

CREATE TABLE micro_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

手动建表后，即使不修复Hibernate配置，插入语句也能正常运行，但推荐使用JPA自动建表保持代码与数据库结构一致。

内容的提问来源于stack exchange，提问作者Tanu