Retrieving Data from a Table

Now that we can insert data into a table, let's look at how we can retrieve data back from tables into Java. First, let's add some more sample data into our playerdata table, which we created in the previous section. We will do this directly from the MySQL console client with the following SQL statement:

mysql> INSERT INTO playerdata VALUES
 -> ('John', 'Jenkings', 'jsmith@email.net'),
 -> ('Rachel', 'Peterson', 'rpeterson@email.net'),
 -> ('Peter', 'Thompson', 'pthompson@email.net'),
 -> ('Katy', 'McKenzie', 'kmckenzie@email.net');

Now we have five rows total in the database. Let's look at a sample program that will allow us to read these five rows from the database and display them into the console.Code Listing 16-3: Retrieving data from a table into Java

import java.sql.*;
public class DatabaseExample3
{
 public static void main(String[] args)
 {
 try
 {
 Class.forName("org.gjt.mm.mysql.Driver");
 }
 catch(ClassNotFoundException e)
 {
 System.out.println(e);
 }
 try
 {
 Connection conn; System.out.println("Attempting to connect...\n"); conn = DriverManager.getConnection("jdbc:mysql://
 localhost/firsttest?user=root&password=");
 System.out.println("Connected\n");
 System.out.println("Attempting to retrieve table
 data...\n");
 Statement myStatement = conn.createStatement();
 ResultSet myResultSet = myStatement.executeQuery("SELECT
 * FROM playerdata");
 while(myResultSet.next())
 {
 System.out.print(myResultSet.getString
 ("forename")+"\t\t"); System.out.print(myResultSet.getString
 ("surname")+"\t");
 System.out.print(myResultSet.getString
 ("email")+"\n");
 }
 System.out.println("\nAttempting to disconnect...\n");
 conn.close();
 System.out.println("Disconnected\n"); }
 catch(SQLException e)
 {
 System.out.println(e);
 } }
}

When we execute this code, it produces the following output:
Screenshot-4: This screen shot shows the output from code listing 16-3.

Let's look at what we have changed and added to the code to retrieve the data from MySQL. First, we have used the executeQuery method instead of the executeUpdate method. This can be seen in the following code fragment:

ResultSet myResultSet = myStatement.executeQuery("SELECT * FROM playerdata");

Notice how the method returns a ResultSet object, which is part of the JDBC. The ResultSet object stores a pointer to the first row of information that was retrieved from the database. The other code that we have added is the while loop to enable us to traverse through the data from the ResultSet. This can be seen in the following code segment:

while(myResultSet.next())
{
 System.out.print(myResultSet.getString
 ("forename")+"\t\t"); System.out.print(myResultSet.getString
 ("surname")+"\t");
 System.out.print(myResultSet.getString
 ("email")+"\n");
}

Basically, while we have more "rows" in our result set, we cycle through the while loop. Then, for each of the rows in the resultset, we use the getString method to access the data relating to the fields of the database.

In other words, ResultSet is like an iterator. The ResultSet, however, does not actually contain any data. When you call the next() method, it fetches one row at a time from the database server. That is because if the result (from the SQL query) is very large, we do not want to transfer all of the data to the app at once. ResultSet could be traversed in backward also, but that is not supported in all of the JDBC implementations.

• Previous
• Next