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();
       .append(REP_FIRST_NAME).append(" FROM")
       .append(CUSTOMER_TABLE).append(" JOIN ").append(REP_TABLE)   
      .append(" AND")  

    // 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)

  • 7
    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. Commented Jul 29, 2016 at 17:22
  • 1
    @Jim Is XML data in a config file a string? That may be its datatype when you read the file, but would you try to manipulate it with string functions and regexes? No. You parse it into a data structure with an XML parser. Likewise SQL is a string with programmatic meaning, if you're manipulating it as a string you're doing it wrong. A query builder with a fluent interface is a good alternative: queryBuilder.select(['title', 'isbn']).from('books').where('author').is('Salman Rushdie'); Commented Jul 29, 2016 at 17:55
  • 6
    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. Commented Jul 29, 2016 at 18:04
  • 4
    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
    Commented Jul 29, 2016 at 19:28
  • 5
    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
    Commented Jul 5, 2017 at 18:38

7 Answers 7


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 makes for a nice printing for debugging purposes.

Then, use a PreparedStatement to substitute "?" thus preventing SQL injection:

PreparedStatement ps = con.prepareStatement(sql);
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 multi-line values for properties. It is indeed interpreted as a single line)

    c.customer_representative_id, \
    c.first_name \
    customer_table c JOIN representative_table r ON \
    r.customer_representative_id=c.id \
    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 uses 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 the property file, like:

        //other columns
    my_reps_view v
  • 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
    Commented Jul 6, 2017 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
    Commented Sep 15, 2017 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
    Commented Sep 20, 2017 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
    Commented Aug 21, 2018 at 13:25

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.


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.


… things have largely stabilized with raw string literals …

    String ss = `a multi-

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.


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

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


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

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


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

See: Multiple-line-syntax

It also support variables in multiline string, for example:

String name="zzg";
String lines = ""/**~!{
    SELECT * 
        FROM user
    WHERE name="$name"


    FROM user
    WHERE name="zzg"

Forget about everyone trying to help you build strings. What you are doing is voluntarily creating a HUGE sql injection target. Hackers will love you. You are giving them everything they need to break into your database, extract your data, or change your data.

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