2

Consider situation in which we have tons of java DAO classes managed by Spring. Every method defined in those beans executes SQL code in Spring's Datasource transaction, defined by annotation on a method. Now we have a requirement to run some custom code just before target execution of mentioned methods, this code MUST be executed in the same transaction as defined by annotation on target method.

Update based on R4J comment:

My proxy for DAO calls which should call procedure PR_Adm_Set_Usr in transaction of every target call in DAO package:

@Aspect
@Order(1000)
@Component
public class DaoProxy {

    private static final Logger logger = LoggerFactory.getLogger(DaoProxy.class);
    @Autowired
    private ContextDAO contextDAO;
    @Autowired
    private DataSourceTransactionManager txManager;


   @Before("execution(* xx.yy.dao.*.*(..)) xx.yy.core.dao.*.*(..))")
    @Transactional(propagation = Propagation.SUPPORTS)
    public void setContext() throws Throwable {
        JdbcTemplate jdbc = new JdbcTemplate(txManager.getDataSource());
        if(SecurityContextHolder.getContext()!=null &&
                SecurityContextHolder.getContext().getAuthentication()!=null &&
                SecurityContextHolder.getContext().getAuthentication().getPrincipal()!=null){
            SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbc)
                    .withProcedureName("PR_Adm_Set_Usr")
                    .withoutProcedureColumnMetaDataAccess().declareParameters(
                            new SqlParameter("user", OracleTypes.VARCHAR));
            jdbcCall.setAccessCallParameterMetaData(false);
            MapSqlParameterSource params = new MapSqlParameterSource()
                    .addValue("user",  SecurityContextHolder.getContext().getAuthentication().getName()  );
            jdbcCall.execute(params);
        }else{
            logger.warn("No context available!!!");
        }
    }
}

Example DAO call in SomeDAO class:

 @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public Object shiftDate(Long fileId, Long interval) {
        String a=null;
        a.toString();
        JdbcTemplate jdbc = new JdbcTemplate(txManager.getDataSource());
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbc)
                .withCatalogName("somepackage")
                .withProcedureName("someprocedure")
                .withoutProcedureColumnMetaDataAccess()
                .declareParameters(
                        new SqlParameter("p_file_id", OracleTypes.NUMBER),
                        new SqlParameter("p_interval", OracleTypes.NUMBER)
                );
        jdbcCall.setAccessCallParameterMetaData(false);
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("p_file_id", fileId)
                .addValue("p_interval", interval);
        jdbcCall.execute(params);
        return null;

    }

As You can see, first two lines in DAO call cause NullPointerException, which should cause rollback on AOP setContext() method. Unfortunately this was not working till I added order to my tag in my context config.

<tx:annotation-driven order="1"/> solved the problem.

Maciej Czarnecki
  • 158
  • 2
  • 10
  • 1
    It should be doable with AOP but the tricky part will be order of Proxy creation. If your Aspect-Component will be called before Spring Proxy that manages transaction, then your custom code will be called outside of that transaction - thats one thing you should be careful with. Other than that it should be perfectly doable. In order to give you any example we need to see a sample of your code and a bit more detailed explanation of what you are trying to do. – Rafal G. Nov 23 '15 at 07:27
  • Can't you use Hibernate-interceptors. You can pass custom queries pre, post execution. Also, as you have sessionFactory in Interceptors already in the same transaction, you might(confirm-this) get rollback mechanism as well. Using aspect also sounds good, but if you want it for multiple classes, then I believe Hibernate Interceptors are more efficient... :-) – We are Borg Nov 23 '15 at 08:17
  • @WeareBorg: In this project there is no hibernate available, only spring-jdbc functionality. – Maciej Czarnecki Nov 23 '15 at 08:33
  • @R4J : I have provided some POC code sample in question, can You point out what is wrong in provided solution? – Maciej Czarnecki Nov 23 '15 at 08:35
  • Sorry, my mistake, didn't notice. With Hibernate, this would have been quite easier. – We are Borg Nov 23 '15 at 08:55
  • 1
    Problem solved, I changed the order of – Maciej Czarnecki Nov 23 '15 at 08:56

0 Answers0