需求：清空三个月前的操作日志，并生成备份.sql文件 Java实现

备份：

 /**
     * 主方法
     * @param tableName 表名
     * @param column   条件字段
     */
    public void insertSql(String tableName, String column){
        // 查询数据
        List<Map<?, ?>> list = deptMapper.getDataByTable(tableName, column);

        logger.info("@@@@  开始备份" + tableName + "数据@@@@");
        // 拼接insert List
        List<StringBuilder> insertSqlList = new ArrayList<>();
        for (Map<?, ?> map : list) {
            StringBuilder sb = new StringBuilder();
            sb.append(getInsertSql(tableName, map));
            insertSqlList.add(sb);
        }

         // 保存成文件格式
        if (insertSqlList.size() > 0){
            createFile(insertSqlList, tableName);
        }
    }

private static final String insert_label = "INSERT INTO %s(";
private static final String values_label = "VALUES(";
private static final String end_label = ");";

/**
 * 拼接insert 语句
 * @param tableName
 * @param map
 * @return
 */
public static String getInsertSql(String tableName, Map<?, ?> map){
    StringBuilder sb = new StringBuilder();
    StringBuilder sbVal = new StringBuilder();
    sb.append(String.format(insert_label, tableName));

    try {
        Iterator<? extends Map.Entry<?, ?>> iterator = map.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<?, ?> next = iterator.next();
            sb.append("`"+ next.getKey().toString() + "`").append(",");
            sbVal.append(Objects.isNull(next.getValue()) ? null : getValue(next.getValue()) ).append(",");
        }
        sb.deleteCharAt(sb.length()-1).append(") ").append(values_label);
        sb.append(sbVal);
        sb.deleteCharAt(sb.length()-1);
    } catch (Exception e) {
        throw new RuntimeException("生成Sql异常");
    }
    sb.append(end_label);
    return sb.toString();
}

/**
 * 格式化对应字段的数据
 * @param value
 * @return
 */
private static Object getValue(Object value) {
    Object object;
    Class<?> aClass = value.getClass();
    String simpleName = aClass.getSimpleName();
    switch (simpleName){
        case "int":
        case "Integer":
        case "long":
        case "Long":
        case "double":
        case "Double":
        case "float":
        case "Float":
            object = value;
            break;
        case "String":
            object = Objects.isNull(value) ? null : "'" + value + "'";
            break;
        default:
            object = Objects.isNull(value) ? null : "'" + FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss").format(value) + "'";
            break;
    }

    return object;
}


/**
 * 创建文件存sql语句
 */
public void createFile(List<StringBuilder> insertSqlList, String fileName){
    String basePath = Global.getProfile() + "/backupsSql/"+ DateUtils.parseDateToStr("yyyyMMdd", new Date());　　　　// 保存文件的路径,自定义
    if (!new File(basePath).exists()){
        new File(basePath).mkdirs();
    }
    String filePath = basePath + "/" + fileName +".sql";
    File file = new File(filePath);
    if (!file.exists()){
        try {
            file.createNewFile();
        } catch (IOException e) {
           logger.error("创建文件" + filePath + " 失败");
            e.printStackTrace();
        }
    }

    FileWriter fw = null;
    BufferedWriter bw = null;
    try {
        fw = new FileWriter(file);
        bw = new BufferedWriter(fw);
        for (StringBuilder sql : insertSqlList) {
            bw.append(sql);
            bw.append("\n");
        }
    } catch (IOException e) {
        e.printStackTrace();
    }finally {
       close(bw, fw);
    }
}

　　

 

查询三个月前的操作数据：

  @Select("select * from ${table} where ${column} < curdate()-INTERVAL 3 month")
    List<Map<?,?>> getDataByTable(@Param("table") String tableName, @Param("column") String column);

　

清空三个月前的操作数据：

deptMapper.deleteLogByTables("sys_oper_log","oper_time");   //  操作日志


// 操作数据库的代码：

@Delete("delete from ${table} where ${column} < curdate()-INTERVAL 3 month")
void deleteLogByTables(@Param("table") String table,@Param("column") String column);