Deprecated: Function ereg() is deprecated in /home3/vvaswani/db-mysql.php on line 174

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 60
The Melonfire Community - Trog
Logo         Community
  Trog


Copyright notice:

This article is copyright Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 119
2018. All rights reserved.

All source code, brand names, trademarks and other content contained herein is proprietary to Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 121
2018. All rights reserved.

Source code within this article is provided with NO WARRANTY WHATSOEVER. It is meant for illustrative purposes only, and is NOT recommended for use in production environments.

Copyright infringement is a violation of law.

Printed from http://www.melonfire.com/community/columns/trog/article.php?id=55



The JSP Files (part 5): No Forwarding Address
Build dynamic, data-driven Web pages with JSP.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Toolbox

JSP offers a number of advantages over other server-side scripting languages - as you've already seen, performance is just one of them. And this performance edge becomes particularly important when you combine it with another important benefit - the ability to seamlessly connect to a variety of database servers.

By offering seamless database connectivity (okay, it's not as transparent as the database connectivity available in PHP, but it's still pretty good!) in combination with faster response times, JSP allows developers to build complex, scalable, data-driven Web applications while simultaneously enjoying short development cycles.

OK, 'nuff said. Let's cut to the chase.

In this article, we're going to demonstrate how to use JSP to connect to a database, extract data from it, and use that data to build a dynamic Web page. We'll be building a simple Web application in order to help make the process clearer; this should also help you quantify how much easier (or harder) JSP is to use, as compared to other server-side scripting languages you may be familiar with.

If you're planning on trying out the examples below (recommended), you'll need to download and install the mySQL database server, available at http://www.mysql.com/. mySQL is a fast, reliable, open-source database management system, which offers a fair amount of power at a price that should move you to tears - it's free!

We'll be assuming that you've installed and configured mySQL, and have the appropriate permissions to create and edit database tables.

Since all database interaction in Java takes place using a technology known as JDBC, or Java Database Connectivity, you'll also need a JDBC module that allows you to connect to the mySQL database server. We'll be assuming that you've downloaded the mm.mySQL JDBC module from http://www.worldserver.com/mm.mysql/ , and configured it to talk to your mySQL database, as described in the article "Slapping Together A JSP Development Environment" at http://www.devshed.com/Server_Side/Jserv/JSPDev/

If you're using a database other than mySQL, fear not - JSP supports all major databases, and you can use the techniques described over the next few pages to talk to other databases too. You'll probably need to consult your database vendor's manual or Web site for information on how to obtain the necessary software.

Finally, some knowledge of SQL would come in handy. In case you don't know SQL, don't worry - it's extremely simple, and a few minutes with the "Speaking SQL" tutorial at http://www.devshed.com/Server_Side/MySQL/Speak/ will have you executing queries like an expert.

With all that out of the way, let's actually get our hands dirty.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Dumped!

If you're familiar with SQL, you know that there are four basic types of operations possible with a database:

SELECT a record;

INSERT a record;

UPDATE a record;

DELETE a record.

In order to demonstrate these operations, we're going to build a little application that requires each of the functions listed above - an address book which allows multiple users to store and view contact information online.

As always, one of the first things you have to think about when designing a data-driven application is the design of the database (duh!). For this application, we've decided to use a single table called "abook", which contains fields for different types of contact information - address, phone, fax, email address, and the like. Every user in the system has a unique login id, and each record in the database is "owned" by a specific user.

We've put together a "dump file", which lets you create the database tables and initial set of records quickly - we suggest that you import this data into your mySQL database server, as we'll be using it throughout this article.

To import the data, download the dump file and use this command at your mySQL prompt:


mysql> mysql -u username -p database < dumpfile


Or you could insert the contents manually - here is what you'll need:



#
# Table structure for table 'abook'
#

DROP TABLE IF EXISTS abook;
CREATE TABLE abook (
   id int(11) unsigned NOT NULL auto_increment,
   uid varchar(255) NOT NULL,
   fname varchar(255) NOT NULL,
   lname varchar(255) NOT NULL,
   tel varchar(255),
   fax varchar(255),
   email varchar(255),
   addr text,
   company varchar(255),
   comment text,
   PRIMARY KEY (id)
);

#
# Dumping data for table 'abook'
#

