MyBatis 3 + Spring 4 Stored Procedure Not Being Called

[Joseph Kratz]

I am having major issues trying to call a stored procedure in Oracle 11g with MyBatis. I have a question on stack overflow http://stackoverflow.com/questions/31924239/mybatis-not-persisting-stored-procedure-call.

Here is the gist. I am using Spring Boot 1.2.5 with MyBatis 3.3.0 and Spring-MyBatis 1.2.3. I have a stored procedure in Oracle which takes NO parameters. It processes data from a staging table and inserts or updates the data in others tables. The stored procedure itself calls commit before it exists. I CAN run the stored procedure perfectly fine from SQL Plus and Oracle SQL Developer. The stored procedure works as intended. However when I try to call the stored procedure from Java using MyBatis nothing happens. I am convinced the stored procedure is never actually being called as no warning, errors, messages, exceptions are ever in the logs.

My configuration class for mybatis is below.

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.transaction.SpringManagedTransactionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;

@Configuration
@MapperScan("org.something.core.persistence")
public class PersistenceConfig {

    @Autowired
    Environment environment;

    @Bean(name = "datasource")
    public ComboPooledDataSource dataSource() throws PropertyVetoException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass(environment.getRequiredProperty("c3p0.driver"));
        dataSource.setJdbcUrl(environment.getRequiredProperty("c3p0.url"));
        dataSource.setUser(environment.getRequiredProperty("c3p0.user"));
        dataSource.setPassword(environment.getRequiredProperty("c3p0.password"));
        dataSource.setInitialPoolSize(environment.getRequiredProperty("c3p0.initialPoolSize", Integer.class));
        dataSource.setMaxPoolSize(environment.getRequiredProperty("c3p0.maxPoolSize", Integer.class));
        dataSource.setMinPoolSize(environment.getRequiredProperty("c3p0.minPoolSize", Integer.class));
        dataSource.setAcquireIncrement(environment.getRequiredProperty("c3p0.acquireIncrement", Integer.class));
        dataSource.setMaxStatements(environment.getRequiredProperty("c3p0.maxStatements", Integer.class));
        dataSource.setMaxIdleTime(environment.getRequiredProperty("c3p0.maxIdleTime", Integer.class));
        return dataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setTypeAliasesPackage("org.something.core.domain");
        sessionFactory.setTransactionFactory(springManagedTransactionFactory());
        return sessionFactory.getObject();
    }

    @Bean(name = "transactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager() throws PropertyVetoException{
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public SpringManagedTransactionFactory springManagedTransactionFactory() {
        return new SpringManagedTransactionFactory();
    }

}

Here is my Interface

public interface StagedDataMapper {

    void processDirectory();

    List<StageDirectory> getStagedDirectory(long institutionId);

    List<StageAppointment> getStagedAppointment(long institutionId);
}

My XML mapper file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.something.core.persistence.StagedDataMapper">

        <insert id="processDirectory" statementType="CALLABLE">
            { CALL PKG_DIRECTORY.SP_PROCESS_STAGED_DATA() }
        </insert>

</mapper>

The controller which is calling the mapper

@Transactional
@RequestMapping(value = "/directory/process", method = RequestMethod.POST)
public ResponseEntity processStagedDirectory() {
    stagedDataMapper.processDirectory();
    return new ResponseEntity(HttpStatus.ACCEPTED);
}

All my other queries are working as intended, but stored procedures are not working at all.

I have tried insert, select, and update when calling the stored procedure, non of which work.

Any ideas? This is starting to become a real issue, so much so I've considered using JDBC!

Logs while in debug mode:

22:45:37.582 DEBUG AbstractBeanFactory.doGetBean - Returning cached instance of singleton bean 'stagedDataController'
22:45:37.588 DEBUG AbstractBeanFactory.doGetBean - Returning cached instance of singleton bean 'transactionManager'
22:45:37.594 DEBUG AbstractPlatformTransactionManager.getTransaction - Creating new transaction with name [org.ohtech.innovationexchange.web.controller.StagedDataController.processStagedDirectory]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
22:45:37.594 DEBUG DataSourceTransactionManager.doBegin - Acquired Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@bd9a309 [wrapping: oracle.jdbc.driver.T4CConnection@74f79605]] for JDBC transaction
22:45:37.597 DEBUG DataSourceTransactionManager.doBegin - Switching JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@bd9a309 [wrapping: oracle.jdbc.driver.T4CConnection@74f79605]] to manual commit
22:45:37.598 DEBUG SqlSessionUtils.getSqlSession - Creating a new SqlSession
22:45:37.598 DEBUG SqlSessionUtils.getSqlSession - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ee3401d]
22:45:37.603 DEBUG SpringManagedTransaction.openConnection - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@bd9a309 [wrapping: oracle.jdbc.driver.T4CConnection@74f79605]] will be managed by Spring
22:45:37.603 DEBUG BaseJdbcLogger.debug - ==>  Preparing: { CALL PKG_DIRECTORY.SP_PROCESS_STAGED_DATA() }
22:45:37.624 DEBUG BaseJdbcLogger.debug - ==> Parameters:
22:45:37.919 DEBUG SqlSessionUtils.closeSqlSession - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ee3401d]
22:45:37.920 DEBUG SqlSessionUtils$SqlSessionSynchronization.beforeCommit - Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ee3401d]
22:45:37.921 DEBUG SqlSessionUtils$SqlSessionSynchronization.beforeCompletion - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ee3401d]
22:45:37.921 DEBUG SqlSessionUtils$SqlSessionSynchronization.beforeCompletion - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ee3401d]
22:45:37.921 DEBUG AbstractPlatformTransactionManager.processCommit - Initiating transaction commit
22:45:37.921 DEBUG DataSourceTransactionManager.doCommit - Committing JDBC transaction on Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@bd9a309 [wrapping: oracle.jdbc.driver.T4CConnection@74f79605]]
22:45:37.998 DEBUG DataSourceTransactionManager.doCleanupAfterCompletion - Releasing JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@bd9a309 [wrapping: oracle.jdbc.driver.T4CConnection@74f79605]] after transaction
22:45:37.998 DEBUG DataSourceUtils.doReleaseConnection - Returning JDBC Connection to DataSource
22:45:38.001 DEBUG DispatcherServlet.processDispatchResult - Null ModelAndView returned to DispatcherServlet with name 'dispatcherServlet': assuming HandlerAdapter completed request handling
22:45:38.001 DEBUG FrameworkServlet.processRequest - Successfully completed request

[Guy Rouillier]

You say you are convinced that nothing is happening, but I see this in your logs:

 

22:45:37.603 DEBUG BaseJdbcLogger.debug - ==>  Preparing: { CALL PKG_DIRECTORY.SP_PROCESS_STAGED_DATA() }
22:45:37.624 DEBUG BaseJdbcLogger.debug - ==> Parameters:

 

So, your stored procedure is being called.

 

Nonetheless, you should turn on MyBatis statement logging so you can see exactly what statements are being invoked.  I can't tell from your code what logging framework you are using, but under log4j and slf4j, you'd want to use TRACE level logging, so MyBatis logs both the statement invoked and the parameters provided.

 

One other thing confuses me.  You've annotated your controller method as @Transactional, which means the supplied transaction manager controls the transactions.  But then you also said you put a commit in your stored procedure.  That seems like a conflict.  If you have your container controlling transactions, then you should not be doing your own commits.

 

--

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

---

This email has been checked for viruses by Avast antivirus software. www.avast.com