How to optimize this Java JDBC code which selects many rows and columns

I used all kinds of database CURD by the Hibernate before. Actually, I have never used JDBC before Hibernate. So I don’t know anything about JDBC. So the following code is working but I believe it can optimize a lot. This query returns join data from 2 tables and it is huge:-

    private final String COL1 = "COL1";
    private final String COL2 = "COL2";
    private final String COL3 = "COL3";
    private final String COL4 = "COL4";
    private final String COL5 = "COL5";
    private final String COL6 = "COL6";
    private final String COL7 = "COL7";
    private final String COL8 = "COL8";
    private final String COL9 = "COL9";
    private final String COL10 = "COL10";


    public void getDataByPp(String pp) {
    PreparedStatement pst = null;
    ResultSet rset = null;
    try {
        pst = conn.prepareStatement(SELECT_MASTER_AND_FEATURE);
        pst.setString(1, pp);
        rset = pst.executeQuery();

        ArrayList<String> data = new ArrayList<String>();

        Map<Integer, Map<String, String>> mapMap = new HashMap<Integer, Map<String, String>>();
        int index = 0;
        while (rset.next()) {
            Map<String, String> dataMap = new HashMap<String, String>();

            dataMap.put(COL1, rset.getString("COL1"));
            data.add(rset.getString("COL1"));

            dataMap.put(COL2, rset.getString("COL2"));
            data.add(rset.getString("COL2"));

            dataMap.put(COL3, rset.getString("COL3"));
            data.add(rset.getString("COL3"));

            dataMap.put(COL4, rset.getString("COL4"));
            dataMap.put(COL4, rset.getString("COL4"));

            dataMap.put(pp, rset.getString("pp"));
            data.add(rset.getString("pp"));

            dataMap.put(COL5, rset.getString("COL5"));
            data.add(rset.getString("COL5"));

            dataMap.put(COL6, rset.getString("COL6"));
            data.add(rset.getString("COL6"));

            dataMap.put(COL7, rset.getString("COL7"));
            data.add(rset.getString("COL7"));

            dataMap.put(COL8, rset.getString("COL8"));
            data.add(rset.getString("COL8"));

            dataMap.put(COL9, rset.getString("COL9"));
            data.add(rset.getString("STATE"));

            dataMap.put(COL10, rset.getString("COL10"));
            data.add(rset.getString("COL10"));

            mapMap.put(index, dataMap);
            index++;

        }
        for (String val : data) {
            System.out.println("data : " + val);
        }

        for (Integer key : mapMap.keySet()) {
            Map<String, String> dataMap = mapMap.get(key);
            for (String key2 : dataMap.keySet()) {
                System.out.println("key: " + key2 + " val: " + dataMap.get(key2));
            }
        }
    } catch (SQLException e) {
        System.err.format("SQL State: %sn%s", e.getSQLState(), e.getMessage());
    } catch (Exception e) {
        System.out.println("Exception when insert into INTERIOR_ROOMS_FEATURES");
    }
}

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Jason Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Jason
Guest

Well, I notice a fairly long while loop which appears to iterate a set number of “COL”(s) and add them in a consistent way (except “STATE” / “COL9”) which may require an if – but otherwise, all of those fields could be read from an array. Also, I would prefer the diamond operator <> for initializing the generic collections. Like, String[] cols = { COL1, COL2, COL3, COL4, "pp", // COL5, COL6, COL7, COL8, COL9, "STATE", COL10 }; List<String> data = new ArrayList<>(); Map<Integer, Map<String, String>> mapMap = new HashMap<>(); int index = 0; while (rset.next()) { Map<String, String> dataMap… Read more »