Wow, I finally got off my ass and finished this posting. Sorry for the long delay. I could probably blame it on the holidays, a busy work schedule, or the lack of sunlight during winter. But the cold reality is I was being lazy and procrastinating. It’s hard to believe I procrastinated for 4 months, but I did. Anyway, I finally have this is a somewhat publishable format, so here goes:
With the addition of generics in Java 5, writing a custom DAO for each domain object is no longer required. There are a wide variety of articles on creating generic DAOs, but my current project uses the approach from this IBM DeveloperWorks article. This approach was choses mainly because of the clearly written article and the integration with Spring. You should be able to extend any generic DAO based on Spring to implement the stored procedure configuration.
Note: If it ain’t broke, don’t fix it. The generic DAO implementation leverages the Spring 1.2.x configuration because the code was created prior to Spring 2.0. I’m sure the configuration could be upgraded to Spring 2.0, but I didn’t see the need for it since the 1.2.x configuration worked under Spring 1.2.x and Spring 2.0.
I have taken this approach and modified it to perform 2 additonal functions. The first is the ability to execute stored procedures. The second is to execute those procedures against various databases such as Oracle and HSQLDB. See this previous post on how to setup stored procs for HSQLDB. The previous post boils down to using HSQLDB for unit testing on my dev machine, and Oracle for functional and integration testing on all other environments (dev server, test server, prod server).
The details below get quite long with all of the code snippets, but hopefully they are not too confusing.
Before getting into the details of the stored proc extension of the generic DAO, let’s first review the generic DAO code and configuration. The IBM article explains in detail how Spring and Hibernate are configured, but this is a simple synopsis of the Spring configuration. Spring AOP is utilized to intercept method calls to determine if a “finder” method is called instead of a standard CRUD method. This configuration allows the custom application DAO interfaces to define just the finder methods and no CRUD methods. The generic DAO finder implementation uses a Hibernate named query in which the named query matches the finder method name.
Initial generic DAO config
<!-- DAO Layer generic config-->
<bean id="extendedFinderNamingStrategy" class="com.myapp.dao.finder.ExtendedFinderNamingStrategy" />
<bean id="finderIntroductionAdvisor" class="com.myapp.dao.finder.FinderIntroductionAdvisor"/>
<bean id="abstractDAOTarget" class="com.myapp.dao.DAOHibernateImpl" abstract="true">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
<property name="namingStrategy">
<ref bean="extendedFinderNamingStrategy" />
</property>
<property name="spParamConfig">
<ref bean="storedProcParamConfig" />
</property>
</bean>
<bean id="abstractDAO" class="org.springframework.aop.framework.ProxyFactoryBean" abstract="true">
<property name="interceptorNames">
<list>
<value>finderIntroductionAdvisor</value>
</list>
</property>
</bean>
<!-- End DAO Layer generic config-->
Extension #1 – Support for stored procedure calls in the generic DAO
The stored procedure extension adds another AOP advisor to intercept method calls that are for stored procedures. With the SP extension, the custom application DAO interfaces can add method definitions for stored procedures. When one of those method calls is intercepted, the generic DAOHibernateImpl class will dynamically generate a JDBC statement to call the stored procedure. The name of the stored procedure DAO method must be the stored procedure name prefixed with either SP_NRS or SP_RS depending on whether the stored procedure returns a result set.
Final Generic DAO Spring config
<!-- DAO Layer generic config-->
<bean id="extendedFinderNamingStrategy" class="com.myapp.dao.finder.ExtendedFinderNamingStrategy" />
<bean id="finderIntroductionAdvisor" class="com.myapp.dao.finder.FinderIntroductionAdvisor"/>
<bean id="storedProcIntroductionAdvisor" class="com.myapp.dao.finder.StoredProcIntroductionAdvisor"/>
<bean id="storedProcParamConfig" class="com.myapp.dao.sp.OracleStoredProcParamConfig"/>
<bean id="abstractDAOTarget" class="com.myapp.dao.DAOHibernateImpl" abstract="true">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
<property name="namingStrategy">
<ref bean="extendedFinderNamingStrategy" />
</property>
<property name="spParamConfig">
<ref bean="storedProcParamConfig" />
</property>
</bean>
<bean id="abstractDAO" class="org.springframework.aop.framework.ProxyFactoryBean" abstract="true">
<property name="interceptorNames">
<list>
<value>finderIntroductionAdvisor</value>
<value>storedProcIntroductionAdvisor</value>
</list>
</property>
</bean>
<!-- End DAO Layer generic config-->
Spring Advisor for Stored Procs:
public class StoredProcIntroductionAdvisor extends DefaultIntroductionAdvisor {
public StoredProcIntroductionAdvisor() {
super(new StoredProcIntroductionInterceptor());
}
}
Spring Interceptor:
**
* Connects the Spring AOP magic with the Hibernate DAO magic
* For any method beginning with "SP_" this interceptor will use the StoredProcExecutor to call a StoredProcedure
*/
public class StoredProcIntroductionInterceptor implements IntroductionInterceptor {
public Object invoke(MethodInvocation methodInvocation) throws Throwable {
StoredProcExecutor executor = (StoredProcExecutor) methodInvocation.getThis();
String methodName = methodInvocation.getMethod().getName();
if(methodName.startsWith( "SP_NRS_" )) {
Object[] arguments = methodInvocation.getArguments();
return executor.executeNoReturnSetProc(methodInvocation.getMethod(), arguments);
/*
} else if (methodName.startsWith( "SP_RS_" )) {
Object[] arguments = methodInvocation.getArguments();
return executor.executeReturnSetProc(methodInvocation.getMethod(), arguments);
*/
} else {
return methodInvocation.proceed();
}
}
public boolean implementsInterface(Class intf) {
return intf.isInterface() && FinderExecutor.class.isAssignableFrom(intf);
}
}
Stored Proc Executor Interface:
public interface StoredProcExecutor {
/**
* Execute a storedProc that returns a result set with the appropriate arguments
*/
//public List executeReturnSetProc(Method method, Object[] queryArgs);
/**
* Execute a storedProc that DOES NOT returns a result set with the appropriate arguments
*/
public Boolean executeNoReturnSetProc(Method method, Object[] queryArgs);
}
Extension #2 – Support for any database regardless of stored procedure support for OUT pararameters
Because Oracle supports OUT parameters, but HSQLDB does not, I added a configuration object to determine if the stored procedure call will return OUT parameters. The configuration object is used by the DAOHibernateImpl class to control how the stored procedure is called.
SP Config interface:
public interface StoredProcParamConfig {
public boolean allowOutParameters();
}
Oracle implementation which supports out parameters:
public class OracleStoredProcParamConfig implements StoredProcParamConfig {
public boolean allowOutParameters() {
return true;
}
}
HSQLDB implementation that does not support out parameters:
public class HSQLStoredProcParamConfig implements StoredProcParamConfig {
public boolean allowOutParameters() {
return false;
}
}
Generic DAO code implementation of the extension
Finally, we get to the meat of the code that does the stored procedure call. The stored procedure code is simply a few additonal methods added to DAOHibernateImpl class and changing DAOHibernateImpl to implement StoredProcExecutor.
// first, setup the StoredProcParamConfig var that can be injected.
private StoredProcParamConfig spParamConfig;
public StoredProcParamConfig getSpParamConfig() {
return spParamConfig;
}
public void setSpParamConfig(StoredProcParamConfig spParamConfig) {
this.spParamConfig = spParamConfig;
}
// StoredProcExecutor methods
/**
* Execute a storedProc that returns a result set. If the current
* spParamConfig object does not support return values, null is
* returned.
*/
@SuppressWarnings("unchecked")
public List executeProcReturnSet(Method method, Object[] queryArgs) {
/*
for (int i=0;i<queryArgs.length;i++) {
System.out.println("%%%%% SP ARGS = " + queryArgs[i]);
}
*/
// set the return objects to be
if (getSpParamConfig().allowOutParameters()) {
final Query namedQuery = prepareQuery(method, queryArgs);
return (List) namedQuery.list();
} else {
return null;
}
}
/**
* Execute a storedProc that DOES NOT return a result set
*/
//TODO could probably use a namedQuery to call the proc and not have to do all of the special code
public Boolean executeProcNoReturnSet(Method method, Object[] queryArgs) {
Connection conn = null;
CallableStatement proc = null;
StringBuffer spCall = new StringBuffer();
try {
/**
* The connection is not closed at the end of the method, b/c the method
* relies on the Hibernate session to be closed by Spring or the OpenSessionInView filter
*/
conn = getHibernateTemplate().getSessionFactory().getCurrentSession().connection();
String spName = method.getName().substring( DAOConstants.PREFIX_SP_NRS.length());
//LogManager.debug(this.getClass(), "***** SPName = " + spName);
// build the call statement with the correct number of params and sp name
spCall.append("{ call " + spName + "(");
for (int i=0; i<queryArgs.length; i++) {
spCall.append("?");
// add a comma if there are more params
// have to subtract 1 since arrays index from 0
if (i < (queryArgs.length-1) ) {
spCall.append(", ");
}
}
spCall.append(") }");
LogManager.debug(this.getClass(), "***** executeNoReturnSetProc - spCall=" + spCall);
proc = conn.prepareCall(spCall.toString());
LogManager.debug(this.getClass(), "***** executeNoReturnSetProc - prepared call");
//LogManager.debug(this.getClass(), "***** SP callableStatement = " + proc);
// add the params to the call
for (int i=0; i<queryArgs.length; i++) {
setParam(proc, i+1, queryArgs[i]);
}
proc.execute();
LogManager.debug(this.getClass(), "***** executeNoReturnSetProc - returned spCall=" + spCall);
return Boolean.TRUE;
} catch (SQLException e) {
LogManager.debug(this.getClass(), "***** executeNoReturnSetProc ERROR = " + e.getMessage() + " with spCall=" + spCall);;
e.printStackTrace();
return Boolean.FALSE;
} finally {
try {
if (proc != null) {
proc.close();
}
} catch (SQLException e) {}
}
}
private void setParam(CallableStatement cs, int pos, Object o) throws SQLException {
if (o == null) {
cs.setNull(pos, java.sql.Types.NULL);
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.NULL);
}
} else if (o instanceof String) {
cs.setString(pos, (String) o);
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.VARCHAR);
}
} else if (o instanceof Short) {
cs.setShort(pos, ((Short) o).shortValue());
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.INTEGER);
}
} else if (o instanceof Integer) {
cs.setInt(pos, ((Integer) o).intValue());
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.SMALLINT);
}
} else if (o instanceof Double) {
cs.setDouble(pos, ((Double) o).doubleValue());
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.DECIMAL);
}
} else if (o instanceof Float) {
cs.setFloat(pos, ((Float) o).floatValue());
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.FLOAT);
}
} else if (o instanceof Long) {
cs.setLong(pos, ((Long) o).longValue());
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.BIGINT);
}
} else if (o instanceof java.sql.Date) {
cs.setDate(pos, (java.sql.Date) o);
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.DATE);
}
} else if (o instanceof java.util.Date) {
cs.setDate(pos, new java.sql.Date(((java.util.Date) o).getTime()));
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.DATE);
}
} else if (o instanceof byte[]) {
cs.setBytes(pos,(byte[]) o);
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.BIT);
}
} else {
cs.setObject(pos, o);
if (spParamConfig.allowOutParameters()) {
cs.registerOutParameter(pos, java.sql.Types.JAVA_OBJECT);
}
}
}
// END StoredProcExecutor methods
You’ll notice that the executeProcReturnSet method is much shorter and cleaner than the NoReturnSet method. There are two reasons for this. The first is that I currently don’t have any procedures in my application that return a resultSet. In the intitial design there was, but those were removed once we got into the implementation of the stored procedures. So, that ends up with a bit of code that I no longer use and have not fully integration tested. The calls do work in HSQLDB because I have unit tests to make sure the method is called, but I have not done any extensive testing and verification with an Oracle procedure that returns data.
The NoReturnSet method is quite long because I am doing direct JDBC code rather than calling a Hibernate namedQuery. There is no technical reason why a namedQuery could not be used, but I have not had a chance to work on this change. This code was implemented within some tight time constraints and I adhered to my mantra of “if it ain’t broke and you ain’t got time, don’t fix it”. A couple hours of coding and testing could probably reduce this mess of code to less than 10 statements.
Integrating into a build system
In my build system, I have extracted out the DAO Spring configuration into a separate file in order to configure the DAO layer based on the environment. For dev, I use the HSQLDB SPConfig object, but for all others I use the Oracle SPConfig object. I use Maven for my builds, and I have a file copy step that uses the proper DAO configuration based on the -Dserver.name=XXX value supplied when Maven is run. If you are a Maven user, profiles could also be used to determine which SPConfig object is used. Or if you only use one database in all environments, you can hardcode that SPConfig object into your Spring configuration.
DoTaL 