Header javaperspective.com
JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.2. Selecting rows from a table

2.2. Selecting rows from a table
Last updated: 28 January 2013.

This tutorial explains how to select rows from a table with JDBC. All the classes and interfaces used in the code samples of this page belong to the package java.sql.

Let's say you have the following table named RIVERS in your database:


ID RIVER LENGTH CONTINENT
1 Nile 4180 Africa
2 Amazon 3920 South America
3 Yangtze 3964 Asia
4 ... ... ...


The column ID is the primary key of the table. The columns RIVER and CONTINENT contain character strings and the column LENGTH contains integers. I am going to use the JDBC API in a method named accessDatabase to select the rivers in Africa whose length is greater than 3000 miles. Here is the code:

public void accessDatabase() throws SQLException {

   
Connection connection = null;
    PreparedStatement statement =
null;

   
try{
         
// Create a database connection
         
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "yourName", "yourPassword");

         
// Create a PreparedStatement
         
statement = connection.prepareStatement("SELECT * FROM RIVERS WHERE CONTINENT = ? AND LENGTH > ?");

         
// Set the statement's parameters
         
statement.setString(1, "Africa");
          statement.setInt
(2, 3000);

         
// Execute the SQL query and get the ResultSet
         
ResultSet resultSet = statement.executeQuery();

         
// Use the ResultSet to walk through the selected rows and retrieve the desired column values for each row
         
while(resultSet.next()){
               
String river = resultSet.getString("RIVER");
               
int length = resultSet.getInt("LENGTH");

               
// do something with river and length
                //...
         
}
    }
   
finally{
         
if(statement != null)
               
statement.close();
         
if(connection != null)
               
connection.close();
   
}

}

You might have noticed that Connection, PreparedStatement and ResultSet are interfaces. In fact, they are implemented by the driver in use.

If you have read the previous tutorial, you already know that the method DriverManager.getConnection establishes a connection with the database and returns a Connection that you can use to issue SQL statements. The first parameter of the method DriverManager.getConnection is a database-specific string known as the database URL. In the example above, I am establishing a connection with a locally installed Oracle database. The second and third parameters are the user's name and password respectively.

The method Connection.prepareStatement issues a parameterized SQL statement to the database and returns a PreparedStatement which allows you to execute the SQL statement multiple times with different parameters. If your SQL statement does not contain any parameters and if it is executed only once, you can use a Statement instead. However, PreparedStatement will meet your needs in all cases because it can be used once or multiple times, and with or without parameters.

In the SQL statement, the question marks (?) are placeholders for parameters, and each of which has an index within the SQL statement. The index of a parameter is its order of appearance in the SQL statement. You must provide a value for each parameter before executing the SQL statement. In the above example, the first parameter (at index 1) is set by the method setString(int parameterIndex, String x). Likewise, the second parameter (at index 2) is set by the method setInt(int parameterIndex, int x). In fact, the setter method that is called depends on the parameter's SQL data type. There is a database-specific mapping between SQL and Java data types (see the documentation of your database). The interface PreparedStatement provides setter methods for every data type.

The method PreparedStatement.executeQuery executes the SQL statement and returns a ResultSet that you can use to walk through the selected rows by calling the method ResultSet.next repeatedly. At each iteration, the cursor moves to the next row. For each row, you can retrieve the desired column values by calling the appropriate getter method, depending on the column's data type. A column's value can be retrieved by the column's name or alias as I did above or by the column's index as shown below:

while(resultSet.next()){
   
String river = resultSet.getString(2);
   
int length = resultSet.getInt(3);

   
// do something with river and length
    //...
}

Note that using column indexes instead of column names or aliases increases performance.

As you can see, the method ResultSet.next is called in a while loop because there may be more than one row in the ResultSet. If you expect a single row in the ResultSet, you can replace the while statement by an if statement. For example, the next sample shows a modified version of the method accessDatabase in which I am now counting the number of rivers in Africa whose length is greater than 3000 miles:

public void accessDatabase() throws SQLException {

   
Connection connection = null;
    PreparedStatement statement =
null;

   
try{
         
// Create a database connection
         
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "yourName", "yourPassword");

         
// Create a PreparedStatement
         
statement = connection.prepareStatement("SELECT COUNT(*) AS C FROM RIVERS WHERE CONTINENT = ? AND LENGTH > ?");

         
// Set the statement's parameters
         
statement.setString(1, "Africa");
          statement.setInt
(2, 3000);

         
// Execute the SQL query and get the ResultSet
         
ResultSet resultSet = statement.executeQuery();

         
// Use the ResultSet to get the number of rivers in Africa whose length is greater than 3000 miles
         
if(resultSet.next()){
               
int count = resultSet.getInt("C");

               
// do something with count
                //...
         
}
    }
   
finally{
         
if(statement != null)
               
statement.close();
         
if(connection != null)
               
connection.close();
   
}
}

Now, the method ResultSet.next is called only once in the if statement since a single row is expected in the ResultSet. If you don't call the method ResultSet.next, you cannot retrieve any value because initially, the cursor is positioned before the first row of the ResultSet. In the above sample, the one and only call to the method ResultSet.next moves the cursor to the unique row of the ResultSet.


You are here :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.2. Selecting rows from a table
Next tutorial :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.3. Inserting rows in a table

Copyright © 2013. JavaPerspective.com. All rights reserved.  ( Terms | Contact | About ) 
Java is a trademark of Oracle Corporation
Image 1 Image 2 Image 3 Image 4 Image 5 Image 6 Image 7