Article
The JSP Files - Parts 1 to 8: Tagged and Bagged
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 Next
No Forwarding Address
Next up, updating records. In order to demonstrate this, the first order of business is to modify the next-to-last example so that each entry displayed has an edit option next to it. Here's the modified code:
<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>
<%
// check submit state
String submit = request.getParameter("submit");
// form not yet submitted
// display initial page
if(submit == null)
{
%>
<form action="view.jsp" method="GET">
Enter your name: <input type="text" name="name"
size="10"> <input type="submit" name="submit"
value="Go"> </form>
<%
}
// form submitted, display result
else
{
%>
<%@ page language="java" import="java.sql.*" %>
<%
// get username
String uid = request.getParameter("name");
// define database parameters
String host="localhost";
String user="us867";
String pass="jsf84d";
String db="db876";
String conn;
%>
<h2><% out.println(uid); %>'s Little Black Book</h2>
<hr>
<table border=1 cellspacing=4 cellpadding=4>
<tr>
<td><b>First name</b></td>
<td><b>Last name</b></td>
<td><b>Tel</b></td>
<td><b>Fax</b></td>
<td><b>Email address</b></td>
<td> </td>
</tr>
<%
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 = "SELECT * FROM abook WHERE uid = '" + uid + "'";
// get result
ResultSet SQLResult = SQLStatement.executeQuery(Query);
// display records
// if available
while(SQLResult.next())
{
String FName = SQLResult.getString("fname");
String LName = SQLResult.getString("lname");
String Tel = SQLResult.getString("tel");
String Fax = SQLResult.getString("fax");
String Email = SQLResult.getString("email");
// get the record number HERE
String ID = SQLResult.getString("id");
// add an edit link to each record with the ID
out.println("<tr><td>" + FName + "</td><td>"
+ LName + "</td><td>" + Tel + "</td><td>" + Fax + "</td><td>"
+ Email + "</td><td><a + href=edit.jsp?id=" ID + ">edit this
entry</a></td></tr>");
}
// close connections
SQLResult.close();
SQLStatement.close();
Conn.close();
}
%>
</table>
</center>
</body>
</html>
Clicking this link will activate the script "edit.jsp" and pass the record number to it via the URL GET method.
Let's now take a look at "edit.jsp"
<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>
<h2>Update Address Book Entry</h2>
<%@ page language="java" import="java.sql.*" %>
<%
// form variables
String fid = request.getParameter("id");
int id = Integer.parseInt(fid);
String fname = "";
String lname = "";
String tel = "";
String fax = "";
String email = "";
String address = "";
String company = "";
String 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 = "SELECT * FROM abook where id=" + id;
// get result
ResultSet SQLResult = SQLStatement.executeQuery(Query);
// get and display record
fname = SQLResult.getString("fname");
lname = SQLResult.getString("lname");
tel = SQLResult.getString("tel");
fax = SQLResult.getString("fax");
email = SQLResult.getString("email");
address = SQLResult.getString("addr");
company = SQLResult.getString("company");
comment = SQLResult.getString("comment");
// close connection
SQLResult.close();
SQLStatement.close();
Conn.close();
%>
<table border=0 cellspacing=5 cellpadding=5>
<form action="edit_res.jsp" method="POST">
<input type="hidden" name="id" value="<%= id %>">
<tr>
<td>First name</td>
<td><input type="Text" name="fname" size="15" value="<%=
fname %>"></td> </tr>
<tr>
<td>Last name</td>
<td><input type="Text" name="lname" size="15" value="<%=
lname %>"></td> </tr>
<tr>
<td>Address</td>
<td><textarea name="address"><%= address %></textarea></td> </tr>
<tr>
<td>Tel</td>
<td><input type="Text" name="tel" size="10" value="<%=
tel %>"></td> </tr>
<tr>
<td>Fax</td>
<td><input type="Text" name="fax" size="10" value="<%=
fax %>"></td> </tr>
<tr>
<td>Email address</td>
<td><input type="Text" name="email" size="10" value="<%=
email %>"></td> </tr>
<tr>
<td>Company</td>
<td><input type="Text" name="company" size="25" value="<%=
company %>"></td> </tr>
<tr>
<td>Comment</td>
<td><input type="Text" name="comment" size="25" value="<%=
comment %>"></td> </tr>
<tr>
<td colspan=2><input type="submit" name="submit"
value="Update"></td> </tr>
</form>
</table>
</center>
</body>
</html>
As you can see, once "edit.jsp" receives the record number, it connects to the database, extracts the record, and then generates a simple form with the values already filled in (note our usage of the shortcut <%= %> construct to display variable values). The user is then free to modify the information displayed in the form; once done, the form is submitted to "edit_res.jsp", which takes care of the UPDATE operation.
<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>
<%@ page language="java" import="java.sql.*" %>
<%
// edit_res.jsp
// form data
String fid = request.getParameter("id");
int id = Integer.parseInt(fid);
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="root";
String pass="";
String db="test";
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 = "UPDATE abook SET fname='" + fname + "', lname='"
+ lname + "', tel='" + tel + "', fax='" + fax + "', email='"
+ email + "', addr='" + address + "', company='" + company
+ "', comment='" + comment + "' WHERE id=" + id;
// get result code
int SQLStatus = SQLStatement.executeUpdate(Query);
if(SQLStatus != 0)
{
out.println("Entry successfully updated.");
}
else
{
out.println("Error! Please try again.");
}
// close connection
SQLStatement.close();
Conn.close();
%>
</center>
</body>
</html>
Of course, we could have combined all the scripts above into one single JSP document - but we'll leave that exercise to you for the moment.
Copyright Melonfire, 2000. All rights reserved.