Java - Avoiding long SQL query in code Java - Avoiding long SQL query in code sql sql

Java - Avoiding long SQL query in 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");    }}



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