Springboot 调用mysql的.sql文件，执行mysql语句

港控/mmm° 2022-12-21 12:57 78阅读 0赞

# 前言 #

该篇内容介绍是 调用指定的 **.sql 文件**， 执行里面的mysql语句。

# 正文  #

## 实战示例准备： ##

1. 一张 user 表 ：

![2020111613505119.png][]

2.2个简单 SQL文件（里面就简单写点插入sql语句）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70][]

sql文件在项目中的位置：  
![20201116140123655.png][]

3.pom.xml 依赖：

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.0</version>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            
            <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";
        }
    
    
    }

测试：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 1][]

运行结果：

![20201116140217109.png][]

好的该篇内容就到这吧。

[2020111613505119.png]: /images/20221120/02d353f4f98e40839daf45e84c8dcccb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70]: /images/20221120/a1a450f12df34dff82478aca0168fdce.png
[20201116140123655.png]: /images/20221120/63949789ecee464498de07abdda1ad60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 1]: /images/20221120/c5be670930a14da9a2562e51eb227a4d.png
[20201116140217109.png]: /images/20221120/4d8f2d9eccc04f0dbacb757959455116.png