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

男娘i 2022-09-11 15:23 236阅读 0赞

### 文章目录 ###

*   *   *  （1）需求
         *  （2）数据表的备份
         *  （3）数据表的回滚
         *  （4）通过查询SQL构建Insert语句SQL脚本
         *  （5）知识整理

### （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">
          
          DROP TABLE IF EXISTS  ${ tableName}_temp;
          
          CREATE  TABLE   ${ tableName}_temp    (LIKE  ${ tableName});
          
          INSERT INTO   ${ tableName}_temp SELECT * FROM  ${ tableName};
          
          TRUNCATE  TABLE   ${ tableName}   ;
          
          INSERT INTO   ${ tableName}  SELECT * FROM ${ tableName}_backup;
          
          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");
    
        }

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAWldaaGFuZ1l1_size_20_color_FFFFFF_t_70_g_se_x_16]

### （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，可以通过下面的方式处理

![在这里插入图片描述][8068f222590c4c6e85fdccad96b8b586.png]

//MySQL的Null处理
      if (value==null){ 
          columnValue.append(value).append(",");
      }

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAWldaaGFuZ1l1_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/bda90d605b214c7d93cb91451777658b.png
[8068f222590c4c6e85fdccad96b8b586.png]: /images/20220828/51ace9e7c71a42478715674c03c63610.png