在Spring Boot JPA中优雅地管理数据库视图与初始化数据

public abstract class AbstractDataLoader {

    // 抽象方法，用于加载环境特定数据或创建视图
    public abstract void loadEnvironmentSpecificData();

    // 可以在这里定义一些通用的辅助方法
    protected void executeSql(String sql, JdbcTemplate jdbcTemplate) {
        try {
            jdbcTemplate.execute(sql);
            System.out.println("Executed SQL: " + sql.substring(0, Math.min(sql.length(), 100)) + "...");
        } catch (Exception e) {
            System.err.println("Failed to execute SQL: " + sql.substring(0, Math.min(sql.length(), 100)) + " - " + e.getMessage());
            // 根据需要处理异常，例如只在视图不存在时创建
        }
    }
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct; // 或者实现 ApplicationRunner/CommandLineRunner

@Profile("dev") // 只在开发环境下激活
@Component
public class DevelopmentDataLoader extends AbstractDataLoader {

    private final SomeEntityRepository someEntityRepository; // 假设有一个JPA Repository
    private final JdbcTemplate jdbcTemplate; // 用于执行原生SQL

    @Autowired
    public DevelopmentDataLoader(SomeEntityRepository someEntityRepository, JdbcTemplate jdbcTemplate) {
        this.someEntityRepository = someEntityRepository;
        this.jdbcTemplate = jdbcTemplate;
    }

    @PostConstruct // 确保在依赖注入完成后执行
    @Override
    public void loadEnvironmentSpecificData() {
        System.out.println("Loading development-specific data and creating views...");

        // 示例：创建数据库视图
        String createViewSql = "CREATE OR REPLACE VIEW my_custom_view AS SELECT id, name FROM some_entity WHERE status = 'ACTIVE'";
        executeSql(createViewSql, jdbcTemplate);

        // 示例：填充初始数据
        if (someEntityRepository.count() == 0) {
            // someEntityRepository.save(new SomeEntity("Initial Dev Data"));
            System.out.println("Initial development data loaded.");
        }
    }
}

@Profile("prod") // 只在生产环境下激活
@Component
public class ProductionDataLoader extends AbstractDataLoader {

    private final JdbcTemplate jdbcTemplate;

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

    @PostConstruct
    @Override
    public void loadEnvironmentSpecificData() {
        System.out.println("Loading production-specific data and creating views...");

        // 生产环境可能只需要创建视图，而不需要填充大量测试数据
        String createProdViewSql = "CREATE OR REPLACE VIEW production_summary_view AS SELECT count(*) as total FROM another_entity";
        executeSql(createProdViewSql, jdbcTemplate);
    }
}

spring.profiles.active=dev