To create a Spring Boot application that calls an Oracle
stored procedure `STORED_PRC_NAME` in a package `PACKAGE_NAME`, follow these
steps:
1. Set Up Your Spring Boot Project: Use Spring Initializr or
your IDE to create a new Spring Boot project with the necessary dependencies.
Step 1: Create the
Project
1. Initialize a Spring Boot project: Include the following
dependencies:
- Spring Web
- Spring Data JPA
- Oracle JDBC
- Spring Boot
Starter JDBC
2. Project Structure: Ensure your project has the following
structure:
src/main/java/com/example/oracleprocedure
├──
OracleProcedureApplication.java
├── config
│ └── DataSourceConfig.java
├──
repository
│ └── OracleRepository.java
└── service
└──
OracleService.java
Step 2: Configure
Oracle DataSource
Create a configuration class `DataSourceConfig.java` to set
up the Oracle DataSource.
JAVA CODE:
package com.example.oracleprocedure.config;
import oracle.jdbc.pool.OracleDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import
org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
@Configuration
public class DataSourceConfig {
@Bean
public DataSource
dataSource() throws SQLException {
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL("jdbc:oracle:thin:@//your_db_url:1521/your_service_name");
dataSource.setUser("your_username");
dataSource.setPassword("your_password");
return
dataSource;
}
@Bean
public
JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new
JdbcTemplate(dataSource);
}
@Bean
public
DataSourceTransactionManager transactionManager(DataSource dataSource) {
return new
DataSourceTransactionManager(dataSource);
}
}
```
Step 3: Create a
Repository to Call the Stored Procedure
Create a repository class `OracleRepository.java` to handle
the stored procedure call.
JAVA CODE:
package com.example.oracleprocedure.repository;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.Types;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
@Repository
public class OracleRepository {
private final
JdbcTemplate jdbcTemplate;
private
SimpleJdbcCall simpleJdbcCall;
public
OracleRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@PostConstruct
public void init()
{
simpleJdbcCall
= new SimpleJdbcCall(jdbcTemplate)
.withCatalogName("PACKAGE_NAME") // Package name
.withProcedureName("STORED_PRC_NAME") // Procedure name
.declareParameters(
new
SqlParameter("input_param1", Types.VARCHAR),
new
SqlParameter("input_param2", Types.INTEGER),
new
SqlOutParameter("output_param", Types.VARCHAR)
);
}
public String
callStoredProcedure(String param1, int param2) {
Map<String,
Object> inParams = new HashMap<>();
inParams.put("input_param1", param1);
inParams.put("input_param2", param2);
Map<String,
Object> outParams = simpleJdbcCall.execute(inParams);
return
(String) outParams.get("output_param");
}
}
```
Step 4: Create a
Service to Use the Repository
Create a service class `OracleService.java` to use the
repository.
JAVA CODE:
package com.example.oracleprocedure.service;
import
com.example.oracleprocedure.repository.OracleRepository;
import org.springframework.stereotype.Service;
@Service
public class OracleService {
private final
OracleRepository oracleRepository;
public
OracleService(OracleRepository oracleRepository) {
this.oracleRepository = oracleRepository;
}
public String
callStoredProcedure(String param1, int param2) {
return
oracleRepository.callStoredProcedure(param1, param2);
}
}
Step 5: Main
Application Class
Create the main application class
`OracleProcedureApplication.java`.
JAVA CODE:
package com.example.oracleprocedure;
import com.example.oracleprocedure.service.OracleService;
import
org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import
org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class OracleProcedureApplication implements
CommandLineRunner {
@Autowired
private
OracleService oracleService;
public static void
main(String[] args) {
SpringApplication.run(OracleProcedureApplication.class, args);
}
@Override
public void
run(String... args) throws Exception {
String result
= oracleService.callStoredProcedure("testParam1", 123);
System.out.println("Stored Procedure Output: " + result);
}
}
Step 6: Add
Dependencies in `pom.xml`
Here is the complete `pom.xml` for the project:
XML Code
<project
xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>oracleprocedure</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>oracle-procedure</name>
<description>Spring Boot project to call an Oracle stored
procedure</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.0.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<!-- Spring
Boot Starter Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Spring
Boot Starter Data JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- Spring
Boot Starter JDBC -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- Oracle
JDBC -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.8.0.0</version>
</dependency>
<!-- Spring
Boot Starter Test -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
```
Step 7: Run Your
Application
Run your Spring Boot application. It should call the Oracle
stored procedure `STORED_PRC_NAME` and print the result.
Make sure your Oracle database is properly set up and
accessible from your application. Also, adjust the stored procedure call
parameters according to your actual stored procedure's input and output
parameters.
