Spring Transaction logging rolled back but not

Question

These are my classes and configurations,

ServiceImpl.java

@Override
@Transactional(propagation = Propagation.REQUIRED,rollbackFor = Exception.class)
public InsertUpdateSuccessBean insertNewSiteEntry(SiteEntry newSiteBean) throws SQLException {
    dao.insert1();
    dao.insert2();
    except();
    return new InsertUpdateSuccessBean(true,LoggerConstants.NEW_SITE_ENTRY_SUCCESS);
}
private void except()throws SQLException{
throw new SQLException();
}

DAOImpl.java

@Repository
public class DAOImpl implements DAO 
{
    @Autowired
    private DataSourceTransactionManager transManager;
    @Autowired
    private CommonDAOUtils commonUtils;
    private static final Logger logger = Logger.getLogger(HunterDAOImpl.class) ;
    @Override
    public Integer insert1() throws SQLException { 
    Integer insertNumRows = 0;
    Connection connectionObject = transManager.getDataSource().getConnection();
    PreparedStatement psObject = connectionObject.prepareStatement(
 SQLQuery );
    insertNumRows = psObject.executeUpdate();
    commonUtils.closer(null,psObject,connectionObject);
        // Function to close open connection resultsets statement objects
    return insertNumRows;
}

     @Override
    public Integer insert2() throws SQLException {
    Integer insertNumRows = 0;
    Connection connectionObject = transManager.getDataSource().getConnection();
    PreparedStatement psObject = connectionObject.prepareStatement(
 SQLQuery );
    insertNumRows = psObject.executeUpdate();
    commonUtils.closer(null,psObject,connectionObject);
        // Function to close open connection resultsets statement objects
    return insertNumRows;
}
}

AppConfig.java

import com.interceptors.AppInterceptor;
import com.utils.Constants;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.*;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;
import org.springframework.core.env.Environment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.client.ClientHttpRequestFactory;
import org.springframework.http.client.HttpComponentsClientHttpRequestFactory;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.client.RestTemplate;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import org.springframework.web.servlet.view.InternalResourceViewResolver;

@Component
@Configuration
@EnableWebMvc
@Configurable
@ComponentScan(basePackages = {Constants.BASE_PACKAGE})
@EnableAspectJAutoProxy(proxyTargetClass = true)
@PropertySource(Constants.DB_PROPERTIES_PATH)
@EnableTransactionManagement
public class AppConfig extends WebMvcConfigurerAdapter implements EnvironmentAware{

    @Autowired
    Environment environmentObject;

    @Bean
    public static PropertySourcesPlaceholderConfigurer propertySourcesPlaceholderConfigurer(){
        PropertySourcesPlaceholderConfigurer placeHolder =new PropertySourcesPlaceholderConfigurer();
        placeHolder.setLocation(new ClassPathResource(Constants.PROP_FILE_NAME));
        return placeHolder;
    }

    @Bean
    public InternalResourceViewResolver viewResolver(){
        InternalResourceViewResolver internalResourceViewResolver = new InternalResourceViewResolver();
        internalResourceViewResolver.setPrefix(Constants.ROOT_PATH);
        internalResourceViewResolver.setSuffix(Constants.JSP_DOT_EXTENSION);
        return internalResourceViewResolver;
    }

    @Override
    public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
        configurer.enable();
    }

    @Bean
    public DriverManagerDataSource dataSource(){
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setDriverClassName(environmentObject.getProperty(Constants.JDBC_DRIVERCLASS_NAME_PROP_FILE));
        driverManagerDataSource.setUrl(environmentObject.getProperty(Constants.JDBC_URL_PROP_FILE));
        driverManagerDataSource.setUsername(environmentObject.getProperty(Constants.JDBC_USERNAME_PROP_FILE));
        driverManagerDataSource.setPassword(new String(Constants.PASSWORD));
        return driverManagerDataSource;
    }

    @Bean
    public AutowiredAnnotationBeanPostProcessor postProcessorBean(){
        return new AutowiredAnnotationBeanPostProcessor();
    }



    private ClientHttpRequestFactory getClientHttpRequestFactory() {
        int timeout = 5000;
        HttpComponentsClientHttpRequestFactory clientHttpRequestFactory = new HttpComponentsClientHttpRequestFactory();
        clientHttpRequestFactory.setConnectTimeout(timeout);
        return clientHttpRequestFactory;
    }

    @Bean
    public DataSourceTransactionManager transactionManager(){
        final DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource());
        transactionManager.setRollbackOnCommitFailure(true);
        return transactionManager;
    }
}

And I also have an around advice for my project:

@Aspect
@Component
public class AroundAdvice
{
    private static final Logger logger = Logger.getLogger(AroundAdvice.class) ;
    // Add pointcuts here - package names here for around advice
    @Around("execution(* com.beans.*.*(..)) || " +
            "execution(* com.config.*.*(..)) || " +
            "execution(* com.controller.*.*(..)) || " +
            "execution(* com.dao.*.*(..)) || " +
            "execution(* com.service.*.*(..)) || " +
            "execution(* com.utils.*.*(..)) || "+
            "execution(* com.interceptors.*.*(..))")
    public Object aroundAdviceMethod(ProceedingJoinPoint proceedingObject)throws Throwable{
        MethodSignature methodSignObject = (MethodSignature) proceedingObject.getSignature();
        logger.debug(Constants.EXECUTING_METH_STR + methodSignObject.getMethod());
        Object value = null;
        try {
            value = proceedingObject.proceed();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(Constants.EXCEPTION_ASPECT_STR+methodSignObject.getMethod());
            logger.error(Constants.EXCEPTION_MESSAGE,e);
            throw e;
        }
        logger.debug(Constants.RETURN_STR+value);
        return value;
    }
}

On executing this flow, the inserts are successful, however when the exception is thrown, it is not rolling back. However, my logger reads that rolling back is initialized and done as follows,

14:11:51 DEBUG DataSourceTransactionManager:851 - Initiating transaction rollback
14:11:51 DEBUG DataSourceTransactionManager:325 - Rolling back JDBC transaction on Connection [org.postgresql.jdbc4.Jdbc4Connection@3b467b21]
14:11:51 DEBUG DataSourceTransactionManager:368 - Releasing JDBC Connection [org.postgresql.jdbc4.Jdbc4Connection@3b467b21] after transaction

Please let me know if I am missing something

Answer 1

The problem is your dao. You are opening connections yourself and therefor bypass all transaction management. Your dao is to complex just use a JdbcTemplate instead of your current code.

@Repository
public class DAOImpl implements DAO {
    private static final Logger logger = Logger.getLogger(HunterDAOImpl.class) ;

    private final JdbcTemplate jdbc;

    public DAOImpl(DataSource dataSource) {
        this.jdbc = new JdbcTemplate(dataSource);
    }

    @Override
    public Integer insert1() throws SQLException { 
        return jdbc.update(SQLQuery);
    }

     @Override
    public Integer insert2() throws SQLException {
        return jdbc.update(SQLQuery);
    }

}

This will do exactly the same as your code, with one main difference it will use the Connection opened when the transaction was started. Your sample used 3 separate connections and thus 3 individual transactions instead of 1 single transaction.