In general, where to place SQL queries in an application? [duplicate]

Question

what is the best place to place SQL queries in an application?

The queries might be big and requires formatting.

Appending the query using StringBuilder looks very cluttered.

Storing them in files and reading them every time when a request is made - looks like a bad idea.(but i think reading from the file can be put in a static block)

Answer 1

Static queries -- the ones which are depend only on binding parameters -- are perfectly fit in *DAO classes, which abstract DB access away -- you only deal with DAO API like loadUser(int userId) or saveUser(User user). This way how queries are stored in the DAO isn't a big question, do as you like.
I don't use dynamic queries usually, so I can't give good advice about them.

Answer 2

Keep the SQL query in a resource file that you read to a constant at class load time:

private static final String PERSON_QUERY;

static{
    InputStream str = null;
    try{
        str = ThisClass.class.getResourceAsStream("/path/to/query.sql");
        PERSON_QUERY = IOUtils.toString(str);
    }catch(IOException e){
        throw new IllegalStateException("Failed to read SQL query", e);
    }finally{
        IOUtils.closeQuitely(str);
    }

}

That way you can use your favorite editor to edit the SQL, but you still get the query in a constant in java.

If you do this a lot, extract the code to a helper method:

public static String loadResourceToString(final String path){
    final InputStream stream =
        Thread
            .currentThread()
            .getContextClassLoader()
            .getResourceAsStream(path);
    try{
        return IOUtils.toString(stream);
    } catch(final IOException e){
        throw new IllegalStateException(e);
    } finally{
        IOUtils.closeQuietly(stream);
    }
}

and use that in your static blocks:

private static final String PERSON_QUERY;
private static final String ADDRESS_QUERY;
private static final String AGE_QUERY;

static{
    PERSON_QUERY = Helper.loadResourceToString("queries/personQuery.sql");
    ADDRESS_QUERY = Helper.loadResourceToString("queries/addressQuery.sql");
    AGE_QUERY = Helper.loadResourceToString("queries/ageQuery.sql");
}

In my opinion, different languages should always be separated. It's an awful practice to assemble SQL, HTML, XML, JavaScript etc. from Java code. Use plain templates or template engines like Velocity whenever possible. That gives you many benefits, one of them being that you can change the template without recompiling the java class.

PS: I am using Apache Commons / IO in the above code, but it's not necessary, just easier.