Using SingleConnectionDataSource doesn't really fit into the Spring way. You don't use a SingleConnectionDataSource in your application context, you use an existing datasource to get a connection, create a SingleConnectionDataSource using new, passing the recently created session, set the SingleConnectionDataSource on the StoredProcedure and then call the StoredProcedure. This is very ugly and not very thread safe. In order to make it thread safe you have to create the StoredProcedure locally. So basically it would look like this:
Code:
Connection singleConnection = getDataSource().getConnection();
DataSource ds = new SingleConnectionDataSource(singleConnection);
MyStoredProcedure sp = new MyStoredProcedure(ds);
// Make sp call.
singleConnection.close();
You can make this a little less ugly by using JdbcTemplate.execute:
Code:
getJdbcTemplate().execute(new ConnectionCallback() {
public Object doInConnection(Connection con) throws JMSException {
DataSource ds = new SingleConnectionDataSource(con);
MyStoredProcedure sp = new MyStoredProcedure(ds);
// Make sp call, and any other calls you need against the connection, including direct JDBC
return null;
}
});
There are several versions of the execute method which can provide some JDBC object that you can make several calls against. When you feel that Spring just isn't fitting the bill, you can fallback to direct JDBC calls, use JdbcTemplate.execute. It still provide Spring transaction support, exception translation, etc.
Using a transaction proxy is more spring like because you take care of making sure the method call uses only one connection outside of the actual object. It doesn't invade your code.
Spring is designed to use J2EE APIs (like JDBC, JMS, EJB) the way they were intended. You get a resource from some object, you use it, then you release it. Performance is typically enchanced by using pooling or caching. But if you need to go further, tying a database call using a transaction proxy can go one step further and use one connection per business method.
I realize this sometimes makes Spring heavier than your typical J2EE code. But Spring is consistant in how it implements these things. It isn't however alwayst the most efficient way or the easiest. What it does provide you is a consistent exception handling mechanism while reducing boilerplate code in your actual business methods. In the long run it makes such code more testable and reusable.
I've been through exactly what you are going through. Trying to find a way around some of the limitations of the way Spring does things. It tends to be more static, with most dependencies resolved at start up. More use of singletons. Trying to solve the infrastructure and performance issues outside of your actual code.
Easing into Spring can sometimes be painful. And if you read any of the critiques of Spring, can produce practices that seem like overkill, or creates another set of problems. And it does.
Where Spring really starts to show its savings is when your application has dozens of stored procedure calls, several DAOs, multiple databases, multiple custom configuration files, tons of boilerplate code for reading config files and doing resource lookups. I've converted such an application from the old way to the Spring way. And now I have all of my objects in one logical context spread across 4 XML files. And when I load it into IDEA or Eclipse using a Spring plugin, I now have a very good idea of how my large collection of services relate to each other. And my components are unit testable if needed. And anytime I want, I can throw away the container and wire up my objects by hand (although personally I don't think I'd want to, given the size of my project)
If Spring isn't really working for you, I advise you to step back and use it less. Just use JdbcTemplate and maybe StoredProcedure. Don't doing any wiring with the container or XML. That is an area that I've seen trip up the most experienced J2EE developers trying to fit Spring into their application.
HTH
Multiple calls to StoredProcedure using same connection
Reply With Quote