Close

Spring - JDBC Stored Procedure Call

[Updated: Feb 25, 2017, Created: Jan 7, 2016]

Spring provides various ways of abstractions on JDBC to call database stored procedures.

We are going to continue with our previous example to explain the different ways.

Let's say we want to call a stored procedures named 'MOVE_TO_HISTORY' which moves a person from 'PERSON' table to 'PERSON_HISTORY' table based on some business logic . The procedure signature is : PROCEDURE MOVE_TO_HISTORY (IN peron_id_in INT, OUT status_out BOOLEAN) We are using HSQL database in embedded mode in our examples.

Followings are the different ways (the code is in our previous example's JdbcTemplatePersonDao, so we have access to jdbcTemplate and dataSource there)

  1. Using JdbcTemplate#call(CallableStatementCreator csc, List<SqlParameter> inOutParams)

    public void moveToHistoryTable(Person person) {
        List<SqlParameter> parameters = Arrays.asList(
                new SqlParameter(Types.BIGINT), new SqlOutParameter("status_out", Types.BOOLEAN));
    
        Map<String, Object> t = jdbcTemplate.call(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                CallableStatement callableStatement = con.prepareCall("{call MOVE_TO_HISTORY (?, ?)}");
                callableStatement.setLong(1, person.getId());
                callableStatement.registerOutParameter(2, Types.BOOLEAN);
                return callableStatement;
            }
        }, parameters);
    }

  2. Using SimpleJdbcCall (This class simplifies greatly the code needed to access stored procedures/functions)

    public void moveToHistoryTable(Person person){
        SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("MOVE_TO_HISTORY")
                . declareParameters(
                        new SqlParameter("peron_id_in", Types.BIGINT),
                        new SqlOutParameter("status_out", Types.BOOLEAN));
    
        Map<String, Object> execute = call.execute(new MapSqlParameterSource("peron_id_in", person.getId()));
    }

  3. Using StoredProcedure. This class is in package org.springframework.jdbc.object which allows us to access the database in a more object-oriented manner. SotredProcedure is abstract so we usually have to extend that or use an existing implementation. Here we are using a subclass, GenericStoredProcedure

    public void moveToHistoryTable(Person person) {
        StoredProcedure procedure = new GenericStoredProcedure();
        procedure.setDataSource(dataSource);
        procedure.setSql("MOVE_TO_HISTORY");
        procedure.setFunction(false);
    
        SqlParameter[] parameters = {
                new SqlParameter(Types.BIGINT),
                new SqlOutParameter("status_out", Types.BOOLEAN)
        };
    
        procedure.setParameters(parameters);
        procedure.compile();
    
        Map<String, Object> result = procedure.execute(person.getId());
    }



Example Project

This complete example project uses StoredProcedure class to make stored procedure call from Spring. Other method which I explained above, are commented out, included just in case if you want to try them yourself.


Example Project

Dependencies and Technologies Used:

  • Spring Context 4.2.3.RELEASE: Spring Context.
  • Spring JDBC 4.2.3.RELEASE: Spring JDBC.
  • HyperSQL Database 2.3.3: HSQLDB - Lightweight 100% Java SQL Database Engine.
  • JDK 1.8
  • Maven 3.0.4

Stored Procedure Call Example Select All Download
  • spring-stored-procedure-call
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources

See Also