Java 如何在应用程序启动时运行 SQL 脚本并获取数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39280340/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to run SQL scripts and get data on application startup?
提问by Laurynas
I am developing a Spring Boot application. At the moment some of my configs are hard coded (e.g. Hystrix properties).
我正在开发一个 Spring Boot 应用程序。目前我的一些配置是硬编码的（例如 Hystrix 属性）。
So I would like to get these configs on my application start up time or just after that.
所以我想在我的应用程序启动时间或之后获得这些配置。
Is it possible to do that using Spring Boot? I mean to run SQL script on start up and get data.
是否可以使用 Spring Boot 做到这一点？我的意思是在启动时运行 SQL 脚本并获取数据。
How should properties/configs be retrieved and stored in my application?
如何在我的应用程序中检索和存储属性/配置？
I am using MyBatis and Oracle DB.
我正在使用 MyBatis 和 Oracle DB。
采纳答案by alexbt
By default, Spring-Boot loads data.sqland/or data-${platform}.sql.
默认情况下，Spring-Boot 加载data.sql和/或data-${platform}.sql.
However, keep in mind that the script would be loaded at every start, so I would think it makes more sense (at least for production), to just have the values already present in the database, not re-inserted at every start. I've personally only used database initialization for test/dev purposes when using a memory database.Still, this is the feature provided by Spring-Boot.
但是，请记住，脚本将在每次启动时加载，因此我认为更有意义（至少对于生产而言），只将值已经存在于数据库中，而不是在每次启动时重新插入。在使用内存数据库时，我个人仅将数据库初始化用于测试/开发目的。尽管如此，这是 Spring-Boot 提供的功能。
source: spring-boot-howto-database-initialization:
来源：spring-boot-howto-database-initialization：
Spring JDBC has a DataSource initializer feature. Spring Boot enables it by default and loads SQL from the standard locations schema.sql and data.sql (in the root of the classpath). In addition Spring Boot will load the schema-${platform}.sql and data-${platform}.sql files (if present).
Spring JDBC 具有 DataSource 初始值设定项功能。Spring Boot 默认启用它并从标准位置 schema.sql 和 data.sql（在类路径的根目录中）加载 SQL。此外，Spring Boot 将加载 schema-${platform}.sql 和 data-${platform}.sql 文件（如果存在）。
src/main/resources/data-oracle.sql:
src/main/resources/ data-oracle.sql:
insert into...
insert into...
- You may define the platform with:
spring.datasource.platform=oracle. - You may change the name of the sql script to load with:
spring.datasource.data=myscript.sql. - Along with
data.sql, Spring-boot also loadsschema.sql(beforedata.sql). - You could also have an "update or insert" logic in your data.sql: oracle sql: update if exists else insert
- 您可以使用以下方式定义平台：
spring.datasource.platform=oracle。 - 您可以更改要加载的 sql 脚本的名称：
spring.datasource.data=myscript.sql. - 与 一起
data.sql，Spring-boot 也加载schema.sql（之前data.sql）。 - 您还可以在 data.sql 中有一个“更新或插入”逻辑：oracle sql: update if exists else insert
回答by Siddharth Sachdeva
If you want to insert data based on some business logic I would recommend you to have Event Listener. So basically on application startup "OnApplicationEvent" as it is annotated with @EventListener method will be called automatically.
如果您想根据某些业务逻辑插入数据，我建议您使用事件侦听器。所以基本上在应用程序启动时“OnApplicationEvent”，因为它用@EventListener 方法注释将被自动调用。
Also as in your case you need to get the data, you simply use your repository object to get the data as well.
同样，在您需要获取数据的情况下，您也只需使用存储库对象来获取数据。
Here's one example:
下面是一个例子：
@Component
public class OnApplicationStartUp {
@Autowired
private ServiceRepository repository;
@EventListener
public void onApplicationEvent(ContextRefreshedEvent event) {
//Write your business logic here.
if (repository.findAll().size() <= 0) {
preloadData();
}else{
fetchData();
}
}
private void preloadData() {
List<Service> services = new ArrayList<>();
Service someService= new Service("name", "type");
services.add(someService);
...
...
repository.saveAll(services);
}
}
回答by Cocuthemyth
If you getting from application.properties file you can use Environment class. Like that
如果您从 application.properties 文件获取，则可以使用 Environment 类。像那样
Autowired
private Environment environment;
...
environment.getProperty("propertyName")
or you can define your own property file. then you can get from it with
@PropertySource(name = "myProperties", value = "example.properties")annotation
或者您可以定义自己的属性文件。然后你可以用@PropertySource(name = "myProperties", value = "example.properties")注释从中获取
You need to use @Value annotation to get a specific value from the property file which you defined.
您需要使用 @Value 注释从您定义的属性文件中获取特定值。
@Value("${propertyNameInYourPropertFile}")
private String url;
And You want to start something when Application is just started, you can use this before a method
并且你想在 Application 刚启动时启动一些东西，你可以在方法之前使用它
@EventListener(ApplicationReadyEvent.class)
But need to use @Service or @Component Annotation, which Class has the method.
但是需要使用@Service或者@Component注解，哪个Class都有这个方法。
Totally, You can use this.
完全，你可以使用这个。
example.properties :
示例.属性：
url=yourValue
userName=yourDBUserName
password=yourDBPassword
example class :
示例类：
@Service
@PropertySource(name = "myProperties", value = "example.properties")
public class Start{
@Value("${url}")
private String url;
@Value("${userName}")
private String userName;
@Value("${password}")
private String password;
//Run this method when application started
@EventListener(ApplicationReadyEvent.class)
public ResultSet getConnection()
{
//Connect to Database
Connection connection = null;
String QUERY="your sql query";
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connection = DriverManager.getConnection(url, userName, password );
} catch (SQLException e) {
}
//Run your query
Statement stmt = null;
try {
stmt = connection.createStatement();
} catch (SQLException e1) {
e1.printStackTrace();
}
ResultSet rs = null;
try {
rs = stmt.executeQuery(QUERY);
} catch (SQLException e1) {
e1.printStackTrace();
}
return rs;
}
}
回答by Emerica
What worked for me is using DataSourceInitializer:
对我有用的是使用DataSourceInitializer：
@Bean
public DataSourceInitializer dataSourceInitializer(@Qualifier("dataSource") final DataSource dataSource) {
ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
resourceDatabasePopulator.addScript(new ClassPathResource("/data.sql"));
DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
dataSourceInitializer.setDataSource(dataSource);
dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
return dataSourceInitializer;
}
Used to set up a database during initialization and clean up a database during destruction.
用于在初始化期间建立数据库并在销毁期间清理数据库。
回答by Philip Dilip
Define a Spring Bean (e.g. GlobalConfigurationBean)
with Scope: @Scope(scopeName = WebApplicationContext.SCOPE_APPLICATION)
This Bean would be responsible for fetching the data from your Database Table (maintaining Configuration properties) during Bean initialization.Annotate a method using @PostConstruct.
This method would have the logic to fetch the Configuration parameters from your database table.The scope of this bean would ensure that required Configurations are fetched only once from the database table (e.g. using Hibernate query or pure native SQL) and
are available to all beans from different contexts within same application.
定义一个
带有范围的 Spring Bean（例如 GlobalConfigurationBean）：@Scope(scopeName = WebApplicationContext.SCOPE_APPLICATION)
这个 Bean 将负责在 Bean 初始化期间从你的数据库表中获取数据（维护配置属性）。使用@PostConstruct 注释方法。
此方法具有从数据库表中获取配置参数的逻辑。这个 bean 的范围将确保所需的配置仅从数据库表中获取一次（例如使用 Hibernate 查询或纯本地 SQL），
并且可用于来自同一应用程序中不同上下文的所有 bean。
Now just inject this bean wherever you wish to use those Configuration Properties or parameters.
OR
Use:: Apache Commons DatabaseConfiguration <--
NOTE:: It doesn't support caching. But I guess you don't need caching as the database configuration properties should be loaded only once, at the start of application launch.
OR
Trandational Old way: Define a Custom Implementation of "PropertyPlaceHolderConfigurer" by extending it, and define it as a Spring Bean.
This implementaion should have logic to fetch the data from your database table, that holds configuration properties.
现在只需在您希望使用这些配置属性或参数的任何地方注入这个 bean。
或
使用:: Apache Commons DatabaseConfiguration <--
注意:: 它不支持缓存。但我猜你不需要缓存，因为数据库配置属性应该只在应用程序启动时加载一次。
OR
Trandational Old way：通过扩展定义“PropertyPlaceHolderConfigurer”的自定义实现，并将其定义为Spring Bean。
此实现应该具有从包含配置属性的数据库表中获取数据的逻辑。
