Java - Avoiding long SQL query in code

Question

In my Java code, I have something like this :

ResultSet rs = statement.executeQuery(
                   "SELECT a,b,c FROM foo -- here starts the long query"+
                   " -- that is not yet finished " +
                   " -- that still has something to say... "+ 
                   " -- now the end !"
               );

I would like to clean up my code like this :

ResultSet rs = statement.executeQuery(all_queries.getQuery("The very long one"));

I have read that ResourceBundle is for localization. So I don't think it matches in my case.

What should all_queries be ?

EDIT : The most important thing for me is to clean up the code.

Answer 1

I would put it in a file with an sql extension and implement Queries like:

Queries {
    static public String getQuery(String name) {
        return loadResource("/com/example/queries/" + name + ".sql");
    }
}

User:

conn.prepareStatement(Queries.getQuery("my_query"));

Of course that's only one way to do it. You can make Queries return Statement, or even use a dynamic proxy to mask it behind a simple Java interface (where proxy handler could create statement, set parameters and run query). Your mileage may vary.

Added benefit: sql files have syntax coloring and are way easier to maintain than Strings in Java.

Answer 2

Datastructure perspective

Since you need a mapping from a key (name) to value (long query), which is achieved using a dictionary (aka map, associative array) datastructure.

Keep your configuration away from your code

You should store your configuration in a file, separate from your code. I recommend the .ini configuration format, which is very readable, can be divided into sections, and has good parser for almost any computer language.

Your configuration file will look like:

[users_queries]    
find_max_user_id = SELECT max(id) 
                   FROM users 
                   WHERE ...
name             = query
...
...

Using the ini4j module, getting your queries would be as easy as:

Ini.Section section = ini.get("users_queries");
String query = section.get("find_max_user_id");