Put Database Connection to ServletContextListener

ServletContextListener is helpful in informing about context Initilazation and destruction. In a typical content management web system, there is normally a database behind. The example below demonstrates how to connect MySQL database during context initialization stage.

Tools and packages: eclipse and JDBC MySQL Connector.

1. Create a dynamic web project TestServlet, import the connector .jar file to the project.

The jar file will be in the lib directory. Create a database "testdb" and a table "user". Put some records inside for testing connection later.

2. Put a listener element in the web.xml Deployment Descriptor and also put the database configuration in it.

<?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>TestServlet</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>
  <listener>
    <listener-class>com.programcreek.testservletlistener.myServletListener</listener-class>
  </listener>
  <context-param>
    <param-name>url</param-name>
    <param-value>jdbc:mysql://localhost/</param-value>
  </context-param>
  <context-param>
  	<param-name>database</param-name>
  	<param-value>testdb</param-value>
  </context-param>
  <context-param>
  	<param-name>user_name</param-name>
  	<param-value>xiaoran</param-value>
  </context-param>
  <context-param>
  	<param-name>password</param-name>
  	<param-value>xiaoran</param-value>
  </context-param>
  <servlet>
    <description></description>
    <display-name>testClass</display-name>
    <servlet-name>testClass</servlet-name>
    <servlet-class>edu.uams.testservlet.testClass</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>testClass</servlet-name>
    <url-pattern>/testClass</url-pattern>
  </servlet-mapping>
 
</web-app>

3. Create a listener class "myServletListener"

package com.programcreek.testservletlistener;
 
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
 
/**
 * Application Lifecycle Listener implementation class myServletListener
 *
 */
public class myServletListener implements ServletContextListener {
 
	/**
     * @see ServletContextListener#contextInitialized(ServletContextEvent)
     */
    public void contextInitialized(ServletContextEvent event) {
 
    	ServletContext sc = event.getServletContext();
 
    	String url = sc.getInitParameter("url");
    	String user_name = sc.getInitParameter("user_name");
    	String password = sc.getInitParameter("password");
    	String database = sc.getInitParameter("database");
    	Database db = new Database(url + database, user_name, password);
    	//System.out.println("in the listener!!");
    	sc.setAttribute("db", db);
 
    }
 
	/**
     * @see ServletContextListener#contextDestroyed(ServletContextEvent)
     */
    public void contextDestroyed(ServletContextEvent arg0) {
        // TODO Auto-generated method stub
    }
 
}

4. Write the Database class which is for connecting database and providing database operations.

package com.programcreek.testservletlistener;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class Database {
 
	private Connection conn = null;
 
	public Database(String url, String user_name, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
 
			this.conn = DriverManager.getConnection(url, user_name, password);
 
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
 
	public Connection getConnection() {
		return this.conn;
	}
 
	public ResultSet runSql(String sql) throws SQLException {
		Statement sta = conn.createStatement();
		return sta.executeQuery(sql);
	}
}

5. Write the testing Servlet class.

package com.programcreek.testservletlistener;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
//import javax.servlet.ServletContext;
 
/**
 * Servlet implementation class testClass
 */
public class testClass extends HttpServlet {
	private static final long serialVersionUID = 1L;
 
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
 
		response.setContentType("text/html");
 
		Database db = (Database) getServletContext().getAttribute("db");
		PrintWriter out = response.getWriter();
 
		String sql = "select * from user";
		ResultSet rs;
		try {
			rs = db.runSql(sql);
 
			while(rs.next()){
				out.println(rs.getString("user_name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
 
	}
 
	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}
 
}

6. Build the project and deploy to tomcat 6.

Build the project and copy the files to tomcat "webapps" directory.
The finally directory and files would look like the following directory:
-TestServlet

---index.html

---WEB-INF

---------------classes
---------------------------com
------------------------------------programcreek
-------------------------------------------------------testservletlistener

-------------------------------------------------------------------------------------------Database.class

-------------------------------------------------------------------------------------------myServletListener.class

-------------------------------------------------------------------------------------------testClass.class

---------------lib

---------------------------mysql-connector-java-5.1.7-bin.jar

That's it. I did this just for fun, and not sure this is the correct way to connect database.

Category >> Database >> JSP/JSF  
If you want someone to read your code, please put the code inside <pre><code> and </code></pre> tags. For example:
<pre><code> 
String foo = "bar";
</code></pre>
  • prince

    how you get the object of the db without invoking its getConnection function in your database class.

  • abc

    i have question its compulsory to call runsql() method to execute the query. its can send the conn response to the servlet so that there we can create statement object and execute the query

  • Elizabeth McGurty

    Just want to say thanks… Very helpful.

  • Nagesh

    its really helpfull

  • nikhil kadam

    Thank you so much..It worked for me 🙂

  • aman abhishek

    evn i m also getting Null Pointer exception

  • naved malik

    i m giving this

    mypack.myServletListener

    but when i m running null pointer exception occured

  • adrian

    Is it also an example of MVC pattern?

  • Annonymous

    thank you so much…i havent found so clear and exact procedure till date…Thank you so much

  • ryanlr

    It is created in context initialization stage, so should not be closed. Otherwise, the request will not have db to use.

  • :D

    where did you put the .close() method ? or this isn’t necessary ?

  • I know, its an old posting, but:
    Database db = (Database) getServletContext().getAttribute(“db”);

    the doGet will throw another NPE if the attribute wasn’t set.

    also POST and GET shouldn’t be split because there won’t be a connection on POSTing

  • aahil

    I was looking for the Database connectivity using listener. I got nothing but your program. Thanks..:))

  • aahil

    Great.. You did a great a job..!! Thank you..:))