I have a spring boot application using hikari pool as usual.
I need to execute a pl/sql procedure before each http request. Will this connection be used with the context of the request?
I’m struggling to see if it is the same or is another connection from the pool.
I’ve tried to use a jpa interceptor but I couldnt make it work.
2
You can try to combine the “open session in view” approach with a custom interceptor, but setting the order of this as the lowest one, so that Spring opens the transaction before the procedure call.
These are the steps:
Activate the open session in view, setting this property in the application.yaml:
spring:
jpa:
open-in-view: true
Implement an interceptor that calls the db procedure in the preHandle method; since it’s a @Component, you can annotate it with @Transactional and inject other beans (i.e. services or repositories) for db procedure calling:
@Component
@Transactional
public class OsivProcedureInterceptor implements HandlerInterceptor {
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
throws Exception {
// Your db procedure call here...
return true;
}
}
Implement a configuration class that tells Spring that you have this custom interceptor, and that you want it to be executed after the others (i.e. after the OpenEntityManagerInViewInterceptor that is strictly connected to the property you set before):
@Configuration
public class OsivWebConfiguration implements WebMvcConfigurer {
@Autowired OsivProcedureInterceptor osivProcedureInterceptor;
@Override
public void addInterceptors(InterceptorRegistry registry) {
registry.addInterceptor(osivProcedureInterceptor).order(Ordered.LOWEST_PRECEDENCE);
}
}
Doing this you will see that the EntityManager is opened before your custom interceptor, and that the transaction would span until the controller completes the response building.