Tomcat DataSource JNDI Example in Java

In my previous post, I explained how to create connection to the database using DataSource element. In this post, I am going to explain how can we configure datasource in tomcat as a resource and how to get the resource using JNDI.

We can configure data source as a resource in three ways.

         a. web.xml configuration

         b. context.xml configuration

         c. Global configuration

a. Configure data source using web.xml file

Data source configuration is defined in META-INF/context.xml file. Container takes care of loading and unloading the data source.

context.xml

<Context>

 <Resource name="jdbc/organization" auth="Container" type="javax.sql.DataSource"
  maxActive="50" maxIdle="30" maxWait="10000" username="derbyuser"
  password="derbyuser" driverClassName="org.apache.derby.jdbc.ClientDriver"
  url="jdbc:derby://localhost:1527/myOrganization;create=true" />

</Context>

We should refer the resource that we defined in META-INF/context.xml in the web.xml file.

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>jdbcDataSourceExample</display-name>
  
  <resource-ref>
    <description>Derby Datasource example</description>
    <res-ref-name>jdbc/organization</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
  
</web-app>

Disadvantages

a. Since the configurations are tightly couple with the application, we need to redeploy the war file, wheneve we change the configurations in context.xml file.

b. The data source is created at application level, so we can't use this data source across different applications.

c. If anyone define a global data source with the same name, then it takes priority than the application level data source.

b. Configure data source using server context.xml file

You can configure the data source in '$CATALINA_BASE/conf/server.xml' file. The file is in tomcat/conf directory. The scope of the resource is application specific.

For example,

if you define a DataSource connection pool of 10 connections in server.xml. Assume there are 50 applications deployed. Then the datasource will be created for each of the application. This will result in 50*10 = 500 connections that will obviously consume all the database server resources and degrade the system performance.

c. Global Configuration

By define the GlobalNamingResources element in server.xml file of the tomcat server, you can make the data source available to all the applications that deployed in this server. 

In the below application, I am going to define data source using first approach.

How to configure MySQL DataSource in Tomcat using application web.xml file

Step 1: Create new dynamic web project in Eclipse.

File -> New -> Other

Search for ‘Dynamic Web Project’.

Give the project name as ‘jdbcDataSourceExample’.

Press Next.

Select the option ‘Generate web.xml deployment descriptor’ and press Finish.

Project structure is created like below.

Step2: Mavenize the project.

Right click on the project -> Configure -> Convert to Maven project.

Add below dependency to the pom.xml.

Step 3: Create META-INF/context.xml

context.xml

<Context>

 <Resource name="jdbc/organization" auth="Container" type="javax.sql.DataSource"
  maxActive="50" maxIdle="30" maxWait="10000" username="derbyuser"
  password="derbyuser" driverClassName="org.apache.derby.jdbc.ClientDriver"
  url="jdbc:derby://localhost:1527/myOrganization;create=true" />

</Context>

Step 4: Define data source in web.xml.

         <resource-ref>

                 <description>Derby Datasource example</description>

                 <res-ref-name>jdbc/organization</res-ref-name>

                 <res-type>javax.sql.DataSource</res-type>

                 <res-auth>Container</res-auth>

         </resource-ref>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>jdbcDataSourceExample</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <resource-ref>
    <description>Derby Datasource example</description>
    <res-ref-name>jdbc/organization</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

Step 5: Define package ‘com.sample’ and define the classes 'DataSourceUtil' and  ‘HelloWorld’ there.

DataSourceUtil.java

package com.sample;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DataSourceUtil {

 public static DataSource getDataSource() {
  DataSource dataSource = null;

  try {
   Context ctx = new InitialContext();
   dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/organization");
  } catch (NamingException e) {
   e.printStackTrace();
  }

  return dataSource;
 }

 public static void createAndInsertDataIntoTable() throws SQLException {
  Connection conn = getDataSource().getConnection();

  /* Create table employee */
  String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
  Statement stmt = conn.createStatement();
  stmt.execute(query);

  /* Insert data to employee table */
  query = "INSERT INTO employee values(1, 'Krishna')";
  stmt.execute(query);
  query = "INSERT INTO employee values(2, 'Arjun')";
  stmt.execute(query);

  query = "SELECT * FROM employee";
  ResultSet rs = stmt.executeQuery(query);

  while (rs.next()) {
   int id = rs.getInt("id");
   String name = rs.getString("name");
   System.out.println(id + " " + name);
  }
  rs.close();
  conn.close();
 }
}

HelloWorld.java

package com.sample;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class HelloWorld
 */
@WebServlet("/HelloWorld")
public class HelloWorld extends HttpServlet {
 private static final long serialVersionUID = 1L;
 

 /**
  * Default constructor.
  */
 public HelloWorld() {

 }

 /**
  * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
  *      response)
  */
 protected void doGet(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  try {
   DataSourceUtil.createAndInsertDataIntoTable();
  } catch (Exception e) {
   response.getWriter().append("data insertion failed, please check logs");
   e.printStackTrace();
  }
  response.getWriter().append("data inserted succesfully to the table employee ");
 }

 /**
  * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
  *      response)
  */
 protected void doPost(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  doGet(request, response);
 }

}

Total project structure looks like below.

Run the Application and hit below url.

http://localhost:8080/jdbcDataSourceExample/HelloWorld

In the console, you can able to see below output.

1 Krishna

2 Arjun

Previous                                                 Next                                                 Home