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.
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
@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();
}
/**
* 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();
}
}
/**
* 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
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);
}
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();
}
}