26

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

Community
  • 1
  • 1
David Webb
  • 190,537
  • 57
  • 313
  • 299

5 Answers5

22

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.

Community
  • 1
  • 1
boutta
  • 24,189
  • 7
  • 34
  • 49
  • 1
    I disagree with you, since putting them in src/main/resources they will end up in your packaged application (on your classpath) thus exposing sensible information about the structure of your DB – sashok_bg Oct 05 '17 at 08:15
  • 2
    But exposing these SQL resources in the classpath is exactly what the OP wants. Thus I don't understand your comment. – boutta Oct 05 '17 at 15:47
5

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 = ?";
}
Olly
  • 7,732
  • 10
  • 54
  • 63
  • 4
    I'd argue that isn't a multi-line string; it's a lot of single line strings concatenated together. I realise it's a matter of personal taste, but having to maintain quotes and plus signs over possibly 30 lines of SQL drives me mad. (I like to have lots of line breaks in my SQL so it's readable.) – David Webb Mar 04 '09 at 07:53
  • @Dave In that case, I would opt for one of the my first two suggestions – Olly Mar 06 '09 at 17:43
  • @Olly: Your first two answers address the question; the last (putting raw SQL into the Java source) doesn't. Your first answer could use some elaboration... I like the idea of putting things in per-class files in a resource hierarchy, but how to organize the contents to retrieve a particular query? – andersoj Apr 10 '09 at 01:37
4

To be consistent with http://maven.apache.org/pom.html#Resources this place may be one of:

  • src/main/sql
  • src/main/upgrade or srv/main/migrate - for upgrade/migrate scripts between versions
  • src/main/db, src/main/schema, src/main/ddl - for current project DB schema, for initial project deployment
  • etc, just put to src/main/NAME directory
  • src/main/resources/NAME is a quickest way to put SQL files to classpath as Maven/Gradle copies everything from ``src/main/resources/` to final artifact by default.

Other things to consider:

IDE may not support directories other then src/main/java and src/main/resources in project viewer in a project browser, use file viewer instead.

gavenkoa
  • 45,285
  • 19
  • 251
  • 303
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.

jon077
  • 10,303
  • 11
  • 39
  • 37
  • 3
    While I like the idea of keeping many SQL queries in a single file, one of the benefits of having SQL separate from Java code is readability and IMHO XML is death to readability. – David Webb Mar 04 '09 at 07:55
  • From my experience with with Hibernate, XML is more readable than using annotations. I don't like multi-line properties files, either. What is preferred? – jon077 Mar 04 '09 at 22:55
  • +1 for the maven-derived directory organization; -1 because @dave-webb I agree with you that XML obscures readability unnecessarily – andersoj Apr 10 '09 at 01:39
0

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.

Community
  • 1
  • 1
Alkini
  • 1,449
  • 2
  • 12
  • 26
  • We're using JavaDB and since that requires writing your Stored Procedures in Java I'd still have the same problem of where to put the SQL statements. – David Webb Mar 04 '09 at 07:50