Spring Boot创建runController Bean报错：UnsatisfiedDependencyException求助

项目启动时触发UnsatisfiedDependencyException，无法创建runController Bean。错误根源是数据库脚本执行失败，导致dataSourceScriptDatabaseInitializer Bean创建失败，进而引发runRepository和runController的依赖注入异常。

报错日志 #

2024-12-01T18:10:05.806+05:30  WARN 21088 --- [demo] [  restartedMain] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'runController' defined in file [C:\Users\91940\Desktop\demo\build\classes\java\main\com\example\demo\run\RunController.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'runRepository' defined in file [C:\Users\91940\Desktop\demo\build\classes\java\main\com\example\demo\run\RunRepository.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Failed to execute database script

相关代码 #

RunController.java #

// RunController
package com.example.demo.run;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import jakarta.validation.Valid;

import java.util.List;
import java.util.Optional;

@RestController
@RequestMapping("/api/runs")
public class RunController {
    private final RunRepository runRepository;

    @Autowired
    public RunController(RunRepository runRepository) {
        this.runRepository = runRepository;
    }

    @GetMapping("")
    List<Run> findAll() {
        return runRepository.findAll();
    }

    @GetMapping("/{id}")
    Run findById(@PathVariable("id") Integer id) {
        Optional<Run> run = runRepository.findById(id);
        if (run.isEmpty()) {
            throw new RunNotFoundException();
        }
        return run.get();
    }

    // post
    @ResponseStatus(HttpStatus.CREATED)
    @PostMapping("")
    void create(@Valid @RequestBody Run run) {
        runRepository.create(run);
    }

    // put
    @ResponseStatus(HttpStatus.NO_CONTENT)
    @PutMapping("/{id}")
    void update(@Valid @RequestBody Run run, @PathVariable("id") Integer id) {
        runRepository.update(run, id);
    }

    // delete
    @ResponseStatus(HttpStatus.NO_CONTENT)
    @DeleteMapping("/{id}")
    void delete(@PathVariable("id") Integer id) {
        runRepository.delete(id);
    }
}

RunRepository.java #

//RunRepository
package com.example.demo.run;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import java.util.List;
import java.util.Optional;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


@Repository
@Configuration
public class RunRepository {
    private static final Logger logger = LoggerFactory.getLogger(RunRepository.class.getName());
    private final JdbcClient jdbcClient;

    @Autowired
    public RunRepository(JdbcClient jdbcClient) {
        this.jdbcClient = jdbcClient;
    }

    public List<Run> findAll() {
        String query = "SELECT * FROM RUN";
        return jdbcClient.sql(query)
                .query(Run.class)
                .list();
    }

    public Optional<Run> findById(Integer id) {
        String query = "SELECT * FROM RUN WHERE id= ?";
        return jdbcClient.sql(query)
                .param(id)
                .query(Run.class)
                .optional();
    }

    public void create(Run run) {
        String query = "INSERT INTO RUN(id,title,started_on,completed_on,miles,location) VALUES(:v)";
        var update = jdbcClient.sql(query)
                .param("v", List.of(run.id(), run.title(), run.startedOn(), run.completedOn(), run.miles(),
                        run.location().toString()))
                .update();

        Assert.state(update == 1, "Failed to create activity: " + run.title());
    }

    public void update(Run run, Integer id) {
        String query = "UPDATE RUN SET title=?, started_on=?, completed_on=?, miles=?, location=? WHERE id=?";
        int update = 0;
        try {
            update = jdbcClient.sql(query)
                    .params(List.of(run.title(), run.startedOn(), run.completedOn(), run.miles(), run.location().toString(),
                            id))
                    .update();

        } catch (Exception e) {
            logger.error(e.getMessage());
        }

        Assert.state(update == 1, "Failed to update activity: " + run.title());
    }

    public void delete(Integer id) {
        String query = "DELETE FROM RUN WHERE id=?";
        var update = jdbcClient.sql(query)
                .param(id)
                .update();

        Assert.state(update == 1, "Failed to delete activity: " + id);
    }

    public int count() {
        String query = "SELECT * FROM RUN";
        return jdbcClient.sql(query)
                .query(Run.class)
                .list().size();
    }

    public void saveAll(List<Run> runs) {
        runs.forEach(this::create);
    }

    public List<Run> findByLocation(String location) {
        String query = "SELECT * FROM RUN WHERE location=?";
        return jdbcClient.sql(query)
                .param(location)
                .query(Run.class)
                .list();
    }
}

错误原因分析 #

1. 数据库脚本执行失败：Spring Boot自动初始化数据源时，执行schema.sql或data.sql出错，这是根因。常见诱因包括：
   • SQL脚本语法错误（表名/字段名拼写错误、语句格式非法）
   • 数据库连接配置错误（URL、用户名、密码无效，数据库服务未启动）
   • 脚本文件位置不符合Spring Boot默认规则（未放在src/main/resources下）
2. 依赖传递异常：runRepository依赖JdbcClient，而JdbcClient的创建依赖可用数据源，数据源初始化失败导致后续Bean无法创建，最终影响runController的注入。
3. RunRepository注解冲突：同时使用@Repository和@Configuration是错误的，@Configuration用于定义配置类，会干扰Repository的实例化逻辑。
4. SQL参数绑定错误：create方法中使用:v绑定List参数的写法不符合JdbcClient规范，会导致SQL执行失败。

解决方案 #

1. 修复数据库初始化脚本 #

• 检查src/main/resources下的schema.sql和data.sql，确保语法正确。例如RUN表的创建语句：

  CREATE TABLE IF NOT EXISTS RUN (
      id INT PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      started_on TIMESTAMP NOT NULL,
      completed_on TIMESTAMP NOT NULL,
      miles DOUBLE NOT NULL,
      location VARCHAR(50) NOT NULL
  );
  

• 验证application.properties/application.yml中的数据库配置：

  spring.datasource.url=jdbc:h2:mem:testdb
  spring.datasource.driverClassName=org.h2.Driver
  spring.datasource.username=sa
  spring.datasource.password=
  spring.sql.init.mode=always
  

2. 修正RunRepository注解 #

移除@Configuration，仅保留@Repository：

@Repository
// 移除@Configuration
public class RunRepository {
    // 原有代码不变
}

3. 修复SQL参数绑定逻辑 #

修改create方法的参数绑定方式，使用逐个参数匹配：

public void create(Run run) {
    String query = "INSERT INTO RUN(id,title,started_on,completed_on,miles,location) VALUES(?, ?, ?, ?, ?, ?)";
    var update = jdbcClient.sql(query)
            .params(run.id(), run.title(), run.startedOn(), run.completedOn(), run.miles(), run.location().toString())
            .update();

    Assert.state(update == 1, "Failed to create activity: " + run.title());
}

4. 查看详细错误日志定位问题 #

在application.properties中开启调试日志，获取具体SQL执行错误信息：

logging.level.org.springframework.jdbc=DEBUG
logging.level.org.springframework.sql.init=DEBUG

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