问题描述
摘要(以下详细信息):
Summary (details below):
我想在使用Spring/JPA堆栈保存/更新/删除任何实体之前进行存储的proc调用.
I'd like to make a stored proc call before any entities are saved/updated/deleted using a Spring/JPA stack.
钻孔细节:
我们有一个Oracle/JPA(Hibernate)/Spring MVC(带有Spring数据仓库)应用程序，该应用程序设置为使用触发器将一些表的历史记录到一组历史表中(每个表一个历史表，我们要对其进行审核) ).这些实体中的每个实体都有一个modifiedByUser，该类通过在更新或插入时扩展EmptyInterceptor的类设置.当触发器归档任何插入或更新时，它可以使用此列轻松查看谁进行了更改(我们对哪个应用程序用户感兴趣，而不是数据库用户感兴趣).问题在于，对于删除操作，我们将无法从执行的SQL中获取最后修改的信息，因为它只是普通的delete from x where y.
We have an Oracle/JPA(Hibernate)/Spring MVC (with Spring Data repos) application that is set up to use triggers to record history of some tables into a set of history tables (one history table per table we want audited). Each of these entities has a modifiedByUser being set via a class that extends EmptyInterceptor on update or insert. When the trigger archives any insert or update, it can easily see who made the change using this column (we're interested in which application user, not database user). The problem is that for deletes, we won't get the last modified information from the SQL that is executed because it's just a plain delete from x where y.
为解决此问题，我们想执行一个存储过程，以在执行任何操作之前告诉数据库哪个应用程序用户已登录.然后，审计触发器将在发生删除时查看此值，并使用它来记录执行删除操作的人.
To solve this, we'd like to execute a stored procedure to tell the database which app user is logged in before executing any operation. The audit trigger would then look at this value when a delete happens and use it to record who executed the delete.
是否有任何方法可以拦截开始事务或通过其他方式执行SQL或存储过程，以告诉db哪个用户正在执行事务中将要发生的插入/更新/删除操作，而其余操作则不会执行.操作发生了吗?
Is there any way to intercept the begin transaction or some other way to execute SQL or a stored procedure to tell the db what user is executing the inserts/updates/deletes that are about to happen in the transaction before the rest of the operations happen?
我不太了解数据库方面的工作方式，但如有必要，可以提供更多信息.要点是，存储的proc将创建一个包含会话变量的上下文，并且触发器将在删除时查询该上下文以获取用户ID.
I'm light on details about how the database side will work but can get more if necessary. The gist is that the stored proc will create a context that will hold session variables and the trigger will query that context on delete to get the user ID.
推荐答案
从数据库端开始，这里有一些讨论:
From the database end, there is some discussion on this here:
https://docs.oracle.com /cd/B19306_01/network.102/b14266/apdvprxy.htm#i1010372
从Spring/JPA的角度来看，请参阅下面的8.2节:
From the Spring/JPA side of things see section 8.2 at the below:
http://docs .spring.io/spring-data/jdbc/docs/current/reference/html/orcl.connection.html
Spring文档中给出的示例使用XML配置.如果您使用的是Java配置，则它看起来像:
The example given in the Spring docs uses XML config. If you are using Java config then it looks like:
@Component
@Aspect
public class ClientIdentifierConnectionPreparer implements ConnectionPreparer
{
@AfterReturning(pointcut = "execution(* *.getConnection(..))", returning = "connection")
public Connection prepare(Connection connection) throws SQLException
{
String webAppUser = //from Spring Security Context or wherever;
CallableStatement cs = connection.prepareCall(
"{ call DBMS_SESSION.SET_IDENTIFIER(?) }");
cs.setString(1, webAppUser);
cs.execute();
cs.close();
return connection;
}
}
通过Configuration类启用AspectJ:
Enable AspectJ via a Configuration class:
@Configuration
@EnableAspectJAutoProxy
public class SomeConfigurationClass
{
}
请注意，尽管在Spring的Oracle扩展的特定部分中隐藏了该内容，但在我看来，第8.2节(与8.1节不同)中没有任何特定于Oracle的(执行的Statement除外)，并且一般方法应为只需指定相关过程调用或SQL，即可在任何数据库中实现:
Note that while this is hidden away in a section specific to Spring's Oracle extensions it seems to me that there is nothing in section 8.2 (unlike 8.1) that is Oracle specific (other than the Statement executed) and the general approach should be feasible with any Database simply by specifying the relevant procedure call or SQL:
例如下面的Postgres，所以我不明白为什么使用Postgres的人不能在下面使用这种方法:
Postgres for example as the following so I don't see why anyone using Postgres couldn't use this approach with the below:
https://www.postgresql.org/docs /8.4/static/sql-set-role.html
这篇关于在插入/更新/删除之前，如何使用Hibernate/JPA告诉数据库用户是谁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！