使用 ORM 还是普通 SQL？

Using an ORM or plain SQL?

提问by hydrapheetz

For some of the apps I've developed (then proceeded to forget about), I've been writing plain SQL, primarily for MySQL. Though I have used ORMs in python like SQLAlchemy, I didn't stick with them for long. Usually it was either the documentation or complexity (from my point of view) holding me back.

对于我开发的一些应用程序（然后忘记了），我一直在编写简单的 SQL，主要用于 MySQL。虽然我在 python 中使用过 ORM，比如SQLAlchemy，但我并没有长期坚持使用它们。通常是文档或复杂性（从我的角度来看）阻碍了我。

I see it like this: use an ORM for portability, plain SQL if it's just going to be using one type of database. I'm really looking for advice on when to use an ORM or SQL when developing an app that needs database support.

我是这样看的：使用 ORM 来实现可移植性，如果只是使用一种类型的数据库，则使用纯 SQL。在开发需要数据库支持的应用程序时，我真的在寻找有关何时使用 ORM 或 SQL 的建议。

Thinking about it, it would be far better to just use a lightweight wrapper to handle database inconsistencies vs. using an ORM.

考虑一下，与使用 ORM 相比，仅使用轻量级包装器来处理数据库不一致会好得多。

采纳答案by Cameron Pope

ORMs have some nice features. They can handle much of the dog-work of copying database columns to object fields. They usually handle converting the language's date and time types to the appropriate database type. They generally handle one-to-many relationships pretty elegantly as well by instantiating nested objects. I've found if you design your database with the strengths and weaknesses of the ORM in mind, it saves a lot of work in getting data in and out of the database. (You'll want to know how it handles polymorphism and many-to-many relationships if you need to map those. It's these two domains that provide most of the 'impedance mismatch' that makes some call ORM the 'vietnam of computer science'.)

ORM 有一些不错的特性。他们可以处理将数据库列复制到对象字段的大部分工作。它们通常处理将语言的日期和时间类型转换为适当的数据库类型。它们通常通过实例化嵌套对象来非常优雅地处理一对多关系。我发现如果您在设计数据库时考虑到 ORM 的优点和缺点，则可以节省大量将数据输入和输出数据库的工作。（如果您需要映射它们，您将想知道它如何处理多态性和多对多关系。正是这两个域提供了大部分“阻抗失配”，这使得一些人将 ORM 称为“计算机科学的越南” .)

For applications that are transactional, i.e. you make a request, get some objects, traverse them to get some data and render it on a Web page, the performance tax is small, and in many cases ORM can be faster because it will cache objects it's seen before, that otherwise would have queried the database multiple times.

对于事务性应用程序，即您发出请求，获取一些对象，遍历它们以获取一些数据并将其呈现在网页上，性能负担很小，并且在许多情况下 ORM 可以更快，因为它会缓存对象之前看到过，否则会多次查询数据库。

For applications that are reporting-heavy, or deal with a large number of database rows per request, the ORM tax is much heavier, and the caching that they do turns into a big, useless memory-hogging burden. In that case, simple SQL mapping (LinQ or iBatis) or hand-coded SQL queries in a thin DAL is the way to go.

对于报告繁重或每个请求处理大量数据库行的应用程序，ORM 负担要重得多，它们所做的缓存变成了一个巨大的、无用的内存占用负担。在这种情况下，简单的 SQL 映射（LinQ 或 iBatis）或在瘦 DAL 中手动编码的 SQL 查询是要走的路。

I've found for any large-scale application you'll find yourself using both approaches. (ORM for straightforward CRUD and SQL/thin DAL for reporting).

我发现对于任何大型应用程序，您都会发现自己使用这两种方法。（用于直接 CRUD 的 ORM 和用于报告的 SQL/thin DAL）。

回答by cletus

Speaking as someone who spent quite a bit of time working with JPA (Java Persistence API, basically the standardized ORM API for Java/J2EE/EJB), which includes Hibernate, EclipseLink, Toplink, OpenJPA and others, I'll share some of my observations.

作为一个花了很多时间使用 JPA（Java Persistence API，基本上是 Java/J2EE/EJB 的标准化 ORM API）的人，其中包括 Hibernate、EclipseLink、Toplink、OpenJPA 等，我将分享我的一些观察。

