Accessing Databases from Servlets and JSP Pages Blog

Version 2

    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 s ervlets 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 andjavax.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: theDriverManager class and the Connection, the Statement, and the ResultSetinterfaces.

    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 To load the driver, you use the following code:


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


    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:


    The DriverManager class's most important method isgetConnection, which returns ajava.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:


    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:


    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:


    If the driver you load recognizes the URL in thegetConnection 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 theConnection interface. An instance of a class that implements Connection is returned by a call to thegetConnection() method of theDriverManager class. Here is the code to obtain aConnection 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 Connectioninterface is createStatement(), which returns aStatement 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. AStatement object is returned by thecreateStatement() method of theConnection 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 areexecuteQuery() and executeUpdate(). TheexecuteQuery() 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() andexecuteQuery() 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 singleResultSet object, which is discussed next. TheResultSet 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 aResultSet 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 returnstrue if the next row is valid, and falseif there are no more records in the ResultSetobject.

    Other important methods are getXXX() methods, whereXXX 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:


    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 typeString.


    The following example shows how you can use thenext() 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 returnedResultSet 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 ( {   System.out.println(resultSet.getString("Address") + "|" + resultSet.getString(2) ); }

    In the previous code, statement is aStatement object returned from aConnection 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 theConnection object to create a Statementobject. You then either updated the data or generated aResultSet 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 theResultSet.

    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("");      // 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 ( {     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.


    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.