Where should I put the SQL files in my Java project?

Question

I have a Java project which will include a number of large SQL statements for querying the database. My question is: where should I store them?

I'm fairly sure I want each statement its own text file managed by source code control. As Java doesn't support multi-line strings I can't easily put the SQL in my .java files and I don't think I'd want to anyway. At build time I can put these text files in JAR and get the contents with ClassLoader.getResourceAsStream().

So my problem becomes in which directories should I put these files and what should I call them. Ideally I'd like people to tell from the .sql file which Java class uses it. What I definitely don't want is a single directory full of lots of files called things like report1.sql and report3.sql and so on.

My inclination is to put them in the package directory with all the .java files but I have a colleague who doesn't like having anything other than .java files in this tree. So this leads to alternative of a separate directory structure which mirrors the Java packages but this seems like an unnecessary overhead.

So I would be interested to hear what you do with your SQL files.

We're using Netbeans 6.5 in case that will affect your answers.

(This question is similar but sadly the answers are very C# specific, which is good for that question but bad for me.)

Answer 1

In a Java/Maven setting we use as project hierarchy:

project/src/main/java/Package/Class.java
project/src/test/java/Package/ClassTest.java
project/src/main/resources/Package/resource.properties
project/src/test/resources/Package/test_resource.properties

And in order to answer your question: I would put the SQL-files along with the resources under src/main/resources.

You may want to have a look at this thread.

Answer 2

I totally agree with boutta. Maven sets a good standard for src folder management. Have you considered storing your SQL in XML? Keeping the queries in a single file may make it easier to manage the SQL. My first intuition is something simple:

<?xml version="1.0" encoding="UTF-8"?>
<queries>
    <query id="getUserByName">
        select * from users where name=?
    </query>
    <query id="getUserByEmail">
        select * from users where email=?
    </query>
</queries>

To parse the file, use xpath or even SAX to create a map of queries keyed by the id for fast query lookups.

Answer 3

I'd be tempted to put the SQL queries in a dedicated SQL folder under src. This separates the Java code from the SQL:

+ src
  + java 
  + sql
     - Package/Class.sql
+ test

Alternatively you could put them into simple properties files using the above structure:

getUserByName = select * from users where name=?

getUserByEmail = select * from users where email=?

getUserByLongQuery = select * from users where email=? \
   and something = ? \
   where something_else = ?

Also, I think it's worth mentioning that you can put multi-line strings into a Java class if you prefer to take that route:

class MyClass {
    MY_QUERY = "select * from users where email = ? " + 
               "and something_else = ?";
}

Answer 4

is that considered best practice? when writing hql/sql we write it directly in the Dao .java files.

when writing more complex sql we simply write it like

 String sql =
  "select * form foo" +
  " where a=?"+
  " and b=?";

note that i use the whitespace at the beginning of the line, this reduces my own error rate :)

most IDE give you full syntax highlighting also in concatenated strings.

having more project artifacts introduces unnecessary complexity.

Answer 5

This is a slight riff on the initial question, but arguably the more complex the SQL is the more it belongs in the database (or in a services layer) and not in your Java code.

Otherwise, as code matures, issues like this arise.