我应该将SQL文件放在Java项目中的哪个位置？

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?

我有一个Java项目，它将包含许多用于查询数据库的大型SQL语句。我的问题是：我应该在哪里存储它们？

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

我很确定我希望每个语句都有自己的源代码控制管理的文本文件。由于Java不支持多行字符串，我不能轻易地将SQL放在我的.java文件中，而且我认为无论如何我都不想。在构建时，我可以将这些文本文件放在JAR中，并使用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.

所以我的问题变成了我应该在哪些目录中放置这些文件以及我应该将它们称为什么。理想情况下，我希望人们从.sql文件中告诉哪个Java类使用它。我绝对不想要的是一个充满大量文件的目录，例如report1.sql和report3.sql之类的东西。

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.

我倾向于将它们放在包目录中，包含所有.java文件，但我有一个同事不喜欢在这个树中使用除.java文件以外的任何东西。所以这导致了一个独立的目录结构的替代，它反映了Java包，但这似乎是一个不必要的开销。

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

所以我很想知道你对SQL文件做了什么。

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

我们正在使用Netbeans 6.5，以免影响您的答案。

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

（这个问题很相似，但遗憾的是答案非常具有C＃，这对这个问题有好处，但对我不好。）

5 个解决方案

#1

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

在Java / Maven设置中，我们将其用作项目层次结构：

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.

并且为了回答你的问题：我会将SQL文件与src / main / resources下的资源一起放入。

You may want to have a look at this thread.

你可能想看一下这个帖子。

#2

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

我很想将SQL查询放在src下的专用SQL文件夹中。这将Java代码与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:

另外，我认为值得一提的是，如果您更喜欢采用该路由，可以将多行字符串放入Java类中：

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

#3

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:

我完全同意布塔。 Maven为src文件夹管理设置了一个很好的标准。您是否考虑过将XML存储在XML中？将查询保存在单个文件中可以使管理SQL更加容易。我的第一个直觉是简单的：

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

要解析文件，请使用xpath甚至SAX创建由id键入的查询映射，以便快速查询查询。

#4

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

为了与http://maven.apache.org/pom.html#Resources保持一致，这个地方可能是以下之一：

src/main/sql
的src /主/ SQL
src/main/upgrade - for upgrade scripts between versions
src / main / upgrade - 用于版本之间的升级脚本
src/main/db, src/main/schema, src/main/ddl - for current project DB schema, for initial project deployment
src / main / db，src / main / schema，src / main / ddl - 用于当前项目数据库模式，用于初始项目部署
etc, just put to src/main/NAME directory
等，只需放到src / main / NAME目录

Other things to consider:

其他需要考虑的事项：

FlyWay DB migration tool assume that upgrade Vxx__YYY.sql scripts located in classpath:resources/db/migration so you place them into project src/main/resources/db/migration directory.
FlyWay数据库迁移工具假设升级Vxx__YYY.sql脚本位于类路径：resources / db / migration中，因此您将它们放入项目src / main / resources / db / migration目录中。
LiquiBase DB migration tool recommends Choose a package in your classpath to store the changelogs, preferably near your database access classes
LiquiBase数据库迁移工具建议在类路径中选择一个包来存储更改日志，最好是在数据库访问类附近

Note that IDE doesn't support listening directories other then src/main/java and src/main/resources in project viewer, for that use file viewer.

请注意，对于该用户文件查看器，IDE不支持项目查看器中除src / main / java和src / main / resources之外的侦听目录。

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

对于最初的问题，这是一个轻微的重复，但可以说，SQL越复杂，它就越多地存在于数据库（或服务层）中，而不是Java代码中。

Otherwise, as code matures, issues like this arise.

否则，随着代码的成熟，出现了类似的问题。

#1