Comparison with other frameworks

For comparison with other frameworks consider the following stored procedure

CREATE procedure plus1inout (IN arg int, OUT res int)
BEGIN ATOMIC
  SET res = arg + 1;
END
/;

It is taken from the Spring Data JPA - JPA 2.1 example and uses HSQLDB syntax. You can find the executable code in stored-procedure-examples.

• JDBC
• Stored Procedure Proxy
• Spring JDBC
• JPA
• Spring Data JPA
• JDBI
• Ebean
• sql2o
• spwarp

JDBC

Using JDBC and the Java 7 try-with-resources Statement we can call the stored procedure like this:

try (Connection connection = this.dataSource.getConnection();
     CallableStatement statement = connection.prepareCall("{call plus1inout(?, ?)}")) {
    statement.setInt(1, argument);
    statement.registerOutParameter(2, Types.INTEGER);

    statement.execute()

    return statement.getInt(2);
}

Note that we still have to handle the SQLException checked exception.

Stored Procedure Proxy

With Stored Procedure Proxy you simply create an interface with a method for the stored procedure you want to call. You can have multiple methods in this interface, one for each stored procedure you want to call.

public interface ComparisonExample {

  @OutParameter
  int plus1inout(int arg);

}

A couple of things to take note:

• we give the method the same name as the stored procedure, see Deriving Names for more information
• we use primitive types to document that neither the argument nor the return value can be null
• we use Java data types matching the SQL data types, see Binding Parameters for more information, Integer would work just the same
• we use @OutParameter to document the result is retrieved using an out parameter rather than a return value, see Result Extraction for more information
• automatic exception translation to an unchecked exception happens, for more information see Exception Translation

Then we create an instance of the interface using a DataSource

ComparisonExample procedures = ProcedureCallerFactory.build(ComparisonExample.class, dataSource);

and finally call the stored procedure using a normal Java method invocation

int result = procedures.plus1inout(1);

This is all the setup you need, the project has no dependency outside of the JDK. The code can be used with Java EE, with Spring, in a plain Servlet environment or in a plain Java SE environment.

Spring JDBC

Spring JDBC offers four possible ways of how you can call a stored procedure. All of them are more verbose than using JDBC directly if the Java 7 try-with-resources Statement is used. The only advantage they offer is they give a DataAccessException which is a RuntimeException so there is no need to deal with the checked SQLException.

StoredProcedure

Spring JDBC allows you to subclass StoredProcedure for calling a stored procedure.

public class Plus1inout extends StoredProcedure {

  Plus1inout(JdbcTemplate jdbcTemplate) {
    super(jdbcTemplate, "plus1inout");
    setFunction(false);
    declareParameter(new SqlParameter("arg", Types.INTEGER));
    declareParameter(new SqlOutParameter("res", Types.INTEGER));
  }

 int plus1inout(int arg) {
    Map<String, Object> results = execute(arg);
    return (Integer) results.get("res");
  }

}

GenericStoredProcedure

Instead of subclassing you can also use GenericStoredProcedure directly. First you have to create and instance and configure it

storedProcedure = new GenericStoredProcedure();
storedProcedure.setDataSource(dataSource);
storedProcedure.setSql("plus1inout");
storedProcedure.setFunction(false);
storedProcedure.declareParameter(new SqlParameter("arg", Types.INTEGER));
storedProcedure.declareParameter(new SqlOutParameter("res", Types.INTEGER));

before you can call it

Map<String, Object> results = this.storedProcedure.execute(arg);
return (Integer) results.get("res");

SimpleJdbcCall

A SimpleJdbcCall can be instantiated similar to a GenericStoredProcedure.

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getDataSource())
            .withProcedureName("plus1inout")
            .declareParameters( // required if you're not schema owner
                    new SqlParameter("arg", Types.INTEGER),
                    new SqlOutParameter("res", Types.INTEGER))
            .withoutProcedureColumnMetaDataAccess(); // required if you're not schema owner

and the also be called similarly

Map<String, Object> results = jdbcCall.execute(arg);
return (Integer) results.get("res");

JdbcTemplate

Stored procedures can also be called using JdbcTemplate

    List<SqlParameter> parameters = Arrays.asList(
            new SqlParameter("arg", Types.INTEGER),
            new SqlOutParameter("res", Types.INTEGER));

    Map<String, Object> results = this.jdbcOperations.call(con -> {
        CallableStatement statement = con.prepareCall("{call plus1inout(?, ?)}");
        statement.setInt(1, argument);
        statement.registerOutParameter(2, Types.INTEGER);
        return statement;
        }, parameters);
    return (Integer) results.get("res");

