RimuDB Logo
Database Access Framework for Java

Stored Procedures and Functions

The RimuDB database access framework makes it easy to execute stored procedures and functions via the StoredProcedure class.

Using the StoredProcedure Class

The following example illustrates basic use of the StoredProcedure class.


StoredProcedure sp = new StoredProcedure(db, "mystoredprocedure");
sp.addINParameter("parameter1", "name");
sp.addINParameter("parameter2", i);
sp.addINParameter("parameter3", new BigDecimal(i * 10));
sp.execute(true);

The StoredProcedure class must be passed a Database (not a CompoundDatabase) and a stored procedure name.

Input and Output parameters and their values should be added to the procedure in the same sequence as the stored procedure defines them.

To run the stored procedure call the execute(boolean autoCommit) method.

Output Parameters

The following example demonstrates how an output parameter is defined and used.


StoredProcedure sp = new StoredProcedure(db, "mystoredprocedure");
sp.addINParameter("parameter1", "name");
sp.addOUTParameter("parameter2", Types.INTEGER);
sp.execute(true);
Integer result_a = (Integer) sp.getParameterValue("parameter2");


Input/Output Parameters

The following example demonstrates how an input/output parameter is defined and used.


StoredProcedure sp = new StoredProcedure(db, "mystoredprocedure");
sp.addINParameter("parameter1", "name");
sp.addOUTParameter("parameter2", Types.INTEGER);
sp.addINOUTParameter("parameter3", Types.INTEGER, 20);
sp.execute(true);
Integer result_a = (Integer) sp.getParameterValue("parameter2");
Integer result_b = (Integer) sp.getParameterValue("parameter3");


Return Values

The following example demonstrates how a return value is defined and used.


StoredProcedure sp = new StoredProcedure(db, "mystoredprocedure");
sp.setReturnType(Types.INTEGER);
sp.addINParameter("parameter1", "name");
sp.execute(true);
Integer result_a = (Integer)sp.getReturnParameterValue();


Returned ResultSets

Some databases, like Oracle and Postgres return the result set as a return value, and require a special return type.


// Oracle
StoredProcedure sp = new StoredProcedure(oracledb, "mystoredprocedure");
sp.setReturnType(OracleTypes.CURSOR);
sp.addINParameter("parameter1", "name");
List resultList = sp.execute(true);

// Postgres
StoredProcedure sp = new StoredProcedure(postgresdb, "mystoredprocedure");
sp.setReturnType(Types.OTHER);
sp.addINParameter("parameter1", "name");
List resultList = sp.execute(true);


Other databases, like MySQL do not use a return value for result sets, and so don't need a return type.


// MySQL
StoredProcedure sp = new StoredProcedure(mysqldb, "mystoredprocedure");
sp.addINParameter("parameter1", "name");
List resultList = sp.execute(false);


Processing ResultSets

The ResultSet returned from a Stored Procedure is a List of rows. Each row is represented as a List of objects.

The example below demonstrates how a stored procedure named 'GET_CUSTOMER_WISHLISTS' is called to retrieve a List of records. Each object in the list is a row of data. Each row is a List of objects representing the columns in the record. The example retrieves each column from the row and assigns it to a column in another table, writing the records to a database table.


public void process(CompoundDatabase cdb, Database remoteDB) throws Exception {

	int count = 0;
	// Execute the stored procedure
	log.info("Executing stored procedure");
	StoredProcedure sp = new StoredProcedure(remoteDB, "dbo.GET_CUSTOMER_WISHLISTS");
	List resultList = sp.execute(false);
	log.info("Writing customer wish list data.");
	for (Object row : resultList) {
		count++;
		List<Object> list = (List<Object>)row;

		CustomerWishlistDO wishList = new CustomerWishlistDO(cdb);
		wishList.setUserId((Integer)list.get(0));
		wishList.setUserName((String)list.get(1));
		wishList.setFirstName((String)list.get(2));
		wishList.setLastName((String)list.get(3));
		wishList.setProductId((Integer)list.get(4));
		wishList.setProductName((String)list.get(5));
		wishList.setStyle((String)list.get(6));
		wishList.setDisplayAttribute1((String)list.get(7));
		wishList.setDisplayAttribute2((String)list.get(8));
		wishList.setDisplayAttribute3((String)list.get(9));
		wishList.setProductVariantId((Integer)list.get(10));
		wishList.setSku((String)list.get(11));
		wishList.setTypeName1((String)list.get(12));
		wishList.setTypeName2((String)list.get(13));
		wishList.setTypeName3((String)list.get(14));
		wishList.setUserItemId((Integer)list.get(15));
		wishList.setCreatedDate((Timestamp)list.get(16));
		wishList.commit();

		if (count % 100 == 0) {
			log.info("count="+count);
		}
	}

	log.info("Finished writing customer wish list data.");

}