A Dynamic Web Project (part II)

The object of this exercise is to create a Dynamic Web Project in Eclipse and deploy it to your local Apache Tomcat server and then to an Apache Tomcat server running in a VirtualBox Ubuntu guest.

The application will contain a simple index.html file, a simple index.jsp file and two servlets DBConnect and JNDIConnectDB. The first servlet will create and connect to a database directly using sql commands. The second servlet will connect to a database using a configured JNDI entry in Apache Tomcat.

Create a simple servlet

Right click on the source folder in the project and create a new servlet as follows:

Java Resources: src (right-click)
new -> other -> web -> servlet
next

set the following in the Create Servlet window:

java package: servlets
class name: DBConnect

and click next to navigate to the next screen in the wizard.
In the URL Mappings window, click on /DBConnect and click the edit button

URL Mappings
/DBConnect
Edit

change the pattern from /DBConnect to /servlet/DBConnect

/DBConnect (change to) /servlet/DBConnect

click ok and then click finish

ok
finish

Enter the following code into DBConnect.java

package servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class DBConnect
 */
public class DBConnect extends HttpServlet
{
	private static final long serialVersionUID = 1L;

	private static String driver = "org.apache.derby.jdbc.ClientDriver";
	private static String protocol = "jdbc:derby://localhost:1527/";
	private static String dbName = "mydb";
	
	private Statement s;

	private static boolean driverloaded = false;
	
	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public DBConnect()
	{
		super();
		// TODO Auto-generated constructor stub
	}

	private void process(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{	
		try
		{
			PrintWriter out = response.getWriter();
			
			if(!driverloaded)
			{
				Class.forName(driver);
				driverloaded=true;
			}
			
			Properties props = new Properties();

			String user = request.getParameter("user");
			String password = request.getParameter("password");
			String create = request.getParameter("create");

			if(user==null) user = "john";
			if(password==null) password = "password";
			
			props.put("user", user);
			props.put("password", password);
			
			Connection conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
			System.out.println("Connected to and created database " + dbName);

			s = conn.createStatement();
			
			if(create!=null && create.equals("true"))
			{
				execute("DROP TABLE test");				
				execute("CREATE TABLE test (id INTEGER NOT NULL,name VARCHAR(32) NOT NULL)");
				execute("ALTER TABLE test ADD PRIMARY KEY (id)");
				execute("INSERT INTO test(id,name) VALUES(1,'john')");
				execute("INSERT INTO test(id,name) VALUES(2,'richard')");
			}
			
			ResultSet rs = s.executeQuery("select * from test");
			
			out.println("<staff>");
			
			while(rs.next())
			{
				ResultSetMetaData rsmd = rs.getMetaData();
				
				out.println("<employee>");
				
				for(int i=1;i<=rsmd.getColumnCount();i++)
				{
					String colName = rsmd.getColumnName(i);
					
					out.println("<"+colName+">");
					out.println(rs.getObject(i));
					out.println("</"+colName+">");
				}
				
				out.println("</employee>");
			}
			
			out.println("</staff>");
		}
		catch (SQLException e)
		{
			throw new ServletException(e);
		}
		catch (ClassNotFoundException e)
		{
			throw new ServletException(e);
		}
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		process(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,
			IOException
	{
		process(request, response);
	}
	
	private void execute(String query)
	{
		try
		{
			s.execute(query);
			System.out.println("executed: "+query);
		}
		catch(SQLException e)
		{
			System.err.println(query);
			System.err.println(e.getMessage());
		}
	}

}

Start the Server and Test the Servlet

First of all make certain that your Derby database is running (see earlier blog entries).

Right click on the project (Tomcat1) and click run on server

project (Tomcat1) - (right click)
run on server

The server will start.

Now open your browser and navigate to the servlet by entering the following url into your browser:

http://localhost:8080/Tomcat1/servlet/DBConnect

The output you should see in your browser window is below:

<staff>
<employee>
<ID>
1
</ID>
<NAME>
john
</NAME>
</employee>
<employee>
<ID>
2
</ID>
<NAME>
richard
</NAME>
</employee>
</staff>