言成言成啊 | Kit Chen's Blog

Java执行SQL脚本

发布于2021-12-02 22:01:01，更新于2021-12-02 22:21:36，标签：java open  文章会持续修订，转载请注明来源地址：https://meethigher.top/blog

参考

记录下工作中的一个需求，一键恢复出厂功能，本质就是恢复成出厂的数据库，连带着字典表、内置数据等。

简单写个demo，源码meethigher/java-exec-sql-script: 基于SpringBoot实现Java执行SQL脚本

首先准备一个sql文件，比如

1
2
3
4
TRUNCATE TABLE notification_read cascade;
DROP TABLE IF EXISTS sort_code_management;

INSERT INTO "public"."option_info" ("option_info_code","metadata_code","metadata_field_code","metadata_field_codes","option_info_name","option_info_order") VALUES ('83959cf6cb2463e6', '6', '52c529505d26fa1c', '', '字符型 C', NULL),('b144a6d78db7cad1', '6', '52c529505d26fa1c', '', '数值型 N', NULL),('9bfb7a1c9815470e', '6', '52c529505d26fa1c', '', '货币型 Y', NULL),('e1d92a5a2e3c9329', '6', '52c529505d26fa1c', '', '日期型 D', NULL),('549cfc840c4dd976', '6', '52c529505d26fa1c', '', '日期时间型 T', NULL),('e596658dadfd9064', '6', '52c529505d26fa1c', '', '逻辑型 L', NULL),('3d66389bb8cc83b0', '6', '52c529505d26fa1c', '', '备注型 M', NULL),('37d6ec57ed618a3d', '6', '52c529505d26fa1c', '', '通用型 G', NULL),('045de183f81488b3', '6', '52c529505d26fa1c', '', '双精度型 B', NULL),('ba627e8d5a89d071', '6', '52c529505d26fa1c', '', '整型 I', NULL),('e90a8a114fee22bf', '6', '52c529505d26fa1c', '', '浮点型 F', NULL),('179c9b0b34654cd8', '5', '948b1824d3ab0fa6', '', '实时', NULL),('87297a92178d7671', '5', '948b1824d3ab0fa6', '', '每日', NULL),('2eaaf9dd41900544', '5', '948b1824d3ab0fa6', '', '每周', NULL),('774685a650f23344', '5', '948b1824d3ab0fa6', '', '每月', NULL),('aec6ab7fde13b442', '5', '948b1824d3ab0fa6', '', '每季度', NULL),('651a90922ec8a736', '5', '948b1824d3ab0fa6', '', '每年', NULL),('16276c0b5690f3d6', '4', '358e9c8a97ca5370', '5bcf42a5556b23ab', '是', NULL),('75ec6021383875d8', '4', '358e9c8a97ca5370', '', '否', NULL),('d07d539c211e3c6b', '3', '472ee60cd8be3e2f', '95ed9e0e960d227e,82704a8e11c0e9af', '无条件共享', NULL),('e76a9785363594b4', '3', '472ee60cd8be3e2f', '95ed9e0e960d227e,f769f43de4bc430a,6ce3589959032786,82704a8e11c0e9af', '有条件共享', NULL),('034070dd18ee93bd', '3', '472ee60cd8be3e2f', '3161d52967b5abae', '不予共享', NULL),('caa0abd02728f653', '3', '95ed9e0e960d227e', '', '作为行政依据', NULL),('2541afe371622479', '3', '95ed9e0e960d227e', '', '作为工作参考', NULL),('ce79a819a1f49b46', '3', '95ed9e0e960d227e', '', '用于数据校核', NULL),('2e22033a43e944a1', '3', '95ed9e0e960d227e', '', '用于业务协同', NULL),('15f0194b4d9a4666', '3', '82704a8e11c0e9af', '', '共享平台', NULL),('641f09105135b8df', '3', '82704a8e11c0e9af', '', '邮件', NULL),('ce9b95116a49145e', '3', '82704a8e11c0e9af', '', '拷盘', NULL),('356d252277a56ed5', '3', '82704a8e11c0e9af', '', '介质交换（纸质报表）', NULL),('de4f37dd2175f3ed', '3', '82704a8e11c0e9af', '', '介质交换（电子文档）', NULL);

pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>top.meethigher</groupId>
    <artifactId>java-exec-sql-script</artifactId>
    <version>1.0.0</version>
    <name>java-exec-sql-script</name>
    <description>chenchuancheng&apos;s demo</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>true</filtering>
                <includes>
                    <include>**/*.yml</include>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                    <include>resetsql/**</include>
                </includes>
                <excludes>
                    <exclude>static/**</exclude>
                    <exclude>userTemplate/**</exclude>
                    <exclude>certificate/*.jks</exclude>
                    <exclude>**/*.jks</exclude>
                </excludes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>false</filtering>
                <includes>
                    <include>static/**</include>
                    <include>userTemplate/**</include>
                    <include>**/*.jks</include>
                </includes>
            </resource>
        </resources>
    </build>

</project>

applicaion.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
      naming:
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
  datasource:
    url: jdbc:postgresql://192.168.110.40:5432/ccc
    username: postgres
    password: postgres
    driver-class-name: org.postgresql.Driver
    type: com.zaxxer.hikari.HikariDataSource
    # Hikari 连接池配置
    # 最小空闲连接数量
    hikari:
      minimum-idle: 5
      # 空闲连接存活最大时间，默认600000（10分钟）
      idle-timeout: 180000
      # 连接池最大连接数，默认是10
      maximum-pool-size: 10
      # 此属性控制从池返回的连接的默认自动提交行为,默认值：true
      auto-commit: true
      # 连接池名称
      pool-name: MyHikariCP
      # 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
      max-lifetime: 1800000
      # 数据库连接超时时间,默认30秒，即30000
      connection-timeout: 30000
      connection-test-query: SELECT 1
  servlet:
    multipart:
      max-file-size: 50MB
      max-request-size: 50MB

实现CommandLineRunner，在项目启动后进行执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
@Component
public class InitializableDatabaseRunner implements CommandLineRunner {

    @Autowired
    private DataSourceProperties dataSourceProperties;

    @Override
    public void run(String... args) throws Exception {
        try {
            Connection conn =getConnection();
            ScriptRunner runner = new ScriptRunner(conn);
            Resources.setCharset(Charset.forName("UTF-8")); //设置字符集,不然中文乱码插入错误
            runner.setLogWriter(null);//设置是否输出日志
            // 从class目录下直接读取
            Reader read = Resources.getResourceAsReader("reset.sql");
            runner.runScript(read);
            runner.closeConnection();
            conn.close();
            System.out.println("sql脚本执行完毕");
        } catch (Exception e) {
            System.out.println("sql脚本执行发生异常");
            e.printStackTrace();
        }
    }


//    @Override
//    public void run(String... args) {
//        try {
//            Connection connection = getConnection();
//            SQLScriptRunner runner = new SQLScriptRunner(connection);
//            ClassPathResource classPathResource = new ClassPathResource("resetsql/reset.sql");
//            InputStream inputStream = classPathResource.getInputStream();
//            Reader reader = new InputStreamReader(inputStream, Charset.forName("UTF-8"));
//            //打印日志
////            runner.setLogWriter(null);
//            runner.runScript(reader);
//            runner.closeConnection();
//            connection.close();
//            System.out.println("sql脚本执行完毕");
//        }catch (Exception e) {
//            System.out.println("sql脚本执行异常");
//            e.printStackTrace();
//        }
//    }

    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(dataSourceProperties.getDriverClassName());
        return DriverManager.getConnection(dataSourceProperties.getUrl(),dataSourceProperties.getUsername(),dataSourceProperties.getPassword());
    }
}
发布：2021-12-02 22:01:01
修改：2021-12-02 22:21:36
链接：https://meethigher.top/blog/2021/java-exec-sql-script/
标签：java open 
付款码 打赏 分享
访客数calculating人
Shift+Ctrl+1 可控制工具栏