前言
该篇内容介绍是 调用指定的 .sql 文件, 执行里面的mysql语句。
正文
实战示例准备:
1. 一张 user 表 :
2.2个简单 SQL文件(里面就简单写点插入sql语句):
sql文件在项目中的位置:
3.pom.xml 依赖:
<!--web-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- mybatis依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- druid数据源驱动 1.1.10解决springboot从1.0——2.0版本问题-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
4.application.yml 配置:
server:
port: 8082
spring:
datasource:
druid:
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=true&rewriteBatchedStatements=true
username: root
password: 123456
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
5. 新建一个工具类ExecuteSQLUtil.java :
可以看到我的有关mysql连接信息在示例里面是从yml文件读取的或者是在代码写死,这个大家根据具体业务场景和项目去调整,甚至放在数据库里面查询也行。
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Component;
import java.sql.*;
/**
* @Author: JCccc
* @Description:
* @Date: 2020/11/16
*/
@Component
public class ExecuteSQLUtil {
@Value("${spring.datasource.druid.url}")
private String DB_URL;
@Value("${spring.datasource.druid.username}")
private String DB_USERNAME;
@Value("${spring.datasource.druid.password}")
private String DB_PWD;
public Connection executeSql(String sqlFileName){
Connection connection = null;
try {
String driverClassName = "com.mysql.cj.jdbc.Driver";
// String DB_URL = "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=true&rewriteBatchedStatements=true";
// String DB_USERNAME = "root";
// String DB_PWD = "123456";
Class.forName(driverClassName);
connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PWD);
} catch (Exception e) {
e.printStackTrace();
}
ClassPathResource rc = new ClassPathResource(sqlFileName);
EncodedResource er = new EncodedResource(rc, "utf-8");
ScriptUtils.executeSqlScript(connection, er);
return connection;
}
}
6. 写个测试接口,使用postman调用一下:
/**
* @Author: JCccc
* @Description:
* @Date: 2020/11/16
*/
@RestController
public class TestController {
@Autowired
ExecuteSQLUtil executeSQLUtil;
@GetMapping("/executeMysql")
public String executeMysql(@RequestParam("scriptName") String scriptName) {
executeSQLUtil.executeSql("static/"+scriptName+".sql");
return "ok";
}
}
测试:
运行结果:
好的该篇内容就到这吧。