Java – How to create multiline strings for sql queries that are readable, maintainable and fast

clean codejavaperformancestrings

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)

Best Answer

My suggestion is:

public class SQLHandler {  

    private String sql=
    "SELECT \n"+
    "   c.customer_representative_id, \n"+
    "   c.first_name \n"+
    "FROM \n"+
    "   customer_table c JOIN representative_table r ON \n"+
    "   r.customer_representative_id=c.id \n"+
    "WHERE \n"+
    "   customer_table.id=? \n";
 
    // methods and so on...

}

The "\n" characters at the end of every line maks for a nice printing for debugging purposes.

Later user a PreparedStatement to substitute "?" thus preventing sql injection:

PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(customed_id);
ResultSet rs = ps.executeQuery();
/// etc...

Another alternative is reading the SQL String from a file that can be done with the Properties class. Such file can have multiple properties, each one being a different SQL query or other kind of configuration values you want.

Sample file (the " \" is to allow multiline value for a property but it's read as a single line)

SQL1=\
SELECT \
    c.customer_representative_id, \
    c.first_name \
FROM \
    customer_table c JOIN representative_table r ON \
    r.customer_representative_id=c.id \
WHERE \
    customer_table.id=? \

Which can be loaded like this:

import java.util.Properties;
///...
Properties prop = new Properties();
prop.load(new FileInputStream("sql_queries.conf"));
String sql = prop.getProperty("SQL1");

Then user a PreparedStatement ...

Bonus suggestion:

Create views with the joins that you'll be doing frequently, so the queries look simpler in the code or in ther property files, like:

SELECT
    v.customer_representative_id,
    v.first_name,
        //other columns
FROM
    my_reps_view v
WHERE
    v.customer_table.id=?
Related Topic