New Friends - Java

This week, find out how to connect your JSPs to a database and build dynamic, data-driven Web pages. This primer covers different techniques to select, insert and delete records, and uses a simple Web-based address book to illustrate the Connection, Statement, and ResultSet objects.

TABLE OF CONTENTS:

By: Vikram Vaswani and Harish Kamath, (c) Melonfire

Poor Best

Rating:

/ 3

March 19, 2001

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Thus far, we've simply been using SELECT queries to pull information out of a database. But how about putting something in?

SQL aficionados know that this happens via an INSERT query. And so, the next item on the agenda involves adding new entries to the address book. Here's the form we'll be using:


<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>
<h2>Add Address Book Entry</h2>
<table border=0 cellspacing=5 cellpadding=5>
<form action="add_res.jsp" method="POST">
<tr>
<td><b>Username</b></td>
<td>
<select name="uid">
<!-- generate list of available usernames from database -->
<%@ page language="java" import="java.sql.*" %>
<%
// database parameters
String host="localhost";
String user="us867";
String pass="jsf84d";
String db="db876";
String connString;
// load driver
Class.forName("org.gjt.mm.mysql.Driver");
// create connection string
connString = "jdbc:mysql://" + host + "/" + db + "?user=" + user +
"&password=" +
pass;
// pass database parameters to JDBC driver
Connection Conn = DriverManager.getConnection(connString);
// query statement
Statement SQLStatement = Conn.createStatement();
// generate query
String Query = "SELECT DISTINCT uid FROM abook";
// get result
ResultSet SQLResult = SQLStatement.executeQuery(Query);
// get and display each record
while(SQLResult.next())
{
String UId = SQLResult.getString("uid");
out.println("<option>" + UId);
}
// close connections
SQLResult.close();
SQLStatement.close();
Conn.close();
%>
</select>
</td>
</tr>
<tr>
<td>First name</td>
<td><input type="Text" name="fname" size="15"></td>
</tr>
<tr>
<td>Last name</td>
<td><input type="Text" name="lname" size="15"></td>
</tr>
<tr>
<td>Address</td>
<td><textarea name="address"></textarea></td>
</tr>
<tr>
<td>Tel</td>
<td><input type="Text" name="tel" size="10"></td>
</tr>
<tr>
<td>Fax</td>
<td><input type="Text" name="fax" size="10"></td>
</tr>
<tr>
<td>Email address</td>
<td><input type="Text" name="email" size="10"></td>
</tr>
<tr>
<td>Company</td>
<td><input type="Text" name="company" size="25"></td>
</tr>
<tr>
<td>Comment</td>
<td><input type="Text" name="comment" size="25"></td>
</tr>
<tr>
<td colspan=2><input type="submit" name="submit" value="Add"></td>
</tr>
</form>
</table>
</center>
</body>
</html>

If you examine it closely, you'll see that this form performs a query to retrieve the list of users currently available in the system, and uses this data to generate a list box containing the different user names. This makes it possible to specify the owner of each record when it is INSERTed.

Once the form has been filled up and submitted, control passes to "add_res.jsp", which takes care of actually performing the INSERT operation. Take a look.


<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>
<%@ page language="java" import="java.sql.*" %>
<%
// add_res.jsp
// form data
String uid = request.getParameter("uid");
String fname = request.getParameter("fname");
String lname = request.getParameter("lname");
String address = request.getParameter("address");
String tel = request.getParameter("tel");
String fax = request.getParameter("fax");
String email = request.getParameter("email");
String company = request.getParameter("company");
String comment = request.getParameter("comment");
// database parameters
String host="localhost";
String user="us867";
String pass="jsf84d";
String db="db876";
String conn;
Class.forName("org.gjt.mm.mysql.Driver");
// create connection string
conn = "jdbc:mysql://" + host + "/" + db + "?user=" + user + "&password=" +
pass;
// pass database parameters to JDBC driver
Connection Conn = DriverManager.getConnection(conn);
// query statement
Statement SQLStatement = Conn.createStatement();
// generate query
String Query = "INSERT INTO abook (id, uid, fname, lname, tel, fax, email,
addr, company, comment) VALUES (NULL, '" + uid + "', '" + fname + "', '" +
lname + "', '" + tel + "', '" + fax + "', '" + email + "', '" + address +
"', '" + company + "', '" + comment + "')";
// get result code
int SQLStatus = SQLStatement.executeUpdate(Query);
if(SQLStatus != 0)
{
out.println("Entry succesfully added.");
}
else
{
out.println("Error! Please try again.");
}
// close connection
SQLStatement.close();
Conn.close();
%>
</center>
</body>
</html>

This example demonstrates yet another method of the Statement object, the executeUpdate() method, used for INSERT or UPDATE operations. This method returns a result code indicating the number of rows affected by the operation - in case of the example above, this result code should be 1. In case it isn't...you've got trouble!

It should be noted at this point that if your INSERT statement contains special characters which need to be escaped (say, commas or single quotes), you'd do better to use the PreparedStatement class, which automatically takes care of escaping special characters and offers some performance benefits as well. A discussion of the PreparedStatement class is beyond the scope of this tutorial, but there's plenty of documentation out there should you ever require it.

Next: No Forwarding Address >>