Java 编程 - SQL 语句应该存储在哪里？

Java Programming - Where should SQL statements be stored?

提问by Adrian

Where should an JDBC-compliant application store its SQL statements and why?

符合 JDBC 的应用程序应该在哪里存储它的 SQL 语句，为什么？

So far, I managed to identify these options:

到目前为止，我设法确定了这些选项：

• Hardcoded in business objects
• Embedded in SQLJclauses
• Encapsulate in separate classes e.g. Data Access Objects
• Metadata driven (decouple the object schema from the data schema - describe the mappings between them in metadata)
• External files (e.g. Properties or Resource files)
• Stored Procedures

• 硬编码在业务对象中
• 嵌入在SQLJ子句中
• 封装在单独的类中，例如 数据访问对象
• 元数据驱动（将对象模式与数据模式分离——在元数据中描述它们之间的映射）
• 外部文件（例如属性或资源文件）
• 存储过程

What are the “Pros” and “Cons” for each one?

每个人的“优点”和“缺点”是什么？

Should SQL code be considered “code” or “metadata”?

SQL 代码应该被视为“代码”还是“元数据”？

Should stored procedures be used only for performance optimisation or they are a legitimate abstraction of the database structure?

存储过程应该仅用于性能优化还是数据库结构的合法抽象？

Is performance a key factor the decision? What about vendor lock-in?

性能是决定的关键因素吗？什么厂商锁定？

What is better – loose coupling or tight coupling and why?

哪个更好——松耦合或紧耦合，为什么？

EDITED: Thank you everyone for the answers – here is a summary:

编辑：谢谢大家的回答——这里是一个总结：

Metadata driven i.e. Object Relational Mappings (ORM)

元数据驱动，即对象关系映射 (ORM)

Pros:

优点：

• Very abstract - DB server can be switched without the need to change the model
• Wide-spread - practically a standard
• Cuts down the amount of SQL needed
• Can store SQL in resource files
• Performance is (usually) acceptable
• Metadata driven approach
• (Database) vendor independence

• 很抽象——DB服务器可以切换，不需要改变模型
• 广泛 - 几乎是一个标准
• 减少所需的 SQL 数量
• 可以将 SQL 存储在资源文件中
• 性能（通常）是可以接受的
• 元数据驱动的方法
• （数据库）供应商独立性

Cons:

缺点：

• Hides SQL and true developers intentions
• SQL difficult to be reviewed/changed by DBA
• SQL might still be needed for odd cases
• Can force usage of a proprietary query language e.g. HQL
• Does not lend itself to optimisation (abstraction)
• Can lack referential integrity
• Substitutes for lack of SQL knowledge or lack of care to code in the DB
• Never match native database performance (even if it comes close)
• Model code is very tight coupled with the database model

• 隐藏 SQL 和真正的开发人员意图
• DBA 难以/更改的 SQL
• 奇怪的情况可能仍然需要 SQL
• 可以强制使用专有查询语言，例如 HQL
• 不利于优化（抽象）
• 可能缺乏参照完整性
• 替代缺乏 SQL 知识或不注意在数据库中编写代码
• 永远不要匹配本机数据库性能（即使接近）
• 模型代码与数据库模型耦合非常紧密

Hardcoded/encapsulated in DAO layer

硬编码/封装在 DAO 层

Pros:

优点：

• SQL is kept in the objects that access data (encapsulation)
• SQL is easy to write (speed of development)
• SQL is easy to track down when changes are required
• Simple solution (no messy architecture)

• SQL保存在访问数据的对象中（封装）
• SQL易于编写（开发速度）
• 当需要更改时，SQL 很容易跟踪
• 简单的解决方案（没有凌乱的架构）

Cons:

缺点：

• SQL cannot be reviewed/changed by DBA
• SQL is likely to become DB-specific
• SQL can become hard to maintain

• DBA 不能/更改 SQL
• SQL 很可能成为 DB-specific
• SQL 可能变得难以维护

Stored Procedures

存储过程

Pros:

优点：

• SQL kept in the database (close to data)
• SQL is parsed, compiled and optimised by the DBMS
• SQL is easy for DBA to review/change
• Reduces network traffic
• Increased security

• SQL 保存在数据库中（接近数据）
• SQL 由 DBMS 解析、编译和优化
• DBA 易于查看/更改 SQL
• 减少网络流量
• 提高安全性

Cons:

缺点：

• SQL is tied to the database (vendor lock-in)
• SQL code is harder to maintain

• SQL 绑定到数据库（供应商锁定）
• SQL 代码更难维护

External files (e.g. Properties or Resource files)

外部文件（例如属性或资源文件）

Pros

优点

