java查询数据库方法show create

实现获取SQL创建代码和获取数据操作（待优化）

public Tip query(HttpServletResponse response) throws IOException {
        var list = queryTablesDao.queryAllTables();
        PrintWriter writer = new PrintWriter(response.getOutputStream());
        List<String> file = new ArrayList<>();
        for (String tableName:list) {
//            var line = queryTablesDao.queryCreateTableSql(tableName);
//            writer.println(line);
            String sql = "show create table " +tableName;
            var str = tableServer.handleResult(sql)+";";
            file.add(str);
            writer.println(str);
            String sql1 = "SELECT * FROM " +tableName;
            var test = tableServer.handleResult2(sql1);
            for (String st:test) {
                writer.println(st);
                file.add(st);
            }
            writer.flush();
        }

import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class TableServer {

    @Resource
    DataSource dataSource;

    Connection conn = null;

    public String handleResult(String sql){
        ResultSet rs = executeQuery(sql);
        try {
            while (rs.next()) {
                ResultSetMetaData md = rs.getMetaData();
                int cols = md.getColumnCount();

                for (int i = 1; i <= cols; i++) {
                    switch (md.getColumnType(i)) {
                        case Types.BIT:
                        case Types.INTEGER:
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.NUMERIC:
                            System.out.print(rs.getInt(i));
                            break;
                        case Types.BIGINT:
                            System.out.print(rs.getLong(i));
                            break;
                        case Types.DECIMAL:
                            System.out.print(rs.getBigDecimal(i));
                            break;
                        case Types.BOOLEAN:
                            System.out.print(rs.getBoolean(i));
                            break;
                        case Types.FLOAT:
                        case Types.REAL:
                            System.out.print(rs.getFloat(i));
                            break;
                        case Types.DOUBLE:
                            System.out.print(rs.getDouble(i));
                            break;
                        case Types.VARCHAR:
                        case Types.NVARCHAR:
                        case Types.CHAR:
                        case Types.NCHAR:
                        case Types.DATE:
                        case Types.TIMESTAMP: {
                            String val = rs.getString(i);
                            if(val==null){
                                System.out.print("null");
                            }else {
                                val = val.replace("\r", "");
                                val = val.replace("\n", "");
                                if(i>1) {
                                    return val;
                                }else{
                                    System.out.print(val);
                                }
                            }
                        }
                        break;
                        case Types.VARBINARY: {
                            byte[] bytes = rs.getBytes(i);
                            if(bytes!=null) {
                                String hex="";
                                for (int c = 0; c < bytes.length; c++) {
                                    hex += String.format("%02X", bytes[c]);
                                }
                                System.out.print("'" + hex + "'");
                            }else{
                                System.out.print("null");
                            }
                        }
                        break;
                        case Types.NULL:
                            System.out.print("null");
                            break;
                        case Types.LONGVARBINARY:
                            System.out.print("[IMAGE]");
                            break;
                        default:
                            System.out.print("Unknown type: " + md.getColumnType(i));
                    }

                    if(i<cols){
                        System.out.print("|");
                    }
                }

                // new line
                System.out.println("");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return null;
    }

    public List<String> handleResult2(String sql){
        ResultSet rs = executeQuery(sql);
        List<String> list = new ArrayList<>();
        try {
            while (rs.next()) {
                ResultSetMetaData md = rs.getMetaData();
                int cols = md.getColumnCount();
                StringBuilder str = new StringBuilder("INSERT INTO "+md.getTableName(1)+" VALUES(");
                for (int i = 1; i <= cols; i++) {
                    switch (md.getColumnType(i)) {
                        case Types.BIT:
                        case Types.INTEGER:
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.NUMERIC:
                            str.append(rs.getInt(i));
                            break;
                        case Types.BIGINT:
                            str.append(rs.getLong(i));
                            break;
                        case Types.DECIMAL:
                            str.append(rs.getBigDecimal(i));
                            break;
                        case Types.BOOLEAN:
                            str.append(rs.getBoolean(i));
                            break;
                        case Types.FLOAT:
                        case Types.REAL:
                            str.append(rs.getFloat(i));
                            break;
                        case Types.DOUBLE:
                            str.append(rs.getDouble(i));
                            break;
                        case Types.VARCHAR:
                        case Types.NVARCHAR:
                        case Types.CHAR:
                        case Types.NCHAR:
                        case Types.DATE:
                        case Types.TIMESTAMP: {
                            String val = rs.getString(i);
                            if(val==null){
                                str.append("null");
                            }else {
                                val = val.replace("\r", "");
                                val = val.replace("\n", "");
                                str.append("'"+val+"'");
                            }
                        }
                        break;
                        case Types.VARBINARY: {
                            byte[] bytes = rs.getBytes(i);
                            if(bytes!=null) {
                                String hex="";
                                for (int c = 0; c < bytes.length; c++) {
                                    hex += String.format("%02X", bytes[c]);
                                }
                                str.append("'"+ hex +"'");
                            }
                        }
                        break;
                        case Types.NULL:
                            str.append("null");
                            break;
                        case Types.LONGVARBINARY:
                            str.append("[IMAGE]");
                            break;
                        default:
                            System.out.print("Unknown type: " + md.getColumnType(i));
                    }

                    if(i<cols){
                        str.append(",");
                    }
                }
                str.append(");");
                String st = str.toString();
            list.add(st);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return list;
    }

    public ResultSet executeQuery(String sql) {
        ResultSet rs=null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement q1 = conn.prepareStatement(sql);
            rs = q1.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public void close() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

io输入流模板

 response.setContentType("application/octet-stream");
        //此处设置请求头为二进制
        response.setHeader(ACCESS_CONTROL_EXPOSE_HEADERS, CONTENT_DISPOSITION);
        var list = queryTablesDao.queryAllTables();
        List<String> file = new ArrayList<String>();
        if(filter!=null && filter.equals(SCHEDULE)){
            for (String tableName : list) {
                String dropSql = "\nDROP TABLE IF EXISTS " +tableName +";\n";
                file.add(dropSql);
                String createSql = "show create table " + tableName;
                var str = tableServer.handleResult(createSql) + ";\n\n";
                file.add(str);
            }
        }else {
            for (String tableName : list) {
                String dropSql = "\nDROP TABLE IF EXISTS " + tableName + ";\n";
                file.add(dropSql);
                String createSql = "show create table " + tableName;
                var str = tableServer.handleResult(createSql) + ";\n\n";
                file.add(str);
                String insertSql = "SELECT * FROM " + tableName;
                var test = tableServer.handleResult2(insertSql);
                for (String st : test) {
                    file.add(st + "\n");
                }
            }
        }
       //把List<String> 转化为byte[]流   
//具体如下，StringBuffer str = new StringBuffer
// str.append(list每一个String插入)
//byte[] data = str.toString.getBytes(StandardCharsets.UTF_8)
//上面那步防止乱码的发生
        var data = tableServer.changToByte(file);
        var dataBase = queryTablesDao.queryDataBase();
       //防止中文乱码
        response.setHeader(CONTENT_DISPOSITION,"attachment; filename="+dataBase+".sql");
        IOUtils.write(data,response.getOutputStream());
//        writer.flush();
        return null;

List排序问题

.stream().sorted(Comparator.comparing(list对象::按此值排序).reversed()).collect(Collectors.toList());

加reversed()为倒叙

事务处理问题
@Transactional(rollbackFor = Exception.class)