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

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

提问by Dave Webb

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 .javafiles 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 .sqlfile which Java class uses it. What I definitely don't want is a single directory full of lots of files called things like report1.sqland report3.sqland so on.

所以我的问题变成了我应该将这些文件放在哪些目录中以及我应该如何称呼它们。理想情况下，我希望人们从.sql文件中知道哪个 Java 类使用它。我绝对不希望是一个目录充满大量的文件叫之类的report1.sql和report3.sql等。

My inclination is to put them in the package directory with all the .javafiles but I have a colleague who doesn't like having anything other than .javafiles 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 questionis similar but sadly the answers are very C# specific, which is good for that question but bad for me.)

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

采纳答案by boutta

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.

你可能想看看这个线程。

回答by Olly

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

我很想将 SQL 查询放在SQLsrc 下的专用文件夹中。这将 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 = ?";
}

回答by gavenkoa

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

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

• src/main/sql
• src/main/upgradeor 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/NAMEdirectory
• src/main/resources/NAMEis a quickest way to put SQL files to classpath as Maven/Gradle copies everything from ``src/main/resources/` to final artifact by default.

• src/main/sql
• src/main/upgrade或srv/main/migrate- 用于在版本之间升级/迁移脚本
• src/main/db, src/main/schema, src/main/ddl- 用于当前项目数据库架构，用于初始项目部署
• 等，只需放入src/main/NAME目录
• src/main/resources/NAME是将 SQL 文件放入类路径的最快方法，因为默认情况下 Maven/Gradle 会将所有内容从“src/main/resources/”复制到最终工件。

Other things to consider:

其他需要考虑的事项：

• FlyWay DB migration tool assume that upgrade Vxx__YYY.sqlscripts located in classpath:resources/db/migrationso you place them into projectsrc/main/resources/db/migrationdirectory.
• LiquiBase DB migration tool recommends Choose a package in your classpath to store the changelogs, preferably near your database access classes

• FlyWay DB 迁移工具假定升级Vxx__YYY.sql脚本位于 中，classpath:resources/db/migration因此您将它们放入项目src/main/resources/db/migration目录中。
• LiquiBase DB 迁移工具推荐在您的类路径中选择一个包来存储更改日志，最好靠近您的数据库访问类

IDE may not support directories other then src/main/javaand src/main/resourcesin project viewerin a project browser, use file viewerinstead.

IDE可能不支持其他目录，然后src/main/java和src/main/resources在项目浏览器在项目浏览器中，使用文件浏览器来代替。

回答by jon077

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 文件夹管理设置了一个很好的标准。您是否考虑过将 SQL 存储在 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 键控的查询映射，以便快速查询查找。

回答by Alkini

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

否则，随着代码的成熟，会出现这样的问题。