I am using Java 7
Problem: I need to retrieve records from a database table based on a condition, orig_setting = 1. This query could return ~1000 records. Parameters from 1-7 of the Params object are set with this query.
In the next query, I need to retrieve records from the same table based on another condition, curr_setting = 1. This query could return ~900 records. Parameter 8 of Params object is set with this query.
Params object has 9 fields altogether, of which paramId maps to the the primary key of the database table.
Solution proposed by me: I have decided that when query 1 is run, all the records will be stored in a hashmap with the key being the paramId of the Params object and the value being the Params object itself
When the second query is fired, a get in performed on the hashmap based on the param id; if Param object is returned, then paramater 8 is set.
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DisplayGrid {
public static void main(String[] args) {
DisplayGrid displayGrid = new DisplayGrid();
displayGrid.showGridDetails();
}
private void showGridDetails() {
Params paramsDO = null;
Map<Integer, Params> gridData = new HashMap<>();
// This could return ~1000 records
// In the Params object, 8 fields out of 9 are set with one field(param_id) being the primary key
String selectQuery = "SELECT * FROM table1 WHERE orig_setting = 1";
try (Connection connection = DriverManager.getConnection("DB Settings");
Statement stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery(selectQuery);) {
while (resultSet.next()) {
paramsDO = new Params();
paramsDO.setParamId(resultSet.getInt("PARAM_ID"));
paramsDO.setParam1(resultSet.getString("PARAM_1"));
paramsDO.setParam2(resultSet.getString("PARAM_2"));
paramsDO.setParam3(resultSet.getString("PARAM_3"));
paramsDO.setParam4(resultSet.getString("PARAM_4"));
paramsDO.setParam5(resultSet.getString("PARAM_5"));
paramsDO.setParam6(resultSet.getString("PARAM_6"));
paramsDO.setParam7(resultSet.getString("PARAM_7"));
// key is the primary key(param_id)
gridData.put(new Integer(paramsDO.getParamId()), paramsDO);
}
} catch (Exception e) {
throw e;
}
// This could return ~900 records
// from this query, the 9th field is set based on the paramId
selectQuery = "SELECT * FROM table1 WHERE curr_setting = 1";
try (Connection connection = DriverManager.getConnection("DB Settings");
Statement stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery(selectQuery);) {
while (resultSet.next()) {
paramsDO = null;
int currParamId = resultSet.getInt("PARAM_ID");
paramsDO = gridData.get(currParamId);
if (paramsDO != null)
paramsDO.setParam8(resultSet.getString("PARAM_8"));
}
} catch (Exception e) {
throw e;
}
}
private class Params {
private int paramId; // Primary Key
private String param1;
private String param2;
private String param3;
private String param4;
private String param5;
private String param6;
private String param7;
private String param8;
public Params() {
super();
}
public Params(int paramId, String param1, String param2, String param3,
String param4, String param5, String param6, String param7,
String param8) {
super();
this.paramId = paramId;
this.param1 = param1;
this.param2 = param2;
this.param3 = param3;
this.param4 = param4;
this.param5 = param5;
this.param6 = param6;
this.param7 = param7;
this.param8 = param8;
}
public int getParamId() {
return paramId;
}
public void setParamId(int paramId) {
this.paramId = paramId;
}
public String getParam1() {
return param1;
}
public void setParam1(String param1) {
this.param1 = param1;
}
public String getParam2() {
return param2;
}
public void setParam2(String param2) {
this.param2 = param2;
}
public String getParam3() {
return param3;
}
public void setParam3(String param3) {
this.param3 = param3;
}
public String getParam4() {
return param4;
}
public void setParam4(String param4) {
this.param4 = param4;
}
public String getParam5() {
return param5;
}
public void setParam5(String param5) {
this.param5 = param5;
}
public String getParam6() {
return param6;
}
public void setParam6(String param6) {
this.param6 = param6;
}
public String getParam7() {
return param7;
}
public void setParam7(String param7) {
this.param7 = param7;
}
public String getParam8() {
return param8;
}
public void setParam8(String param8) {
this.param8 = param8;
}
@Override
public String toString() {
return "Params [paramId=" + paramId + ", param1=" + param1
+ ", param2=" + param2 + ", param3=" + param3 + ", param4="
+ param4 + ", param5=" + param5 + ", param6=" + param6
+ ", param7=" + param7 + ", param8=" + param8 + "]";
}
}
}
Kindly suggest how this code can be improved to increase the performance. Also, suggest other possible solutions.
What other java data structure can I use for this purpose; could be one from other java libraries too
Posting the sample database table
+----+-----+-------+---------+---------+
| Id | Key | Value | Is_Orig | Is_Prev |
+----+-----+-------+---------+---------+
| 1 | 10 | 1000 | 1 | 0 |
| 2 | 10 | 2000 | 0 | 0 |
| 3 | 10 | 3000 | 0 | 1 |
| 4 | 10 | 4000 | 0 | 0 |
| 5 | 20 | 100 | 1 | 0 |
| 6 | 20 | 300 | 0 | 1 |
| 7 | 20 | 400 | 0 | 0 |
| 8 | 30 | 10 | 1 | 0 |
+----+-----+-------+---------+---------+
Expected result
+-----+------------+------------+
| Key | Orig Value | Prev Value |
+-----+------------+------------+
| 10 | 1000 | 3000 |
| 20 | 100 | 300 |
| 30 | 10 | n/a |
+-----+------------+------------+
