解决SpringBoot application.yaml文件配置schema 无法执行sql问题

在 SpringBoot 中我们通常会使用 application.yaml 或 application.properties 作为项目的配置文件，其中可以配置数据源信息、SQL 语句等信息。但是在实际使用中，有些同学会遇到一些问题，比如 application.yaml 中配置 SQL 语句无法执行的问题。下面我将分享一下解决这个问题的攻略：

问题原因

在 application.yaml 中配置的 SQL 语句没有指定对应的 schema，因此无法执行。

解决方案

方案一：使用完整的 SQL 语句

在 YAML 中使用完整的 SQL 语句来解决这个问题，即在 SQL 语句中指定对应的 schema。

例如，我们在 application.yml 中配置以下 SQL 语句：

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC
    username: root
    password: 123456
    schema: test
  sql:
    init:
      mode: always
      schema-locations: classpath*:sql/*.sql

我们可以将 SQL 语句修改为以下形式：

SELECT * FROM test.user;

这样就指定了执行该 SQL 语句的 schema 为 test，我们就可以正常执行 SQL 语句了。

方案二：在 application.yaml 中指定默认 schema

如果你不想在 SQL 语句中指定 schema，可以在 application.yaml 中指定默认 schema。

例如，在上面的例子中，我们可以在 application.yaml 中添加以下的配置：

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC
    username: root
    password: 123456
    schema: test

这样，Spring Boot 就会默认使用 test 作为该数据源的 schema。

示例说明

示例一

有一个数据库名为 db_test，包含两个表：t_user 和 t_order，我们需要执行 t_user 表的查询操作。我们在 application.yaml 中配置了以下数据源信息：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_test
    username: root
    password: 123456

我们在 service 层使用 JdbcTemplate 执行 SQL 语句，代码如下：

@Autowired
private JdbcTemplate jdbcTemplate;

public void getUserList() {
    List<Map<String, Object>> userList = jdbcTemplate.queryForList("SELECT * FROM t_user");
    userList.forEach(System.out::println);
}

执行 getUserList 方法时，会抛出以下异常：

Caused by: java.sql.SQLSyntaxErrorException: Table 'db_test.t_user' doesn't exist

这是因为在 SQL 语句中没有指定对应的 schema，导致无法找到 t_user 表。要解决这个问题，我们可以在 SQL 语句中指定 schema，如下所示：

List<Map<String, Object>> userList = jdbcTemplate.queryForList("SELECT * FROM db_test.t_user");

或者也可以在 application.yaml 中指定默认 schema，如下所示：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_test
    username: root
    password: 123456
    # 指定默认 schema
    schema: db_test

这样就可以正常执行 SQL 语句了。

示例二

假设我们需要在启动时执行一个 SQL 文件，创建用户表并插入数据。我们在 application.yaml 中添加以下配置：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_test
    username: root
    password: 123456
    # 指定默认 schema
    schema: db_test
  sql:
    init:
      mode: always
      schema-locations: classpath*:sql/*.sql

在 resources 目录下新建一个 sql 目录，新建一个 user.sql 文件，并在其中编写创建用户表和插入数据的 SQL 语句，如下所示：

DROP TABLE IF EXISTS `User`;
CREATE TABLE `User`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `User` VALUES (1, 'Lucy');

代码中，我们使用 schema-locations 指定了 SQL 文件的路径，其中 classpath*: 表示在 classpath 下查找文件，如果有多个文件，用逗号分隔。

启动项目后，会自动执行指定的 SQL 文件，并创建用户表并插入数据。在 controller 中查询该用户的代码如下：

@Autowired
private JdbcTemplate jdbcTemplate;

@RequestMapping("/getUser")
public String getUser() {
    List<Map<String, Object>> userList = jdbcTemplate.queryForList("SELECT * FROM User");
    return userList.toString();
}

其中，我们在 SQL 语句中没有指定 schema，因为在 application.yaml 中已经指定了默认 schema。