4

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)

  • 5
    Are you really gaining anything by using a string builder/query builder approach instead of sticking the actual query directly into the code? Also, seems pointless to append "_table" to table names. – GrandmasterB Jul 29 '16 at 17:22
  • 1
    @JaredSmith:Isn't it too much for such a simple case? – Jim Jul 29 '16 at 18:00
  • 5
    No. First, its a bad habit. Second, it cuts against the grain of the language: in case the 8 million calls to .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. – Jared Smith Jul 29 '16 at 18:04
  • 3
    As multiple comments and both answers (so far) have pointed out, never put your parameters in the query. That is setting yourself up for a SQL injection attack. Always use SQL parameters with a java.sql.PreparedStatement. It is more work, but infinitely more secure (your current way has zero security). – user22815 Jul 29 '16 at 19:28
  • 3
    No again! This is an extraordinary bad idea. You might gain some nanoseconds by creating the query faster and you will (probably) lose milliseconds more when the query gets executed. Then you change customerId to customerEmail and my email is bye 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. – maaartinus Jul 5 '17 at 18:38
4

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=?
  • I usually use this approach, but typically I leave the selected columns in a string constant that is separated by commas. The columns may change (you may need to add few during the lifetime of the program), but the table names usually don't change, and the join columns are also set in stone usually. If you join multiple tables, do a static columns(String) function returning String that substitutes the "c." part into the return value. The rationale is that you may need many queries that select the same set of columns, because they return the same types of objects. – juhist Jul 6 '17 at 18:16
  • 2
    +1. Note that this is no less efficient than using a StringBuilder, because the compiler converts it to code that uses a StringBuilder in pretty much exactly the same way as OP's code. – Jules Sep 15 '17 at 16:14
  • 2
    @Jules Consecutive String constants (literals and final Strings of literals) are concatenated at compile time. If the entire String is constant, no StringBuilder is instantiated. While you are correct that the above example is no less efficient, it can be more efficient to use concatenation in this particular case. In practice, the difference is likely negligible. – Eric Sep 20 '17 at 17:03
  • My personal preference is to store queries in external .sql files and load them via Class#getResourceAsStream(). This is especially helpful if your editor supports syntax highlighting. – Greg Brown Aug 21 '18 at 13:25
3

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.

1

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

0

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
    }
}
0

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();
0

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"

protected by gnat Sep 15 '17 at 15:42

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.