Skip to main content

Accessing Databases from Servlets and JSP Pages

June 20, 2003




Accessing data in a database or in other data sources is an important task in web programming. This article shows you how to do the most common database manipulations from servlets and JSP pages. As with other types of Java applications, data access from JSPs and servlets is done through Java Database Connectivity (JDBC). We will begin with an introduction to JDBC followed by some examples of servlets that allows you to access the data in the database. This article assumes that you have some basic knowledge of servlets and JSP and that you are familiar with Structured Query Language (SQL).

Introduction to JDBC

JDBC is an API that allows you to store, retrieve, and manipulate data in virtually any data source, and to manipulate the data structures. Although the JDBC 4.0 Specification proposal has been drafted, we will cover the current version, JDBC 3.0, which is included in J2SE 1.4. This section explains the JDBC object model and the most important classes and interfaces, to enable you to develop database-based servlet and JSP applications. Here are the steps required to access data in a database:

  1. Load the JDBC database driver.
  2. Create a connection.
  3. Create a statement.
  4. Create a resultset, if you expect the database server to send back some data.

There are two packages in JDBC 3.0: java.sql and javax.sql. The java.sql package is often referred to as the JDBC core API and is sufficient to do basic data manipulations. The javax.sql package is the JDBC Optional Package API. It provides additional features, including connection pooling, which I'll discuss in another article. The following subsections will discuss the four most important members of the java.sql package: the DriverManager class and the Connection, the Statement, and the ResultSet interfaces.

The DriverManager Class

The DriverManager class is used to obtain a connection to a database. Database servers use their own proprietary protocols for communication, which are different from each other. However, you don't need to worry about these protocols because you can use "translators." These "translators" come in the form of JDBC drivers. Therefore, you only need to write your Java code once to access any database. JDBC drivers are available for most popular databases today. Oracle, Microsoft SQL Server, Sybase, DB2, MySQL, and so on. As long as you can find the JDBC driver for a database server, you can access the database from your Java code. The list of JDBC drivers can be found here.

JDBC drivers normally come in a .jar file. The first thing you need to do once you have the driver is copy it into the WEB-INF\lib directory under your application directory.

Then, from your servlet/JSP page, you use the DriverManager class to load JDBC drivers. The JDBC driver must be located in the WEB-INF/lib directory under the application directory. To load the JDBC driver, you use this code:

try {
  Class.forName("JDBC.driver");
}
catch (ClassNotFoundException e) {
  // driver not found
}

You will need to replace JDBC.driver with the fully qualified name of the JDBC driver class. This name can be found in the documentation accompanying the JDBC driver.

For example, for a MySQL database, the most popular driver is the one written by Mark Matthews of Purdue University and downloadable from www.worldserver.com/mm.mysql/. To load the driver, you use the following code:

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

For an ODBC database, use the following code to load the driver:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Or, if you are using the FreeTds Type 4 JDBC driver to connect to a Microsoft SQL Server, you use this code to load the driver:

Class.forName("com.internetcds.jdbc.tds.Driver");

The DriverManager class's most important method is getConnection, which returns a java.sql.Connection object. This method has three overloads whose signatures are as follows:

public static Connection getConnection(String url)
public static Connection getConnection(String url, Properties info)
public static Connection getConnection(String url, String user, String password)

The url is of this syntax:

jdbc:subprotocol:subname

The subprotocol and subname parts depend on the database server you use. The documentation of the JDBC driver should tell you the subprotocol and the subname to use.

For instance, if you are using a JDBC-ODBC bridge driver, the subprotocol is "odbc" and the subname is the Data Source Name (DSN) for that database. For instance, for a DSN called CompanySecret, your URL will be:

jdbc:odbc:CompanySecret

With a MySQL database, the subprotocol part is "mysql" and the subname part should be given the name of the machine and the database. For example, for a database named AdminStuff in a machine called xeon, use the following:

jdbc:mysql///xeon/AdminStuff

If the driver you load recognizes the URL in the getConnection method, it will attempt to establish a connection with the database server.

The Connection Interface

To access a database, first you need to establish a connection to the database server. In JDBC, a connection is represented by the Connection interface. An instance of a class that implements Connection is returned by a call to the getConnection() method of the DriverManager class. Here is the code to obtain a Connection object to the AdminStuff database in a MySQL server called xeon:

Connection connection =    

  DriverManager.getConnection("jdbc:mysql///xeon/AdminStuff", "budi", "secret");

In the code above, "budi" is the user name and "secret" is the password for user budi.

The most frequently used method of the Connection interface is createStatement(), which returns a Statement object for sending SQL statements to the database. This method has two overloads with the following signatures:

public Statement createStatement() throws SQLException

public Statement createStatement(int resultSetType,

                                 int resultSetConcurrency) throws SQLException

Once you're finished with the connection, you should close it to save resources by calling the close method.

The Statement Interface

You use the Statement interface method to execute an SQL statement and obtain the produced results. A Statement object is returned by the createStatement() method of the Connection interface. Here is an example of how to create a Statement object.

// connection is an open Connection object

Statement statement = connection.createStatement();

The two most important methods of this interface are executeQuery() and executeUpdate(). The executeQuery() method executes an SQL statement that returns a single ResultSet object. The executeUpdate() method executes an insert, update, and delete SQL statement. The method returns the number of records affected by the SQL statement execution.

