24.6. Executing Queries through Statement Objects

24.6.1. Building a Statement Object

The first step to getting data from an EnterpriseDB database is to build a Statement object. The Statement object acts as a bridge between our database connection and the results returned after executing an SQL statement. When a SQL Statement object executes a query, it typically returns a ResultSet object. In our example, this is achieved through the following piece of code:

Statement statement = con.createStatement();
	      

24.6.2. Executing SQL

Once we have a Statement object in place, its time to execute SQL statements designed to return results for use in our sample application. The Statement object has several types of query methods each designed for a specific purpose. In this section we will discuss the executeQuery() method which is designed to execute a SQL statement that will return a result, meaning that this method expects to execute a SELECT query.

In our example, the following line of code sets off this process:

ResultSet rs = statement.executeQuery("SELECT * FROM emp");
 

The executeQuery() method expects the SQL query to passed as a String, this query is then passed to the database, which in turn executes this query. EnterpriseDB JDBC Connector does not make any type of check on the validity of the SQL statement being passed to the database. If for some reason the database is unable to execute the SQL statement, a SQLException is thrown, otherwise, if all goes well, the executeQuery() method will return a ResultSet object containing the rows from the database.

In case the JVM is unable to locate the driver, it outputs a message to the console and exits the application. The DriverManager is designed to handle multiple JDBC driver objects as long as they register with a class, meaning that you can write a Java application that connects with more than one database system via JDBC. Please keep in mind that no connection is made with the database at this time and point, and that the driver is simply loaded.

24.6.3. Displaying Results

Our example takes the ResultSet produced after the execution of our SQL query and displays the first column (in this case the employee number, i.e. the empno column) of each row in the emp table in our database.

24.6.4. Using the ResultSet Object

The ResultSet object is pretty much like a two-dimensional array, and acts as the primary storage mechanism for the rows returned from a query on an EnterpriseDB database.

The ResultSet object contains all of the rows returned by the database based on the query that was executed. The columns of the ResultSet object are the fields from the database as specified in the SQL query. If the SQL query used a * in the SELECT (as we did in our example), then all the columns from the database will be represented in the ResultSet. Whereas, if the columns are specified in the SELECT statement, then only those columns will appear in the ResultSet.

A large number of getter methods are available for retrieving data from the ResultSet object. These methods get the data from a specific database column and attempt to convert that column's datatype to a Java datatype as specified by the getter method.

You should keep the following points in mind when using the ResultSet interface:

1. Before reading any values, you must call next(). This returns true if there is a result, but more importantly, it prepares the row for processing.

2. Under the JDBC specification, you should access a field only once. It is safest to stick to this rule, although at the current time, the EnterpriseDB driver will allow you to access a field as many times as you want.

3. You must close a ResultSet by calling close() once you have finished using it.

4. Once you make another query with the Statement used to create a ResultSet, the currently open ResultSet instance is closed automatically.

24.6.5. More Advanced Example

Our first example included just the basics, now lets expand things a little by making them more user friendly by adding a GUI to view our employee related information. Later on we will take the GUI example further by adding variations to insert, update and delete information in the database. This example makes use of two helper classes:

• ConnectionUtils - used for establishing the database connection, which keeps the database connection information in a properties file.

• Console - used to provide a simple way to read from the console one line at a time

Let us first look at the properties file, and then the helper ConnectionUtils and Console classes and then the SelectTest class making use of both of these classes.

resource.properties file

host=localhost
port=5444
db=edb
user=enterprisedb
password=enterprisedb

The helper "ConnectionUtils.java" Class

import java.sql.*;
import java.util.*;
/**
 * Utility class to mainipulate the database connections.
 */
public class ConnectionUtils 
{
  private static Connection con = null;
  public static void main(String[] args)
  {
    ConnectionUtils.getConnection();
  }  
  public static Connection getConnection()
  {
        try
    {
      Class.forName("com.edb.Driver");
      ResourceBundle rb = ResourceBundle.getBundle("resource");
      con = DriverManager.getConnection("jdbc:edb://"+rb.getString("host")+
      ":"+rb.getString("port")+"/"+rb.getString("db")+
      "",""+rb.getString("user")+"",rb.getString("password"));
    }
        catch(ClassNotFoundException cnfe)
        {
      System.out.println("Unable to load the EDB JDBC Driver.");
      System.out.println("Please check your classpath property.");
      con = null;
    }
        catch(Exception exp)
        {
      System.out.println("Unable to Connect to the destination database.");
      System.out.println("Please check that Host/ IP and the authentication\r\n"+
                         "parameters are correct and the database is listening the connections.");
      System.out.println("See below for full error description.");                         
      exp.printStackTrace();
      con = null;
    }
        return con;
  }
  public static void closeConnection()
  {
    try
        {
                if(con!=null) 
                        con.close();
    }
        catch(Exception exp)
        {
        }
  }
}

The helper "Console.java" Class

import java.io.*;
public class Console
{
  private static BufferedReader br = null;
  static
  {
    try
    {
      br = new BufferedReader(new InputStreamReader(System.in));
    }catch(Exception exp){}
  }
  public String readLine()
  {
    String line="";
    try
    {
     line = br.readLine();
    }catch(Exception exp)
    {}
    return line;
  }
}

SelectTest.java Class

import java.sql.*;
import java.util.Vector;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;

public class SelectTest 
{
  
  public static Vector getNextRow(ResultSet rs,int cols) throws SQLException
  {
    Vector v = new Vector();
    for(int i=1;i<=cols;i++)
    {
      v.addElement(rs.getString(i));
    }
    return v;
  }
  public static void main(String[] args)
  {
    try
    {
      Connection con = ConnectionUtils.getConnection();
      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery("SELECT * FROM EMP");
      ResultSetMetaData rsmd = rs.getMetaData();
      Vector labels = new Vector();
      Vector rows = new Vector();
      int cols = rsmd.getColumnCount();
      for(int i=0;i<cols;i++)
      {
        labels.addElement(rsmd.getColumnLabel(i+1));        
      }
      while(rs.next())
      {
        rows.addElement(getNextRow(rs,cols));
      }
      JTable table = new JTable(rows,labels);
      JFrame jf = new JFrame("Browsing table: EMP ( from EnterpriseDB)");
      jf.getContentPane().add(new JScrollPane(table));      
      jf.setSize(400,400);
      jf.setVisible(true);
      jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      System.out.println("Command successfully executed");
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the query..");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

You should see the following when you compile and run the above sample application.