Technology: Spring-jdbc is the abstraction framework, it take care the below following list:

 

1) Defining connection parameters

2) Open Connection

3) Specify the statement

4) Prepare and execute statement/prepared statement

5) Iterate through the ResultSet

6) Process any exception

7) Handles the transactions

8) Close connection.

 

Spring-jdbc provides number of ways to connect to JDBC database access. There are three flavors of the JdbcTemplate, a new "SimpleJdbc" approach taking advantage of database metadata, and there is also the "RDBMS Object" style for a more object oriented approach.

 

1) JdbcTemplate - this is the classic Spring JDBC approach and the most widely used. This is the "lowest level" approach and all other approaches use a JdbcTemplate under the covers.

2) NamedParameterJdbcTemplate - wraps a JdbcTemplate to provide more convenient usage with named parameters instead of the traditional JDBC "?" place holders.

3) SimpleJdbcTemplate - this class combines the most frequently used features of both JdbcTemplate and NamedParameterJdbcTemplate plus it adds additional convenience by taking advantage of some Java 5 features like varargs, autoboxing and generics to provide an easier to use API.

4) SimpleJdbcInsert and SimpleJdbcCall - designed to take advantage of database metadata to limit the amount of configuration needed. This will simplify the coding to a point where you only need to provide the name of the table or procedure and provide a Map of parameters matching the column names. Designed to work together with the SimpleJdbcTemplate.

5) RDBMS Objects including MappingSqlQuery, SqlUpdate and StoredProcedure - an approach where you create reusable and thread safe objects during initialization of your data access layer.

 

In the present blog, we learn how to create a simple stored procedure, and how to execute the stored procedure from spring-jdbc, and how to parse the results.

 

Creating and compiling Stored Procedure:

Create an employee table, with id, name, role columns.

CREATE TABLE EMPLOYEE(

ID NUMBER(19),

NAME VARCHAR(255),

ROLE VARCHAR(255),

PRIMARY KEY (ID)

);

Create a sequence employee, to get the sequence values,

CREATE SEQUENCE seq_EMPLOYEE

 MINVALUE 1

 START WITH 1

            INCREMENT BY 1

            CACHE 10;

 

Now we will create a procedure, to get all  managers.

CREATE OR REPLACE PROCEDURE GET_MANAGERS(manager_cursor OUT SYS_REFCURSOR)

IS

BEGIN

OPEN manager_cursor FOR

  SELECT * FROM EMPLOYEE e WHERE e.role='Manager';

END;

Insert some the records into table with some Manager role.

insert into EMPLOYEE values (seq_EMPLOYEE.nextval, 'Jane', 'Developer');

insert into EMPLOYEE values (seq_EMPLOYEE.nextval, 'Mike', 'Manager');

insert into EMPLOYEE values (seq_EMPLOYEE.nextval, 'Rose', 'Manager');

Now lets create persistence.xml file for bootstrapping JPA.

Create a folder “META-INF” under src/main/resources and create persistence.xml file in META-INF folder, add the below content into it.

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="2.1"

             xmlns="http://xmlns.jcp.org/xml/ns/persistence"

             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

 

    <persistence-unit name="example-unit" transaction-type="RESOURCE_LOCAL">

        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <exclude-unlisted-classes>false</exclude-unlisted-classes>

        <properties>

            <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>

            <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@localhost:1521:xe"/>

            <property name="javax.persistence.jdbc.user" value="system"/>

            <property name="javax.persistence.jdbc.password" value="root"/>

        </properties>

    </persistence-unit>

</persistence>

Next, we will create Employee class, with same fields as employee table columns.

 

Employee .java

@Entity

@Table(name="EMPLOYEE")

public class Employee {           

            @Id

@SequenceGenerator(sequenceName="seq_EMPLOYEE",name="employee_sequence",initialValue=1,allocationSize=1)

            @GeneratedValue(generator="employee_sequence",strategy=GenerationType.SEQUENCE)

            private long id;

           

            @Column

            private String name;

            @Column

            private String role;

 

            //setters and getters

            @Override

            public String toString() {

                        return "Employee{" +

                "id=" + id +

                ", name='" + name + '\'' +

                ", role='" + role +

                '}';

            }          

}

Now, lets create a main class  for to return values from database using “GET_MANAGERS” procedure.

 

Main.java

public class Main {

            private static EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("example-unit");

