Java表备份、回滚以及根据SQL查询生成Insert语句

最新推荐文章于 2024-05-06 08:53:54 发布
最新推荐文章于 2024-05-06 08:53:54 发布
阅读量3.2k 收藏 7
点赞数
分类专栏： 程序设计实践整理 开发工具和组件 文章标签： sql java 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Octopus21/article/details/120356841
版权

文章目录

（1）需求

在构建数据相关的功能需求，因基础数据杂乱，经常性需要进行更新/删除等清洗工作，但是毕竟所涉及的都是基础数据，必须要注意数据的丢失和错误操作等情况处理。
【1】在每次处理基础数据时如果涉及新增数据，那么必须自动化的进行数据表的备份
【2】如果涉及的数据不多，而且大多都是删除操作等清洗工作，那么可以不需要再单独构建一个备份表，如果涉及的备份表过多，一方面增加维护的成本另一方面会导致备份表过多且难以定位删除的数据，不过也可以通过一些SQL的差集查询语句匹配出删除更新的数据，我这边所采用的策略是直接生成INSERT的SQL语句，然后将这些SQL以特定的日期和操作行为命名然后上传至文件服务器作为保存备份。

（2）数据表的备份

以下的操作基于MySQL数据库，在进行数据的新增和较大改动时需要备份表，以防止操作有误需要回滚表，注意在构建备份表时需要将原表的主键列、索引列等等都要复制过去，而这不能简单的使用create table as select 方式，该种方式不会迁移索引结构，以下操作经过测试可以实现表完整结构和数据的迁移备份，其中${tableName}是表名参数，备份表名称统一为：XXX_backup

【备份表代码参考】

<update id="backupTheOperatingTable">
      <!--    删除旧的备份表   -->
      DROP TABLE IF EXISTS  ${tableName}_backup ;
        <!--  复制表结构-复制表索引等结构定义  -->
      CREATE  TABLE   ${tableName}_backup    (LIKE  ${tableName});
        <!--  复制原表数据        -->
      INSERT INTO  ${tableName}_backup SELECT * FROM  ${tableName};
  </update>

（3）数据表的回滚

如果操作数据表出现问题需要回滚数据，那么可以通过下面变量交换的方式构建一个临时表将表结构和数据进行回滚

  <update id="rollbackData">
      <!--    [1]删除临时表   -->
      DROP TABLE IF EXISTS  ${tableName}_temp;
      <!--    [2]创建临时表   -->
      CREATE  TABLE   ${tableName}_temp    (LIKE  ${tableName});
      <!--    [3]复制主表数据到临时表   -->
      INSERT INTO   ${tableName}_temp SELECT * FROM  ${tableName};
      <!--    [4]清空主表数据   -->
      TRUNCATE  TABLE   ${tableName}   ;
      <!--    [5]将备份表数据复制到主表中   -->
      INSERT INTO   ${tableName}  SELECT * FROM ${tableName}_backup;
      <!--    [6]将原来主表的数据复制到备份表，相当于主表和备份表互换   -->
      TRUNCATE  TABLE  ${tableName}_backup;
      <!--  复制原表数据        -->
      INSERT INTO  ${tableName}_backup SELECT * FROM  ${tableName}_temp;
    </update>

（4）通过查询SQL构建Insert语句SQL脚本

【1】由于这里直接通过JDBC操作，在每次使用完数据库连接后必须及时关闭
【2】因为这里的操作基本上不存在并发和频繁使用，可以直接使用常规的JDBC操作，如果涉及并发和频繁使用需要调整方式，注意资源的合理的使用和释放
【3】下面的示例是直接生成sql本地文件，这里在实际使用中不管是内部使用还是生产环境都建议将sql文件自动上传到文件服务器保存


import org.springframework.util.Assert;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/***
 *
 *  自定义导出SQL脚本工具
 *
 * @author ZhangYu
 * @date 2021/9/17
 */
public class ExportSqlUtil {

    /**  JDBC数据库连接  **/
    private static Connection conn = null;
    /**  SQL执行操作对象  **/
    private static Statement sm = null;
    /**  查询sql  **/
    private static final   String SELECT = "  SELECT * FROM  ";
    /**  插入sql  **/
    private static final  String INSERT = "INSERT INTO";
    /**  values关键字  **/
    private static final String VALUES = "VALUES";
    /**  SQL语句的空格  **/
    private static final String  BLANK_SPACE = " " ;
    /**  全局存放sql语句的容器 **/
    private static final List<String> INSERT_LIST = new ArrayList<>();