1. ORMs are not fast. They can be adequate and most of the time adequate is OK but in a high-volume low-latency environment they're a no-no;
2. In general purpose programming languages like Java and C# you need an awful lot of magic to make them work (eg load-time weaving in Java, instrumentation, etc);
3. When using an ORM, rather than getting further from SQL (which seems to be the intent), you'll be amazed how much time you spend tweaking XML and/or annotations/attributes to get your ORM to generate performant SQL;
4. For complex queries, there really is no substitute. Like in JPA there are some queries that simply aren't possible that are in raw SQL and when you have to use raw SQL in JPA it's not pretty (C#/.Net at least has dynamic types--var--which is a lot nicer than an Object array);
5. There are an awful lot of "gotchas" when using ORMs. This includes unintended or unexpected behavior, the fact that you have to build in the capability to do SQL updates to your database (by using refresh() in JPA or similar methods because JPA by default caches everything so it won't catch a direct database update--running direct SQL updates is a common production support activity);
6. The object-relational mismatch is always going to cause problems. With any such problem there is a tradeoff between complexity and completeness of the abstraction. At times I felt JPA went too far and hit a real law of diminishing returns where the complexity hit wasn't justified by the abstraction.

1. ORM 并不快。它们可以是足够的，并且大多数时候足够是可以的，但是在高容量低延迟环境中它们是禁忌的；
2. 在像 Java 和 C# 这样的通用编程语言中，您需要大量的魔法才能使它们工作（例如 Java 中的加载时编织、检测等）；
3. 当使用 ORM 时，与其从 SQL 中获取更多信息（这似乎是我们的意图），您会惊讶地发现您花费了多少时间调整 XML 和/或注释/属性来让 ORM 生成高性能 SQL；
4. 对于复杂的查询，确实没有替代品。就像在 JPA 中一样，有些查询在原始 SQL 中根本不可能，当您必须在 JPA 中使用原始 SQL 时，它并不漂亮（C#/.Net 至少具有动态类型--var--这是很多比对象数组更好）；
5. 使用 ORM 时有很多“陷阱”。这包括意外或意外的行为，您必须构建对数据库执行 SQL 更新的功能（通过在 JPA 中使用 refresh() 或类似方法，因为 JPA 默认缓存所有内容，因此它不会捕获直接数据库更新——运行直接 SQL 更新是一种常见的生产支持活动）；
6. 对象-关系不匹配总是会导致问题。对于任何此类问题，都需要在抽象的复杂性和完整性之间进行权衡。有时我觉得 JPA 走得太远了，遇到了真正的收益递减规律，在这种情况下，抽象不能证明对复杂性的影响是合理的。

There's another problem which takes a bit more explanation.

还有一个问题需要更多的解释。

The traditional model for a Web application is to have a persistence layer and a presentation layer (possibly with a services or other layers in between but these are the important two for this discussion). ORMs force a rigid view from your persistence layer up to the presentation layer (ie your entities).

Web 应用程序的传统模型是有一个持久层和一个表示层（可能中间有一个服务或其他层，但这是本次讨论的重要两层）。ORM 强制从持久层到表示层（即您的实体）使用严格的视图。

One of the criticisms of more raw SQL methods is that you end up with all these VOs (value objects) or DTOs (data transfer objects) that are used by simply one query. This is touted as an advantage of ORMs because you get rid of that.

对更多原始 SQL 方法的批评之一是，您最终会得到所有这些 VO（值对象）或 DTO（数据传输对象），它们仅被一个查询使用。这被吹捧为 ORM 的一个优势，因为你摆脱了它。

Thing is those problems don't go away with ORMs, they simply move up to the presentation layer. Instead of creating VOs/DTOs for queries, you create custom presentation objects, typically one for every view. How is this better? IMHO it isn't.

问题是 ORM 不会解决这些问题，它们只是向上移动到表示层。您不是为查询创建 VO/DTO，而是创建自定义表示对象，通常为每个视图创建一个。这如何更好？恕我直言，它不是。

I've written about this in ORM or SQL: Are we there yet?.

我已经用ORM 或 SQL写过这个：我们到了吗？.

My persistence technology of choice (in Java) these days is ibatis. It's a pretty thin wrapper around SQL that does 90%+ of what JPA can do (it can even do lazy-loading of relationships although its not well-documented) but with far less overhead (in terms of complexity and actual code).

我最近选择的持久性技术（在 Java 中）是 ibatis。它是一个围绕 SQL 的非常薄的包装器，它完成了 JPA 可以做的 90% 以上的事情（它甚至可以延迟加载关系，尽管它没有很好的记录）但开销要少得多（就复杂性和实际代码而言）。

This came up last year in a GWT application I was writing. Lots of translation from EclipseLink to presentation objects in the service implementation. If we were using ibatis it would've been far simpler to create the appropriate objects with ibatis and then pass them all the way up and down the stack. Some purists might argue this is Bad™. Maybe so (in theory) but I tell you what: it would've led to simpler code, a simpler stack and more productivity.

这在去年我正在编写的 GWT 应用程序中出现。从 EclipseLink 到服务实现中的表示对象的大量转换。如果我们使用 ibatis，那么使用 ibatis 创建适当的对象然后将它们一直向上和向下传递到堆栈会简单得多。一些纯粹主义者可能会争辩说这是 Bad™。也许是（理论上）但我告诉你的是：它会导致更简单的代码、更简单的堆栈和更高的生产力。

回答by Max Toro

I say plain SQL for Reads, ORM for CUD.

我说Read 的普通 SQL ，CUD 的ORM 。

Performance is something I'm always concerned about, specially in web applications, but also code maintainability and readability. To address these issues I wrote SqlBuilder.

性能是我一直关心的事情，特别是在 Web 应用程序中，还有代码的可维护性和可读性。为了解决这些问题，我编写了SqlBuilder。

回答by Anton Gogolev

ORM is not just portability (which is kinda hard to achieve even with ORMs, for that matter). What it gives you is basically a layer of abstraction over a persistent store, when a ORM tool frees you from writing boilerplate SQL queries (selects by PK or by predicates, inserts, updates and deletes) and lets you concentrate on the problem domain.

ORM 不仅仅是可移植性（就此而言，即使使用 ORM 也很难实现）。当 ORM 工具使您无需编写样板 SQL 查询（通过 PK 或谓词、插入、更新和删除进行选择）并让您专注于问题域时，它为您提供的基本上是一个持久存储的抽象层。

回答by dkretz

Any respectable design will need some abstraction for the database, just to handle the impedance mismatch. But the simplest first step (and adequate for most cases) I would expect would be a DAL, not a heavyweight ORM. Your only options aren't those at the ends of the spectrum.

任何体面的设计都需要对数据库进行一些抽象，以处理阻抗不匹配。但是我期望的最简单的第一步（并且对于大多数情况来说已经足够了）是 DAL，而不是重量级的 ORM。你唯一的选择不是那些在频谱的尽头。

---

---

EDIT in response to a comment requesting me to describe how I distinguish DAL from ORM:

编辑回应要求我描述我如何区分 DAL 和 ORM 的评论：

A DAL is what you write yourself, maybe starting from a class that simply encapsulates a table and maps its fields to properties. An ORM is code you don't write or abstraction mechanisms inferred from other properties of your dbms schema, mostly PKs and FKs. (This is where you find out if the automatic abstractions start getting leaky or not. I prefer to inform them intentionally, but that may just be my personal preference).

DAL 是您自己编写的，可能从一个简单地封装一个表并将其字段映射到属性的类开始。ORM 是您不编写的代码或从 dbms 架构的其他属性（主要是 PK 和 FK）推断出的抽象机制。（这是您发现自动抽象是否开始泄漏的地方。我更愿意故意通知他们，但这可能只是我个人的偏好）。

回答by Lukas Eder

Every tool has its purpose and vision. I have created http://www.jooq.org/exactly to suit your needs, albeit iBatis is probably a good solution for you as well.

每个工具都有其目的和愿景。我创建了http://www.jooq.org/ 来完全满足您的需求，尽管 iBatis 对您来说也可能是一个很好的解决方案。

jOOQ has basic ORM features, but it mainly focuses on the things that I guess most developers need most, when trying to find the best ORM for their needs:

jOOQ 具有基本的 ORM 功能，但它主要关注我猜大多数开发人员在试图找到最适合他们需求的 ORM 时最需要的东西：

• code generation
• variable binding (that's a pain in JDBC)
• SQL syntax abstraction (to prevent syntax errors)

• 代码生成
• 变量绑定（这是 JDBC 中的一个痛点）
• SQL 语法抽象（防止语法错误）

But often they go too far and provide so much abstraction, you wouldn't think they're running against an RDBMS. On the other hand, you chose an RDBMS precisely because

但通常它们走得太远，提供了如此多的抽象，你不会认为它们是在运行 RDBMS。另一方面，您选择 RDBMS 正是因为

• it is a robust data source
• SQL can do many good, performant things (nested selects, unions, complex joins, etc). Often ORM's cannot do these things.
• you can handle transactions and sessions yourself
• you have UDT's and stored procedures

• 它是一个强大的数据源
• SQL 可以做很多好的、高性能的事情（嵌套选择、联合、复杂连接等）。通常 ORM 不能做这些事情。
• 您可以自己处理事务和会话
• 你有 UDT 和存储过程

jOOQ addresses exactly these points. It will perform as well as JDBC, but without the pain.

jOOQ 正好解决了这些问题。它的性能与 JDBC 一样好，但没有痛苦。

回答by MrTelly

The key that made my ORM use really fly was code generation. I agree that the ORM route isn't the fastest, in code performance terms. But when you have a medium to large team, the DB is changing rapidly the ability to regenerate classes and mappings from the DB as part of the build process is something brilliant to behold, especially when you use CI. So your code may not be the fastest, but your coding will be - I know which I'd take in most projects.

让我使用 ORM 的关键是代码生成。我同意 ORM 路线不是最快的，在代码性能方面。但是，当您拥有一个大中型团队时，DB 正在迅速变化，作为构建过程的一部分从 DB 重新生成类和映射的能力是值得一看的，尤其是当您使用 CI 时。所以你的代码可能不是最快的，但你的编码会 - 我知道我会在大多数项目中采用哪种方式。

My recommendation is to develop using an ORM while the Schema is still fluid, use profiling to find bottlenecks, then tune those areas which need it using raw Sql.

我的建议是在 Schema 仍然流动时使用 ORM 进行开发，使用分析来查找瓶颈，然后使用原始 Sql 调整那些需要它的区域。

Another thought, the caching built into Hibernate can often make massive performance improvements if used in the right way. No more going back to the DB to read reference data.

另一个想法是，如果以正确的方式使用，内置于 Hibernate 中的缓存通常可以极大地提高性能。不再返回数据库读取参考数据。

回答by Rutesh Makhijani

Dilemma whether to use a framework or not is quite common in modern day software development scenario.

是否使用框架的困境在现代软件开发场景中很常见。

What is important to understand is that every framework or approach has its pros and cons - for example in our experience we have found that ORM is useful when dealing with transactions i.e. insert/update/delete operations - but when it comes to fetch data with complex results it becomes important to evaluate the performance and effectiveness of the ORM tool.

重要的是要了解每个框架或方法都有其优点和缺点——例如，根据我们的经验，我们发现 ORM 在处理事务（即插入/更新/删除操作）时很有用——但是当涉及到复杂的数据获取时结果 评估 ORM 工具的性能和有效性变得很重要。

Also it is important to understand that it is not compulsory to select a framework or an approach and implement everything in that. What we mean by that is we can have mix of ORM and native query language. Many ORM frameworks give extension points to plugin in native SQL. We should try not to over use a framework or an approach. We can combine certain frameworks or approaches and come with an appropriate solution.

同样重要的是要了解，选择框架或方法并在其中实施所有内容并不是强制性的。我们的意思是我们可以混合使用 ORM 和本地查询语言。许多 ORM 框架都为原生 SQL 中的插件提供了扩展点。我们应该尽量不要过度使用框架或方法。我们可以结合某些框架或方法，并提出适当的解决方案。

You can use ORM when it comes to insertion, updation, deletion, versioning with high level of concurrency and you can use Native SQL for report generation and long listing

您可以在插入、更新、删除、具有高并发级别的版本控制方面使用 ORM，并且可以使用 Native SQL 进行报告生成和长列表

回答by Frederik Gheysels

There's no 'one-tool-fits-all' solution, and this is also true for the question 'should i use an or/m or not ? '.

没有“一刀切”的解决方案，对于“我应该使用 or/m 还是不？”这个问题也是如此。'。

I would say: if you have to write an application/tool which is very 'data' focused, without much other logic, then I 'd use plain SQL, since SQL is the domain-specific language for this kind of applications.

我会说：如果您必须编写一个非常注重“数据”的应用程序/工具，而没有太多其他逻辑，那么我会使用纯 SQL，因为 SQL 是此类应用程序的特定领域语言。

On the other hand, if I was to write a business/enterprise application which contains a lot of 'domain' logic, then I'd write a rich class model which could express this domain in code. In such case, an OR/M mapper might be very helpfull to successfully do so, as it takes a lot of plumbing code out of your hands.

另一方面，如果我要编写一个包含大量“域”逻辑的业务/企业应用程序，那么我会编写一个可以用代码表达这个域的丰富类模型。在这种情况下，OR/M 映射器可能对成功执行此操作非常有帮助，因为它不需要您处理大量管道代码。

回答by hydrapheetz

One of the apps I've developed was an IRC bot written in python. The modules it uses run in separate threads, but I haven't figured out a way to handle threading when using sqlite. Though, that might be better for a separate question.

我开发的应用程序之一是用 Python 编写的 IRC 机器人。它使用的模块在单独的线程中运行，但我还没有想出一种在使用 sqlite 时处理线程的方法。不过，这对于单独的问题可能会更好。

I really should have just reworded both the title andthe actual question. I've never actually used a DAL before, in any language.

我真的应该改写标题和实际问题。我以前从未真正使用过任何语言的 DAL。