Java - Avoiding long SQL query in code

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 开发者_JS百科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.

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.

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

I would just make them

 static final String someMeaningfulName = " ... ";

Externalising to a text file such as a resource bundle would work, but I'm not convinced that it is necessary, or even a good idea as it might lead to a way of thinking that these are not really "code" and hence changes don't really need testing.

A simple solution would be to use the normal properties file, answer is from Cleanest way to build an SQL string in Java

Only problem is that new line needs to be separated by "\" e.g.

CURRENT_DATE=select sysdate \
from dual

then you can use

Queries.getQuery("CURRENT_DATE");

Yes, "\" is still ugly but it's cleaner and easier to format compared to using Java's String / StringBuilder concatenation, imo.

If you want to support a cleaner format, maybe you can create your own parser or use XML format. But I think it's an overkill.

Off topic: Gotta love Groovy's multiline String (shameless):

public static final String MY_QUERY = """\
  select col1, col2
  from table1
  where col1=:param1
""";

A HashMap would be simple, since you want to map from a query name/key to a query/value. Any Map would do really.

public class Queries extends HashMap {
    public Queries() {
        add("My long query",
            "Super long..."+
            "...long long..."+
            "...long query.");
        // add others
    }
}

You could use a singleton if you wanted to keep it static.

public class Queries {
    private static HashMap store = new HashMap();
    {
        // constructor
        add("My long query",
            "Super long..."+
            "...long long..."+
            "...long query.");
        // add others
    }
    public String getQuery(String queryName) { return store.get(queryName); }

Or you could just use static Strings as suggested by djna:

public class Queries {
    final public static myQuery = "My long query";
}

public class MyProgram extends Queries {
    ...
    public void someMethod() {
        ...
        doQuery(myQuery);
        ...
    }
}

Maybe the problem is in the structure of your application. Do you separate your java classes into "dao", "service", etc packages?

If you organize your project, you won't need to call ResultSet rs = statement.executeQuery( all_queries.getQuery("The very long one") ), but instead to call Result res = dao.getSomethingYouNeed(param1, param2, ...);

If we write multiple queries in text file (not in properties file), we can retrieve or fetch a single query out of all.

MyBatis does this out of the box and works like champ!