• SQL can be changed without a need to rebuild the application
• Decouples the SQL logic from the application business logic
• Central repository of all SQL statements – easier to maintain
• Easier to understand

• 无需重新构建应用程序即可更改 SQL
• 将 SQL 逻辑与应用程序业务逻辑分离
• 所有 SQL 语句的中央存储库 - 更易于维护
• 更容易理解

Cons:

缺点：

• SQL code can become un-maintainable
• Harder to check the SQL code for (syntax) errors

• SQL 代码可能变得不可维护
• 更难检查 SQL 代码的（语法）错误

Embedded in SQLJ clauses

嵌入在 SQLJ 子句中

Pros:

优点：

• Better syntax checking

• 更好的语法检查

Cons:

缺点：

• Ties too closely to Java
• Lower performance than JDBC
• Lack of dynamic queries
• Not so popular

• 与 Java 的关系过于紧密
• 性能低于 JDBC
• 缺乏动态查询
• 不那么受欢迎

采纳答案by BalusC

Usually, the more the application grows in terms of size and/or reusability, the more the need is to externalize/abstractize the SQL statements.

通常，应用程序在大小和/或可重用性方面增长得越多，就越需要外部化/抽象化 SQL 语句。

Hardcoded (as static final constants) is the first step. Stored in a file (properties/xml file) is the next step. Metadata driven (as done by an ORM like Hibernate/JPA) is the last step.

硬编码（作为静态最终常量）是第一步。存储在一个文件（properties/xml 文件）中是下一步。元数据驱动（如 Hibernate/JPA 之类的 ORM 所做的那样）是最后一步。

Hardcoded has the disadvantage that your code is likely to become DB-specific and that you need to rewrite/rebuild/redistribute on every change. Advantage is that you have it in 1 place.

硬编码的缺点是您的代码可能会成为特定于数据库的代码，并且您需要在每次更改时重写/重建/重新分发。优点是您可以在 1 个地方使用它。

Stored in a file has the disadvantage that it can become unmaintainable when the application grows. Advantage is that you don't need to rewrite/rebuild the app, unless you need to add an extra DAO method.

存储在文件中的缺点是当应用程序增长时它可能变得不可维护。优点是您不需要重写/重建应用程序，除非您需要添加额外的 DAO 方法。

Metadata driven has the disadvantage that your model code is very tight coupled with the database model. For every change in the database model you'll need to rewrite/rebuild/redistribute code. Advantage is that it is very abstract and that you can easily switch from DB server without the need to change your model (but ask yourself now: how often would a company switch from DB server? likely at least only once per 3 years, isn't it?).

元数据驱动的缺点是您的模型代码与数据库模型耦合非常紧密。对于数据库模型中的每个更改，您都需要重写/重建/重新分发代码。优点是它非常抽象，您可以轻松地从数据库服务器切换而无需更改模型（但现在问问自己：公司多久会从数据库服务器切换一次？可能至少每 3 年一次，是吗？是吗？）。

I won't call stored procedures a "good" solution for this. They have an entirely different purpose. Even though, your code would be dependent on the DB / configuration used.

我不会将存储过程称为“好的”解决方案。他们有一个完全不同的目的。尽管如此，您的代码将取决于所使用的数据库/配置。

回答by Otávio Décio

By using an ORM (such as hibernate) you hopefully will have noSQL statements to worry about. Performance is usually acceptable and you get vendor independence as well.

通过使用 ORM（例如休眠），您希望无需担心 SQL 语句。性能通常是可以接受的，而且您还可以独立于供应商。

回答by flybywire

I don't know if this is optimal, but in my experience they end up hardcoded (i.e. String literals) in the DAO layer.

我不知道这是否是最优的，但根据我的经验，它们最终在 DAO 层中进行了硬编码（即字符串文字）。

回答by Jim Ferrans

We happen to use the iBatis SQL mapper, which is closer to the metal than ORMs like Hibernate. In iBatis you put the SQL statements into resource files (XML), which need to be in the classpath.

我们碰巧使用了 iBatis SQL 映射器，它比 Hibernate 等 ORM 更接近金属。在 iBatis 中，您将 SQL 语句放入资源文件 (XML) 中，资源文件需要位于类路径中。

Your list of approaches seems pretty comprehensive if you add @ocdecio's ORM option. I would say that using an ORM and using an SQL mapper and resource files are the two best approaches. I'd steer clear from SQLJ, which hasn't seen much uptake and ties you too closely to Java. Also stay away from stored procedures, since they tie you to a specific database vendor (standards are almost non-existent for stored procedures).

如果您添加@ocdecio 的 ORM 选项，您的方法列表似乎非常全面。我会说使用 ORM 和使用 SQL 映射器和资源文件是两种最好的方法。我会避开 SQLJ，它并没有看到太多的应用并且将您与 Java 联系得太紧密。还要远离存储过程，因为它们将您与特定的数据库供应商联系在一起（存储过程的标准几乎不存在）。

