Quantcast

Hooking Up To A Database Using Servlets

Get the WebProNews Newsletter:


[ Business]

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:

entry_id integer not null auto_increment, email varchar(40) null, firstName varchar(40) null, lastName varchar(40) null, company varchar(40) null, # the cars chosen golf varchar(3) null, jetta varchar(3) null, passat varchar(3) null, cabrio varchar(3) null, # the good stuff ip_address varchar(20) null, servlet_os varchar(20) null, created timestamp primary key guestbook_key (entry_id)

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.

<!– VolksTechnik.com & vwgolfservice.com –>
<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.

Dragos Mincinoiu is a staff writer for iEntry.

Hooking Up To A Database Using Servlets
Comments Off


Top Rated White Papers and Resources

Comments are closed.

  • Join for Access to Our Exclusive Web Tools
  • Sidebar Top
  • Sidebar Middle
  • Sign Up For The Free Newsletter
  • Sidebar Bottom