{Core Java with Spring}通过普通JDBC调用PL SQL存储过程 - 谁应该理想地进行事务管理 [英] {Core Java with Spring} calling PL SQL stored procedure via plain JDBC - who should ideally do transaction management

问题描述

我的应用程序从Java / Spring调用Oracle存储过程。没有Hibernate或iBatis或Spring JDBC模板。
这个服务器端/中间层是瘦的，因为没有业务逻辑检查，没有验证;它只是在UI和数据库之间进行数据传输
层。 java代码在数据检索或数据持久性的情况下调用存储过程。
存储过程是与各种表/表关系交互并聚合数据的大家伙。

My application calls the Oracle Stored Procedure from Java/Spring. There is no Hibernate or iBatis or Spring JDBC templates. This server side/middle tier is 'thin' in the sense there is no business logic checks, no validations; It just acts a data transfer layer between the UI and the database. The java code calls the stored procedure in case of data retrieval or data persistence. The Stored Procedure is the big guy who interacts with the various table/table-relationships and aggregates the data.

问题 - 在这种情况下，谁应该理想地管理交易？从Java代码或存储过程？

通常当中间层管理数据或执行业务逻辑时，或者我们要么进行验证使用普通的JDBC / ORM框架来
与各种表进行交互，从而管理事务。
但在我的用例中，SP正在与表进行交互，决定数据是否被正确检索或能够持久化，
为什么它应该依靠中间层来单独管理事务。它可以很好地知道是提交事务还是回滚它。

Usually when the middle tier manages the data or does business logic, or does the validation we would either use plain JDBC/ORM frameworks to interact with the various tables and hence manages the transaction as well. But in my use-case, the SP is interacting with the tables, takes the decision whether the data is properly retrieved or able to persist, why should it rely on middle-tier to manage the transaction alone. It can very well know whether to commit a transaction or rollback it.

总是编写一个SP作为聚合函数的高级接口是不是更好数据检索的情况，
或数据持久性的汇总函数，最后做事务提交或事务回滚？

Isn't it better to always write a SP that act a high level interface for the aggregation function in case of a data retrieval, or a summarize function in case of data persistence, and finally do a transaction commit or transaction rollback?

中间层需要仅当预期为特定操作调用多个SP
时才处理事务管理。只要java代码仅为特定操作[fetch / update / delete]调用一个SINGLE存储过程，
在存储过程本身中管理事务提交和回滚是不是很好？

The middle tier needs to handle the transaction management only if it is expected to call more than one SP for a particular operation. As long as the java code calls ONLY a SINGLE stored procedure for a particular operation[fetch/update/delete], isn't it good to manage the transaction commit and rollback in the Stored procedure itself?

如果SP内部出现问题，它可以回滚事务并引发异常，然后Java
将传递该异常消息[或者记录并传递一个自定义UI的例外情况。

In case something went wrong inside the SP, it can rollback the transaction and raise an exception, and Java would then pass that exception message [or log it and pass a custom exception] to the UI.

计数器的想法是让SP在遇到任何异常的情况下引发异常，并且Java代码反过来捕获异常并执行事务回滚。
如果在调用SP时没有异常，则让它进行事务提交。
但是在这里，SP已经知道交易是成功还是失败，那么为什么我们不能自己做提交或回滚，而是
以异常的形式传递这些信息或没有例外，让java代码进行提交/回滚吗？

A counter thought is let the SP's raise an exception in case of any exception encountered, and the Java code in-turn catch the exception and do a transaction rollback. If there is no exception in calling the SP, let it do a transaction commit. But then here, the SP already whether the transaction is a success or failure, then why we can't do a commit or rollback then itself, and instead pass that information in the form of a exception or no-exception and let java code do the commit/rollback?

更新：有一点可以证明需要Java代码来管理事务，这就是它调用MULTIPLE SP的时候一个单一的操作。
但是，同样的结果可以通过执行内部调用单个例程的逻辑的高级例程来实现，最后
做出提交/回滚的决定。

update: One thing that justifies the need of Java code to manage the transaction is when it calls MULTIPLE SP's for a SINGLE operation. But then, the same result can be achieved by a high level routine that does the logic of internally calling the individual routines, and finally take teh decision of commit/rollback.

请分享您对此的想法/建议/设计建议。

Please share your thoughts/recommendations/design suggestions on this.

PS：我这里没有共享任何代码，但这是一个程序化的设计问题，关于谁应该管理交易？

PS: I haven't share any code here, but this a programmatic design question, as to who should manage the transaction?

推荐答案

这是一个哲学问题。
这里没有更好的选择，这取决于你的偏好和专业领域。

This is a philosophic question. There is no better here it’s a matter of your preference and your area of specialty.

DBA会喜欢java程序员不会的商店程序。

DBA’s will love store procedures approach java programmers won’t.

关于交易管理，我更喜欢统一的方法评估者，然后是混合解决方案。
事务管理很复杂，如果你使用SP使用数据库事务管理，我宁愿坚持使用一种方法。如果您选择使用jdbc或ORM，请使用jdbc事务管理或spring抽象来进行jdbc trx管理。

Regarding transaction management I will prefer a unified approach rater then a hybrid solution. Transaction management is complex and I would prefer sticking to one approach if you use SP use the database transaction management. If you chose to use jdbc or ORM use the jdbc transaction management or spring abstraction for jdbc trx management.

SP方法有其优点和缺点：

SP approach has its pros and cons:

专业版：

1. 您可以定制特定于数据库的查询，并可能获得优越的
   性能


2. 减少
   分配的第三方依赖性


3. 您是DBA

缺点：

1. 您是数据库依赖


2. 您的JUnit需要与数据库交互


3. 您需要DBA

无论如何，我会使用spring JDBC Template，从我的角度来看，它是一个很好的JDBC抽象，可以节省样板代码和错误。

Anyway I would use spring JDBC Template which, from my point of view, is a good JDBC abstraction that save you boilerplate code and bugs.

如果你去进行java事务管理，我会添加Spring PlatformTransactionManager。

If you go for java transaction management I will add Spring PlatformTransactionManager.

添加spring-jdbc和spring-tx jar不会产生巨大的影响。
这两个罐子大约是800k，我想还会增加一些额外的依赖罐子。

Adding spring-jdbc and spring-tx jar will not have a huge size effect. The two jars are around 800k I guess some additional depended jar will be added as well .

这篇关于{Core Java with Spring}通过普通JDBC调用PL SQL存储过程 - 谁应该理想地进行事务管理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！