java - How do I separate Big chunk of code of JDBC into different classes?

问题描述

I wrote a big chunk of codes that downloads CSV file from url, then i bulk inserted into sql database, then call data from SQL server and display on Java console. Finally select the column I want to keep and export as a new CSV file. but all of those codes are in the same class right now. How can I separate them into different class like I want a class for just download file and another class just do the Bulk insert and another class to just do the Select Query. Thanks for helping me out below is my code in one class now
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();
    }

}
}
标签： javasqljdbc
java - How do I separate Big chunk of code of JDBC into different classes?

问题描述

解决方案

推荐阅读
