I am setting up a Java Spring project with multiple StoredProcedures to two completely different Oracle databases. It is not allowed to use any auto-generated SQL. I didn't find anywhere a complete solution or implementation example so I will try to sum up the question and clean solution here. I sincerely hope this will help someone someday.

You will need a working Spring Boot project.

Please let me know if there is anything confusing and I should explain it better.

Database connection settings

The Database connection Properties (db.properties)

Please place this file in resources folder.

db1.datasource.url=jdbc:oracle:thin:@url:sid
db1.datasource.username=username
db1.datasource.password=password
db1.datasource.driver-class-name=oracle.jdbc.OracleDriver

db2.datasource.url=jdbc:oracle:thin:@url:sid
db2.datasource.username=username
db2.datasource.password=password
db2.datasource.driver-class-name=oracle.jdbc.OracleDriver

Database configuration class (DbConfiguration.java)

@Configuration
@Order(1)
@PropertySource("classpath:/db.properties")
public class DbConfiguration {

  /**
   * Configuration beans for establishing a connection to db1 database.
   * The primary database dataSource is automatically populated to the constructor in StoredProcedure extended class.
   */
  @Bean
  @Primary
  @ConfigurationProperties("db1.datasource")
  public DataSourceProperties db1DataSourceProperties() {
    return new DataSourceProperties();
  }

  @Bean(name = "db1")
  @Primary
  public DataSource db1DataSource() {
    return db1DataSourceProperties().initializeDataSourceBuilder().build();
  }

  /**
   * Configuration beans for establishing a connection to db2 database.
   */
  @Bean
  @ConfigurationProperties("db2.datasource")
  public DataSourceProperties db2DataSourceProperties() {
    return new DataSourceProperties();
  }

  @Bean(name = "db2")
  public DataSource db2DataSource() {
    return db2DataSourceProperties().initializeDataSourceBuilder().build();
  }

---

Db1 stored procedure classes

StoredProcedure for retrieving a single payment (SPGetSinglePayment.java)

/**
 * The type Sp get payment.
 * A StoredProcedure class where we define IN and OUT parameters.
 */
@Component
public class SPGetSinglePayment extends StoredProcedure {
  public static final String PROCEDURE_GET_PAYMENT = "GET_PAYMENT";
  public static final String PROCEDURE_GET_PAYMENT_PARAM_IN_ID = "P_PAYMENT_ID";
  public static final String PROCEDURE_GET_PAYMENT_PARAM_OUT_RESULT = "PAYMENT_RESULT";

  public SPGetSinglePayment(final DataSource dataSource) {
    super(dataSource, PACKAGE_NAME + PROCEDURE_GET_PAYMENT);
    declareParameter(new SqlParameter(PROCEDURE_GET_PAYMENT_PARAM_IN_ID, OracleTypes.VARCHAR));
    declareParameter(new SqlOutParameter(PROCEDURE_GET_PAYMENT_PARAM_OUT_RESULT, OracleTypes.CURSOR));
    compile();
  }
}

StoredProcedure Response builder class (SinglePaymentResponseBuilder.java)

/**
 * The type Payment response builder. Gets an object from Oracle DB and populates POJOs.
 */
@Component
public class SinglePaymentResponseBuilder {

  /**
   * Builds list of payment transaction details from stored procedure result set.
   *
   * @param getPaymentObject       the object containing payment details result set
   * @param getItineraryDataObject the object containing itinerary data result set
   * @return list of payment details for payment
   */
  public List<SinglePaymentDto> build(final Object getPaymentObject, final Object getItineraryDataObject) {
    final List<Map<String, Object>> spMap = getListOfObjectMaps(getPaymentObject);
    final List<SinglePaymentDto> response = new ArrayList<>();
    for (Map<String, Object> dtos : spMap) {
      SinglePaymentDto payment = new SinglePaymentDto(
          new PaymentInfo(getStringValue(dtos.get(PaymentInfo.PAYMENT_ID)),
... build and return response

StoredProcedure Helper class (StoredProcedureHelper.java)

Here we actually execute two stored procedures to a single database.

/**
 * Contains methods to call Oracle prepared statements. Responsible for handling procedure specific input and output parameters.
 */
@Component
public class StoredProcedureHelper {
  public static final String PACKAGE_NAME = "A_PACKAGE_NAME.";
  
  private final SPGetSinglePayment getSinglePayment;
  private final SinglePaymentResponseBuilder singlePaymentResponseBuilder;


  @Autowired
  public StoredProcedureHelper(
        final SPGetSinglePayment getSinglePayment,
        final SinglePaymentResponseBuilder  singlePaymentResponseBuilder,
                                                  ...){
    this.getSinglePayment = getSinglePayment;
    this.singlePaymentResponseBuilder = singlePaymentResponseBuilder;
    ...
  }

  /**
   * Calls stored procedure to get all payment details for given payment.
   *
   * @param id the payment id
   * @return payment details
   */
  public List<SinglePaymentDto> findSinglePayment(final String id) {
    LOG.info(LOG_PATTERN, SPGetSinglePayment.class.getSimpleName(),
        PACKAGE_NAME, PROCEDURE_GET_PAYMENT);
    Object spGetPaymentResult = getSinglePayment.execute(id).get(PROCEDURE_GET_PAYMENT_PARAM_OUT_RESULT);
    Object spGetItineraryDataResult = getItineraryData.execute(id).get(PROCEDURE_GET_ITINERARY_DATA_PARAM_OUT_RESULT);
    return singlePaymentResponseBuilder.build(spGetPaymentResult, spGetItineraryDataResult);
  }

---

Db2 stored procedure classes

StoredProcedure for retrieving a decrypted toothbrush from its identifier token (SPGetToothbrush.java)

I'd like to expose just below class here. Please note that if you'd like to use a db2 you will have to define it by @Qualifier annotation. Other classes will follow the above pattern for each stored procedure. On request I can also provide unit test examples.

/**
 * The type Sp get toothbrush.
 * A StoredProcedure class where we define IN and OUT parameters.
 */
@Component
public class SPGetToothbrush extends StoredProcedure {
  public static final String PROCEDURE_GET_TOOTHBRUSH = "GET_IDENTIFIER";
  public static final String PROCEDURE_GET_TOOTHBRUSH_PARAM_IN_INSTRUMENT_ID = "P_TOKEN";
  public static final String PROCEDURE_GET_TOOTHBRUSH_PARAM_OUT_RESULT = "OUT_IDENTIFIER";

  /**
   * Instantiates a new Sp get toothbrush.
   *
   * @param dataSource is populated by db2 properties by use of @Qualifier.
   */
  public SPGetToothbrush(@Qualifier("db2") final DataSource dataSource) {
    super(dataSource, StoredProcedureToothbrushHelper.PACKAGE_NAME + PROCEDURE_GET_TOOTHBRUSH);
    declareParameter(new SqlParameter(PROCEDURE_GET_TOOTHBRUSH_PARAM_IN_INSTRUMENT_ID, OracleTypes.VARCHAR));
    declareParameter(new SqlOutParameter(PROCEDURE_GET_TOOTHBRUSH_PARAM_OUT_RESULT, OracleTypes.VARCHAR));
    compile();
  }
}