spwrap

Stored Procedure caller; simply execute stored procedure from java code.
Compatible with jdk >= 1.5, with only single dependency (slf4j-api)

Step 0: Create Stored Procedures:

Suppose you have 3 Stored Procedures to save customer to database, get customer by id and list all customer.

For example here's SP code using HSQL:

CREATE PROCEDURE create_customer(firstname VARCHAR(50), lastname VARCHAR(50), OUT custId INT, 
        OUT code SMALLINT, OUT msg VARCHAR(50))
    MODIFIES SQL DATA DYNAMIC RESULT SETS 1
    BEGIN ATOMIC
        INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname);
        SET custId = IDENTITY();
        SET code = 0 -- success;
    END

CREATE PROCEDURE get_customer(IN custId INT, OUT firstname VARCHAR(50), OUT lastname VARCHAR(50), 
        OUT code SMALLINT, OUT msg VARCHAR(50)) 
    READS SQL DATA
    BEGIN ATOMIC
        SELECT first_name, last_name INTO firstname, lastname FROM customers WHERE id = custId;
        SET code = 0 -- success;
    END

CREATE PROCEDURE list_customers(OUT code SMALLINT, OUT msg VARCHAR(50))
    READS SQL DATA DYNAMIC RESULT SETS 1
    BEGIN ATOMIC
        DECLARE result CURSOR FOR SELECT id, first_name firstname, last_name lastname FROM CUSTOMERS;
        OPEN result;
        SET code = 0 -- success;
    END

NOTE: Every Stored Procedure by default need to have 2 additional Output Parameters at the end of its parameter list. One of type SMALLINT and the other of type VARCHAR for result code and message respectively, where result code 0 means success. You can override the 0 value or remove this default behviour at all, see the configuration wiki page.

Step 1: Create The DAO interface:

public interface CustomerDAO {

    @StoredProc("create_customer")
    void createCustomer(@Param(VARCHAR) String firstName, @Param(VARCHAR) String lastName);

    @StoredProc("get_customer")
    Customer getCustomer(@Param(INTEGER) Integer id);	
	
    @StoredProc("list_customers")
    List<Customer> listCustomers();
}

Step 2: Map Output parameters and Result set (if any):

Before start using the CustomerDAO interface, one last step is required, to map the result of the get_customer and list_customers stored procedures.

• get_customer stored procs returns the result as Output Parameters, so you need to have a class to implement TypedOutputParamMapper interface.
• list_customers stored proc returns the result as Result Set, so you need to have a class to implement ResultSetMapper interface.

Let's create Customer class to implement both interfaces (for getCustomer and listCustomers):

public class Customer implements TypedOutputParamMapper<Customer>, ResultSetMapper<Customer> {

	private Integer id;
	private String firstName, lastName;

	public Customer() {
	}

	public Customer(Integer id, String firstName, String lastName) {
		super();
		this.id = id;
		this.firstName = firstName;
		this.lastName = lastName;
	}

	public Integer id() {
		return id;
	}

	public String firstName() {
		return firstName;
	}

	public String lastName() {
		return lastName;
	}
	
	 //You can acess result set columns/output parameters by name as well
	@Override
	public Customer map(Result<?> result) {
		if (result.isResultSet()) {
			return new Customer(result.getInt(1), result.getString(2), result.getString(3));
		} else {
			return new Customer(null, result.getString(1), result.getString(2));
		}
	}

	// for TypedOutputParamMapper
	@Override
	public List<Integer> getTypes() {
		return Arrays.asList(VARCHAR, VARCHAR);
	}
}

See more examples on spwrap-examples github project and read more about Mappers in the wiki.

NOTE: If your stored procedure returns a single output parameter with no result set, then you can use the @Scalar annotation and you will not need to provide a Mapper class yourself, the mapping will done for you. see wiki page about scalars for more

NOTE: You can use @AutoMappers to do the mapping for you instead of Mapping the Result object into your domain object yourself.

Step 3: Using the DAO interface:

Now you can start using the interface to call the stored procedures:

DataSource dataSource = ...
DAO dao = new DAO.Builder(dataSource).build();
CustomerDAO customerDao = dao.create(CustomerDAO.class);

customerDao.createCustomer("Abdullah", "Muhammad");
Customer customer = customerDao.getCustomer1(1);
Assert.assertEquals("Abdullah", customer.firstName());

Installation

Gradle:

compile group: 'com.github.mhewedy', name: 'spwrap', version: '0.0.20'

Maven:

<dependency>
    <groupId>com.github.mhewedy</groupId>
    <artifactId>spwrap</artifactId>
    <version>0.0.20</version>
</dependency>

Additional staff:

• If you don't supply the stored procedure name to @StoredProc, it will use the method name by default.

• @Param annotation should used for ALL method parameters and accepts the SQL Type per java.sql.Types.

• If you don't want to tie your Domain Object with spwrap as of step 3 above, you can have another class to implement the Mapper interfaces (TypedOutputParamMapper and ResultSetMapper) and pass it to the annotaion @Mapper like:

@Mapper(CustomResultSetMapper.class)
@StoredProc("list_customers")
List<Customer> listCustomers();

• @Mapper annotation overrides the mapping specified by the return type object, i.e. spwrap extract Mapping infromation from the return type class, and then override it with the classes set by @Mapper annotation if found.

• Your Stored procedure can return output parameter as well as One Result set in one call, to achieve this use Tuple return type:

@Mapper({MyResultSetMapper.class, MyOutputParameterMapper.class})
@StoredProc("list_customers_with_date")
Tuple<Customer, Date> listCustomersWithDate();

Limitations:

• spwrap doesn't support INOUT parameters.

• spwrap doesn't support returning multi-result sets from the stored procedure.

• When the Stored procedure have input and output parameters, input parameters should come first and then the output parameters.

Database Support:

Because spwrap is based on JDBC API, theoretically it should support any Database Management System with a JDBC Driver, However it is tested on HSQL, MySQL, SQL Server and Oracle with jdk 1.6~~, 1.7 and 1.8~~ (1.7 and 1.8 are remove to reduce build time). (Plan to test againest: Postgresql, Sybase, DB2)

See wiki page for more info and test cases/spwrap-examples for more usage scenarios.