Home arrow Java & J2EE arrow Page 4 - The JSP Files (part 5): No Forwarding Address

One Step At A Time - 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.

  1. The JSP Files (part 5): No Forwarding Address
  2. Dumped!
  3. The Scenic Route
  4. One Step At A Time
  5. New Friends
  6. No Forwarding Address
  7. Cleaning Up
By: Vikram Vaswani and Harish Kamath, (c) Melonfire
Rating: starstarstarstarstar / 3
March 19, 2001

print this article


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

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.{mospagebreak title=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:<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> </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 = ""; } %>

>>> More Java & J2EE Articles          >>> More By Vikram Vaswani and Harish Kamath, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- More Java Bugs Lead to More Attacks
- Oracle's Java One Brings News, Surprises
- Oracle Patches Java Runtime Environment
- Apple Syncs Java Update with Oracle
- Spring 3.1 Java Development Framework Compat...
- Jelastic Java PaaS Availability and Pricing ...
- NetBeans 7.1 Released, Supports JavaFX 2
- SolarWinds Releases Newest Version of Java M...
- Free Monitoring Tool for Java Apps on Heroku
- Heroku Adds JCloud Platform Support, Java 7 ...
- Java SE 8 Speculation in Full Swing
- Java SE 7 Now Available
- New JVM Language and Java Reporting Tool
- Java 7 Release Update and New Eclipse Toolkit
- The Best Java Netbeans IDE Plugins

Developer Shed Affiliates


Dev Shed Tutorial Topics: