Accessing a database from a servlet


File Database.java

The servlet displays a form where a user can type in parameters of the query. The servlet connects to the database, retrieves the data from the database as a ResultSet object via the method executeQuery(), and displays the results on the page.

Are you wondering where the database driver mckoidb.jar is? It's in the directory examples/WEB-INF/lib. There is also a driver in your project web application, so you can write servlets that access a database without worrying about including the driver in the class path. The TOMCAT web application finds it automatically!


import java.io.*;
import java.text.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
//need for JDBC
import java.sql.*;



public class Database extends HttpServlet {
    String first = null;
    String last = null;

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response)
        throws IOException, ServletException
    {
	response.setContentType("text/html");
	PrintWriter out = response.getWriter();

	out.println("<html>");
        out.println("<body bgcolor=\"white\">");
        out.println("<head>");

	out.println("<title> Database servlet </title>");
        out.println("</head>");
        out.println("<body>");

	out.println("<h1>Find out date of birth of residents of Smallville!</h1>"); 

	if ((first != null || last != null)
	    && (!last.equals("") || !first.equals(""))) {
	    out.println("The answer to you request is:<P>");
	    //out.println(first + " " + last);
	    String result = getInfo(out);
	    out.println(result);
	}
	// the writer is passed for error messages
        out.println("<P>");
        out.print("<form action=\"");
        out.print(response.encodeURL("Database"));
        out.print("\" ");
        out.println("method=POST>");
	out.println("Please type in a name<BR>");
	out.println("You can give last name and/or first name,<BR>");
	out.println("The entire name or just a part of a name<P>");
	out.println("First name: ");
        out.println("<input type=text size=15 name=\"First\"><P>");
	out.println("Last name: ");
        out.println("<input type=text size=15 name=\"Last\">");
        out.println("<P>");
        out.println("<input type=submit value=\"Look it up\">");
        out.println("</form>");

	out.println("</body>");
	out.println("</html>");

	out.close();

	first = null;
	last = null;

    }

    public void doPost(HttpServletRequest request,
                      HttpServletResponse response)
        throws IOException, ServletException
    {
	
	first = request.getParameter("First");
	last = request.getParameter("Last");

	// the rest is the same for POST and GET, 
	// so we let doGet() do the job
        doGet(request, response);
    }

    public String getInfo(PrintWriter out) {
	try {
	    Class.forName("com.mckoi.JDBCDriver").newInstance();
	}
	catch (Exception e) {
	    out.println("Unable to register the JDBC Driver.\n");
	    out.println(e);
	}
	
	// This URL specifies we are connecting with a database server
	// on localhost.
	String url = "jdbc:mckoi://birch/";

	// The username / password to connect under.
	String username = "cs349";
	String password = "cs349";

	String result = "";
	     

	// Make a connection with the database.
	Connection con;
	try {
	    con = DriverManager.getConnection(url, username, password);
	}
	catch (SQLException e) {
	    out.println("Unable to make a connection to the database.<BR>");
            out.println("The reason: " + e.getMessage() + "<P>");
	    return "";
           }
	try {
	       
	    Statement stmt = con.createStatement();
           
	    // using executeQuery():
	    String query = "SELECT First_Name, Last_Name, DOB FROM Resident " +
		"WHERE First_Name LIKE '%" + first + "%' AND " +
		"Last_Name  LIKE '%" + last + "%'";
	    ResultSet rs = stmt.executeQuery(query);


	    // moving forward in the result set:
	    while (rs.next()) {
		 String fname = rs.getString("First_Name");
		 String lname = rs.getString("Last_Name");

		 // need to put java.sql.Date so that it doesn't get
		 // confused with java.util.Date
		 java.sql.Date date = rs.getDate("DOB");
		 result = result + fname + " " + lname;
		 result = result  + " was born on " + date + "<P>";
	     }

             // Close the connection when finished
             con.close(); 

           }
           catch (SQLException e) {
             out.println(
               "An error occured\n" +
               "The SQLException message is: " + e.getMessage());
             return "";
           }

	return result;
    }

}

This page has been created and is maintained by Elena Machkasova
Comments and suggestions are welcome at emachkas@wellesley.edu

Spring Semester 2002