A Closer look at fruitServlet.java

1. The java.sql package contains several database related classes. This package is used for database access, data retrieval, and data modification. The following imports in fruitServlet.java retrieve the required classes.

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

2. The DriverManager is used to get a reference to the JDBC driver. The connection object represents a connection to an ODBC source.
3. The Statement class stores database commands in SQL and the ResultSet object holds the set of the rows returned from the database in response to a command or query.
4. Note that the code is in doGet (not doPost). The fruitServlet class is invoked through a link, and not by form submission. Such HTTP requests use the 'get' method by default. POST is normally used when forms are submitted.

String dbName = "fruits";
String url = "jdbc:odbc:" + dbName;
ResultSet rs ;
Connection con = null;

5. We created a Data Source named 'fruits' during database setup. We set a variable 'dbName' to the name of the data source. JDBC uses a String of the form "jdbc:odbc:<Data_source_name> to get to our driver and data source. This string is different for different drivers. We also declare a new Connection and resultset.

/* Load the jdbc-odbc driver */
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/* Open a connection to the odbc data source entered by the user */
con =DriverManager.getConnection(url,"","");

6. The comments above the code are self explanatory; we load the bundled sun jdbc-odbc driver. We open a connection to our data source using the 'url' variable.

Statement sqlCommand = con.createStatement();
rs = sqlCommand.executeQuery("select * from fruitlist");

7. The next step involves creating a statement object for the connection. We execute the query "select * from fruitlist" on the database via this statement object. This query fetches all the rows in the fruitlist table. The results are stored in the ResultSet object rs.
8. Methods of the form rs.get<Object_type>("<Column_Name> ) are used to recover the value of a specific column from the database. For example, rs.getString("name") retrieves the name column while rs.getInt("calories") recovers the calories column.
9. A call to rs.next() moves an internal pointer to the next row in the returned result set. If the next row exists, the call to the method itself returns true. When there are no more rows in the database, rs.next() returns the boolean false. As long as there are rows to process, we iteratively process each row by executing the while loop over and over again.

while (rs.next())
{
out.println("<tr><td>" + java.lang.String.valueOf(rs.getInt("id"))
+ "</td>");
out.println("<td>" + rs.getString("name") + "</td>");
out.println("<td>" + rs.getString("color") + "</td>");
out.println("<td><img alt=\"fruit image\" src=\"images/"
+ rs.getString("image") + "\"></td>");
out.println("<td>" + java.lang.String.valueOf(rs.getInt("calories"))
+ "</td></tr>");
}

10. The knowledge gleaned through this exercise allows us to conceptualize the Java Application - JDBC - Database Interface in greater detail:

Figure 10b: JDBC/Application/Database Interface