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