Having trouble with Datasource & session cross reference in a multi URL application

Question

I have an application which is configured to connect to a specific database as per the URL with their unique credentials-

eg -

1. demo.mydomain.com (DB Name - demo)
2. client1.mydomain.com (DB Name - client1)
3. client2.mydomain.com (DB Name - client2)

I have defined the data source in context.xml in my web-inf dir as below -

<!-- demo Environment Configuration Starts  -->

<Resource type="javax.sql.DataSource"
            name="jdbc/demo" 
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/demo"
            username="demo" 
            password="demo" 
            initialSize="10"
            maxActive="100" 
            maxIdle="30" 
            minIdle="0"
            suspectTimeout="60"
            timeBetweenEvictionRunsMillis="30000"
            minEvictableIdleTimeMillis="60000"
           />

<!-- Client1 Environment Configuration Starts   -->

<Resource type="javax.sql.DataSource"
            name="jdbc/client1" 
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/client1"
            username="client1" 
            password="client1" 
            initialSize="10"
            maxActive="100" 
            maxIdle="30" 
            minIdle="0"
            suspectTimeout="60"
            timeBetweenEvictionRunsMillis="30000"
            minEvictableIdleTimeMillis="60000"
            />

The list goes on for all the available clients.

The SysServlet is defined in web.xml with a URL mapping -

<servlet>
    <servlet-name>SysInfo</servlet-name>
    <servlet-class>com.emsproject.action.common.SysServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>SysInfo</servlet-name>
    <url-pattern>/eapp/*</url-pattern>
</servlet-mapping>

---

public class SysServlet extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet{
static final long serialVersionUID = 1L;
private static DataSource dataSource;

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
    try {
        String clientName = request.getPathInfo();
        String esmPool = clientName.substring(1, clientName.length()).trim();
        /******************************************************
         * Below code prepares the datasource by JNDI lookup * 
         ******************************************************/
        dataSource = (DataSource) new InitialContext().lookup("java:/comp/env/jdbc/"+esmPool);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

/*Returns a connection */
public Connection getConnection() throws Exception {
    return dataSource.getConnection();
}

/*Closing connection when transaction is over*/
public void freeConnection(Connection dbConn) {
    try { 
        if(null != dbConn){
            dbConn.close();
        }
    } catch (SQLException e){
        e.printStackTrace();
    }
}

}

The Application is built using -

1. Struts 2
2. MySql 5
3. Tomcat 6

The application works fine without any issue for a single client at a time but the problems I am facing are as below -

1. When a client1 performs some insert/update it is reflected to client2 & so on when application is accessed simultaneously by them.

2. When a Client1 performs a login (I keep certain details in session) & later Client2 logs in to the system the Client2 info is shared or visible to client1.

Simultaneous access is troubling me Kindly suggest me a solution for the same ....

---

I have made few changes to the code as below while fetching a connection -

public Connection getConnection(String clientName) throws Exception {
    DataSource dataSource = (DataSource) new InitialContext().lookup("java:/comp/env/jdbc/"+clientName);
    return dataSource.getConnection();
} 

But I have one concern regarding the statement -

DataSource dataSource = (DataSource) new InitialContext().lookup("java:/comp/env/jdbc/"+clientName);

Is it ok to initialize a DataSource every time I need a connection...

Kindly suggest...

Answer 1

You are not technically initializing a DataSource each time you need a connection. You are looking up the DataSource in the JNDI context each time. There will only ever be one instance of each DataSource.

The lookup is pretty fast (essentially just a map lookup) so I personally wouldn't worry too much.

What is going to be much much slower is creating the InitialContext. The creation of InitialContext() is slow and should only be done once in your application and cached.

Answer 2

@Pace

I am adding it as an answer to better format the code in the post I have modified,

The ctx throws a null pointer ........ (I have no idea about caching the InitialContext)

public class SysServlet extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet{
    static final long serialVersionUID = 1L;

    //Context ctx = null;
    InitialContext ctx = null;



    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
        try {
            ctx = new InitialContext();
            String clientName = request.getPathInfo();
            String esmPool = clientName.substring(1, clientName.length()).trim();
            } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /*Returns a connection */
    public Connection getConnection(String clientName) throws Exception {
        DataSource dataSource = (DataSource) ctx.lookup ("java:/comp/env/jdbc/"+clientName);
        return dataSource.getConnection();
    }     
    /*Closing connection when transaction is over*/
    public void freeConnection(Connection dbConn) {
        try { 
            if(null != dbConn){
                dbConn.close();
            }
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

I need sugestions to avoid doing new InitialContext() on every getConnection();

Answer 3

I made few changes to initialize the Context only once per login & this seems to be working fine.... Only had to change the

InitialContext ctx = null;

to

private static InitialContext ctx = null;

Answer 4

Just construct the InitialContext during object construction of the Servlet:

public class SysServlet ....{
    private InitialContext ctx = new InitialContext();
    ....
}

After that, you can do the look-up in the getConnection() and it should work.