如何编写独立于数据库的应用程序？
我的老板要求我只编写 ANSI SQL 以使其独立于数据库。 但我了解到这并不容易，因为没有数据库完全兼容 ANSI SQL。 SQL 代码很少可以在不进行修改的情况下在数据库系统之间移植。
我看到人们采用不同的方式来使他们的程序数据库独立。 例如：
- 将 SQL 语句外部化到资源文件中。
- 编写许多提供者类来支持不同的数据库。
- 只编写简单的 SQL，并远离高级函数/连接。
您是否总是以“任何数据库就绪”的方式编写代码？ 还是只在需要时才这样做？ 如果是，你是如何实现的？
My boss asks me to write only ANSI SQL to make it database independent. But I learned that it is not that easy as no database fully ANSI SQL compatible. SQL code can rarely be ported between database systems without modifications.
I saw people do different way to make their program database independent. For example:
- Externalize SQL statements to resource files.
- Write many providers class to support different database.
- Write only simple SQL, and keep away from advance functions/joins.
Do you always write your code "any database ready"? Or do it only if needed?
If yes, how do you achieve it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您可以使用众多对象/关系映射器工具之一，例如 Hibernate/NHibernate、LLBLGen 等。 这可以让您在数据库可移植性方面走得更远。 无论您做什么，您的模型和其余代码之间都需要有一些抽象层。 这并不意味着您需要某种依赖注入基础设施，但良好的面向对象设计可以让您走得更远。 另外，坚持使用简单的 SQL 并认为这样可以获得可移植性是相当天真的。 如果您的应用程序很简单并且只使用了非常简单的查询，那么情况确实如此。
至于始终编写“任何数据库就绪”的应用程序，我通常使用某种抽象层，因此从一个数据库系统迁移到另一个数据库系统并不困难。 然而，在许多情况下，这不是必需的，您正在为 Oracle 平台或 SQL Server 或 MySQL 进行开发，因此您不应该仅仅为了完全无缝过渡的可能性而牺牲所选 RDBMS 的优势。 然而，如果您构建了一个良好的抽象层，即使针对特定的 RDBMS 也不一定很难迁移到不同的 RDBMS。
You could use one of the many Object/Relational Mapper tools, like Hibernate/NHibernate, LLBLGen, and so forth. That can get you a long way to database portability. No matter what you do, you need to have some abstraction layer between your model and the rest of your code. This doesn't mean you need some sort of dependency injection infrastructure, but good OO design can get you pretty far. Also, sticking with plain SQL and thinking that will get you portability is rather naive. That would be true if your application was trivial and only used very trivial queries.
As for always writing an application to be "any database ready," I usually use some sort of abstraction layer so it is not hard to move from one database system to another. However, in many circumstances, this is not required, you are developing for the Oracle platform or SQL Server or MySQL whatever so you shouldn't sacrifice the benefits of your chosen RDBMS just for the possibility of an entirely seamless transition. Nevertheless, if you build a good abstraction layer, even targeting a specific RDBMS won't necessarily be too difficult to migrate to a different RDBMS.
要将数据库引擎与应用程序分离，请使用数据库抽象层（也称为数据访问层，或者达尔）。 您没有提到您使用什么语言，但是所有主要语言都有很好的数据库抽象库。
然而，如果避免特定于数据库的优化，您将错过特定品牌的优势。 我通常会抽象出可能的内容并使用可用的内容。 更改数据库引擎是一个重大决定，并且不会经常发生，最好充分利用现有的工具。
To decouple the database engine from your application, use a database abstraction layer (also data access layer, or DAL). You didn't mention what language you use, but there are good database abstraction libraries for all the major languages.
However, by avoiding database-specific optimizations you will be missing out on the advantages of your particular brand. I usually abstract what's possible and use what's available. Changing database engines is a major decision and doesn't happen often, and it's best to use the tools you have available to the max.
告诉你的老板管好他自己的事。 不，当然不能对老板说这样的话，但请继续关注。
有趣的是这个需求应该支持什么商业价值。 一个明显的候选者似乎是数据库代码应该准备好在当前数据库引擎之外的其他数据库引擎上工作。 如果是这样的话，那么这就是要求中应该说明的内容。
从那时起，作为工程师的你就需要找出实现这一目标的不同方法。 人们可能正在编写 ANSI SQL。 人们可能会使用数据库抽象层。
此外，您有责任告知老板不同替代方案的成本（在性能、开发速度等方面）。
“编写 ANSI SQL”...啊！
Tell your boss to mind his own business. No, of course one can't say such things to one's boss, but stay tuned.
What's interesting is what business value is supposed to be supported by this requirement. One obvious candidate seems to be that the database code should be ready for working on other database engines than the current. If that's the case then that's what should be stated in the requirement.
From there it's up to you as an engineer to figure out the different ways to achieve that. One might be writing ANSI SQL. One might be using a database abstraction layer.
Further it's your responsibility to inform your boss what the costs of the different alternatives are (in terms of performance, speed of development, etcetera).
"Write ANSI SQL"... gah!
只是为了记录。 Stackoverflow 上有一个类似的问题：
与数据库无关的应用程序的数据库设计< /strong>
Just for the record. There is a similar question here on Stackoverflow:
Database design for database-agnostic applications
100% 符合 ANSI SQL 是一个很难实现的目标，但无论如何它并不能保证可移植性。 所以这是一个人为的目标。
据推测，您的老板之所以要求这样做，是为了将来可以轻松快捷地切换数据库品牌以实现某些假设的目的（实际上可能永远不会实现）。 但他现在正在用未来的效率来换取更多的工作量，因为使代码与数据库无关变得更加困难。*
因此，如果您可以根据经理的目标来表述问题应该关注的是，比如按时、按预算完成当前的项目阶段，这可能比仅仅告诉他使代码与数据库无关太困难更有效。
在一种情况下，您需要编写真正与数据库无关的代码，即当您正在开发需要支持多个品牌的数据库的收缩包装应用程序时。
无论如何，即使您目前只支持一个品牌，在某些情况下您也可以选择使用专有功能来编写一些 SQL，但也存在一种更便携的方法来实现相同的结果。 您可以将这些视为“容易实现的目标”案例，并且可以在将来需要时更轻松地移植代码。 但也不要限制自己，如果专有解决方案具有良好的价值，请使用它们。 也许在评论中添加一条注释，表明如果/当您需要进行移植时，这值得审查。
* 在谈论平台独立性时，我更喜欢“中立”这个词，而不是“不可知论”。 它避免了宗教色彩。 :-)
Being 100% compliant to ANSI SQL is a difficult goal to meet, and yet it doesn't guarantee portability anyway. So it's an artificial goal.
Presumably your boss is asking for this in order to make it easy and quick to switch database brands for some hypothetical purpose in the future (which actually may never come). But he's trading that future efficiency for a greater amount of work now, since it's harder to make the code database-neutral.*
So if you can phrase the problem in terms of the goals your manager should be focusing on, like finishing the current project phase on time and on budget, it may be more effective than just telling him it's too difficult to make the code database-neutral.
There is a scenario when you need to make truly database-neutral code, that is when you are developing a shrink-wrap application that is required to support multiple brands of database.
Anyway, even if you currently support only one brand, there are certainly cases where you have a choice to code some SQL using proprietary features, but there also exists a more portable way to achieve the same result. You can treat these as "low-hanging fruit" cases, and you can make it easier to port the code in the future if the need arises. But don't limit yourself either, use proprietary solutions if they give good value. Perhaps add a note in the comments that this deserves review if/when you need to make a port.
* I prefer the word "neutral" instead of "agnostic" when talking about platform-independence. It avoids the religious overtone. :-)