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

Question

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)

Answer 1

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 everyline makes 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 kinds 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=?

Answer 2

Raw String Literals

Work is underway to introduce raw string literals into the Java language. Along with that comes the multi-line strings desired in this Question.

JEP 326

See JEP 326: Raw String Literals.

Excerpts:

Add raw string literals to the Java programming language. A raw string literal can span multiple lines of source code and does not interpret escape sequences, such as \n, or Unicode escapes, of the form \uXXXX …

supply strings targeted for grammars other than Java, …

supply strings that span several lines of source without supplying special indicators for new lines.

Nesting SQL is one of the example use-cases given in the JEP.

Status update by Brian Goetz

See Raw string literals -- where we are, how we got here by Brian Goetz, 2018-03-27.

They are leaning towards using any number of backticks as the raw-string-literal starting-stopping delimiters. A single backtick would be most common, but use a number of them if the text value happens to contain backticks. So no escaping needed at all.

Excerpts:

… things have largely stabilized with raw string literals …

    String ss = `a multi-
     line-string`;

---

By the way, you may find the article, New Methods on Java Strings With JDK 11 by Dustin Marx to be interesting. It covers some new Unicode-savvy string-trimming methods being added to Java 11. These may be related to the eventual raw string literals feature.

Answer 3

Just for the sake of completness you could add an extra step to compilation to make it possible to inject the value from the javadoc comment like by using multiline-string project and have something like:

/**
 * SELECT 'Hello world'
 * FROM dual
 */
@Multiline
public static String getHelloWorld;

It will replace the annotation at compile time with

public static String getHelloWorld= "SELECT 'Hello world'\nFROM dual";

And just in case that you have some concerns I've used it with a JPA entity manager like

javax.persistence.Query qry = em.createQuery(QueryRepository.getHelloWorld); 

where QueryRepository is a conventional class for constants.

Tutorial on development and usage: http://www.adrianwalker.org/2011/12/java-multiline-string.html

Answer 4

If you don't want the overhead of a "fluent"-style Java query builder (like jOOQ or something), I've used static strings for queries formatted like so:

public class SQLHandler {
    private static final String REPRESENTATIVES_QUERY =
        " SELECT" +
        "   representative_table.id," +
        "   representative_table.first_name," +
        " FROM" +
        "   customer_table" +
        " JOIN representative_table ON" +
        "   representative_table.id = customer_table.id" +
        " WHERE" +
        "   customer_table.id = ?";

    public static ArrayList<Representatives> getRepresentatives(int customerId) {
        // do query
    }
}

Answer 5

For large complicated queries I usually use a string template. This way, when I want to change the query, it's just a big .sql file that I can edit with syntax highlighting to boot. There's a lot of different string template libraries around I'll leave it generalized.

It's definitely slower to process the template than it is to concatenate a bunch of strings, but sometimes it's worth it to me for the ease of maintenance.

String Template

SELECT *
FROM table1
JOIN table2 ON table1.this = table2.that
WHERE table1.foo = ?
AND table2.baz = ?

Java

StringTemplate template = new MyQueryTemplate().
String query = template.process();
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, "bar");
statement.setString(2, "boo");
ResultSet resultSet = statement.executeQuery();

Answer 6

See: Multiple-line-syntax

It also support variables in multiline string, for example:

String name="zzg";
String lines = ""/**~!{
    SELECT * 
        FROM user
    WHERE name="$name"
}*/;
System.out.println(lines);

Output：

SELECT * 
    FROM user
    WHERE name="zzg"