I have some SQL commands that I am trying to figure out the best way to have them in code so that:
- They are very readable
- They would be easy to update
- They won't be performance overhead due to many string construction.
I have something like the following that did not turn out too good.
public class SQLHandler {
private static final String CUSTOMER_TABLE = "customer_table";
private static final String CUSTOMER_ID = "id";
private static final String CUSTOMER_FIRST_NAME = "first_name";
private static final String CUSTOMER_LAST_NAME = "last_name";
private static final String CUSTOMER_TELEPHONE = "customer_telephone";
private static final String REP_ID = "customer_representative_id";
private static final String REP_TABLE = "representative_table";
private static final String REP_ID = "id";
private static final String REP_FIRST_NAME = "first_name";
private static final String LAST_LAST_NAME = "last_name";
private static final String DEPT_ID = "rep_dep_id";
public static ArrayList<Representatives> getRepresentatives(int customerId) {
StringBuilder sb = new StringBuilder();
sb.append("SELECT")
.append(REP_TABLE).append(".").append(REP_ID)
.append(",")
.append(REP_TABLE)
.append(".")
.append(REP_FIRST_NAME).append(" FROM")
.append(CUSTOMER_TABLE).append(" JOIN ").append(REP_TABLE)
.append("ON").append(REP_TABLE).append(".")
.append(REP_ID).append("=").append(CUSTOMER_TABLE)
.append(".").append(REP_ID)
.append(" AND")
.append(CUSTOMER_TABLE).append(".").append(CUSTOMER_ID)
.append("=").append(String.valueOf(customerId));
// do query
}
}
}
As you can see none of the (3) are met.
I can't easily update the query and if I saw it again I wouldn't remember exactly what was it.
How can I improve this? (Couldn't format it properly in post)
