8

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)

Improve this question
12
  • 8
    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
    CommentedJul 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');
    – Jared Smith
    CommentedJul 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.
    – Jared Smith
    CommentedJul 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
    CommentedJul 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
    CommentedJul 5, 2017 at 18:38

6 Answers 6

Reset to default
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); 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 multi-line values for properties. It is indeed interpreted 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 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:

SELECT v.customer_representative_id, v.first_name, //other columns FROM my_reps_view v WHERE v.customer_table.id=?
Improve this answer
4
  • 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
    CommentedJul 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
    CommentedSep 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
    CommentedSep 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
    CommentedAug 21, 2018 at 13:25
5

Text Blocks

Java 15+ gained the Text Blocks feature. This feature is a revamp of the Raw String Literals feature discussed below.

See also: Does Java have support for multiline strings?

I will leave intact the outmoded content below as history. And I’ll add a mention of making your IDE aware of your SQL-within-Java.

Language-savvy strings

The IntelliJ IDE provides for language injections. See this article and video by Helen Scott. You can flag the embedded language by using either annotation or a comment.

Language injection works well with Java text blocks.

@Language ("SQL") String sql = """ SELECT * FROM product_ ; """;

IntelliJ will colorize the embedded language. And you can open a dedicated editor section to provide for language-appropriate editing.

Language injection works for SQL, JSON, RegExp, and more.

Other IDEs may provide a similar feature.

[Outmoded content below left intact as history.]

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.

Improve this answer
    1

    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 } }
    Improve this answer
      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

      Improve this answer
        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();
        Improve this answer
          -1

          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"
          Improve this answer

            Start asking to get answers

            Find the answer to your question by asking.

            Ask question

            Explore related questions

            See similar questions with these tags.