SQL in a Nutshell, 2nd Edition [Electronic resources] نسخه متنی

5.6 Retrieving Data

A typical database
program retrieves data from a database server and processes it. These
programs will execute SELECT statements
following a process similar to that described in the previous
section. The difference between executing a
SELECT statement as opposed to a statement that
does not return results is that with SELECT you
must execute additional code to process the results returned by
SELECT.

5.6.1 Retrieving Data Using ADO.NET

The following C# code fragment executes a SQL
SELECT statement that returns author names from
the authors table and then iterates
through the results one row at a time, printing out each
author's name. The code is explained in the detailed
steps that follow:

{Odbc|OleDb|Sql}Command statement = connection.CreateCommand( );
statement.CommandText = "SELECT au_fname, au_lname FROM authors";
{Odbc|OleDb|Sql}DataReader resultSet = statement.ExecuteReader( );
while( resultSet.Read( ) )
{
String fname = "NULL";
String lname = "NULL";
if( !resultSet.IsDBNull( 0 ) ) fname = resultSet.GetString( 0 );
if( !resultSet.IsDBNull( 1 ) ) lname = resultSet.GetString( 1 );
System.Console.WriteLine( lname + ", " + fname );
}
resultSet.Close( );
statement.Close( );

To execute a SQL query and process the
results using ADO.NET, take the following steps:

Create the Command
object that will be used to execute the SELECT
statement and attach a SELECT statement to it:

{Odbc|OleDb|Sql}Command statement = connection.CreateCommand( );
statement.CommandText = "SELECT au_fname, au_lname FROM AUTHORS";

Invoke the ExecuteReader method on the
Command object, creating a new
DataReader object.

{Odbc|OleDb|Sql}DataReader resultSet = statement.ExecuteReader( );

Iterate through each row in the result set. After execution of the
SELECT statement, retrieve rows using the
DataReader's
Read method. If you expect a multiple row result,
you should invoke the Read method from within a
while loop:

while( resultSet.Read( ) )
{

Having fetched a row of data into the DataReader
object, the column data can be extracted using the
DataReader's
Get methods. Before calling the
Get methods, we check to see if the data is NULL
using the DataReader's
IsDBNull method. If the value returned by the
IsDBNull method is true, then the value of the
string will be NULL. There are many Get methods
for each type of column. Using the appropriate datatype for the
column is important, because a conversion is not always possible. For
a list of available Get methods and the ANSI SQL
types they should be used with, please see Table 5-4.

   String fname = "NULL";
String lname = "NULL";
if( !resultSet.IsDBNull( 0 ) ) fname = resultSet.GetString( 0 );
if( !resultSet.IsDBNull( 1 ) ) lname = resultSet.GetString( 1 );

When creating an ADO.NET program that fetches data that can contain
NULL values, it is always safest to check the return value of the
DataReader object's
IsDBNull method prior to extracting a value.

The Read method of DataReader
returns false when all the data has been read from the database
server. At that point, the Close methods on the
Command object and DataReader
should be invoked to free up the resources used internally to process
the statement.

resultSet.Close( );
statement.Close( );

Beyond the Get methods, there are three other
methods on the DataReader type that are frequently
used when processing data from a
query. Those three methods are listed in
Table 5-5.

5.6.2 Retrieving Data Using JDBC

The following
Java code fragment executes a SQL SELECT
statement that returns author names from the authors table and then iterates through the
results one row at a time, printing out each
author's name:

java.sql.Statement statement = connection.createStatement( );
java.sql.ResultSet result = 
statement.executeQuery("SELECT au_fname, au_lname FROM authors" );
while( result.next( ) ) {
String fname = result.getString( 1 );
if( result.wasNull( ) ) fname = "NULL";
String lname = result.getString( 2 );
if( result.wasNull( ) ) lname = "NULL";
System.out.println( lname + ", " + fname );
}
result.close( );
statement.close( );

5.6.2.1 Use the following steps to execute query statements in JDBC:

Create a JDBC Statement object by invoking the
createStatement method on a valid
Connection object:

java.sql.Statement statement = connection.createStatement( );

The query is executed by invoking one of the
execute methods on the
Statement object. Result sets from query
statements are processed by JDBC ResultSet
objects, which are returned from the executeQuery
method of JDBC Statement objects.

java.sql.ResultSet result = 
statement.executeQuery("SELECT au_fname, au_lname FROM authors" );

After creating a ResultSet object, you can iterate
through one row at a time by invoking the next
method. The next method returns a Boolean value,
true for each row in a result set and false after all rows have been
iterated through. It is common to invoke the next
method within a while loop to process the rows
one-by-one. The ResultSet does not begin on the
first row of the result, so you must invoke the
next method to advance to the first row before
calling any of the get methods.

while( result.next( ) ) {

To retrieve column data from the rows within a result set, invoke the
appropriate get method on the
ResultSet object. For a list of the most common
get methods, check Table 5-6.
Note that the column data is checked for a NULL value after the
get method has been invoked, since the nullness of
a value can't be determined from the value returned
by the get methods.

   String fname = result.getString("au_fname");
if( result.wasNull( ) ) fname = "NULL";
String lname = result.getString("au_lname");
if( result.wasNull( ) ) lname = "NULL";

When creating a JDBC program that fetches data from nullable columns,
it is always safest to check the value returned from a
ResultSet object for a NULL value using the
wasNull method.

Free the resources held by the result
set and statement objects. When finished with the
ResultSet and Statement
objects, invoke their close methods so that
database resources can be freed.

result.close( );
statement.close( );

Beyond the get methods, there are three other
methods on the ResultSet type that are frequently
used when processing data from a query. Those three methods are
listed in Table 5-7.