Home

JSP and Beyond

a pragmatic primer on building web-based solutions with Java technologies


Retrieving Data from a Database

The most common action that any database driven application or web site will perform is data retrieval. For the sample below we will simply show how the data is returned by using Servlet code. We will also use the connection object, cnDB , that was established in the above example, “Connecting to a Database with JDBC”.

// Prepare a statement object that will be used to request the data
Statement stmt = cnDB.createStatement();

// Create an object to hold the results set
ResultSet results;

// Populate the results object with the data from the SQL statement above
results = stmt.executeQuery(”SELECT * FROM tblCustomer ORDER BY CustomerName”);

// Use a string to hold the HTML results from the query
String strResults = “<table cellpadding=\”3\” width=\”100%\”>”;

// Move through the result set object
while(results.next())
{
results.getRow();
strResults += “<tr><td>” + results.getString(”CompanyName”) +
“</td><td>10/10/06</td><td><a href=\”CustomerEdit.jsp?CustomerID=” + results.getString(”CustomerID”) +
“\”>edit account</a></td><td><a href=\”NoteAdd.jsp?CustomerID=” + results.getString(”CustomerID”) +
“\”>add note</a></td></tr>”;
}

strResults += “</table>”;

// Close the result set
results.close();

// Close the result set
stmt.close();

// Close the connection
cnDB.close();

// Print strResults somewhere

Statement stmt = cnDB.createStatement();
Assuming that you are already using the cnDB database connection object from the previous example, you will be able to create a statement object that will provide a way to update or query the database. The createStatement object will mainly be used by way of the executeQuery and executeUpdate methods. In this example we will use the executeQuery method to give us a result set.

ResultSet results;
This instantiates a ResultSet object call that holds the response from the executeQuery statement with the records returned.
results = stmt.executeQuery(”SELECT * FROM tblCustomer ORDER BY CustomerName”);
This line combines a few things to populate the results object with the relevant rows from the database. The executeQuery method of our statement object lets us pass an SQL query like “SELECT * FROM tblCustomer ORDER BY CustomerName” that will return a ResultSet that will be stored in the results object.

while(results.next()) {
This line uses a method of the ResultSet object called next to iterate through the series of results. In this example we have created a string called strResults that we will add to in the ? does this make sense? loop. During the loop we access the results object to get different parts of the database row. For instance results.getString(“CompanyName”) will return the company name from the database for the current row in the loop. After the loop, we add an additional string to the strResults variable to complete an HTML table containing the database information.

results.close();
This line releases the connection object’s database and JDBC resources immediately instead of waiting for them to be automatically released. Closing ResultSet explicitly gives a chance for garbage collector to recollect memory as early as possible, because ResultSet objects may occupy lots of memory depending on the query.

stmt.close();
Close statement object as soon as you finish working with that. It explicitly gives a chance for garbage collector to recollect memory as early as possible, which in turn affects performance.

cnDB.close();
Close statement object as soon as you finish working with that. It explicitly gives a chance for garbage collector to recollect memory as early as possible, which in turn affects performance.