but parameters have to be registered twice.

JPA

JPA give you two different ways to call a stored procedure. The first is to create a NamedQuery on an entity

@Entity
@NamedStoredProcedureQuery(name = "User.plus1", procedureName = "plus1inout", parameters = {
		@StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
		@StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class) })
public class User {
}

Which can be be called using the EntityManager

StoredProcedureQuery query = em.createNamedStoredProcedureQuery("User.plus1");

query.setParameter("arg", 1);
query.execute();

return query.getOutputParameterValue("res");

The other is to create a StoredProcedureQuery directly

StoredProcedureQuery query = em.createStoredProcedureQuery("plus1inout")
      .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
      .registerStoredProcedureParameter(2, Integer.class, ParameterMode.OUT)
      .setParameter(1, arg);
query.execute();

return (Integer) query.getOutputParameterValue(2);

The example is only a small improvement over straight JDBC, the biggest advantage is the checked SQLException is traded for an unchecked PersistenceException.

If the procedure is a function instead it needs to be called differently using a native query which is arguably bypassing JPA entirely.

Spring Data JPA

The full example can be found at Spring Data JPA - JPA 2.1 example. As Spring Data JPA is a layer upon JPA the options are very similar.

Spring Data JPA offers two different ways of calling the stored procedure.

The first is close to JPA and requires defining the procedure using annotations on a JPA entity even though the stored procedure has no relation to an entity.

@Entity
@NamedStoredProcedureQuery(name = "User.plus1", procedureName = "plus1inout", parameters = {
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class) })
public class User {

}

Then the procedure can be called using a CrudRepository.

public interface UserRepository extends CrudRepository<User, Long> {

  // Explicitly mapped to named stored procedure {@code User.plus1} in the {@link EntityManager}.
  // By default, we would've try to find a procedure declaration named User.plus1BackedByOtherNamedStoredProcedure
  @Procedure(name = "User.plus1")
  Integer plus1BackedByOtherNamedStoredProcedure(@Param("arg") Integer arg);

}

The second is simply using

public interface UserRepository extends CrudRepository<User, Long> {

  // Directly map the method to the stored procedure in the database (to avoid the annotation madness on your domain classes).
  @Procedure
  Integer plus1inout(Integer arg);
}

Note that here the method is in a CrudRepository as well even though the stored procedure has no relation to a user.

If the procedure is a function instead it needs to be called differently using a native query which is arguably bypassing JPA entirely.

JDBI

JDBI falls back to manual parameter registration for out parameters.

OutParameters outParameters = this.handle.createCall("call plus1inout(?, ?);")
        .bind(0, arg)
        .registerOutParameter(1, Types.INTEGER)
        .invoke();
return outParameters.getInt(1);

This is almost exactly like JDBC minus the checked exception.

Ebean

Ebean falls back to manual parameter registration for out parameters.

String sql = "{call plus1inout(?,?)}";

CallableSql callableSql = database.createCallableSql(sql);
callableSql.setParameter(1, arg);
callableSql.registerOut(2, Types.INTEGER);

database.execute(callableSql);
return (int) callableSql.getObject(2);

This is almost exactly like JDBC minus the checked exception.

Sql2o

sql2o hasn't really support for stored procedures and requires transforming them to a generic SELECT.

String sql =
        "declare @outputParam as INTEGER " +
        "exec plus1inout @inputParam = :arg, @outputParam = @outputParam OUTPUT " +
        "select @outputParam as myval";

try (Connection connection = this.sql2o.open()) {
  return connection.createQuery(sql)
                   .addParameter("arg", arg)
                   .executeScalar(Integer.class);
}

spwarp

spwarp is similar in sprit to stored-procedure-proxy. It also relies on a interface for stored procedure declaration but requires more annotations

interface MathDAO {

  @Scalar(INTEGER)
  @StoredProc("plus1inout")
  int plus1inout(@Param(INTEGER) int arg);
}

An instance of the interface can be created with

DAO dao = new DAO.Builder(this.getDataSource())
        .config(new Config().useStatusFields(false))
        .build();
mathDao = dao.create(MathDAO.class);

the stored procedure can then be called like any Java method

int result = mathDao.plus1inout(1);