Technical Stuff: September 2014

Step 1: Download commons-io.jar, commons-lang-2.6.jar and log4j-1.2.15.jar.

Step2: Put jar files in WEB-INF/lib.

Step3: Create a web application with servlet.

Step 4. Complete Servlet Code for reference:

log4j.xml : (for JBoss -- uncomment "" and comment "<param name="File" value="../logs/loggers.log" />")

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//log4j/log4j Configuration//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="abc-date-rollout" class="org.apache.log4j.DailyRollingFileAppender">
<param name="File" value="../logs/loggers.log" />

<param name="DatePattern" value="'.'yyyy-MM-dd" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d{dd MMM yyyy HH:mm:ss,SSS} %-5p: %c - %m%n"/>
</layout>
</appender>

<appender name="ASYNC" class="org.apache.log4j.AsyncAppender">
<param name="BufferSize" value="500"/>
<appender-ref ref="abc-date-rollout"/>
</appender>

<root>
<priority value="INFO"/>
<appender-ref ref="abc-date-rollout" />
</root>
</log4j:configuration>

Complete 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" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>DBConnServlet</display-name>
<context-param>
<param-name>log4j-properties-location</param-name>
<param-value>WEB-INF/classes/log4j.xml</param-value>
</context-param>
<resource-ref>
<description>COS Database Connection</description>
<res-ref-name>jdbc/DSName</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
<mapped-name>jdbc/DSName</mapped-name>
</resource-ref>
<listener>
<listener-class>com.test.listener.AppContextListener</listener-class>
</listener>
<servlet>
<description>Proxy servlet for all Technical workflows</description>
<display-name>DBConnServlet</display-name>
<servlet-name>DBConnServlet</servlet-name>
<servlet-class>com.test.servlet.DBConnServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DBConnServlet</servlet-name>
<url-pattern>/Connection/*</url-pattern>
</servlet-mapping>
<session-config>
<session-timeout>1440</session-timeout>
</session-config>
</web-app>

Listener Class

package com.test.listener;

import java.io.File;
import java.util.Date;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;
import org.apache.log4j.xml.DOMConfigurator;

public class AppContextListener implements ServletContextListener {

/**
* @see ServletContextListener#contextInitialized(ServletContextEvent)
*/
public void contextInitialized(ServletContextEvent objServletContextEvent) {
final ServletContext servletContext = objServletContextEvent.getServletContext();

// Logger initialization starts
Logger log = null;
System.out.println("AppContextListener is initializing log4j");
String log4jLocation = servletContext.getInitParameter("log4j-properties-location");
if (log4jLocation == null) {
System.err
.println("*** No log4j-properties-location init param, so initializing log4j with BasicConfigurator");
BasicConfigurator.configure();
} else {
String webAppPath = servletContext.getRealPath("/");
String log4jProp = webAppPath + log4jLocation;
File log4j = new File(log4jProp);
if (log4j.exists()) {
System.out.println("Initializing log4j with: " + log4jProp);
DOMConfigurator.configure(log4jProp);
} else {
System.err
.println("*** " + log4jProp + " file not found, so initializing log4j with BasicConfigurator");
BasicConfigurator.configure();
}
}
//Get logger to log events
log = Logger.getLogger(AppContextListener.class.getName());
// Logger initialization Ends
log.info("contextInitialized(): ServletContextListener started : " + new Date());
}

/**
* @see ServletContextListener#contextDestroyed(ServletContextEvent)
*/
public void contextDestroyed(ServletContextEvent objServletContextEvent) {
Logger log = Logger.getLogger(AppContextListener.class.getName());
// get our timer from the Context
log.info("run(): ServletContextListener destroyed : ");
}

}

Servlet Class for Connection:

package com.test.servlet;

import java.io.IOException;

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.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;

import org.apache.log4j.Logger;

