java - 如何将大块 JDBC 代码分成不同的类？

Question

我写了一大块代码，从url下载CSV文件，然后批量插入sql数据库，然后从SQL服务器调用数据并显示在Java控制台上。最后选择我想要保留的列并导出为新的 CSV 文件。但现在所有这些代码都属于同一类。我如何将它们分成不同的类，就像我想要一个类只下载文件，另一个类只执行批量插入，另一个类只执行选择查询。谢谢你的协助下面是我现在在一个类(class)中的代码

public class ProjectTest extends CreateTable {

public static void main(String[] args) throws MalformedURLException {
    BufferedReader br = null;
    String line = "";
    String cvsSplitBy = ",";
    URL url = new URL(
            "https://quality.data.gov.tw/dq_download_csv.php?nid=43983&md5_url=9d38afbca8243a24b5b89d03a8070aff");

    try (InputStream inputStream = url.openStream();
            FileOutputStream fos = new FileOutputStream(
                    "C:\\Users\\ALICE\\Desktop\\Java\\Dropbox\\Java\\virus.csv"); 
            Connection connection = DriverManager
                    .getConnection("jdbc:sqlserver://localhost:1433;databaseName=JDBCDB", "andy3", "andy"); // andy3
                                                                                                            // //
                                                                                                            // ,andy
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            FileOutputStream fos2 = new FileOutputStream(
                    "C:\\Users\\ALICE\\Desktop\\Java\\Dropbox\\Java\\NEWvirus.csv");
            OutputStreamWriter osw = new OutputStreamWriter(fos2, "MS950");
            BufferedWriter bw = new BufferedWriter(osw);

    ) {
        byte[] buffer = new byte[1024]; 
        int length;
        while ((length = inputStream.read(buffer)) != -1) {
            fos.write(buffer, 0, length);
        }
        fos.close();

        stmt.executeUpdate("DROP TABLE Virus");
        boolean rs = stmt.execute(CreateTable);
        System.out.println("Database Created");
        PreparedStatement pstmt = connection.prepareStatement(InsertData);
        int executeUpdate = pstmt.executeUpdate();
        if (executeUpdate > 0) {
            System.out.println("Data Inserted");
        } else {
            System.out.println("Insert ERROR");
        }
        ResultSet rs4 = stmt.executeQuery("SELECT Count(*) FROM  Virus");
        int numberOfData = rs4.getInt(1);
        System.out.println(numberOfData);

        ResultSet rs3 = stmt.executeQuery(selectQuery);
        ResultSetMetaData metaData = rs3.getMetaData();

        DatabaseMetaData DmetaData = connection.getMetaData(); 
        String[] types = { "TABLE" };
        ResultSet rs5 = DmetaData.getTables(null, null, "Virus", types);
        List<String> ColNameList = new ArrayList<String>();
        while (rs5.next()) {
            String tableName = rs5.getString("TABLE_NAME");
            ResultSet columnRs = DmetaData.getColumns(null, null, tableName, null);
            while (columnRs.next()) {
                String columnName = columnRs.getString("COLUMN_NAME");
                ColNameList.add(columnName);
            }
            System.out.print("|" + ColNameList.get(0) + "             |");
            System.out.print(ColNameList.get(1) + "  |");
            System.out.print(ColNameList.get(2) + "|");
            System.out.print(ColNameList.get(3) + "|");
            System.out.print(ColNameList.get(4) + "　|");
            System.out.print(ColNameList.get(5) + "　|");
            System.out.print(ColNameList.get(6) + "　 |");
            System.out.print(ColNameList.get(7) + "　 |");
            System.out.print(ColNameList.get(8) + "|");
            System.out.print(ColNameList.get(9) + "　　　　   |");
            System.out.print(ColNameList.get(10) + " |");
            System.out.print(ColNameList.get(11) + "|");
            System.out.print(ColNameList.get(12) + " |");
            System.out.print(ColNameList.get(13) + "  |");
            System.out.print(ColNameList.get(14) + "       |");
            System.out.print(ColNameList.get(15) + "");
        }
        System.out.println();

        while (rs3.next()) {
            coList1.add(rs3.getString(1));
            coList2.add(rs3.getString(2));
            coList3.add(rs3.getString(3));
            coList4.add(rs3.getString(4));
            coList5.add(rs3.getString(5));
            coList6.add(rs3.getString(6));
            coList7.add(rs3.getString(7));
            coList8.add(rs3.getString(8));
            coList9.add(rs3.getString(9));
            coList10.add(rs3.getString(10));
            coList11.add(rs3.getString(11));
            coList12.add(rs3.getString(12));
            coList13.add(rs3.getString(13));
            coList14.add(rs3.getString(14));
            coList15.add(rs3.getString(15));
            coList16.add(rs3.getString(16));
            coList17.add(rs3.getString(17));
        }

        for (int p = 0; p < 20; p++) { // coList9.size(
            System.out.print("|" + coList1.get(p) + "|");
            String str2 = coList2.get(p);
            if (str2.length() < 3) {
                String blank = "　";
                String repeated = new String(new char[(3 - str2.length())]).replace("\0", blank);
                System.out.print(repeated + coList2.get(p) + "|");
            } else {
                System.out.print(coList2.get(p) + "|");
            }
            System.out.print("　" + coList3.get(p) + "|");
            System.out.print(coList4.get(p) + "|");
            System.out.print(coList5.get(p) + "|");
            System.out.print(coList6.get(p) + "|");
            System.out.print(coList7.get(p) + "|");
            System.out.print(coList8.get(p) + "|");
            String str = coList9.get(p);
            if (str.length() < 5) {
                String blank = "　";
                String repeated = new String(new char[(5 - str.length())]).replace("\0", blank);
                System.out.print(repeated + coList9.get(p) + "|");
            } else {
                System.out.print(coList9.get(p) + "|");
            }
            System.out.print(coList10.get(p) + "|");
            System.out.print(coList11.get(p) + "|");
            String str12 = coList12.get(p);
            if (str12.length() < 5) {
                String blank = " ";
                String repeated = new String(new char[(6 - str12.length())]).replace("\0", blank);
                System.out.print(repeated + coList12.get(p) + "|");
            } else {
                System.out.print(coList12.get(p) + "|");
            }
            String str13 = coList13.get(p);
            if (str13.length() < 5) {
                String blank = " ";
                String repeated = new String(new char[(4 - str13.length())]).replace("\0", blank);
                System.out.print(repeated + coList13.get(p) + "|");
            } else {
                System.out.print(coList12.get(p) + "|");
            }
            System.out.print(coList14.get(p) + "|");
            System.out.print(coList15.get(p) + "|");
            System.out.print(coList16.get(p) + "|");
            System.out.print(coList17.get(p) + "|");
            System.out.println();
        }
        rs3.beforeFirst();
        StringBuilder builder = new StringBuilder();
        builder.append("CaseID").append(",").append("Age").append(",").append("Gender").append(",").append("City")
                .append(",").append("SampleDate").append(",").append("VirusType").append(",")
                .append("SubType").append(",").append("Locus").append(",").append("Primer").append(",").append("GeneDirection")
                .append(",").append("TypingMethod").append(",").append("DNASeq").append(",").append("AminoAcidSeq");
        System.out.println(rs3.next());
        while (rs3.next()) {
            builder.append(System.lineSeparator());
            builder.append(rs3.getString(1)).append(",").append(rs3.getString(2)).append(",")
                    .append(rs3.getString(3)).append(",").append(rs3.getString(5)).append(",")
                    .append(rs3.getString(7)).append(",").append(rs3.getString(10)).append(",")
                    .append(rs3.getString(11)).append(",").append(rs3.getString(12)).append(",")
                    .append(rs3.getString(13)).append(",").append(rs3.getString(14)).append(",")
                    .append(rs3.getString(15)).append(",").append(rs3.getString(16)).append(",").append(rs3.getString(17)).append(",");
        }

        bw.write(builder.toString());
    } catch (IOException e) {

        e.printStackTrace();
    } catch (SQLException e1) {
        e1.printStackTrace();
    }

}

}

Answer 1

开始逐步重构您的代码，然后根据需要迭代改进您的设计。

正如您提到的算法方法，利用它应该是您的开始步骤。

1. 来自网址的 CSV 文件，
2. 然后我批量插入到sql数据库中，
3. 然后从 SQL Server 调用数据
4. 在 Java 控制台上显示。
5. 最后选择我要保留的列
6. 并导出为新的 CSV 文件。

每个步骤的小辅助函数。了解更多关于 SOLID设计原则(如果有助于改进您的解决方案)。