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>

  1. aahil on 2012-2-23

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

  2. aahil on 2012-2-23

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

  3. FibreFoX on 2012-5-25

    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

  4. :D on 2013-4-23

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

  5. ryanlr on 2013-4-23

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

  6. Annonymous on 2013-10-8

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

  7. adrian on 2013-10-11

    Is it also an example of MVC pattern?

  8. naved malik on 2013-11-22

    i m giving this

    mypack.myServletListener

    but when i m running null pointer exception occured

  9. aman abhishek on 2014-1-27

    evn i m also getting Null Pointer exception

  10. nikhil kadam on 2014-3-29

    Thank you so much..It worked for me 🙂

  11. Nagesh on 2015-9-24

    its really helpfull

  12. Elizabeth McGurty on 2016-6-2

    Just want to say thanks… Very helpful.

  13. abc on 2016-6-18

    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

  14. prince on 2016-7-1

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

Leave a comment

*