Hasitha's Tech Blessings

Some times we might need to access a database within a web application. It is true that we can create a connection directly from a web application servlet to a database with relevant drivers packed into the web application. But, in real world, most of the time, these common resources are shared by web applications deployed inside an Application Server. Connections are managed by the server and web applications can use them.

WSO2 Application server does this using Data sources. You can configure a data source and access it using JNDI lookup. Let us see how this can be done.

Download WSO2 Application Server, extract it to a preferred location and start it (navigate to bin folder and execute sh wso2server.sh on Linux).

Creating a Data Source

Access the management console on a browser pointing it to https://192.168.2.3:9443/carbon/
Now navigate to Configure >> Data Sources

Create a datasource as below

Writing the web application 

package org.wso2.carbon.sample;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.Hashtable;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;


public class ControllerServlet extends HttpServlet {

    private static final long serialVersionUID = -6161453398989741214L;
    private static final Log wso2Log = LogFactory.getLog(ControllerServlet.class);
    private DataSource datasource;

    public void init(ServletConfig servletConfig) throws ServletException {
        super.init(servletConfig);
        lookUpDataSource();
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) {
        String userID = request.getParameter("user_id");
        String userName = request.getParameter("user_name");

        try {

            Connection connection = datasource.getConnection();
            Statement stmt = connection.createStatement();
            stmt.execute("INSERT INTO users (userID, userName)"
                    + "VALUES ('" + userID + "','" + userName +"')");

            wso2Log.info("Successfully created the database record...");

        } catch (SQLException e) {
            e.printStackTrace();
            wso2Log.error("This is the caught error" ,e);
        }

    }

    private void lookUpDataSource() {
        try {
            Hashtable environment = new Hashtable();
            environment.put("java.naming.factory.initial", "org.wso2.carbon.tomcat.jndi.CarbonJavaURLContextFactory");
            Context initContext = new InitialContext(environment);
            wso2Log.info("Look up datasource...");
            datasource = (DataSource) initContext.lookup("jdbc/MyCarbonDataSource");
            if (datasource != null) {
                wso2Log.info("Datasource found");
            } else {
                wso2Log.error("Cannot find MyCarbonDataSource");
            }

        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
}

Note how JNDI lookup is done and get a connection. You can find the complete web application at
https://github.com/abeykoon/Blog-Resources/tree/master/web-applications/DataSourceWebApp.

Refer Readme.txt on how to create tables and run the web application.


**IMPORTANT NOTE**

Following JNDI property is a custom property that MUST be added when accessing WSO2 Datasources. Otherwise it will not work.

 environment.put("java.naming.factory.initial", "org.wso2.carbon.tomcat.jndi.CarbonJavaURLContextFactory");

Or if you want to externalize it from the code, when creating the data source add a JNDI property by that name.

name: java.naming.factory.initial
value: org.wso2.carbon.tomcat.jndi.CarbonJavaURLContextFactory