    /***
     *  连接数据库
     * @param driver 数据库驱动
     * @param url 数据库地址
     * @param userName 用户名
     * @param password 密码
     * @author ZhangYu
     * @date 2021/9/17
     */
    public static void initConnection(String driver, String url, String userName, String password) {
        try {
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            sm = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }




    /***
     *   根据查询SQL构建插入脚本
     * @param schema  模式名
     * @param tableName  表名
     * @param condition 查询条件  eg: where id =1
     * @return java.util.List<java.lang.String>
     * @author ZhangYu
     * @date 2021/9/17
     */
    public static List<String> executeSQLAndBuildSqlFile(String schema, String tableName, String condition,String filePath)  {
        Assert.notNull(conn,"数据库未连接");
        //建立查询语句
        String querySql = SELECT + BLANK_SPACE + "." + tableName + BLANK_SPACE + condition;
        try {
            //执行查询并拼装insert语句
            executeSQL(conn,sm, querySql,tableName,schema);
            //创建Sql文件
            createFile(filePath);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return INSERT_LIST;
    }

    /**
     * 建立sql文件并导出数据
     */
    private static void createFile(String filePath) {
        File file = new File(filePath);
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                System.out.println("建立文件名失败！！");
                e.printStackTrace();
            }
        }
        FileWriter fw = null;
        BufferedWriter bw = null;
        try {
            fw = new FileWriter(file);
            bw = new BufferedWriter(fw);
            if (INSERT_LIST.size() > 0) {
                for (int i = 0; i < INSERT_LIST.size(); i++) {
                    bw.append(INSERT_LIST.get(i));
                    bw.append("\n");
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bw.close();
                fw.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /***
     *
     *   执行SQL查询并构建insert语句
     * @param conn  conn
     * @param sm sm
     * @param querySql 查询SQL
     * @param tableName  表名
     * @param schema  模式名
     * @author ZhangYu
     * @date 2021/9/17
     */
    private static void executeSQL(Connection conn, Statement sm, String querySql,String tableName,String schema) throws SQLException {
        ResultSet rs = null;
        try {
            rs = getDataAndBuildInsertSql(sm, querySql, tableName, schema);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            assert rs != null;
            rs.close();
            sm.close();
            conn.close();
        }
    }


    /***
     *   根据数据提取列名和列值
     *
     * @param sm     sm
     * @param sql   查询SQL
     * @param tableName  表名
     * @param schema  模式名
     * @return java.sql.ResultSet
     * @author ZhangYu
     * @date 2021/9/17
     */
    private static ResultSet getDataAndBuildInsertSql(Statement sm, String sql,String tableName ,String schema) throws SQLException {
        ResultSet rs = sm.executeQuery(sql);
        //获取表元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取当前表列的长度
        int columnCount = rsmd.getColumnCount();
        while (rs.next()) {
            StringBuffer columnName = new StringBuffer();
            StringBuffer columnValue = new StringBuffer();
            for (int i = 1; i <= columnCount; i++) {
                String value = rs.getString(i);
                if (i == 1 || i == columnCount) {
                    if(i==columnCount){
                        columnName.append(",");
                    }
                    //获取当前列数据
                    columnName.append(rsmd.getColumnName(i));
                    //字段列名称
                    if( i== 1){
                        if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {
                            columnValue.append("'").append(value).append("',");
                        } else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)|| Types.TINYINT == rsmd.getColumnType(i)) {
                            columnValue.append(value).append(",");
                        } else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                            columnValue.append("timestamp'").append(value).append("',");
                        } else {
                            columnValue.append(value).append(",");
                        }
                    }else{
                        //最后一行数据
                        //MySQL的Null处理
                        if (value==null){
                            columnValue.append(value);
                        } else if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {
                            columnValue.append("'").append(value).append("'");
                        } else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)|| Types.TINYINT == rsmd.getColumnType(i)) {
                            columnValue.append(value);
                        } else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                            columnValue.append("timestamp'").append(value).append("'");;
                        } else {
                            columnValue.append(value);
                        }
                    }
                } else {
                    //数据库列表数据
                    columnName.append("," + rsmd.getColumnName(i));
                    //MySQL的Null处理
                    if (value==null){
                        columnValue.append(value).append(",");
                    } else if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {
                        columnValue.append("'").append(value).append("'").append(",");
                    } else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)|| Types.TINYINT == rsmd.getColumnType(i)) {
                        columnValue.append(value).append(",");
                    } else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                        columnValue.append("timestamp'").append(value).append("',");
                    } else {
                        columnValue.append(value).append(",");
                    }
                }
            }
            buildInsertSql(columnName, columnValue,tableName,schema);
        }
        return rs;
    }


    /**
     *
     *     构建Insert语句
     * @param columnName 列名
     * @param columnValue 列值
     * @param tableName 表名
     * @param schema  模式名
     */
    private static void buildInsertSql(StringBuffer columnName, StringBuffer columnValue, String tableName, String schema) {
        StringBuffer insertSql = new StringBuffer();
        insertSql.append(INSERT).append(" ").append(schema).append(".")
                .append(tableName).append("(").append(columnName.toString()).append(")").append(VALUES).append("(").append(columnValue.toString()).append(");");
        INSERT_LIST.add(insertSql.toString());
    }

}

