I have some SQL commands that I am trying to figure out the best way to have them in code so that:
1. They are very readable
2. They would be easy to update
3. 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)
.append
didn't tip you off, the OO version is much more natural in Java. Third, SQL via string concatenation is notoriously prone to SQL injection security vulnerabilities.java.sql.PreparedStatement
. It is more work, but infinitely more secure (your current way has zero security).customerId
tocustomerEmail
and my email isbye bye'; DROP DATABASE
or like (the exact exploit depends on your escaping method). There are many DB-related optimizations out there, but none of them really cares about fast string concatenation. That's orders of magnitude off.