SpringBoot Demo Project for executing Oracle Stored Procedure

Pre-requisite

  • This requires Oracle DB. Follow this article to spin an Oracle docker image. Alternatively, you can use the docker-compose.yml
  • Once the database is up and running, refer to oracle-setup.sql to set up the DB user, table and procedure.

We can invoke Oracle stored procedure in couple different ways.

  1. Using Spring Data JPA
  2. Using the javax.persistence. We used this method in our case

Irrespective of which way we choose, there are some key aspects/properties we should set when using spring-data (epsecially when we don’t let Spring handle the Database related beans)

  1. For the @Entity classes to map properly to the right columns names to the database we need to set the NamingStategy appropriately. Since we created our Database Beans, spring will not take care of this. The following JPA properites must be set explicitly

     spring:
       application:
         name: spring-oracle-stored-proc
       datasource:
         username: pivotal
         password: bekind
         url: "jdbc:oracle:thin:@//localhost/ORCLPDB1.localdomain"
       jpa:
         hibernate:
           ddl-auto: none
           naming:
             physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
             implicit-strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
         properties:
           hibernate:
             proc.param_null_passing: true
         show-sql: true
         database-platform: org.hibernate.dialect.Oracle10gDialect
    
  2. In scenarios where the database related beans are configured inside the Java class, refer below

     @Configuration
     @EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class, HibernateJpaAutoConfiguration.class,
             DataSourceTransactionManagerAutoConfiguration.class})
     @EnableTransactionManagement
     @EnableJpaRepositories(basePackages = {"io.pivotal.storedproc.repository"})
     public class DBConfiguration {
        
         @Primary
         @Bean
         @ConfigurationProperties(prefix = "spring.datasource")
         public DataSource dataSource() {
             return DataSourceBuilder.create().build();
         }
        
         @Primary
         @Bean
         LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        
             HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
             vendorAdapter.setGenerateDdl(false);
             vendorAdapter.setShowSql(true);
        
             LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
             factoryBean.setDataSource(dataSource());
             factoryBean.setJpaVendorAdapter(vendorAdapter);
             factoryBean.setPackagesToScan("io.pivotal.storedproc.domain");
        
             Properties jpaProperties = new Properties();
             jpaProperties.put("hibernate.proc.param_null_passing", new Boolean(true));
             jpaProperties.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
             jpaProperties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName());
             factoryBean.setJpaProperties(jpaProperties);
        
             return factoryBean;
         }
        
         @Primary
         @Bean
         public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
             return new JpaTransactionManager(entityManagerFactory);
         }
     }
    
  3. Inside our @Repository class we can declare the stored procedure as follows. In th example below, we have all 3 parameter types i.e. IN, INOUT and OUT

     @Repository
     public class ProcedureRepository {
        
         @Autowired
         EntityManager entityManager;
        
         public ProcedureResult addEmployeeThroughProcedure(String firstName, String lastName, String email) {
        
             StoredProcedureQuery proc = entityManager.createStoredProcedureQuery(
                     "EMPLOYEEPROCEDURE");
             proc.registerStoredProcedureParameter("FIRST_NAME", String.class, ParameterMode.IN);
             proc.registerStoredProcedureParameter("LAST_NAME", String.class, ParameterMode.IN);
             proc.registerStoredProcedureParameter("EMAIL", String.class, ParameterMode.INOUT);
             proc.registerStoredProcedureParameter("ID", Integer.class, ParameterMode.OUT);
             proc.registerStoredProcedureParameter("CREATED_AT", Date.class, ParameterMode.OUT);
        
             proc.setParameter("FIRST_NAME", firstName);
             proc.setParameter("LAST_NAME", lastName);
             proc.setParameter("EMAIL", email);
             proc.execute();
        
             return ProcedureResult.builder()
                     .email((String) proc.getOutputParameterValue("EMAIL"))
                     .id((Integer) proc.getOutputParameterValue("ID"))
                     .createdAt((Date) proc.getOutputParameterValue("CREATED_AT"))
                     .build();
         }
     }
    

Testing

  1. Perform a PUT request to the endpoint /add SCREENSHOT
  2. Perform a PUT request to the endpoint /random SCREENSHOT
  3. Perform a PUT request to the endpoint /procedure SCREENSHOT
  4. Perform a PUT request to the endpoint /null. This inserts NULL value to the EMPLOYEE.LAST_NAME column SCREENSHOT
  5. Perform a GET request to the endpoint /all SCREENSHOT
  6. Perform a DELETE request to the endpoint /remove/{id} SCREENSHOT

You can find the complete source code spring-oracle-stored-proc

Written on May 26, 2018