回答by Jay

I suggest using DAOs with a factory layout. So the example objects you need would be:

我建议使用带有工厂布局的 DAO。所以你需要的示例对象是：

public class CoolBusinessObject
public class DAOFactory.java
public implementation CoolBusinessOjectDAO
public class CoolBusinessOjectDAOOracleImpl implements CoolBusinessOjectDAO

This style layers the data interaction, so you should only have to change one layer of code if you switch databases, or move to ORM technologies.

这种风格将数据交互分层，因此如果您切换数据库或转向 ORM 技术，您应该只需要更改一层代码。

回答by Michael Lloyd Lee mlk

I don't think anyone will give you the pro/con break down you want as it is a rather large question. So instead here is what I've used in the past, and what I will be using going forward.

我认为没有人会给你你想要的赞成/反对分解，因为这是一个相当大的问题。因此，这里是我过去使用过的，以及我今后将使用的。

I use to use SQL hardcoded in the DAL. I thought this was fine until the DBAs wanted to play with the SQL. Then you have to dig it out, format it and fire it over to the DBAs. Who will laugh at it and replace it all. But without the nice question marks, or the question marks in the wrong order and leave you to stick it back in the Java code.

我过去常常在 DAL 中使用硬编码的 SQL。我认为这很好，直到 DBA 想要使用 SQL。然后，您必须将其挖掘出来，对其进行格式化并将其发送给 DBA。谁会嘲笑它并替换它。但是没有漂亮的问号，或者问号顺序错误，让您把它贴回到 Java 代码中。

We have also used a ORM, and while this is great for developers our DBAs hated it as there is no SQL for them to laugh at. We also used a odd ORM (a custom one from 3rd party supplier) which had a habit of killing the database. I've used JPA since and was great, but getting anything complicated using it past the DBAs is a up hill battle.

我们还使用了 ORM，虽然这对开发人员非常有用，但我们的 DBA 讨厌它，因为没有 SQL 可供他们嘲笑。我们还使用了一个奇怪的 ORM（来自 3rd 方供应商的自定义 ORM），它有杀死数据库的习惯。从那以后我就一直使用 JPA 并且很棒，但是通过 DBA 使用它来处理任何复杂的事情都是一场艰苦的战斗。

We now use Stored Procedures (with the call statement hardcoded). Now the first thing everyone will complain about is that you are tied to the database. You are. However how often have you changed database? I know for a fact that we simply could not even attempt it, the amount of other code dependent on it plus retraining our DBAs plus migrating the data. It would be a very expensive operation. However if in your world changing DBs at a drop of a hat is required SPs are likely out.

我们现在使用存储过程（使用硬编码的 call 语句）。现在每个人都会抱怨的第一件事是您被绑定到数据库。你是。但是，您多久更改一次数据库？我知道我们根本无法尝试它，依赖它的其他代码数量加上重新培训我们的 DBA 以及迁移数据。这将是一项非常昂贵的手术。但是，如果在您的世界中需要立即更改 DB，则 SP 可能会被淘汰。

Going forward I would like to use stored procedures with code generation tools to create Java classes from Oracle packages.

展望未来，我想使用带有代码生成工具的存储过程从 Oracle 包创建 Java 类。

Edit 2013-01-31: A few years and DBAs later and we now use Hibernate, going to SQL (stored procs in the DB) only when absolutely required. This I think is the best solution. 99% of the times the DBs don't need to worry about the SQL, and the 1% they do it is in a place they are already comfortable with.

编辑 2013-01-31：几年和 DBA 之后，我们现在使用 Hibernate，仅在绝对需要时才使用 SQL（存储在 DB 中的过程）。这是我认为最好的解决方案。99% 的情况下 DB 不需要担心 SQL，而他们做的 1% 是在他们已经习惯的地方。

回答by OMG Ponies

Should SQL code be considered “code” or “metadata”?

SQL 代码应该被视为“代码”还是“元数据”？

Code.

代码。

Should stored procedures be used only for performance optimization or they are a legitimate abstraction of the database structure?

存储过程应该仅用于性能优化还是数据库结构的合法抽象？

Stored procedures allow for reuse, including inside of other stored procedures. This means that you can make one trip to the database & have it execute supporting instructions - the least amount of traffic is ideal. ORM or sproc, the time on the wire going to the db & back is something you can't recoup.

存储过程允许重用，包括在其他存储过程中。这意味着您可以访问数据库并让它执行支持指令 - 最少的流量是理想的。ORM 或 sproc，连接到 db 和返回的线路上的时间是您无法弥补的。