                        public static void main(String[] args) {

                        try {

                                    getManagers();

                        } finally {

                                    entityManagerFactory.close();

                        }

            }

            private static void getManagers()

            {

                        EntityManager entityManager = entityManagerFactory.createEntityManager();

                        StoredProcedureQuery procedureQuery = entityManager.createStoredProcedureQuery("GET_MANAGERS",Employee.class);

                        procedureQuery.registerStoredProcedureParameter("manager_cursor", void.class, ParameterMode.REF_CURSOR);

                        procedureQuery.execute();

                        @SuppressWarnings("unchecked")

                        List<Employee> resultList = procedureQuery.getResultList();

                        resultList.forEach(System.out::println);

            }

}

After executing the main class, we can observe the storedprocudre method call in log:

12:45:12.415 [main] DEBUG org.hibernate.procedure.internal.ProcedureCallImpl - Preparing procedure call : {call GET_MANAGERS(?)}

12:45:12.418 [main] DEBUG org.hibernate.SQL - {call GET_MANAGERS(?)}

 

Implementing using spring-jdbc simpleJdbcCall:

Create a configuration class for datasource.

@ComponentScan

@Configuration

public class AppConfig {

 

             @Bean

              public DataSource dataSource() {

                  DriverManagerDataSource ds = new DriverManagerDataSource();

                  ds.setDriverClassName("oracle.jdbc.OracleDriver");

                  ds.setUrl("jdbc:oracle:thin:@localhost:1521:xe");

                  ds.setUsername("system");

                  ds.setPassword("root");

                  return ds;

              }

}

Create dao interface and its implementation:

public interface EmployeeDao {

            public List<Employee> getAllManagers();

}

@Repository

public class EmployeeDaoImpl implements EmployeeDao {

 

            @Autowired

            private DataSource dataSource;

            private JdbcTemplate jdbcTemplate;

            private SimpleJdbcCall simpleJdbcCall;

 

            @PostConstruct

            private void postConstruct() {

                        jdbcTemplate = new JdbcTemplate(dataSource);

                        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("get_managers")

                                                .returningResultSet("manager_cursor", BeanPropertyRowMapper.newInstance(Employee.class));

            }

 

            @SuppressWarnings("unchecked")

            @Override

            public List<Employee> getAllManagers() {

                        Map<String, Object> result = simpleJdbcCall.execute(new HashMap<String, Object>(0));

                        return (List<Employee>) result.get("manager_cursor");

            }

}

 

First we need to create a datasource, and inject into EmployeeDAoImpl class, then create Jdbc Template by passing datasource class. simpleJdbcCall is the class provided by spring-jdbc library, we will pass these jdbcTemplate and procedure name “get_managers” and using returningResult() method specify the cursor name. 

 

Specifying parameters to StoredProcedures:

SimpleJdbcCall.declareParameters() method is used to specify the parameters to stored procedure. This method will accept the array of the SqlParameter type, where we can specify the parameter name, type of the parameter, by default the mode of the parameter in IN, SqlOutParameter and SqlInOutParameter are classes used to specify the OUT, and IN OUT parameter modes.

Example: new SimpleJdbcCall(jdbcTemplate)

                        .withProcedureName("read_actor")

                        .declareParameters(

                    new SqlParameter("in_id", Types.NUMERIC),

                    new SqlOutParameter("out_first_name", Types.VARCHAR),

                    new SqlOutParameter("out_last_name", Types.VARCHAR),

              new SqlOutParameter("out_birth_date", Types.DATE)

         );

They are some situation where stored procedures parameters will have some default value, and not needed to pass them, in these cases we can use In ParameterNames() method and specify the parameter name.

 

Passing Parameter values to StoredProcedure:

SimpleJdbcCall.execute will accept variable number of arguments, if the procedure has any IN parameters then, create a Map type of the object, add key as parameter name and value as value we want to pass to stored procedure. While calling execute method, pass these map object to execute method.

 

Another way also we can pass values to procedure,

 

Create an object of the type MapSqlParameterSource, and using addValue() method, by specifying parameter name as first argument, value as the second argument.

 

Then pass this object to execute method.

 

returningResultSet is the method to specify the cursor name, and row mapper for each result set row.

 

Conclusion: In this article shared by Java Developers India, we learn how to execute the stored-procedure using spring-data and also using plain spring-jdbc, and we learned how to specify the parameters type,  how to pass values to stored procedure.