（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文件自动上传到文件服务器保存
importorg.springframework.util.Assert;importjava.io.BufferedWriter;importjava.io.File;importjava.io.FileWriter;importjava.io.IOException;importjava.sql.*;importjava.util.ArrayList;importjava.util.List;/***
*
* 自定义导出SQL脚本工具
*
* @author ZhangYu
* @date 2021/9/17
*/publicclassExportSqlUtil{/** JDBC数据库连接 **/privatestaticConnection conn=null;/** SQL执行操作对象 **/privatestaticStatement sm=null;/** 查询sql **/privatestaticfinalString SELECT=" SELECT * FROM ";/** 插入sql **/privatestaticfinalString INSERT="INSERT INTO";/** values关键字 **/privatestaticfinalString VALUES="VALUES";/** SQL语句的空格 **/privatestaticfinalString BLANK_SPACE=" ";/** 全局存放sql语句的容器 **/privatestaticfinalList<String> INSERT_LIST=newArrayList<>();/***
* 连接数据库
* @param driver 数据库驱动
* @param url 数据库地址
* @param userName 用户名
* @param password 密码
* @author ZhangYu
* @date 2021/9/17
*/publicstaticvoidinitConnection(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
*/publicstaticList<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文件并导出数据
*/privatestaticvoidcreateFile(String filePath){File file=newFile(filePath);if(!file.exists()){try{
file.createNewFile();}catch(IOException e){System.out.println("建立文件名失败！！");
e.printStackTrace();}}FileWriter fw=null;BufferedWriter bw=null;try{
fw=newFileWriter(file);
bw=newBufferedWriter(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
*/privatestaticvoidexecuteSQL(Connection conn,Statement sm,String querySql,String tableName,String schema)throwsSQLException{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
*/privatestaticResultSetgetDataAndBuildInsertSql(Statement sm,String sql,String tableName,String schema)throwsSQLException{ResultSet rs= sm.executeQuery(sql);//获取表元数据ResultSetMetaData rsmd= rs.getMetaData();//获取当前表列的长度int columnCount= rsmd.getColumnCount();while(rs.next()){StringBuffer columnName=newStringBuffer();StringBuffer columnValue=newStringBuffer();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("',");}elseif(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(",");}elseif(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);}elseif(Types.CHAR== rsmd.getColumnType(i)||Types.VARCHAR== rsmd.getColumnType(i)||Types.LONGVARCHAR== rsmd.getColumnType(i)){
columnValue.append("'").append(value).append("'");}elseif(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);}elseif(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(",");}elseif(Types.CHAR== rsmd.getColumnType(i)||Types.VARCHAR== rsmd.getColumnType(i)||Types.LONGVARCHAR== rsmd.getColumnType(i)){
columnValue.append("'").append(value).append("'").append(",");}elseif(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(",");}elseif(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 模式名
*/privatestaticvoidbuildInsertSql(StringBuffer columnName,StringBuffer columnValue,String tableName,String schema){StringBuffer insertSql=newStringBuffer();
insertSql.append(INSERT).append(" ").append(schema).append(".")