ORM doesn't lend itself to optimization because of its abstraction. IME, ORM also means a lack of referencial integrity - make a database difficult to report from. What was saved in complexity, has now increased to be able to get the data out in a workable fashion.

由于其抽象性，ORM 不适合优化。IME、ORM 也意味着缺乏参照完整性——使数据库难以报告。在复杂性中保存的内容现在增加到能够以可行的方式获取数据。

Is performance a key factor the decision? What about vendor lock-in?

性能是决定的关键因素吗？供应商锁定呢？

No, simplicity is. Vendor lockin happens with the database as well - SQL is relatively standardized, but there are still vendor specific ways of doing things.

不，简单才是。供应商锁定也发生在数据库中——SQL 相对标准化，但仍然有供应商特定的做事方式。

回答by The Machine

From what experience , I have had, hard coding sql statements in the DAO objects is what is widely used, though , I think it should be the least preferred method. The best practice should be to store the sql statements in a properties file. And get the statements in the DAO object through an interface to properties files, say java.util.Properties. The sql statements can be interspersed with '?'s to pass parameters , through a Prepared Statementapproach.

根据我的经验，DAO 对象中的硬编码 sql 语句是广泛使用的，不过，我认为它应该是最不受欢迎的方法。最佳实践应该是将 sql 语句存储在属性文件中。并通过属性文件的接口获取 DAO 对象中的语句，例如java.util.Properties。sql 语句可以穿插 '?' 来传递参数，通过Prepared Statement方法。

Such an approach helps decouple the sql logic from the application business logic. This makes available a central repository of all sql statements , which makes modification easier, eliminating the need to search for database statements within application logic.Understandability improves too.

这种方法有助于将 sql 逻辑与应用程序业务逻辑分离。这使得所有 sql 语句的中央存储库可用，这使得修改更容易，消除了在应用程序逻辑中搜索数据库语句的需要。可理解性也提高了。

回答by Jé Queue

Like most of us, I've seen the whole gammut but we need to consider SQL a first-class language. I've even seen SQL stored in the DB that is pulled down then executed back up.

像我们大多数人一样，我已经看到了整个范围，但我们需要将 SQL 视为一流的语言。我什至看到存储在数据库中的 SQL 被下拉然后执行备份。

The most successful systems I've seen employ stored procedures, functions and views.

我见过的最成功的系统使用了存储过程、函数和视图。

Stored procs keep the SQL text back at the DB and allow for relatively immediate change by those DEPLOYING and CUSTOMIZING (which requires a lot of proper design to support it).

存储过程将 SQL 文本保留在数据库中，并允许那些部署和自定义（这需要大量适当的设计来支持它）进行相对即时的更改。

All projections should be via views and simple selects for the same reasons, all projection logic should be contained within the view.

出于同样的原因，所有的投影都应该通过视图和简单的选择，所有的投影逻辑都应该包含在视图中。

回答by Kenny Drobnack

The only question you ask that has a definite answer is "Is SQL code or metadata?" It is most definitely code and as such should be kept in some kind of source code control and have a system for easily updating to the latest version and rolling back whennot if things go wrong.

您问的唯一有明确答案的问题是“是 SQL 代码还是元数据？” 它绝对是代码，因此应该保存在某种源代码控制中，并且有一个系统可以轻松更新到最新版本并在出现问题时回滚。

I've seen three ways of doing SQL in an application and each has their pros & cons. There is no best way, but the best thing is just pick one that works well with your application and stick with it.

我已经看到在应用程序中执行 SQL 的三种方法，每种方法都有其优点和缺点。没有最好的方法，但最好的办法就是选择一种适合您的应用程序并坚持使用的方法。

• ORM - this cuts down on the amount of SQL you need to write and handles lots of details for you. You will need to do some custom SQL. Make sure you have an ORM that handles this gracefully.
• Data Access Objects - keep the SQL in the objects that access the data. This encapsulates your database and makes it so the rest of your application doesn't need to know about the underlying DB structure, just the interface to these objects.
• Stored Procedures - this keeps all your SQL in your database and makes it easy for your DBA's to know what is going on. All you need to do is have your code call the stored procs

• ORM - 这减少了您需要编写的 SQL 数量并为您处理大量细节。您将需要执行一些自定义 SQL。确保你有一个优雅地处理这个的 ORM。
• 数据访问对象 - 将 SQL 保留在访问数据的对象中。这封装了您的数据库并使其应用程序的其余部分不需要了解底层数据库结构，只需了解这些对象的接口。
• 存储过程 - 这会将您的所有 SQL 保存在您的数据库中，并使您的 DBA 可以轻松了解正在发生的事情。您需要做的就是让您的代码调用存储过程