INSERT INTO abook (id, uid, fname, lname, tel, fax, email, addr, company, comment) VALUES ( '1', 'john', 'Bugs', 'Bunny', '7376222', '', 'bugs@somedomain.com', 'The Rabbit Hole, Dark Woods, Somewhere On Planet Earth', '', 'Big-ears in da house!');
INSERT INTO abook (id, uid, fname, lname, tel, fax, email, addr, company, comment) VALUES ( '2', 'john', 'Elmer', 'Fudd', '', '7628739', 'fuddman@somedomain.com', '', '', '');
INSERT INTO abook (id, uid, fname, lname, tel, fax, email, addr, company, comment) VALUES ( '3', 'joe', 'Peter', 'Parker', '162627 x34', '', 'webcrawler@somedomain.com', 'Your Friendly Neighbourhood Newspaper', '', 'My spidey-sense is tingling!');
INSERT INTO abook (id, uid, fname, lname, tel, fax, email, addr, company, comment) VALUES ( '4', 'bill', 'Clark', 'Kent', '1-800-SUPERMAN', '', 'superdude@somedomain.com', '', '', 'Is it a bird? Is it a plane?');


This will create a table named "abook" with columns for different types of contact information; these records are owned by three mythical users, "bill", "john" and "joe".

Now check whether or not the data has been successfully imported with a SELECT query (the SELECT SQL statement is used to retrieve information from a database). Enter this at your mySQL command prompt:


mysql> select uid, fname, lname from abook;


which, in English, means "display the columns uid, fname and lname from the address book". Here's what you should see:


+------+-------+--------+
| uid  | fname | lname  |
+------+-------+--------+
| john | Bugs  | Bunny  |
| john | Elmer | Fudd   |
| joe  | Peter | Parker |
| bill | Clark | Kent   |
+------+-------+--------+
4 rows in set (0.00 sec)



Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
The Scenic Route

All working? Good. Now, let's use JSP to do exactly the same thing - fire a SELECT query at the database, and display the results in an HTML page.


<html>
<head>
<basefont face="Arial">
</head>
<body>
<%@ page language="java" import="java.sql.*" %>

<%!
// define variables
String UId;
String FName;
String LName;

// define database parameters
String host="localhost";
String user="us867";
String pass="jsf84d";
String db="db876";
String conn;
%>

<table border="2" cellspacing="2" cellpadding="5">

<tr>
<td><b>Owner</b></td>
<td><b>First name</b></td>
<td><b>Last name</b></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 uid, fname, lname FROM abook";

// get result
ResultSet SQLResult = SQLStatement.executeQuery(Query);

   while(SQLResult.next())
   {
      UId = SQLResult.getString("uid");
      FName = SQLResult.getString("fname");
      LName = SQLResult.getString("lname");
     
      out.println("<tr><td>" + UId + "</td><td>" + FName + "</td><td>" + LName
+ "</td></tr>");
   }  

// close connection
SQLResult.close();
SQLStatement.close();
Conn.close();

%>

</table>
</body>
</html>


And you'll see something like this:


Owner    First name    Last name
john    Bugs       Bunny
john    Elmer       Fudd
joe    Peter       Parker
bill    Clark       Kent



Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
One Step At A Time

Using JSP to extract data from a database involves several steps. Let's dissect each one.

1. First, we need to make sure that all the modules required for a JDBC connection are available to the JSP document. This is accomplished by means of the


<%@ page
...
%>


directive, used to define attributes that affect the JSP document.


<%@ page language="java" import="java.sql.*" %>


The "import" attribute is used to import all the packages and classes required for the script to execute - here, all the packages in the "java.sql.*" tree.

2. Next, it's necessary to declare all the variables required for this scriptlet; we've kept aside some for the results of the SQL query, and also created variables to hold database-specific information, such as the name of the database server, the username and password required to gain access, and the database to use for all queries. This information is used to build a connection string, at a later stage.

3. The next step is to load the JDBC driver required to access a mySQL database - this is accomplished with the statement


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


The name of the driver to be used for a specific database can always be obtained from the documentation you receive with the driver.

4. Now that the drivers have been loaded, it's time to open a connection to the database server. This is accomplished by means of the Connection object and its getConnection() method.

The getConnection() method requires a connection string as argument; this connection string is created by combining the server name, the username and password, and the name of the database to use into a single URL-like string.


// create connection string
conn = "jdbc:mysql://"; + host + "/" + db + "?user=" + user + "&password=" + pass;

// pass database parameters to JDBC driver
Connection Conn = DriverManager.getConnection(conn);


The getConnect() method then returns a connection identifier, which is used for subsequent SQL queries. All communication between JSP and the database server takes place through this connection. In this case, the specific instance of the Connection object is called "Conn".

5. Once a connection to the database is available, the Statement object is used to prepare a SQL statement for execution.


// query statement
Statement SQLStatement = Conn.createStatement();


6. At this point, a query is created


// generate query
String Query = "SELECT uid, fname, lname FROM abook";


and the ResultSet object is used to store the results of the query.


// get result
ResultSet SQLResult = SQLStatement.executeQuery(Query);


