Integrating the Data Access Layer with Openbravo XSQL and ConnectionProvider

More and more developers are using the Data Access Layer (DAL) for coding business logic in the Openbravo application. I sometimes get questions related to how to integrate DAL constructs with standard Openbravo approaches like Stored Procedures and XSQL generated classes.

As there are several options I felt it was time to spend a blog on this. In this blog I will discuss a number of 'good-to-know' classes which can be handy when you want to integrate DAL with the classic Openbravo approaches.

The first one to cover is the probably the most important one: the DalConnectionProvider. The DalConnectionProvider can be used when you call utility methods which need a ConnectionProvider object. The great thing of the DalConnectionProvider is that it shares the same connection and transaction as the DAL itself. Let's take an example. This one is from the MassInvoicing module, computing the DocumentNo for a new invoice:

// create a new invoice object
final Invoice invoice = OBProvider.getInstance().get(Invoice.class);
// set some properties for the invoice
invoice.setOrganization(org);
invoice.setBusinessPartner(bp);
invoice.setInvoiceDate(invoiceDate);
invoice.setAccountingDate(invoiceDate);
// .... code truncated for clarity

// now compute a documentno, in the same database transaction
final String documentNo = Utility.getDocumentNo(conn,

new DalConnectionProvider(), vars, "", Invoice.TABLE_NAME,

invoice.getDocumentType().getId(),

invoice.getDocumentType().getId(), false, true);

invoice.setDocumentNo(documentNo);


The above code creates an invoice and then calls a stored procedure using the DalConnectionProvider as the connect provider. As the same transaction is used any updates done by the stored procedure can be committed together with the rest of your work. As you can see the DalConnectionProvider is easy to work with as it does not need any extra parameters, you can create and use it directly.
Sometimes it makes sense to call OBDal.getInstance().flush() before calling a stored procedure. This ensures that Hibernate has flushed all your changes to the database so that the stored procedure can see them. Also read the remark at the end of this blog!

The next two are somewhat related: CallProcess and CallStoredProcedure, they both call/run database logic in the database. The first, the CallProcess, can be used to call a process (defined in AD_PROCESS) directly. You can get a CallProcess by calling CallProcess.getInstance() (there is one instance shared by all threads). Again an example from the MassInvoicing module, calling the C_Order_Post process for a list of invoices:

// get an AD_Process instance, 111 is the C_Invoice_Post process
final org.openbravo.model.ad.ui.Process process = OBDal.getInstance().get(org.openbravo.model.ad.ui.Process.class, "111");

// iterate over the invoices and post them
for (Invoice invoice : invoices) {

final ProcessInstance processInstance = CallProcess.getInstance().call(process,

invoice.getId(), new HashMap<String, String>());

// the processInstance now contains the result
final String errorMsg = processInstance.getErrorMsg();
final Object result = processInstance.getResult();
final String recordID = processInstance.getRecordID();
// code truncated for clarity

}

The last parameter in the CallProcess.call method is a map of parameters, these are placed in the ad_pinstance_para table.

The CallStoredProcedure class makes it possible to call any stored procedure in the database in a java programmer friendly way. The nice thing of this class is that you can pass in actual java objects as parameters, so you don't need to worry about String conversions. The return is also a type-specific java object. This code snippet shows its usage (calling the C_Divide stored procedure):

// set some parameters

final List

parameters.add(new BigDecimal("10.1"));

parameters.add(new BigDecimal("2.0"));

// the procedure name

final String procedureName = "C_Divide";

// calling the procedure and getting the result

final BigDecimal bigDecimal = (BigDecimal)CallStoredProcedure.getInstance().call(procedureName, parameters, null);

The last one to discuss: the OBObjectFieldProvider class makes it possible to wrap an Openbravo business object in a FieldProvider interface. The FieldProvider is used throughout the Openbravo system to wrap data read from the database (for example through a XSQL generated class). The OBObjectFieldProvider is useful when you read business objects through the DAL and need to pass them on to code expecting a FieldProvider.

The discussed classes are all documented with javadoc describing the meaning of parameters and methods and you can ofcourse study the source code directly.

Before concluding this blog, as a last tip: The DAL (Hibernate) queues actions to the database. So when updating the database through DAL and then calling a stored procedure using a DalConnectionProvider make sure to call OBDal.getInstance().flush() before calling the stored procedure. This flushes the queue to the database and ensures that the stored procedures sees your changes. Also when you want to work with the results from the stored procedure and read them back through the DAL read this tip.

As always I hope this is an interesting read, feedback is always welcomed. If you have any questions or remarks visit the Openbravo forge forum and post them there.

Happy Coding!