Spring Boot+JDBC执行多SQL语句报错及循环Bean问题排查

我正在开发一个基于MySQL的Spring Boot应用，用JDBC操作数据。遇到两个问题：

• 当create_table.sql里有多条SQL语句时，会触发SQL语法错误，还引发循环Bean错误
• 只放单条语句时没问题，但如果数据库里没有对应表，单条语句也会触发Bean循环错误

@PostConstruct
public void createTables() {
    try (InputStream script = new ClassPathResource("create_table.sql").getInputStream()) {
        jdbcTemplate.execute(new String(script.readAllBytes()));
    } catch (IOException e) {
        throw new RuntimeException("Failed to execute create_table.sql script", e);
    }
}

Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE IF NOT EXISTS counter_data (
    id bigint NOT NULL AUTO_INCREMENT,
    timeStamp varchar(150) NOT NULL,
    counter_1 int,
    counter_2 int,
    counter_3 int,
    counter_4 int,
    counter_5 int,
    counter_6 int,
    counter_7 int,
    counter_8 int,
    PRIMARY KEY (id)
);
--
-- CREATE TABLE IF NOT EXISTS molding_data (
--     id bigint NOT NULL AUTO_INCREMENT,
--     timeStamp varchar(150) NOT NULL,
--     counter_1 int,
--     counter_2 int,
--     counter_3 int,
--     counter_4 int,
--     counter_5 int,
--     counter_6 int,
--     counter_7 int,
--     counter_8 int,
--     PRIMARY KEY (id)
--     );

CREATE TABLE IF NOT EXISTS production_data (
     id bigint NOT NULL AUTO_INCREMENT,
     timeStamp varchar(150) NOT NULL,
     total_production int,
     PRIMARY KEY (id)
     );
-- CREATE TABLE IF NOT EXISTS shift_data (
--     id bigint NOT NULL AUTO_INCREMENT,
--     timeStamp varchar(150) NOT NULL,
--     shift_production int,
--     PRIMARY KEY (id)
--     );]
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:103) ~[spring-jdbc-6.1.2.jar:6.1.2]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.1.2.jar:6.1.2]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548) ~[spring-jdbc-6.1.2.jar:6.1.2]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408) ~[spring-jdbc-6.1.2.jar:6.1.2]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:444) ~[spring-jdbc-6.1.2.jar:6.1.2]
    at io.xdoto.dao.impl.DataDaoImpl.createTables(DataDaoImpl.java:34) ~[classes/:na]
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:578) ~[na:na]
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMethod.invoke(InitDestroyAnnotationBeanPostProcessor.java:457) ~[spring-beans-6.1.2.jar:6.1.2]
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:401) ~[spring-beans-6.1.2.jar:6.1.2]
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:219) ~[spring-beans-6.1.2.jar:6.1.2]
    ... 46 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS production_data (
     id bigint NOT NULL AUTO_INCRE' at line 29
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) ~[mysql-connector-j-8.1.0.jar:8.1.0]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.1.0.jar:8.1.0]
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763) ~[mysql-connector-j-8.1.0.jar:8.1.0]
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[mysql-connector-j-8.1.0.jar:8.1.0]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-5.0.1.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:435) ~[spring-jdbc-6.1.2.jar:6.1.2]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393) ~[spring-jdbc-6.1.2.jar:6.1.2]
    ... 53 common frames omitted

1. 解决多SQL语句执行问题 #

JdbcTemplate.execute(String)默认不支持执行多条SQL语句，直接拼接执行会触发语法错误。改用Spring提供的ResourceDatabasePopulator或ScriptUtils工具类，它们能自动解析脚本中的分号分隔符、忽略注释，正确执行每条语句：

修改后的DAO方法示例：

@PostConstruct
public void createTables() {
    try {
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScript(new ClassPathResource("create_table.sql"));
        populator.execute(jdbcTemplate.getDataSource());
    } catch (Exception e) {
        throw new RuntimeException("Failed to execute create_table.sql script", e);
    }
}

2. 解决Bean循环依赖问题 #

@PostConstruct阶段Spring容器未完成所有Bean初始化，此时执行数据库操作容易引发循环依赖。建议把表初始化逻辑移到容器完全启动后执行，比如使用ApplicationRunner：

@Component
public class TableInitializer implements ApplicationRunner {

    private final JdbcTemplate jdbcTemplate;

    public TableInitializer(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void run(ApplicationArguments args) throws Exception {
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScript(new ClassPathResource("create_table.sql"));
        populator.execute(jdbcTemplate.getDataSource());
    }
}

3. 额外建议 #

• 确保create_table.sql中每条语句结尾的分号不遗漏
• 开启MySQL日志，方便排查SQL执行细节
• 生产环境推荐用Flyway或Liquibase这类数据库迁移工具管理表结构，比手动执行脚本更可靠

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