Any better approach rather than result.getString()

问题:

I have 6 columns in a table. I have a select query which selects some records from the table. While iterating over the result set, im using the following logic to extract the values in the columns:

Statement select = conn.createStatement();
        ResultSet result = select.executeQuery
        ("SELECT * FROM D724933.ECOCHECKS WHERE ECO = '"+localeco+"' AND CHK_TOOL = '"+checknames[i]+"'");
         while(result.next()) { // process results one row at a time
           String eco = result.getString(1);
           mapp2.put("ECO", eco);
           String chktool = result.getString(2);
           mapp2.put("CHECK_TOOL", chktool);
           String lastchktime = result.getString(3);
           mapp2.put("LAST_CHECK_TIME", lastchktime);
           String status = result.getString(4);
           mapp2.put("STATUS", status);
           String statcmts = result.getString(5);
           mapp2.put("STATUS_COMMENTS", statcmts);
           String details = result.getString(6);
           mapp2.put("DETAILS_FILE", details);
         }            

I have 2 questions here: 1. Is there any better approach rather than using result.getString()??? 2. Lets say, another column gets added to the table at a later point. Is there any way my code handles this new addition without making change to the code at that point of time

回答1:

You can use ResultSetMetaData to determine the number and names of the columns in your ResultSet and deal with it this way. Note however that changing the number of columns in the database - affecting your code - and having the code still work may not always be a good idea.

Additionally, note that you're overwriting the values in your map on each iteration of the loop. You probably want to add those maps to some sort of List?

Finally, you need to make sure that your getString methods will not return null anywhere, otherwise putting it into a map will throw an exception.

Statement select = conn.createStatement();
ResultSet result = select.executeQuery("SELECT * FROM D724933.ECOCHECKS WHERE ECO = '"+localeco+"' AND CHK_TOOL = '"+checknames[i]+"'");
ResultSetMetaData rsmd = result.getMetaData();

int numberOfColumns = rsmd.getColumnCount();    
List data = new ArrayList<Map>();
Map mapp2;

while(result.next()) { // process results one row at a time
    mapp2 = new HashMap<String, String>();
    for(int i=1; i<=numberOfColumns; i++) {
        mapp2.put(rsmd.getColumnName(i), rs.getString(i));
    }
    data.add(mapp2);
}

回答2:

Each of the get family of methods on ResultSet has an overloaded variant that takes a column name as argument. You can use this instead to reduce reliance on ordering of columns.

ResultSet results = ...;
results.getString(1);

You could do this:

results.getString("name");

But the preferred way of handling this sort of problem is to impose an ordering of your own on the result set, by explicitly selecting the columns you want in the initial query.

回答3:

If your table adds a new column, then obviously you have to change your code, because in your code you use hardcoded value, I mean getString(1).

Instead use ResultSetMetaData's getColumnCount and do some other logic to get that many column values dynamically.

Another thing for your first question, ResultSet contains getXXX() methods with two types of parameters, String column name and int column index. You used the index instead of column name which will perform little faster.

回答4:

It is bad practice to use SELECT *, instead you should select only the columns you are interested in. The reason is exactly what you mentioned: What happens if your DB changes. you don't want to go trhough the whole code and find and edit all SELECT * statements.

You don't need to put the result into your own map because you can already do:

result.getString("DETAILS_FILE");

But there are already other answers explaining that.

It would be further helpful to use a constant instead of the string "DETAILS_FILE". You can use the constant in the SELECT and in the result.getString(). In case your DB changes you only need to introduce a new constant or change an existing one.