7. Once the query has been executed and the results returned, a number of methods can be used to iterate through the result set. The example above uses the next() method, which simply moves forward through the list of records returned by the query. A "while" loop is used to iterate through the result set in combination with the next() method.



// get and display each record
   while(SQLResult.next())
   {
      UId = SQLResult.getString("uid");
      FName = SQLResult.getString("fname");
      LName = SQLResult.getString("lname");
     
      out.println("<tr><td>" + UId + "</td><td>" + FName + "</td><td>" + LName
+ "</td></tr>");
   }  


Incidentally, the ResultSet object also comes with a handy prev() method, which allows you to display the preceding record.

The getString() method is used to access specific columns in the record currently being examined; these values are stored as strings in the JSP document. In addition to the getString() method, you can also use the getInt(), getTimeStamp() and getBoolean() methods to obtain column values as specific variable types.

8. Finally, each result set returned after a query occupies some amount of memory - and if your system is likely to experience heavy load, it's a good idea to use the various close() methods to free up memory.


// close connection
SQLResult.close();
SQLStatement.close();
Conn.close();


As you can see, connecting to a database through JSP is a little more complicated than the equivalent procedure in PHP. There's not much you can do about this but grin and bear it.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
What's Your Name?

Now that you know how to connect to a database, let's begin developing the bare bones of the address book application. This first script asks for a user name and then connects to the database to display entries owned by that user.

We'll be using a single page for the entire operation - the "submit" variable (you remember this technique, don't you?) is used to decide whether to display the initial form or the result page. Take a look:


<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:&nbsp;<input type="text" name="name" size="10">
&nbsp;
<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>
</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");
           
      out.println("<tr><td>" + FName + "</td><td>" + LName + "</td><td>" + Tel
+ "</td><td>" + Fax + "</td><td>" + Email + "</td></tr>");
   }
// close connections
SQLResult.close();
SQLStatement.close();
Conn.close();

}
%>

</table>
</center>
</body>
</html>


As you can see, by checking the value of the "submit" variable, we've successfully combined both the initial page and the results page into a single JSP script. This script simply accepts a user name, connects to the database, and displays records for that user (assuming any exist). Log in as "bill", "joe" or "john" to view the records available for that user.

If you don't like the word "null" being displayed in columns which have no data, you can add a few "if" loops to replace it with an empty space.


<%
if (Fax.equals("null"))
{
Fax = "&nbsp;";
}
%>



Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
New Friends

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.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
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:&nbsp;<input type="text" name="name" size="10">
&nbsp;
<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>
<!-- one cell added HERE -->
<td>&nbsp;</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.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Cleaning Up

Finally, it's time to do a little routine maintenance. This example demonstrates how to use the DELETE statement to delete a particular entry. Again, the basic principles remain the same, with only the query string changing.

First, the initial list page has to be altered to include a link to delete a specific entry - this is similar to the manner in which the "edit this entry" link was added. Assuming that's taken care of, the script "delete.jsp" should be called with the number of the record to be deleted. So, just as you have the link


"<a href=edit.jsp?id=" + ID + ">edit this entry</a>"


you will now have the additional link


"<a href=delete.jsp?id=" + ID + ">delete this entry</a>"


Let's take a look at "delete.jsp".



<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>

<%@ page language="java" import="java.sql.*" %>

<%
// delete.jsp

// form data
String fid = request.getParameter("id");
int id = Integer.parseInt(fid);

// 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 = "DELETE FROM abook WHERE id=" + id;

// get result code
int SQLStatus = SQLStatement.executeUpdate(Query);

   if(SQLStatus != 0)
   {
   out.println("Entry successfully deleted.");  
   }
   else
   {
   out.println("Error! Please try again.");  
   }

// close connection
SQLStatement.close();
Conn.close();

%>

</center>
</body>
</html>


And that's about all we have for this issue of The JSP Files. Next time, we'll be taking a look at the HTTP session management capabilities available in JSP - so make sure you come back for that one!

Note: All examples in this article have been tested on Linux/i586 with Tomcat 3.2 and JServ 1.1. Examples are illustrative only, and are not meant for a production environment. YMMV!


Copyright notice:

This article is copyright Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 193
2018. All rights reserved.

All source code, brand names, trademarks and other content contained herein and proprietary to Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 195
2018. All rights reserved.

Source code within this article is provided with NO WARRANTY WHATSOEVER. It is meant for illustrative purposes only, and is NOT recommended for use in production environments.

Copyright infringement is a violation of law.

Printed from http://www.melonfire.com/community/columns/trog/article.php?id=55



Copyright © 1998-
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 211
2018 Melonfire. All rights reserved
Terms and Conditions | Feedback