Connecting JDBC Procedure Calls Using Spring Boot

In this guide, we’ll walk through the steps to connect and invoke procedure calls using Spring Boot, which is a popular framework for building Java applications. Procedure calls can be used to execute code or operations on a remote server or database. We will assume you have a basic understanding of Spring Boot and Java development. Let’s get started:

Step 1: Set Up Your Spring Boot Project

1. Create a new Spring Boot project using your preferred IDE (e.g., IntelliJ IDEA, Eclipse) or Spring Initializr (https://start.spring.io).
2. Include the necessary dependencies for your project. Ensure you have the required libraries for the database or service you want to connect to.

Step 2: Configure Database or Service Connection

1. If you’re connecting to a database, add the relevant database configuration properties in your application.properties or application.yml file. For example:

spring:
  datasource:
    url: jdbc:oracle:thin:@${host}:${port}:${name}
    username: ${username}
    password: ${password}
    hikari:
      connection-test-query: SELECT 1 FROM DUAL
      connectionTimeout: 30000
      idleTimeout: 100000
      minimumIdle: 5
      maximum-pool-size: 10
      maxLifetime: 0
      registerMbeans: true
      leak-detection-threshold: 30000
      data-source-properties: 
        v$session.program: proc-test

Customize the properties based on your database setup.

Add below dependencies

  <dependency>
   <groupId>com.oracle</groupId>
   <artifactId>ojdbc7</artifactId>
   <version>12.1.0.2</version>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jdbc</artifactId>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>

2. If you’re connecting to a remote service, create a configuration class that contains the necessary properties (e.g., service URL, authentication details) as Spring beans.

Step 3: Create the Procedure Call

1. Define the procedure call interface or class. This will contain the method(s) that represent the procedure(s) you want to call. For example:

package com.medium.proc.test.service;


public interface MyProcedureService {

String executeProcedure(Request request);

}

Step 4: Implement the Procedure Call

1. Create a class that implements the procedure call interface or class you defined in Step 3. This implementation will contain the logic to invoke the procedure. For database connections, you may use JdbcTemplate or Spring Data JPA to execute stored procedures.

Step 5: Configure Spring to Recognize the Procedure Call Bean

1. In your Spring Boot configuration class, add the procedure call bean by annotating the implementation class with @Service or @Component:

package com.medium.proc.test.repository;


public interface MyProcedureRepository {

 String testProc(Request request);

}

package com.medium.proc.test.repository;

import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.stereotype.Repository;


import lombok.extern.log4j.Log4j2;
import org.springframework.util.ObjectUtils;

@Log4j2
@Repository
public class MyProcedureRepositoryImpl implements MyProcedureRepository{

 private TestProc testProc;
 private DataSource dataSource;

 
 @Autowired
 public MyProcedureRepositoryImpl(DataSource datasource) {
  this.dataSource = datasource;
  this.testProc = new TestProc();
 }

 @Override
 public String  testProc(Request request) {
  String response;
  Map<String, Object> responseMap = new HashMap<>();
    responseMap = testProc.execute(request);
  return responseMap.get("result").toString()
 }

protected class TestProc extends StoredProcedure {
  
  private TestProc() {
   super(dataSource, "PACKAGE.PROC_TEST");
   declareParameter(new SqlParameter("evnt", Types.VARCHAR));
   declareParameter(new SqlParameter("session", Types.VARCHAR));
   declareParameter(new SqlParameter("key", Types.VARCHAR));
   

   declareParameter(new SqlOutParameter("result", Types.VARCHAR));
   declareParameter(new SqlOutParameter("msg", Types.VARCHAR));
   
   compile();
  }
  
  public Map<String, Object> execute(Request request) {


   Map<String, Object> inParams = new HashMap<>();

   inParams.put("Event", request.getEvent);
   inParams.put("session", request.getEvent);
   inParams.put("key", request.getEvent);

   log.debug("Input params {}", inParams);
   Map<String, Object> outParams = super.execute(inParams);
   log.debug("Output params {}", outParams);
   return outParams;
  }
 }

}

@Service
public class MyProcedureServiceImpl implements MyProcedureService {

 private MyProcedureRepository myProcedureRepository;

 @Autowired
 public MyProcedureService(MyProcedureRepository repo) {
   this.myProcedureServiceRepository = repo; 
 }

@Override
 public String executeProcedure(Request request) {

String response = myProcedureRepository.testProc(request);

}

}

Step 6: Use the Procedure Call

1. Inject the procedure call bean into the classes where you want to use it. You can use constructor injection or field injection. For example:

@RestController
public class MyController {
    private final MyProcedureService myProcedureService;

    public MyController(MyProcedureService myProcedureService) {
        this.myProcedureService = myProcedureService;
    }

 @PostMapping(value = "/v1/proc-test", produces = MediaType.APPLICATION_JSON_VALUE, consumes = MediaType.APPLICATION_JSON_VALUE)
 public ResponseEntity<String> procTest(
   @RequestBody TestRequest request) {

  String response = myProcedureService.executeProcedure(request);
 
  return new ResponseEntity<>(response, HttpStatus.OK);
 }
}

Step 7: Run Your Spring Boot Application

1. Run your Spring Boot application and test the procedure call using your chosen method of invocation (e.g., REST endpoint, service method, scheduled task).

That’s it! You’ve successfully connected and invoked procedure calls using Spring Boot. Depending on your specific use case, you may need to handle exceptions, perform additional configuration, or set up security. Remember to follow best practices and keep your code organized and maintainable. Happy coding!