Queries using JDBC

The Statement object executes static queries, as well as statements that do not return result sets. For queries, you use the executeQuery method of the Statement object. This returns the result set in a ResultSet object.

The following code fragment illustrates how queries can be handled within JDBC. The code fragment places the total inventory value for a product into a variable named inventory. The product name is held in the String variable prodname. This example is available as the Query method of the JDBCExamples class.

The example assumes an internal or external connection has been obtained and is held in the Connection object named conn. It also assumes a variable

public static void Query () {
int max_price = 0;
    try{
      conn = DriverManager.getConnection( 
                   "jdbc:default:connection" );

      // Build the query
      String sqlStr =  "SELECT id, unit_price "
	+ "FROM product" ;

      // Execute the statement
      Statement stmt = conn.createStatement();
      ResultSet result = stmt.executeQuery( sqlStr );

      while( result.next() ) {
	int price = result.getInt(2);
	System.out.println( "Price is "  + price );
	if( price > max_price ) {
	  max_price = price ;
	}
      }
    }
    catch( Exception e ) {
      System.out.println("Error: " + e.getMessage());
      e.printStackTrace();
    }
      return max_price;
  }

Once you have installed the JDBCExamples class into the sample database, you can execute this method using the following statement in Interactive SQL:

select JDBCExamples>>Query()

• The query selects the quantity and unit price for all products named prodname. These results are returned into the ResultSet object named result.

• There is a loop over each of the rows of the result set. The loop uses the next method.

• For each row, the value of each column is retrieved into an integer variable using the getInt method. ResultSet also has methods for other data types, such as getString, getDate, and getBinaryString.

  The argument for the getInt method is an index number for the column, starting from 1.

  Data type conversion from SQL to Java is carried out according to the information in “Java / SQL data type conversion” in the “SQL Data Types” chapter of the Sybase IQ Reference Manual.

• Sybase IQ supports bidirectional scrolling cursors. However, JDBC provides only the next method, which corresponds to scrolling forward through the result set.

• The method returns the value of max_price to the calling environment, and Interactive SQL displays it in the Results pane.