How to configuring a SQL Server datasource in Apache Tomcat using Java Naming and Directory Interface (JNDI) InitialContext Program?

Download the latest sql server database driver and place it into the lib folder. Restart Tomcat server, once the below changes are done.

1. web.xml configuration
Go to Tomcat root directory -> then explore webapps directory -> select cos (i.e., it is your project folder) and finally select WEB-INF folder. Open web.xml file, if not one, then create web.xml configuration file. Add the below code after <web-app> root tag.

<resource-ref>
  <description>COS Database Connection</description>
  <res-ref-name>jdbc/COS_Test_DB</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

web.xml configuration defined elements are

<web-app>
  <display-name>DNS</display-name>
  <description>DNS Application.</description>

  <resource-ref>
     ..... resources information
  <resource-ref/>

  <servlet>
    <servlet-name>AdvertiserACH</servlet-name>
    <servlet-class>com.dns.payment.AdvertiserACH</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>AdvertiserACH</servlet-name>
    <url-pattern>/advertiserACH.jsp</url-pattern>
  </servlet-mapping>
<web-app/>

The above elements may be used in the web application deployment descriptor(/WEB-INF/web.xml) of your web application.

2. context.xml configuration
Go to Tomcat root directory -> then explore conf directory -> and finally open context.xml file.

It can be available either $CATALINA_BASE/conf/server.xml or the per-web-application context XML file (META-INF/context.xml).

<?xml version='1.0' encoding='utf-8'?>
<Context>
  <WatchedResource>WEB-INF/web.xml>/WatchedResource>

  <Resource name="jdbc/COS_Test_DB"
    auth="Container"
    type="javax.sql.DataSource"
    username="uid"
    password="pwd"
    driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDrive
    maxActive="20"
    maxIdle="10"
    url="jdbc:sqlserver://1.2.3.4:1433;databaseName=cos_test_08"
    SelectMethod="cursor"/>

</Context>

The above configuration is for the sql server database connection. Used JDBC 4.0 API for the database program. Defined selectMethod, it contains value either ["direct"] or ["cursor"].

"cursor" - created for each query created on the connection for TYPE_FORWARD_ONLY and CONCUR_READ_ONLY cursors. Only if the application generates very large result sets that cannot be fully contained in client memory.

3. Accessing resources

import java.sql.*;
import javax.naming.*;
import javax.sql.DataSource;

public class DbConnection
{
  public Connection GetConnection() throws SQLException
  {
    Connection conDB = null;
    try
    {
      //sqljdbc4.jar - support JDBC 4.0, it works only JRE 6.0 or later

      //Get value from web.xml
      Context initCtx = new InitialContext();
      Context envCtx = (Context) initCtx.lookup("java:comp/env");
      DataSource sqlDS = (DataSource)envCtx.lookup("jdbc/COS_Test_DB");
      conDB = sqlDS.getConnection();
    }
    catch (Exception e)
    {
      System.out.println(" ***** DbConnection.GetConnection ERROR ***** " + e);
    }
    return conDB;
  }
}

The InitialContext is configured as a web application is initially deployed, and is made available to web application components (for read-only access). All configured entries and resources are placed in the java:comp/env portion of the JNDI namespace.