Hooking Up To A Database Using Servlets

Submit Your Site For Free!

Email Address:
* URL:
*
*Indicates Mandatory Field

Terms & Conditions

Hooking Up To A Database Using Servlets

By Dragos Mincinoiu
Expert Author
Article Date: 2002-03-07

In the first part of this article, I did not present any installation procedures and I felt that I left you in the cold. Therefore, I will do it here. Please keep in mind that the program presented here in not very difficult and if you cannot connect to the database, it is probably because of your setup.

The Database
The following example describes a command line application, (no GUI for you yet). We are going to use the following classes of the java.sql package: Connection, DriverManager, SQLException. We are connecting to a MySQL database that has the following structure:

As you can tell, the structure of the database is extremely simple; besides the name and email we store the VW models chosen, and some good stuff like the IP address and OS just for kicks.

My environment:
Windows 2000, JDK1.3.1, MySQL. The servlet container is JSDK2.1. The JDBC driver is mm.mysql-2.0.4-bin.jar.CLASSPATH is a User variable you need to create for your machine Control Panel/System/System properties/Environment variables/User variables (the upper box)
Set your CLASSPATH to: C:jdbc_driversmm.mysql-2.0.4-bin.jar;

jdbc_drivers is an actual directory on your drive c: the semicolon is extremely important; do not forget it. Well, there is some reasoning behind this; you are trying to tell your JVM look in that directory ( in this case C:jdbc_drivers ) There you will find your JAR file. So your directory can be Foo (the name really doesn't matter).

Variable "Path" is a System variable on your machine. You will find it under "System variables" Control Panel/System/System properties/Environment variables/System variables (the lower box) The Path variable for your machine should include C:jdk1.3in; do not forget the semicolon, it is really important. Before C: there should be another semicolon. So on Windows2000 you should use JDK1.3

Now, what is the structure of the program?

// vwgolfservice.com guest book that is useless
// but is 3 tier and silly
import java.io.*;
import java.net.*;

import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
import java.sql.*;

public class guestbook extends HttpServlet {
private Statement statement = null;
private Connection connection = null;

public void init( ServletConfig config )
throws ServletException
 {
  super.init( config );

//the try block
//In this block we are attempting to load the driver (create a new 
//instance of the DRIVER class) and connect to the database using a 
//user name and password. The database name is test and the ip 
//address shown here is the location of the database (the ip value 
//changed for obvious reasons).

try 
   {
   Class.forName("org.gjt.mm.mysql.Driver");

connection = DriverManager.getConnection(
    "jdbc:mysql://11.111.111.11/test?user=dragos
    &password=dragos");
  }

//the catch block
//If the attempt fails, an exception is generated and the user 
//prompted.

catch ( Exception e ) 
   {
   e.printStackTrace();
   connection = null;
   }
}

//If you have the environment set up right, you should be connected 
//at this stage, and very cheerful

public void doPost( HttpServletRequest req, HttpServletResponse res )
throws ServletException, IOException 
  {
  String email, firstName, lastName, company,
  golfList, jettaList, passatList, cabrioList, ip_address, servlet_os;

// all you typed or selected online is going to be retrieved with the
// following statements:

email = req.getParameter( "Email" );
  firstName = req.getParameter( "FirstName" );
  lastName = req.getParameter( "LastName" );
  company = req.getParameter( "Company" );

golfList = req.getParameter( "golf" );

jettaList = req.getParameter( "jetta" );

passatList = req.getParameter( "passat" );

cabrioList = req.getParameter( "cabrio" );

//this is going to be retrieved from the browser
  ip_address = req.getRemoteAddr();
  servlet_os = System.getProperty("os.name");

PrintWriter output = res.getWriter();
  res.setContentType( "text/html" );

if ( email.equals( "" ) 
|| firstName.equals( "" ) ||
lastName.equals( "" ) ) 
   {
   output.println( "<H3> Please click the back " +
   "button and fill in all " + "fields.</H3>" );
   output.close();
   return;
   }

// creating the string to insert and inserting into the database

boolean success = insertIntoDB(
  "'" + email + "','" + firstName + "','" + lastName +
  "','" + company + "','" +

//golf
  ( golfList != null ? "yes" : "no" ) + "','" +

//jetta
  ( jettaList != null ? "yes" : "no" ) + "','" +

//passat
  ( passatList != null ? "yes" : "no" ) + "','" +

//corrado
  ( cabrioList != null ? "yes" : "no" ) + "','" + ip_address + "','" +   servlet_os + "'");

if ( success )
   output.print( "<H2>Thank you " + firstName +
   " for registering.</H2>" );
  else
   output.print( "<H2>An error occurred. " +
   "Please try again later.</H2>" );

output.close();
 }

private boolean insertIntoDB( String stringtoinsert )
 {
  try 
  {
   statement = connection.createStatement();

// the problem is here
  statement.execute(
  "INSERT INTO guestbook ( email, firstName, lastName, company,   golf," + "jetta, passat, cabrio, ip_address, servlet_os) values (" +
  stringtoinsert + ");" );
  statement.close();
  }
  catch ( Exception e ) {
  System.err.println(
  "ERROR: Problems with adding new entry" );
  e.printStackTrace();
  return false;
 }

return true;
}

public void destroy()
 {
  try 
   { 
//do not forget to close the connection to the database
   connection.close();
   }
  catch( Exception e ) 
 {
   System.err.println( "Problem closing the database" );
  }
 }
}

JSDK2.1 and the HTML code
The code for your guestbook.html should include the link to your servlet. The servlet host name is usually localhost; I call mine dragos. I have also changed the port number from 8080 to just 80; guess what? You don't have to type 8080 anymore; you just type the name of the local host. After you modify and recompile your code you will have to make sure you have the fresh version of your servlet class in your
webpagesWEB-INFservlets directory if you work in JSDK 2.1. If you have problems configuring Tomcat, you can try JSDK2.1 (an early version of Tomcat) or JRun.

<HTML>
<HEAD>
<TITLE>guestbook</TITLE>
</HEAD>

<BODY bgcolor="#9999FF">
<bg color=9999ff> 
<H1><font color="#000099"> GUESTBOOK</font></H1>
<H1><font color="#000099">demo for a servlet that will save all your info into 
guestbook database!!</font></H1>
<FORM
ACTION=http://dragos/servlet/guestbook
METHOD=POST>
<PRE><font color="#000066">
<font color="#FF0000">*</font> Email address: <INPUT TYPE=text NAME=Email>
<font color="#FF0000">*</font> First Name: <INPUT TYPE=text NAME=FirstName>
<font color="#FF0000">*</font> Last name: <INPUT TYPE=text NAME=LastName>
Company: <INPUT TYPE=text NAME=Company>

* fields are required
</font></PRE>
<P><font color="#000066"><b>Select the VW model you like:</b><BR>
<INPUT TYPE=CHECKBOX NAME=golf VALUE=golf>
VW Golf<BR>
<INPUT TYPE=CHECKBOX NAME=jetta VALUE=jetta>
VW Jetta<BR>
<INPUT TYPE=CHECKBOX NAME=passat VALUE=passat>
VW Passat<BR>
<INPUT TYPE=CHECKBOX NAME=cabrio VALUE=cabrio>
VW Cabrio<BR>
</font></P>
<font color="#000066">
<INPUT TYPE=SUBMIT Value="Submit">
</font> 
</FORM>
</BODY>
</HTML>

I hope you found this useful and have enjoyed it. Java and JDBC offers very fast and convenient way handle database connectivity. You may say, "I can do this in PHP or ASP so why do it in Java?". If you are new to Java and know PHP, ASP or any other server side scripting language, use the language you know. I like Java, I had to write this, so I used Java.

About the Author:
Dragos Mincinoiu is a staff writer for iEntry.

DatabaseProNews is an iEntry, Inc.® publication - 1998-2008 All Rights Re