/**

* Servlet implementation class DBConnectionServlet

public class DBConnServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

static Logger log = Logger.getLogger(DBConnServlet.class.getName());

* (non-Javadoc)

* @see javax.servlet.GenericServlet#init(javax.servlet.ServletConfig)

public void init(ServletConfig config) {

}

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

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

doPost(request, response);

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,

IOException {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

con = getConnection();

// Execute SQL query

stmt = con.createStatement();

String sql;

sql = "SELECT FirstName, LastName FROM [Config].[UserMaster]";

rs = stmt.executeQuery(sql);

while (rs.next()) {

// Retrieve by column name

String first = rs.getString("FirstName");

String last = rs.getString("LastName");

System.out.println("Name : " + first + " " + last);

}

rs.close();

stmt.close();

con.close();

} catch (Exception e){

try{

rs.close();

stmt.close();

con.close();

}catch(SQLException sqle){

}

/**

* Get connection from data source.

* @return

* @throws SQLException

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/DSName");

conDB = sqlDS.getConnection();

} catch (Exception e) {

System.out.println(" ***** DbConnection.GetConnection ERROR ***** " + e);

}

return conDB;

}

Step 1: Download the Jar file for JDBC 4.

Step2: Put sqljdbc4.jar in /TomcatHome/Tomcat6.0/lib for tomcat and Jboss-5.1.0.-jdk\server\default\lib for JBoss.

Step3: Create a web application with servlet which will be used to test the database connection through JBoss and Tomcat.

Application Details:
1.WEB-INF/web.xml of web application will contain (This is required for both Tomcat and JBoss).
<resource-ref>
   <description>COS Database Connection</description>
   <res-ref-name>jdbc/DSName</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
   <res-sharing-scope>Shareable</res-sharing-scope>
   <mapped-name>jdbc/DSName</mapped-name>
</resource-ref>

2. META-INF/context.xml will contain an entry for resource (Required for Tomcat)
<?xml version='1.0' encoding='utf-8'?>
<Context path="/DBConnectionServlet/Connection" docBase="DBTest"
debug="5" reloadable="true" crossContext="true" sessionCookiePathUsesTrailingSlash='false'>
<Resource
name="jdbc/DSName"
auth="Container"
type="javax.sql.DataSource"
removeAbandoned="true" removeAbandonedTimeout="30" maxActive="100" maxIdle="30" maxWait="10000"
username="username" password="password" testOnBorrow="true" validationQuery="select 1"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://XXX.XXX.XXX.XXX:1433;DatabaseName=DatabaseName"/>

</Context>

3. WEB-INF/jboss-web.xml will contain resource-ref for database. (Required for JBoss)
<?xml version="1.0" encoding="UTF-8"?>
<jboss-web>
<resource-ref> <description>COS Database Connection</description> <res-ref-name>jdbc/DSName</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope> <mapped-name>jdbc/DSName</mapped-name> </resource-ref>

</jboss-web>

4. Create mssql-ds.xml and put it at \Jboss-5.1.0.-jdk\server\default\deploy (Required for JBoss)

<?xml version="1.0" encoding="UTF-8"?>

















<datasources>
<local-tx-datasource>
<jndi-name>jdbc/DSName</jndi-name>
<use-java-context>false</use-java-context>
<connection-url>jdbc:sqlserver://XXX.XXX.XXX.XXX:1433; DatabaseName=DatabaseName</connection-url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<user-name>username</user-name>
<password>password</password>





<metadata>
<type-mapping>MS SQLSERVER2000</type-mapping>
</metadata>
</local-tx-datasource>

</datasources>

5. Complete Servlet Code for reference:

Complete 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" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>DBConnServlet</display-name>
<context-param>
<param-name>log4j-properties-location</param-name>
<param-value>WEB-INF/classes/log4j.xml</param-value>
</context-param>
<resource-ref>
<description>COS Database Connection</description>
<res-ref-name>jdbc/DSName</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
<mapped-name>jdbc/DSName</mapped-name>
</resource-ref>
<listener>
<listener-class>com.test.listener.AppContextListener</listener-class>
</listener>
<servlet>
<description>Proxy servlet for all Technical workflows</description>
<display-name>DBConnServlet</display-name>
<servlet-name>DBConnServlet</servlet-name>
<servlet-class>com.test.servlet.DBConnServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DBConnServlet</servlet-name>
<url-pattern>/Connection/*</url-pattern>
</servlet-mapping>
<session-config>
<session-timeout>1440</session-timeout>
</session-config>
</web-app>

Listener Class

package com.test.listener;

import java.io.File;
import java.util.Date;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;
import org.apache.log4j.xml.DOMConfigurator;

public class AppContextListener implements ServletContextListener {

/**
* @see ServletContextListener#contextInitialized(ServletContextEvent)
*/
public void contextInitialized(ServletContextEvent objServletContextEvent) {
final ServletContext servletContext = objServletContextEvent.getServletContext();

// Logger initialization starts
Logger log = null;
System.out.println("AppContextListener is initializing log4j");
String log4jLocation = servletContext.getInitParameter("log4j-properties-location");
if (log4jLocation == null) {
System.err
.println("*** No log4j-properties-location init param, so initializing log4j with BasicConfigurator");
BasicConfigurator.configure();
} else {
String webAppPath = servletContext.getRealPath("/");
String log4jProp = webAppPath + log4jLocation;
File log4j = new File(log4jProp);
if (log4j.exists()) {
System.out.println("Initializing log4j with: " + log4jProp);
DOMConfigurator.configure(log4jProp);
} else {
System.err
.println("*** " + log4jProp + " file not found, so initializing log4j with BasicConfigurator");
BasicConfigurator.configure();
}
}
//Get logger to log events
log = Logger.getLogger(AppContextListener.class.getName());
// Logger initialization Ends
log.info("contextInitialized(): ServletContextListener started : " + new Date());
}

