0

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)

AshwinK
  • 1,039
  • 1
  • 12
  • 31
  • From registerCompany method you are making a call to getCompanyDetails, and you are saying that you are receiving some data from this call, later you are validating "if(companyInfo!=null)" which means if you get data it never goes into else block where you have your second stored procedure. Correct me if we are on a different page. – mahadev kalyan srikanth Oct 22 '19 at 21:10
  • I'll update my post. If companyInfo exist, it will return data else it will go into else block. So in nutshell first SP is getting executed & if no data found then it will go into else blocl. – AshwinK Oct 22 '19 at 21:14
  • Okay, did you try executing the second stored procedure without executing the first stored procedure to make sure individually both are working fine. – mahadev kalyan srikanth Oct 22 '19 at 21:23
  • Yup, I tried that & in that case both Stored Procedures are working individually. – AshwinK Oct 22 '19 at 21:27
  • Here I noticed few annotations, like EnableTransactionManagement on your DataSourceConfig class and Transactional on CompanyService are not there. Though not completely sure if it is causing this issue but it looks like with first call it is getting connection but not able to get for the second call since may be it is not aware of the transactional context. – Ashok Prajapati Oct 23 '19 at 05:48
  • @AshokPrajapati I've added them and tried with the same. Still not working. It give the same issue. – AshwinK Oct 31 '19 at 13:38

0 Answers0