DAO factory pattern createConnection from datasource

Question

Referring to this article on DAO factory pattern, http://www.oracle.com/technetwork/java/dataaccessobject-138824.html

I have a CloudscapeDAOFactory , that has a public static Connection createConnection() method. I am using DriverManager.registerDriver() and DriverManager.getConnection() to create connection.

//DriverManager.registerDriver(new OracleDriver());
//conn = DriverManager.getConnection(CONNECTION_URL);

The individual DAO classes like say CloudscapeCustomerDAO [Example 9.4] calls CloudscapeDAOFactory.createConnection() to get a connection as required.

public class CloudscapeCustomerDAO implements 
    CustomerDAO {

  public CloudscapeCustomerDAO() {
    // initialization 
  }

  // The methods in the class can use
  // CloudscapeDAOFactory.createConnection() 
  // to get a connection as required

Question: Now I am implementing connection pooling, and my problem is retaining the 'static' keyword in the createConnection() of the CloudscapeDAOFactory.

CloudscapeDAOFactory.java

private DataSource dataSource;
private static Connection conn = null;

public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
}


public static Connection createConnection() throws SQLException {
  conn = dataSource.getConnection()// incorrect static reference// compile time error
  // If I remove 'static' then CloudscapeCustomerDAO need an instance of CloudscapeDAOFactory to call this method!
  // If I plan to retain the 'static' then I need to declare DataSource also as static, which I feel is incorrect.

}

Springconfig.xml

<bean id="springDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" >
  <property name="url" value="jdbc:oracle:thin:@localhost:1521:SPRING_UNNI" />
  <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
  <property name="username" value="unni" />
  <property name="password" value="unni" />
  <property name="removeAbandoned" value="true" />
  <property name="initialSize" value="20" />
  <property name="maxActive" value="30" />
</bean>

<bean id="cloudscapeDAOFactory" class="com.myapp.dao.CloudscapeDAOFactory">
   <property name="dataSource" ref="springDataSource"/>
</bean>

update: JDBCTemplate can be used to call Stored Procedures too. Reference: Spring JDBC Template for calling Stored Procedures This question is not w.r.to using JDBCTemplate. Its just a core java question on effectively using the createConnection() of a factory

update2: Irrelevant to this thread, but placing a note:

note: Got an issue while attempting for connection: error: TNS:listener does not currently know of SID given in connect descriptor

fixed by changing

 <property name="url" value="jdbc:oracle:thin:@localhost:1521:SPRING_UNNI" />

to

 <property name="url" value="jdbc:oracle:thin:@localhost:1521/SPRING_UNNI" />

Just inject the datasource and get a connection or even better use a `JdbcTemplate` don't start messing aruond yourself with factories. — M. Deinum, Oct 28 '14 at 14:46
My application has this factory design pattern in place, hence I am looking for a way to resolve it in the same approach. I understand, injecting the datasource directly in the individual DAO's, which I don't prefer as per design. Also, I am not using `JDBC Template` because I am calling the Stored Procedures from my code — spiderman, Oct 28 '14 at 14:52
I'll look at JDBC Template, I can use it to call SPs, but any way to resolve this w/o going to that — spiderman, Oct 28 '14 at 14:58
Don't tie things together with static methods simple as that. Also the reason to not use `JdbcTemplate` doesn't make any sense you can call procedures with it just as that. — M. Deinum, Oct 28 '14 at 14:58
Yes, I took back that reason for not using the `JdbcTemplate`. I updated my comment. Ok, so your suggestion would be not declaring the `createConnection` method as `static` and let the individual DAO's call the `createConnection` method thru the instance of the `CloudscapeDAOFactory` , Is that understanding correct? from a core java perspective — spiderman, Oct 28 '14 at 15:02
I would inject the `DataSource` in each dao. Calling `createConnection` on the `CloudscapeDAOFactory` or `getConnection` on a `DataSource` is basically the same. However I strongly suggest you take a better look into spring especially regarding JDBC access and resource management (i.e. connection). — M. Deinum, Oct 28 '14 at 15:04
I agree with the first statement, thanks, For the reference, will this do? http://docs.spring.io/spring-framework/docs/current/spring-framework-reference/html/jdbc.html . And Can you please post as an answer so that I can close it — spiderman, Oct 28 '14 at 15:08

DAO factory pattern createConnection from datasource

0 Answers0