Spring Boot连接PostgreSQL后无法获取表数据求助

我是Spring Boot新手，正在开发一个操作PostgreSQL数据库（库名recipes，模式public，表recipe）的增删查程序。目前遇到问题：数据库已初始化数据，但Postman发送GET请求返回null。排查发现service层的jdbcTemplate.query(sql, new RecipeRowMapper())无数据返回。已确认数据库非空（执行SELECT * from recipe能查到数据），应用已连接数据库，但DB浏览器中看不到recipe表。

初始化数据库SQL #

INSERT INTO recipe(id, name, ingredients, instructions, date_added)
values (1, 'ini test1', '10 cows 20 rabbits', 'cook ingredients with salt', '2004-01-02'),
       (2, 'ini test2', '30 apples 20 pears', 'peel then boil', '2004-01-13');

application.yml配置 #

app:
  datasource:
    main:
      driver-class-name: org.postgresql.Driver
      jdbc-url: jdbc:postgresql://localhost:5432/recipes?currentSchema=public
      username: postgres
      password: password

server:
  error:
    include-binding-errors: always
    include-message: always

spring.jpa:
  database: POSTGRESQL
  hibernate.ddl-auto: create
  show-sql: true
  dialect: org.hibernate.dialect.PostgreSQL9Dialect
  format_sql: true

spring.flyway:
  baseline-on-migrate: true

Service层代码 #

public List<Recipe> getRecipes(){
    var sql = """
        SELECT id, name, ingredients, instructions, date_added
        FROM public.recipe
        LIMIT 50
        """;
    return jdbcTemplate.query(sql, new RecipeRowMapper());
}

Controller层代码 #

@GetMapping(path = "/test")
public String testRecipe(){
    return recipeService.test();
}

RowMapper实现 #

public class RecipeRowMapper implements RowMapper<Recipe> {
    @Override
    public Recipe mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Recipe(
                rs.getLong("id"),
                rs.getString("name"),
                rs.getString("ingredients"),
                rs.getString("instructions"),
                LocalDate.parse(rs.getString("date_added"))
        );
    }
}

Recipe实体类 #

@Data
@Entity
@Table
public class Recipe {
    @Id
    @GeneratedValue(
            strategy = GenerationType.IDENTITY
    )
    @Column(name = "id", updatable = false, nullable = false)
    private long id;
    @Column(name = "name")
    private String name;
    @Column(name = "ingredients")
    private String ingredients;
    @Column(name = "instructions")
    private String instructions;
    @Column(name = "date_added")
    private LocalDate dateAdded;

    public Recipe(){};
    public Recipe(long id, String name, String ingredients, String instructions, LocalDate date){}

    public Recipe(String name,
                  String ingredients,
                  String instructions,
                  LocalDate dateAdded
                  ) {
        this.name = name;
        this.ingredients = ingredients;
        this.instructions = instructions;
        this.dateAdded = dateAdded;
    }
}

问题原因及解决办法 #

1. Hibernate自动建表清空数据

   • hibernate.ddl-auto: create会在应用启动时删除原有表并重建，直接清空手动插入的数据，甚至可能和Flyway执行顺序冲突导致表不可见。
   • 解决：将hibernate.ddl-auto改为none（不需要Hibernate自动建表）、validate（仅验证表结构）或update（保留现有数据更新表结构）。

2. 实体类构造函数未赋值

   • RecipeRowMapper中调用的5参数构造函数内部未给成员变量赋值，导致返回的Recipe对象属性全为默认值（id为0、字符串为null）。
   • 解决：修改构造函数：

     public Recipe(long id, String name, String ingredients, String instructions, LocalDate dateAdded){
         this.id = id;
         this.name = name;
         this.ingredients = ingredients;
         this.instructions = instructions;
         this.dateAdded = dateAdded;
     }
     

3. 数据源配置前缀错误

   • 自定义的app.datasource.main前缀不会被Spring Boot默认识别，可能导致应用连接到错误数据源（比如默认H2库），因此看不到recipe表。
   • 解决：改为Spring Boot默认前缀：

     spring:
       datasource:
         driver-class-name: org.postgresql.Driver
         url: jdbc:postgresql://localhost:5432/recipes?currentSchema=public
         username: postgres
         password: password
     

4. Controller未调用正确查询方法

   • 当前Controller调用的recipeService.test()未在提供的Service代码中出现，无法返回查询结果。
   • 解决：修改Controller方法：

     @GetMapping(path = "/test")
     public List<Recipe> testRecipe(){
         return recipeService.getRecipes();
     }
     

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