Home

To call a stored procedure you need to do two things:

1. create an interface that represents the stored procedures you want to call
2. create and instance of the interface

The interface is a simple POJO interface. If for example you have a salesTax stored procedure that takes a NUMBER and returns a NUMBER the interface declaration would look like this:

public interface TaxProcedures {

  BigDecimal salesTax(BigDecimal subtotal);

}

The instance can be created using only a javax.sql.DataSource

TaxProcedures taxProcedures = ProcedureCallerFactory.build(TaxProcedures.class, dataSource);

Invoking the interface method will then call stored procedure.

taxProcedures.salesTax(new BigDecimal("100.00"));

will actually call the stored procedure.

Deriving Names

As you can see from the above example we have to derive database names from Java names. For example we have the name of the Java method and need to derive the name of the stored procedure. We differentiate between

• objects: procedures, packages, schemas
• parameters

If the Java names do not match the database names then there are different options how we can derive them.

Objects

If there are simple rules how a database name can be derived from a Java name you can use NamingStrategy. This is most of the case when you have a naming convention that you follow. If this is not the case you can use the @ProcedureName, @Namespace and @Schema annotations.

For example let's say you want to call a stored procedure named sp_Blitz. You can either give the Java method the same name

public interface Demo {

  void sp_Blitz();

}

or use the @ProcedureName annotation

public interface Demo {

  @ProcedureName("sp_Blitz")
  void blitz();

}

or use a NamingStrategy. This is most useful when you have several stored procedures that all follow the same naming convention.

public interface Demo {

  void blitz();

}

Demo demo = ProcedureCallerFactory.of(Demo, dataSource)
    .withProcedureNamingStrategy(NamingStrategy.capitalize().thenPrefix("sp_"))
    .build();

Parameters

Parameter names are only required should you decide by parameter name rather than parameter index. There are two options how parameter names are derived. The first is you compile with -parameters and optionally use a NamingStrategy e.g. to perfix every parameter with p_. The second is you annotate a parameter with @ParameterName.

Connection Pooling

We assume the DataSource you provide is a connection pooling DataSource.

Transactions

We don't do transactions. You'll have to do transactions around the stored procedure calls either directly or indirectly through JTA or through Spring or a similar way..

Supported Types

All types that your JDBC driver supports through #getObject and #setObject are supported, we just delegate to the driver. Primitive types are supported and we automatically box and unbox them for you.