/**
* @see ServletContextListener#contextDestroyed(ServletContextEvent)
*/
public void contextDestroyed(ServletContextEvent objServletContextEvent) {
Logger log = Logger.getLogger(AppContextListener.class.getName());
// get our timer from the Context
log.info("run(): ServletContextListener destroyed : ");
}

}

Servlet Class for Connection:

package com.test.servlet;

import java.io.IOException;

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.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;

import org.apache.log4j.Logger;

/**

* Servlet implementation class DBConnectionServlet

public class DBConnServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

static Logger log = Logger.getLogger(DBConnServlet.class.getName());

* (non-Javadoc)

* @see javax.servlet.GenericServlet#init(javax.servlet.ServletConfig)

public void init(ServletConfig config) {

}

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

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

doPost(request, response);

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,

IOException {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

con = getConnection();

// Execute SQL query

stmt = con.createStatement();

String sql;

sql = "SELECT FirstName, LastName FROM [Config].[UserMaster]";

rs = stmt.executeQuery(sql);

while (rs.next()) {

// Retrieve by column name

String first = rs.getString("FirstName");

String last = rs.getString("LastName");

System.out.println("Name : " + first + " " + last);

}

rs.close();

stmt.close();

con.close();

} catch (Exception e){

try{

rs.close();

stmt.close();

con.close();

}catch(SQLException sqle){

}

/**

* Get connection from data source.

* @return

* @throws SQLException

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/DSName");

conDB = sqlDS.getConnection();

} catch (Exception e) {

System.out.println(" ***** DbConnection.GetConnection ERROR ***** " + e);

}

return conDB;

}

Complete context.xml:

<?xml version='1.0' encoding='utf-8'?>
<Context path="/DBConnApp/Connection" docBase="DBTest"
debug="5" reloadable="true" crossContext="true" sessionCookiePathUsesTrailingSlash='false'>

<Resource
name="jdbc/DSName"
auth="Container"
type="javax.sql.DataSource"
removeAbandoned="true" removeAbandonedTimeout="30" maxActive="100" maxIdle="30" maxWait="10000"
username="aj83793" password="Mahavir9" testOnBorrow="true" validationQuery="select 1"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://10.173.56.215:1433;DatabaseName=CobaltDEV"/>

</Context>

Complete jboss-web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<jboss-web>
<class-loading java2ClassLoadingCompliance="false">
<loader-repository>
org.myapp:loader=MyClassLoader
<loader-repository-config>java2ParentDelegation=false</loader-repository-config>
</loader-repository>
</class-loading>

<resource-ref> <description>COS Database Connection</description> <res-ref-name>jdbc/DSName</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope> <mapped-name>jdbc/DSName</mapped-name> </resource-ref>

</jboss-web>

log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//log4j/log4j Configuration//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="cblt-date-rollout" class="org.apache.log4j.DailyRollingFileAppender">
<param name="File" value="../logs/loggers.log" />

<param name="DatePattern" value="'.'yyyy-MM-dd" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d{dd MMM yyyy HH:mm:ss,SSS} %-5p: %c - %m%n"/>
</layout>
</appender>

<appender name="ASYNC" class="org.apache.log4j.AsyncAppender">
<param name="BufferSize" value="500"/>
<appender-ref ref="cblt-date-rollout"/>
</appender>

<root>
<priority value="INFO"/>
<appender-ref ref="cblt-date-rollout" />
</root>
</log4j:configuration>

Technical Stuff

Friday, 5 September 2014

Log4j Configuration for web application in JBOSS and Tomcat

Thursday, 4 September 2014

JDBC Connection configuration to MicroSoft SQL server database in Tomcat 6.0 and JBoss 5.1.0.GA

Blog Archive

About Me