Datasource lookup

[Andy Hahn]

Hi, i am using Tomcat 4.1.31 to run a simple web application. I am looking for advice on coding my DAOs. For example, when I access a DAO to retrieve a single column value, the first thing in my code is:

try {
context = new InitialContext();
datasource = (DataSource) context.lookup("java:comp/env/jdbc/testPortletDB");
} catch (NamingException ne) {
LOG.debug("Unable to lookup datasource. " + ne.getMessage());
}

try {
conn = datasource.getConnection();
} catch (SQLException sqlex) {
LOG.debug("Unable to open database connection. " + sqlex.getMessage());
}


The above code works fine but I am suspecting that I should not be looking up the datasource each time the DAO is called. Should the above code be placed in the constructor? Or should I have a static method that would return a reference to the datasource?
Any advice would be great. Thanks.

[Shailesh Chandra]

Originally posted by Andy Hahn:

I am suspecting that I should not be looking up the datasource each time the DAO is called. Should the above code be placed in the constructor? Or should I have a static method that would return a reference to the datasource?

good thought !! A example for need of ServiceLocator & caching Pattern.

Use ServiceLocator Pattern, and cache the look-ups in a hashmap of Servicelocator class.
It will not only saperate your look up code but other code can also re-use same lookup in other class

Shailesh
[ April 10, 2005: Message edited by: Shailesh Chandra ]

[Andy Hahn]

That looks like the perfect solution. Thanks!!

[Andy Hahn]

Question:
What advantage does caching provide? I have used the Service Locator class to ensure the datasource is looked up only once. Should each request for a connection generate a new connection or would I want to reuse a single connection? I would think I would just return a new connection for each request.

Thanks.


public static ServiceLocator getInstance() {
return instance;
}

private ServiceLocator() {
try {
context = new InitialContext();
datasource = (DataSource) context.lookup(datasourcename);
} catch (NamingException ne) {
LOG.debug("Unable to lookup datasource. " + ne.getMessage());
}
}

public Connection getDBConnection() {
try {
conn = datasource.getConnection();
} catch (SQLException sqlex) {
LOG.debug("Unable to open a database connection. " + sqlex.getMessage());
}

return conn;
}

[Shailesh Chandra]

Originally posted by Andy Hahn:

What advantage does caching provide?

In SeriveLocator we cache the look up datasource,hence it removes overhead of looking up every time,becauase lookups are considered little costly in comparision to using same reference.

Should each request for a connection generate a new connection or would I want to reuse a single connection? I would think I would just return a new connection for each request.

Here you are using connection from DataSource, and connection management would be done by DataSource.
It depends on datasource if it return same connection or creates new conenction.You need not to worry as datasource will handle based on connection pool parameter you code if perfectly good.

By using DataSource we get advantage of connection pooling and distributed transactions. Connection pooling increases performance by reusing connections rather than creating a new physical connection



Shailesh
[ April 10, 2005: Message edited by: Shailesh Chandra ]

[Andy Hahn]

Let me try to explain my questions a little differently.
1. What advantage does caching provide?
- The concept of why to cache makes sense.
- I was deciding whether to use a static Datasource reference to store the datasource OR a HashMap reference. Would it be necessary to use a HashMap if all the service is returning is a single Datasource?

2. Should each request for a connection generate a new connection or would I want to reuse a single connection? I would think I would just return a new connection for each request.
- I was wondering if it would be ok to use the following every time a connection is requested:
conn = datasource.getConnection();

- Or would I cache conn (above) and return that reference for every request for a connection.

Thanks.

[Shailesh Chandra]

Originally posted by Andy Hahn:

I was deciding whether to use a static Datasource reference to store the datasource OR a HashMap reference. Would it be necessary to use a HashMap if all the service is returning is a single Datasource?

If you are retruning only datasource then only a static Datasource referece would suffice.The use of HashMap is not mandory.

ServiceLocator is not only used for DataSource Lookup but also used for other resourse look up like ejb home etc etc or some environment paramter value. Then It would not be a good idea creating a stating variable for every resource.
That is why we use hashmap where we put our look up against a key.

Here is code snippend of my ServiceLocator

public class ServiceLocator {
 
private Context context;
private Map cacheMap;
private static ServiceLocator serviceLocatorInstance;
 

private ServiceLocator () {
try {
  context = getInitialContext();
  cacheMap = Collections.synchronizedMap(new HashMap());
}  catch (Exception ex) {
error(ex);
}
}
 
static public ServiceLocator getInstance() {
if (serviceLocatorInstance == null)
{
  serviceLocatorInstance = new ServiceLocator();
  return serviceLocatorInstance;
}
 
return serviceLocatorInstance;
}
public javax.sql.DataSource getDataSource() throws Exception {
  javax.sql.DataSource dataSource = null;
  String dbJndi = EnvironmentProperties.getProperty("database.JNDI_NAME");
  
  try {
  if (cacheMap.containsKey(dbJndi)) {
  dataSource = (javax.sql.DataSource) cacheMap.get(dbJndi);
  } else {
  dataSource = (javax.sql.DataSource) context.lookup(dbJndi);
  cacheMap.put(dbJndi, dataSource);
  }
  }catch (Exception ex) {
debug(ex);
throw ex;
  }
  return dataSource;
}
private String getEnvValue(String envName)
  {
  String envValue = null;
  envName = "java:comp/env/" + envName;
  try
  {
  if(cacheMap.containsKey(envName))
  {
  envValue = (String)cacheMap.get(envName);
  }
  else
  {
  envValue = (String)context.lookup(envName);
  debug("Picking : " + envName + " from jndi. The value is : " + envValue);
  cacheMap.put(envName, envValue);
  }
  }
  catch(NamingException ne)
  {
  cacheMap.put(envName, null);
  ne.printStackTrace();
  }
  catch (Exception e)
  {
  e.printStackTrace();
  }
  return envValue ;
  }
}

here I am using service locator for various look up looking ejb home, environment parameter etc etc

I cache conn (above) and return that reference for every request for a connection.

I would recommend not to do this, because if one code closes conection then other code will get a closed connection and this would cause an error.
Also this will cause only one connection to be used for all and you will not be able to take advantage of connection pool


Shailesh

[Andy Hahn]

Got it. Works great now. Thanks!

[Pho Tek]

It depends on datasource if it return same connection or creates new conenction.You need not to worry as datasource will handle based on connection pool parameter you code if perfectly good.

What if I wanted a new connection everytime ? How would I configure this in say e.g. JBoss ?

Pho

[Shailesh Chandra]

Originally posted by Pho Tek:

What if I wanted a new connection everytime ? How would I configure this in say e.g. JBoss ?

What advantage do you want to achieve by this ?