Both executeUpdate() and executeQuery() methods accept a String containing an SQL statement. The SQL statement does not end with a DBMS statement terminator, which can vary from DBMS to DBMS. For example, Oracle uses a semicolon (;) to indicate the end of a statement, and Sybase uses the word "go." The driver you are using will automatically supply the appropriate statement terminator, and you will not need to include it in your JDBC code.

The executeUpdate() method executes an SQL INSERT, UPDATE, or DELETE statement and also data definition language (DDL) statements to create, drop, and alter tables. This method returns the row count for INSERT, UPDATE, or DELETE statements or returns 0 for SQL statements that return nothing.

The executeQuery() method executes an SQL SELECT statement that returns data. This method returns a single ResultSet object, which is discussed next. The ResultSet object contains the data produced by the given query. This method never returns a null. For example, to create a table named Users with two fields, you can use the following code:

String sql = "CREATE TABLE Users " +

             "(FirstName VARCHAR(32), LastName VARCHAR(32)";

statement.executeUpdate(sql);

Then, to add a record in the Users table, you use the following code:

String sql = "INSERT INTO Users " + 

             "VALUES ('Frank', 'Crayman')";

statement.executeUpdate(sql);

You use the executeQuery() method when you expect a ResultSet object, as described in the next section.

The ResultSet Interface

The ResultSet interface represents a table-like database result set. A ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. Therefore, to access the first row in the ResultSet, you use the next() method. This method moves the cursor to the next record and returns true if the next row is valid, and false if there are no more records in the ResultSet object.

Other important methods are getXXX() methods, where XXX is the data type returned by the method at the specified index, including String, long, and int. The indexing used is 1-based. For example, to obtain the second column of type String, you use the following code:

resultSet.getString(2);

You can also use the getXXX() methods that accept a column name instead of a column index. For instance, the following code retrieves the value of the column LastName of type String.

resultSet.getString("LastName");

The following example shows how you can use the next() method as well as the getString() method. Here you retrieve the Address and PhoneNumber columns from a table called Addresses. You then iterate through the returned ResultSet and print all the address and phone number in the format "address|phone number" to the console.

String sql = "SELECT Address, PhoneNumber FROM Addresses";

ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()) {

  System.out.println(resultSet.getString("Address") + "|" + resultSet.getString(2) );

}

In the previous code, statement is a Statement object returned from a Connection object. The first column is retrieved by passing its column name. The second column is obtained by passing its column index.

Accessing Data from a Servlet or JSP Page

To summarize from the section above, You loaded the JDBC and returned a Connection object. You used the Connection object to create a Statement object. You then either updated the data or generated a ResultSet object.

Now, let's see how you can use JDBC to manipulate data in a database from a JSP page.

This example uses the mm MySQL JDBC driver to access the Users table in a MySQL Server database named CompanySecret. The database server is called xeon and you need to pass the user name "budi" and password "secret" to log into the database server. The SQL statement queries two columns: FirstName and LastName. Upon retrieving the ResultSet, the statement will loop through it to print all the first names and last names in the ResultSet.

In this example, I mix Java code with HTML to make the example simpler. In real-world applications, you should always separate your Java code into a JavaBean or a custom tag library.

Here is the JSP page.

<html>
<head>
<title>Accessing data in a database</title>
</head>
<body>
<%

try {
  // Step 1. Load the JDBC driver
  Class.forName("org.gjt.mm.mysql.Driver");
  
  // Step 2. Create a Connection object
  Connection con = DriverManager.getConnection(
    "jdbc:mysql///xeon/CompanySecret",
    "budi", "secret");

  System.out.println("got connection");

  
  // Step 3. Create a Statement object and call its executeUpdate
  // method to insert a record
  Statement s = con.createStatement();
  String sql =
    "INSERT INTO Users VALUES ('Michael', 'Franks', '12/12/2003', 'm')";
  s.executeUpdate(sql);

  // Step 4. Use the same Statement object to obtain a ResultSet object
  sql = "SELECT FirstName, LastName FROM Users";
  ResultSet rs = s.executeQuery(sql);
  while (rs.next()) {
    out.println(rs.getString(1) + " " + rs.getString(2) + "<br>");
  }
  rs.close();
  s.close();
  con.close();
}
catch (ClassNotFoundException e1) {
  // JDBC driver class not found, print error message to the console
  System.out.println(e1.toString());
}
catch (SQLException e2) {
  // Exception when executing java.sql related commands, print error message to the console
  System.out.println(e2.toString());
}
catch (Exception e3) {
  // other unexpected exception, print error message to the console
  System.out.println(e3.toString());
}
%>
</body>
</html>

Now, if you open your browser and type in the URL to the JSP page, you can see the data from the database displayed on it, as shown in Figure 1. What appears on your browser might be different than Figure 1, depending on what has been inserted into your table.

Screen shot.
Figure 1. Displaying data from the database.

Summary

This article introduced JDBC and showed how you can manipulate data in a relational database from your servlet or JSP page. For this purpose, you need to use the four members of the java.sql package: DriverManager, Connection, Statement, and ResultSet. Bear in mind, however, that this is only an introduction. To create a scalable Web application, you need to dig more into JDBC to use advanced features such as prepared statements and connection pooling.

Budi Kurniawan is a senior J2EE architect.
Related Topics >> Databases   |   JSP   |