Generic methods in DAO
All,
Is loose coupling/method reuse in the DAO a good thing?
In the method example below, methods in the DAO would all invoke this generic method to dynamically build the stored proc to call, and execute it.
Is this good design? or overkill? or problematic?
privatevoid executeStoredProcedure(Long key, String storedProcTagName)throws DAOException
{
Connectionconn =null;
CallableStatement stmt =null;
intreturnValue= -1;
StringpropPath="MyService.storedProcedures." + storedProcTagName;
StringstoredProcName = m_config.getString(propPath +".procedure");
if (m_log.isDebugEnabled())
m_log.debug("Calling " + storedProcName +" for : " + key);
try
{
conn = getDBConnection();
stmt = conn.prepareCall("{ ? = call " + storedProcName +"(?) }");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.setLong(2, key.longValue());
stmt.execute();
returnValue = stmt.getInt(1);
boolean successful = m_config.getBoolean(propPath +".returnCodes.returnCode_" + returnValue +".success");
if (! successful){
String msg = m_config.getString(propPath +".returnCodes.returnCode_" + returnValue +".message");
thrownew DAOException(msg);
}
}
catch (SQLException se)
{
thrownew DAOException(se);
}
finally
{
closeAllResources(stmt, conn);
}
if (m_log.isDebugEnabled())
m_log.debug("Result of " + storedProcName +" for " + key +" was " + returnValue);
}
Thanks in advance,
KL
[2756 byte] By [
Liggya] at [2007-10-2 10:05:39]
I could envision placing that type of code into a JDBCHelper object. It could eliminate duplicate code where the same signature can be used to execute a given stored procedure that will return a given data type.
HOWEVER, I would never allow a model object to call that method directly. Rather, the model object would call a dedicated DAO that would in turn delegate the actual procedure invocation to the JDBCHelper object.
- Saish
Thanks Saish, although I still have a concern.It doesn't seem practical to guarantee that all procedures will have the same number of parameters and same types of parameters (in and out).Is invoking a SP so painful that it really requires a generic method?
I totally agree. However, if you did have a system where all the primary keys a stored proc might use would be of the same type (say, every table has an integer uid as the primary key), then it would offer more, though still limited, value.- Saish
> I totally agree. However, if you did have a system
> where all the primary keys a stored proc might use
> would be of the same type (say, every table has an
> integer uid as the primary key), then it would offer
> more, though still limited, value.
>
> - Saish
But is there enough value warrant the seperation? I suppose I'll have to wait for the surplus of procedure invocations before I see the value.
KL
Not in the method proposed. However, I could see adding a method to a JDBCHelper class ala:
public final Object[] executeStoredProc(final Object[] bindVars) throws SQLException {}
Within the method, a given transaction would be created, along with the CallableStatement and ResultSet. The method would ensure that all resources are properly closed. For stored procedures participating within a transaction or logical unit of work:
public final Object[] executeStoredProc(final Object[] bindVars, final Connection conn) throws SQLException {}
However, again, I would not allow a developer to simply call the executeStoredProc() method from a model or service class. He or she should write a dedicated DAO with a strongly-typed method such as:
public final Date calculateNextBillingCycle(final String userId) {}
The method above is clearly named and offers type safety. It could easily delegate its call to JDBCHelper#executeStoredProc(Object[]).
- Saish
- Saish
With the parameter and return type being Object[], wouldn't I now have to write the code to get my parameters in an Object[] and then some additional code to get the values out of the Object[]?
So again I'd ask... what does generalizing these procedure invocations buy me?
Good point, tho, on the overloaded method to support in transaction calls.
KL
You would have to populate the bind variable Object[] array. However, this should not be a difficult task. You would also have to extract (and cast for type-safety) the values (if any) returned as OUT parameters by the stored proc. Again, this should not be a difficult issue. It is a totally generic method.
What does it buy you? JDBC resource safety mainly. It is easy for even an experienced developer to forget to free a scarce database resource.
- Saish
> You would have to populate the bind variable Object[]
> array. However, this should not be a difficult task.
I feel the same way about coding the CallableStatement myself. ;)
> You would also have to extract (and cast for
> r type-safety) the values (if any) returned as OUT
> parameters by the stored proc. Again, this should
> not be a difficult issue.
Once again, a similar argument for not creating the "generic method"
>It is a totally generic
> method.
>
> What does it buy you? JDBC resource safety mainly.
> It is easy for even an experienced developer to
> o forget to free a scarce database resource.
JDBC resource management... I guess the closeAllResources() method on the base class could be missed. But now we're resolving a different issue.
I guess I'm still not sold on this one. :)
KL
> > You would have to populate the bind variable
> Object[]
> > array. However, this should not be a difficult
> task.
>
> I feel the same way about coding the
> CallableStatement myself. ;)
>
Yes, but one entails a runtime issue of resource management. The other may have a runtime casting issue, but this would immediately be apparent, whereas a resource leak might take a while to notice.
> > You would also have to extract (and cast for
> > r type-safety) the values (if any) returned as OUT
> > parameters by the stored proc. Again, this should
> > not be a difficult issue.
>
> Once again, a similar argument for not
> creating the "generic method"
>
See above.
> >It is a totally generic
> > method.
> >
> > What does it buy you? JDBC resource safety
> mainly.
> > It is easy for even an experienced developer to
> > o forget to free a scarce database resource.
>
> JDBC resource management... I guess the
> closeAllResources() method on the base class
> could be missed. But now we're resolving a
> different issue.
>
I have no issue with also having that method, say in a helper or abstract superclass to DAO's. In fact, the generic stored proc method could invoke it! :^)
> I guess I'm still not sold on this one. :)
>
> KL
- Saish
