Using Dynamic SQL with Custom DAO
As of version 2.4 build 149 (now available for download) it is now possible to execute Custom Data Access Objects with dynamic SQL. Effectively this means that the same DTO and DAO mapping can now be used with multiple SQL statements.
A typical use-case for using Dynamic SQL statements with a Custom DAO is where a multi-table SELECT is implemented as a Custom DAO but there is a need for multiple "finder" methods.
For example, in an accounting database there would be separate tables for "Department", "Customer", and "Invoice" but a Custom DAO called "InvoiceSummary" is implemented to perform a join across these tables to bring back a list of invoices complete with department and customer details.
In the past, FireStorm/DAO would generate a single execute method with the following signature:
InvoiceSummary[] execute() throws InvoiceSummaryDaoException;
FireStorm/DAO now generates an additional method:
InvoiceSummary[] executeDynamicSQL(String sql) throws InvoiceSummaryDaoException;
Also, the SQL statement contained in the generated DAO implementation is now a protected member variable, making it easy to sublass the DAO implementation to provide additional execute methods.
An example of extending the Custom DAO:
public class InvoiceSummaryDaoExtended extends InvoiceSummaryDaoImpl {
public InvoiceSummary[] findLargeInvoices() throws InvoiceSummaryDaoException {
return executeDynamicSQL( SQL + " AND amount > 5000 ORDER BY amount DESC" );
}
}
This assumes that the SQL in the generated DAO has a WHERE clause for performing the join but does not have any other SQL directives (such as ORDER BY) so it is simply a case of appending additional criteria on the end of the SQL statement.
For further information on using this new feature please feel free to email our support team.