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.

Perhaps you could remove the ambiguity of whether you want to eliminate the long strings from your code entirely (i.e. move them to a text file) or just want to refactor them in to a single, manageable place? — Charles Goodwin, Jul 25 '11 at 11:18
Linked http://stackoverflow.com/questions/370818/cleanest-way-to-build-an-sql-string-in-java in my answer below. It's probably the same question. — Manny, Jul 25 '11 at 11:50

score 13 · Accepted Answer · answered Jul 25 '11 at 11:34

13

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.

answered Jul 25 '11 at 11:34

Konrad Garus

53,145
43
157
230

Where is the implementation of `loadResource` ? – Stephan Jul 25 '11 at 11:37
1

could be based on `Queries.class.getResourceAsStream()`. – Thilo Jul 25 '11 at 11:41
@Stephan We know how to read file in Java. Could be `FileInputStream` or `getClass().getResource()`. – Konrad Garus Jul 25 '11 at 11:41
so this will effectively read the same static sql contents every time it is called, do not do this! this is probably the absolutely worst way you could do this. – Aug 28 '17 at 18:55

Adam Matan · Answer 2 · 2011-07-25T14:01:33.547

3

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

edited Jul 25 '11 at 14:01

answered Jul 25 '11 at 11:08

Adam Matan

128,757
147
397
562

1

I think the question was mostly about how to easily put the SQL into a text file. – Thilo Jul 25 '11 at 11:11
@Thilo: he does not mention text files anywhere though. – Charles Goodwin Jul 25 '11 at 11:13
1

how can you have multi-lines query ? – Stephan Jul 25 '11 at 11:31
@Stephan, changed my module to ini4j which supports multi line values. – Adam Matan Jul 25 '11 at 13:15
@stephan Thanks for the comment and compliments. – Adam Matan Jul 25 '11 at 13:56

score 2 · Answer 3 · answered Jul 25 '11 at 11:12

2

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.

answered Jul 25 '11 at 11:12

djna

54,992
14
74
117

1

I'd agree if only Java had multi-line string literals. As of now, I'd externalize it just to be able to read/edit/format it more easily. – Thilo Jul 25 '11 at 11:17
2

the further the sql is from the rest of the 'code' the harder it is to figure out what is going on... – Randy Jul 25 '11 at 11:18
I find that my SQL tends to be breakable across lines quite nicely. So "SELECT ...." + newline + "FROM ..." + newline + "WHERE" etc is quite readable. – djna Jul 26 '11 at 15:24
@Randy - all a matter of taste. Well named variables tend to help, and then I find having my query strings together lets me thing about queries en-masse. I like my code in short sections of like-minded stuff. – djna Jul 26 '11 at 15:26

score 1 · Answer 4 · edited May 23 '17 at 12:00

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

score 0 · Answer 5 · edited Jan 22 '13 at 17:29

0

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

edited Jan 22 '13 at 17:29

David L

32,885
8
62
93

answered Jan 22 '13 at 17:02

user2001125

1

Is this a question or an answer? It's unclear and if it is an answer, an illustration or external reference would be helpful to the original poster. – David L Jan 22 '13 at 17:27

score 0 · Answer 6 · answered Jul 25 '11 at 11:12

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

@Thilo: ah, I see where you are coming from now although the OP is a bit ambiguous about whether he wants to eliminate the long queries entirely from the code or just from the point of use in the code. — Charles Goodwin, Jul 25 '11 at 11:17

Alexey Grigorev · Answer 7 · 2011-07-25T11:44:40.283

0

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, ...);

edited Jul 25 '11 at 11:44

answered Jul 25 '11 at 11:26

Alexey Grigorev

2,415
28
47

How does `getSomethingYouNeed` execute the query ? – Stephan Jul 25 '11 at 11:33
It depends on how you will implements it. You may include this query into your method and do the thing you described above. – Alexey Grigorev Jul 25 '11 at 11:42

score -1 · Answer 8 · answered Feb 07 '14 at 23:22

-1

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

answered Feb 07 '14 at 23:22

Nestor Hernandez Loli

1,412
3
21
26

Can you provide an example ? – Stephan Feb 08 '14 at 03:12
@Alex, of course, here a good page http://loianegroner.com/2011/02/getting-started-with-ibatis-mybatis-xml-configuration/ – Nestor Hernandez Loli Feb 08 '14 at 04:50
@Alex take a look to Contact.xml – Nestor Hernandez Loli Feb 08 '14 at 04:50
Well MyBatis has many benefits over plain JDBC: SQL and Java code separation, Cache, Named parameters, Simple design with generics using Mappers, etc. – Nestor Hernandez Loli Feb 08 '14 at 04:52
1

Please read [How do I write a good answer?](http://stackoverflow.com/help/how-to-answer) before attempting to answer more questions. – Aug 28 '17 at 19:09

Java - Avoiding long SQL query in code

8 Answers8

Datastructure perspective

Keep your configuration away from your code