前言
该篇内容介绍是 调用指定的 .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"; } }
测试：
运行结果：
好的该篇内容就到这吧。
