SimpleJdbcCall: Two Stored Procedure call in a class hang up application

1.2k Views Asked by At

I'm using SimpleJdbcCall to make a call to two stored procedure in two different functions using JdbcTemplate. The first Stored procedure call get successfull, but second Stored procedure call just hangs up & following message appears in log & nothing goes forward:

2019-10-23 02:00:33,043 DEBUG [http-nio-8080-exec-13:org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource

Here is the source code for config & code part

DataSourceConfig.java

@Configuration
public class DataSourceConfig {
    @Autowired
    private DataSource dataSource;

    @Bean
    public JdbcClientDetailsService jdbcClientDetailsService() {
        return new JdbcClientDetailsService(dataSource);
    }

}

OAuth2AuthorizationServerConfigurer

@Configuration
@EnableAuthorizationServer
public class OAuth2AuthorizationServerConfigurer extends AuthorizationServerConfigurerAdapter {

    @Autowired
    private JdbcClientDetailsService jdbcClientDetailsService;

    @Override
    public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
//        clients.jdbc(dataSource);
        clients.withClientDetails(jdbcClientDetailsService);
    }
}

Class where two Stored Procedures are getting called & application hang's up at 2nd Stored Procedure call. (First stored procedure call is getting executed. If no data found in it's result, it will go into else block which is currently happening)

CompanyService.java

@Component
public class CompanyService {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    private static Logger logger = LoggerFactory.getLogger(CompanyService.class);


    public CompanyInfo getCompanyDetails(Integer companyId){

        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);
        simpleJdbcCall.withSchemaName("foo")
                .withCatalogName("foo_pkg")
                .withProcedureName("SELECT_COMPANY"); //select company Stored Procedure

        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("companyId", companyId)
                .addValue("name",null)
                .addValue("uuid",null);
        try {
            Map<String, Object> out = simpleJdbcCall.execute(in);
            return //some method to convert out into CompanyInfo
        }catch (DataAccessException ex){
            logger.error("getCompanyDetails"+ex.getMessage());
            return null;
        }
    }

    public CompanyInfo registerCompany(RegisterCompany registerCompany) {
        CompanyInfo companyInfo = getCompanyDetails(registerCompany.getCompanyId());
        if(companyInfo!=null){
            //TODO: throw an exception
        }
        else{
            SimpleJdbcCall mergeJdbcCall = new SimpleJdbcCall(jdbcTemplate);
            mergeJdbcCall.withSchemaName("foo")
                    .withCatalogName("foo_pkg")
                    .withProcedureName("MERGE_COMPANY"); //Merge company Stored Procedure

            SqlParameterSource in = new MapSqlParameterSource()
                    .addValue("companyId", registerCompany.getCompanyPartyClassId())
                    .addValue("name",getName())
                    .addValue("uuid",getUuid());

            Map<String, Object> out = mergeJdbcCall.execute(in); //It's getting hang up at this level

        }
        return null;
    }
}

What is the configuration I've missed here, which is causing an issue. I also went throug details of SimpleJdbcCall which describe

A SimpleJdbcCall is a multi-threaded, reusable object representing a call to a stored procedure or a stored function.

that's why I created two different object of it in class. Tried with defining it at class level as well, still the same issue. Is it the case that SimpleJdbcCall works for one call in one class ? What are the other alternatives that I can use ? (Apart from PreparedStatement)

0

There are 0 best solutions below