Multiple dynamic data sources for a servlet context

Question

I'm developing a java servlet web application that manages information from multiple databases (all structurally the same) each corresponding to a different "business". The user selects "the current business" which is stored in the session and the application can display or modify that "current business".

I would like to use tomcat Resources in a dynamic way to have access to these businesses using jndi. In this way I can use the jstl sql tags or context lookups in servlets. I can not define each Resource in the web.xml file because they are stored in a SQL table. The end result is to be able to write simple jsp that has lines like these:

<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>

<sql:query var = "users" dataSource="sources/${sessionScope.currentBusiness}">
  select id, firstName, lastName FROM user
</sql:query>

or servlets that can have lines like these

String request.getSession().getAttribute("currentBusiness");

Context initial = new InitialContext();
Context context = (Context) initial.lookup("java:comp/env");
DataSource source = (DataSource) context.lookup("sources/" + currentBusiness);

where I can get the correct datasource for the "current business".

I have experimented with writing my own ObjectFactories derived from javax.naming.spi.ObjectFactory without success. Any pointers on how to easily do this?

Answer 1

Create the data sources in a ServletContextListener and place them in the ServletContext.

Answer 2

This approach will certainly "work", but the notion of a separate, identical database for each business seems wrong to me. Surely being able to delineate business somewhere in the schema seems possible. Separating them this way requires a new database per business, where a schema would only require a new business identifier.

I'd also argue that any possibility of cross-business data mining is lost, unless you ETL data from separate databases into a dimensional cube for ad hoc reporting and querying.

And JSTL <sql> tags should only be used in the simplest web apps. You leave yourself open to the possibility of SQL injection attacks when you forego validation on a middle tier.

UPDATE:

You have to declare resources in your web.xml AFAIK, so whenever you have a new database you have to stop the application, configure the new JNDI source, and restart Tomcat. I hope you're clustered, because all the previous clients will be affected by the app being down every time you add a new business/database.

Answer 3

I finally settled for the following solution consisting on a SessionListener and a Servlet that work as follows. The SessionListener has the following form:

public class SessionListener implements HttpSessionListener {

  public void sessionCreated(HttpSessionEvent event) {

    HttpSession session = event.getSession();

    // get list of possible data sources available to this session
    List<DataSource> sources = new ArrayList<DataSource>();
    ... code to get the available sources

    // get the current data source
    DataSource source = null;
    ... code to get the current source                               
    source = sources.get(0); // for example

    // setup the session attributes
    session.setAttribute("availableSources", sources);
    session.setAttribute("currentSource", source); 

  }

}

Whenever a user logs in and a session is created, the list of available DataSources, and the current one, are placed into the session. This is done at the Session level because DataSources depend on the user login in. It is now possible to have access at them from within the application. To change the current DataSource I created a Servlet with this simplified version:

public abstract class BoxletServlet extends HttpServlet {

  protected void doGet(HttpServletRequest request, HttpServletResponse response) 
    throws ServletException, IOException {

    HttpSession session = request.getSession(true);
    String s = request.getParameter("source");

    // based on 's' choose from the available DataSource
    List<DataSource> sources = (List<DataSource>) session.getParameter("availableSources");
    Source source = chooseFrom(sources, s);                                                       
    session.setParameter("currentSource", source);          

    // forward to a page saying that the DataSource changed

  }

}

With this implementation it is now possible to create the following jsps:

<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>

<sql:query var = "users" dataSource="${sessionScope.currentSource}">
  select id, firstName, lastName FROM user
</sql:query>

Hope it helps someone else.