在spring boot中执行一个SQL脚本去MySQL数据库插入数据
时间: 2023-11-12 19:09:07 浏览: 215
spring-jdbc-h2-schema:此存储库演示了在MySQL模式下使用基于文件系统的h2数据库进行的Spring-boot JDBC
在Spring Boot中执行SQL脚本,可以使用Spring JDBC提供的JdbcTemplate来实现。以下是一个示例:
1. 创建一个SQL脚本,例如"insert_data.sql",并编写插入数据的SQL语句,例如:
```
INSERT INTO my_table (column1, column2, column3) VALUES ('value1', 'value2', 'value3');
INSERT INTO my_table (column1, column2, column3) VALUES ('value4', 'value5', 'value6');
```
2. 在Spring Boot项目中,创建一个名为"DatabaseInitializer"的类,并注入JdbcTemplate:
```
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.nio.charset.StandardCharsets;
@Component
public class DatabaseInitializer {
@Autowired
private DataSource dataSource;
public void init() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
executeScript(jdbcTemplate, "insert_data.sql");
}
private void executeScript(JdbcTemplate jdbcTemplate, String scriptPath) {
try {
ClassPathResource resource = new ClassPathResource(scriptPath);
String script = new String(resource.getInputStream().readAllBytes(), StandardCharsets.UTF_8);
ScriptUtils.executeSqlScript(jdbcTemplate.getDataSource().getConnection(), script);
} catch (Exception e) {
e.printStackTrace();
}
}
}
```
3. 在Spring Boot项目的启动类中,注入DatabaseInitializer,并在启动时调用init()方法:
```
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import javax.annotation.PostConstruct;
@SpringBootApplication
public class MyApplication {
@Autowired
private DatabaseInitializer databaseInitializer;
public static void main(String[] args) {
SpringApplication.run(MyApplication.class, args);
}
@PostConstruct
public void init() {
databaseInitializer.init();
}
}
```
这样,在Spring Boot启动时,会执行insert_data.sql脚本,将数据插入到MySQL数据库中。
阅读全文