H2数据库FILE_IMPORT表未创建问题求助

问题背景 #

开发项目需同时连接H2数据库和S3用于文件上传，S3连接正常，PostgreSQL对应的Schema创建无问题，但H2数据库的FILE_IMPORT表始终无法创建，导致定时任务检查PENDING状态待上传文件时抛出表不存在错误。启动方式为IntelliJ中选择local profile启动。

报错信息 #

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Tabela "FILE_IMPORT" does not exist.
Table "FILE_IMPORT" not found; SQL statement:
SELECT id FROM file_import WHERE status = ? ORDER BY created_at ASC LIMIT 1 [42102-214]

相关配置与代码 #

application-local.yaml #

spring:
  datasource:
    url: jdbc:h2:mem:test
    username: sa
    password:
    driverClassName: org.h2.Driver
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    hibernate:
      ddl-auto: update  # or your preferred value
    open-in-view: false
  h2:
    console:
      enabled: true  # This line enables the H2 console for development
  servlet:
    multipart:
      max-file-size: 5000MB
      max-request-size: 5000MB
  upload:
    dir: "src/main/resources/files-to-import"
  integration:
    jdbc:
      initialize-schema: always
server:
  port: 8080
file-storage:
  solution: local

logging:
  level:
    org:
      hibernate:
        SQL: DEBUG

schema-h2.sql #

CREATE TABLE IF NOT EXISTS public.file_import (
                                                  id BIGINT AUTO_INCREMENT PRIMARY KEY,
                                                  created_at TIMESTAMP(6),
    file_path VARCHAR(255),
    finished_at TIMESTAMP(6),
    last_processed_row BIGINT,
    started_at TIMESTAMP(6),
    status VARCHAR(255),
    CONSTRAINT file_import_status_check CHECK (status IN ('PENDING', 'SUCCESS', 'FAILED'))
    );

LocalFileStorage类 #

package com.example.personmanagement.file;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;


@Component
@ConditionalOnProperty(prefix = "file-storage", name = "solution", havingValue = "local")
public class LocalFileStorage implements FileStorage {

    @Value("${spring.upload.dir}")
    private String UPLOAD_DIR;

    @Override
    public String save(InputStream inputsStream, String originalFilename, long byteSize) throws IOException {
        String uniqueFilename = System.currentTimeMillis() + "_" + originalFilename;
        Path filePath = Path.of(UPLOAD_DIR, uniqueFilename);
        Files.copy(inputsStream, filePath, StandardCopyOption.REPLACE_EXISTING);
        return uniqueFilename;
    }

    @Override
    public BufferedReader load(String fileName) throws FileNotFoundException {
        Path filePath = Path.of(UPLOAD_DIR, fileName);
        return new BufferedReader(new InputStreamReader(new FileInputStream(String.valueOf(filePath))));
    }

}

FileImportRepository类（定时任务调用） #

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
@Slf4j
public class FileImportRepository {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public FileImportRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void insert(FileImport fileImport) throws DataAccessException {
        String sql = "INSERT INTO file_import (file_path, last_processed_row, status, created_at) VALUES (?, ?, ?, ?)";
        jdbcTemplate.update(sql, fileImport.getFilePath(), fileImport.getLastProcessedRow(),
                fileImport.getStatus().toString(), fileImport.getCreatedAt());
    }

    public void update(FileImport fileImport) throws DataAccessException {
        String sql = """
                UPDATE file_import
                SET
                    file_path = ?,
                    last_processed_row = ?,
                    status = ?,
                    finished_at = ?,
                    started_at = ?
                WHERE id = ?
                """;
        jdbcTemplate.update(sql, fileImport.getFilePath(), fileImport.getLastProcessedRow(),
                fileImport.getStatus().toString(), fileImport.getFinishedAt(), fileImport.getStartedAt(), fileImport.getId());
    }

    public Optional<Long> findFirstByStatusOrderByCreatedAtAsc() throws EmptyResultDataAccessException {
        String sql = "SELECT id FROM file_import WHERE status = ? ORDER BY created_at ASC LIMIT 1";
        Long fileId = jdbcTemplate.queryForObject(sql, new Object[]{FileStatus.PENDING.toString()}, Long.class);
        return Optional.ofNullable(fileId);
    }

    public Optional<FileImport> findById(Long id) throws DataAccessException {
        String sql = "SELECT * FROM file_import WHERE id = ?";
        FileImport fileImport = jdbcTemplate.queryForObject(sql, new Object[]{id}, new FileImportRowMapper());
        return Optional.ofNullable(fileImport);
    }

    public void deleteAll() {
        String sql = "TRUNCATE TABLE file_import";
        jdbcTemplate.update(sql);
    }
}

已尝试的操作 #

• 修改Schema语句
• 切换配置文件
• 无法连接H2控制台（登录由Spring Boot管控）

解决方案建议 #

1. 检查SQL脚本加载规范 #

Spring Boot默认加载schema.sql或schema-${platform}.sql，确保schema-h2.sql放在src/main/resources根目录下，且Spring能正确识别当前数据源为H2。

2. 调整H2连接URL指定默认Schema #

修改application-local.yaml中的数据源URL，添加Schema参数：

spring:
  datasource:
    url: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;DEFAULT_SCHEMA=public

确保脚本创建的public.file_import与Repository中使用的表名处于同一Schema。

3. 强制指定SQL脚本位置 #

在application-local.yaml中添加配置，明确指定Schema脚本路径：

spring:
  sql:
    init:
      schema-locations: classpath:schema-h2.sql
      mode: always

注：Spring Boot 2.5+版本使用spring.sql.init替代旧版spring.datasource.schema配置。

4. 解决DDL配置冲突 #

当前同时启用了Hibernate的ddl-auto: update和Spring Integration的initialize-schema: always，可能存在冲突：

• 暂时将ddl-auto改为none，优先依赖schema-h2.sql创建表
• 或确保FileImport实体类注解与Schema脚本定义一致，让Hibernate自动生成表

5. 修复H2控制台连接问题 #

添加H2控制台路径配置：

spring:
  h2:
    console:
      path: /h2-console

启动应用后访问http://localhost:8080/h2-console，使用配置中的URL（jdbc:h2:mem:test）、用户名sa、空密码登录，查看表是否存在。

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