测试：

   public static void main(String[] args) {
        initConnection("com.p6spy.engine.spy.P6SpyDriver", "jdbc:p6spy:mysql:XXXX?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true&verifyServerCertificate=false&useSSL=false&allowMultiQueries=true", "XXXX", "XXX");
        executeSQLAndBuildSqlFile("main","department","where  id = 1 ","D:\\demo.sql");

    }

在这里插入图片描述

（5）知识整理

使用原生JDBC执行SQL并获取结果

 private static Statement sm = null;
 ResultSet rs = sm.executeQuery(sql); 

  //获取表元数据
  ResultSetMetaData rsmd = rs.getMetaData();

 //获取当前表列的长度，共有多少列
 int columnCount = rsmd.getColumnCount()

 //获取表某列的值，注意索引值从1开始
 String value = rs.getString(i);

//获取表某列的名称
rsmd.getColumnName(i)

//获取表某列的类型，注意需要注意MySQL的Null值
rsmd.getColumnType(i)

这里需要注意MySQL中Null值的数据获取类型不是Types.NULL而是Types.VARCHAR，可以通过下面的方式处理

在这里插入图片描述

 //MySQL的Null处理
  if (value==null){
      columnValue.append(value).append(",");
  }
ZWZhangYu
关注
  • 0
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
专栏目录
把任意数据库导出为其它数据库的insert（可执行程序+java代码）
03-31
软件功能： java编写。 此软件用于把数据库里的数据导出为指定数据库类型的insert语句，用于数据备份或迁移到另一个数据库， 大文本和二进制字段被忽略。 当前支持oracle、sybase、mysql和ms sqlServer数据库。 你可以修改参数文件、增加新的jdbc驱动来增加新数据库种类的支持。 使用方法： 软件为免安装的绿色软件。系统必须已经安装1.4版本以上的java虚拟机。 解压缩包到任意目录，执行getIni.bat运行程序。 下载后觉得可以用，请ding一下，谢谢！。
java 查询数据结果集生成insert 插入语句，并生成sql文件
weixin_41560845的博客
05-18 1369
java 查询数据结果集生成insert 插入语句，并生成sql文件
通过DataGrip将mysql表结构信息转存excel 复制select结果的insert插入语句
最新发布
扎哇太枣糕的笔记博客
05-06 841
各位小伙伴们大家好，欢迎来到这个小扎扎的专栏，在这个系列专栏中记录了博主在学习期间总结的大块知识点，以及日常工作中遇到的各种技术点┗|｀O′|┛
java类实现导出mysql数据库全表 insert语句
zhaofuqiangmycomm的博客
03-10 1951
import java.io.BufferedWriter; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLExceptio.
JavaBean的list集合拼接成Insert语句
zhang1206214477的专栏
04-21 3066
最近工作可恶心了，整的框架不让用，还得自己拼接sql语句，于是自己封装了一个方法：public static String beans2db(List objs, String table) { String sql=""; StringBuffer prama=new StringBuffer(); StringBuffer values=new StringBuffer();
用SQL脚本 生成INSERT SQL语句
weixin_30813225的博客
08-03 791
配置表B 中的数据，可以从A表中查询到，在实际配置时，通过sql脚本生成B表的insert脚本，最多用到的是sql中连接符【||】，以及双引号【''''】 例1：电销系统中地区出单机构关系表配置数据生成sql select 'insert into tbl_area_org_code (area_code,area_name,parent_area_code ,yc_area_code,...
sql：基于Goinception SQL执行生成回滚语句
02-18
INSERT INTO `库名`.`users`(`id`, `created_at`, `updated_at`, `name`, `password`) VALUES (1, '2020-08-04 20:52:11', '2020-08-04 20:52:16', 'root', '$2a$10$uV3noykM1.aNkh14r3T39.4E62TKebEQ9...
my2sql:解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息以及大事务分析信息
05-13
my2sqlgo版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等，本工具基于my2fback、binlog_...
java面试题（SQL语句）.pdf
04-25
理解并熟练掌握这些SQL语句对于Java开发者来说至关重要，因为它们在开发过程中用于构建数据处理逻辑，执行数据库操作，以及优化查询性能。在面试中能够熟练运用和解释这些概念，将大大提升你的专业形象。
Delphi根据SQL查询语句生成相关程序.rar
07-10
本资源“Delphi根据SQL查询语句生成相关程序.rar”是一个实用工具，它可以帮助开发者，特别是初学者，更便捷地编写和理解SQL查询语句。下面将详细介绍这个程序及其相关知识点。 1. **SQL查询语言**：SQL...
SQL Server 表Insert脚本生成工具
05-09
通过它，用户可以快速、方便地生成特定表的INSERT语句脚本，这些脚本可以随后在目标数据库上执行，以插入相同的数据。 在数据库管理中，INSERT语句是用于向表格添加新记录的基本SQL命令。通常，手动编写大量INSERT...
Java ArrayList 的insert实现
t_jl1979的专栏
10-20 4933
list支持insert操作，但是由于 ArrayList为数组实现
Mysql的备份回滚
weixin_52190986的博客
09-18 708
​ 数据库备份策略大体上可以分为以下几种:​：每次对数据库进行完整的备份​：备份自从上次完全备份后被修改过的文件，下次备份备份的是相对原来的完全备份后增加啊 的差异的文件而不是相对于差异备份后增加的文件​：备份相对于前一次备份后被修改的文件。
导入数据java生成逆向sql，用于回滚，你试过吗？
RunDreaming的博客
03-27 1908
自定义注解，生成增删改的相反sql，适用于导入大量数据的情况，数据存储在redis中，可设置时长，key可以存储在数据库中，如果要回滚，可以直接读取redis中该key的list,从开始执行到结束为止。
java 将数据库中的所有数据导出标准的insert语句 属于手写备份还原 区别于mysqldump命令 不包含结构 用于项目版本迭代
weixin_45447370的博客
06-19 376
java 将数据库中的所有数据导出标准的insert语句 属于手写备份还原 区别于mysqldump命令 不包含结构 用于项目版本迭代
怎么直接把sql查询出来的结果转成insert 语句
weixin_42611310的博客
02-17 1981
可以使用SQL语句的INSERT INTO SELECT语句，具体的步骤是：1. 定义新表的结构；2. 使用INSERT INTO SELECT语句将查询结果插入到新表中。例如：INSERT INTO 新表名 (字段名) SELECT 字段名 FROM 旧表名; ...
在java数据库的插入语句_java类实现导出各种数据库insert语句
weixin_39521808的博客
02-24 560
导出insertsql语句import java.io.BufferedWriter;import java.io.File;import java.io.FileWriter;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;i...
Java基础小案例之编写动态插入sql语句，查询sql
xtho62的博客
03-09 824
给出一个数组或者集合，即字段，插入给定的表。 如字段数组为： “name”, “position”, “salary” 表为：employee public class Main { public static void main(String[] args) { String[] fields = { "name", "position", "salary" }; String table = "employee"; String insert =
sqlsever回滚操作_mssql sqlserver sql脚本中事务回滚的四种方法分享
weixin_29051149的博客
12-24 4115
摘要:下文通过举例的方式，分享在sql脚本编写中，显示和自动事务回滚的处理方法，如下所示:实验环境:sql server 2008 R2例:---数据表准备:create table test(keyId int identity,info varchar(100))go---方法1:使用 rollback tran 回滚对应的事务begin traninsert into test (info)...
JDBCTemple 多sql语句回滚
05-20
在使用 JDBC 进行多 SQL 语句操作时，如果其中有一条语句执行失败，需要回滚所有已经执行的 SQL 语句。JDBC 提供了一种机制，可以将多个 SQL 语句作为一个事务来执行，并且可以设置事务的隔离级别、提交或回滚等属性。以下是一个使用 JDBC 进行多 SQL 语句回滚的示例代码： ```java Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection(url, username, password); conn.setAutoCommit(false); //关闭自动提交事务 stmt = conn.createStatement(); stmt.executeUpdate("INSERT INTO table1 VALUES (1, 'value1')"); stmt.executeUpdate("INSERT INTO table2 VALUES (2, 'value2')"); stmt.executeUpdate("INSERT INTO table3 VALUES (3, 'value3')"); conn.commit(); //提交事务 } catch (SQLException e) { if (conn != null) { try { conn.rollback(); //回滚事务 } catch (SQLException ex) { ex.printStackTrace(); } } e.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } ``` 在上述代码中，首先通过 `DriverManager.getConnection()` 方法获取数据库连接，并关闭自动提交事务模式，然后创建一个 `Statement` 对象，并执行多个 SQL 语句。如果其中任何一条 SQL 语句执行失败，则在 `catch` 块中通过 `Connection.rollback()` 方法回滚事务。如果所有 SQL 语句执行成功，则在 `try` 块的末尾通过 `Connection.commit()` 方法提交事务。最后在 `finally` 块中关闭 `Statement` 和 `Connection` 对象。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

ZWZhangYu

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值