Using JDBC to access data

User's Guide
PART 3. Java in the Database
CHAPTER 18. Data Access Using JDBC

Using JDBC to access data

Java applications that hold some or all classes in the database have significant advantages over traditional SQL stored procedures.

At an introductory level, however, it may be helpful to use the parallels with SQL stored procedures to demonstrate the capabilities of JDBC. In the following examples, we write Java classes that insert a row into the Department table.

As with other interfaces, SQL statements in JDBC can be either static or dynamic. Static SQL statements are constructed in the Java application, and sent to the database. The database server parses the statement, and selects an execution plan, and executes the statement. Together, parsing and selecting an execution plan are referred to as preparing the statement.

If a similar statement has to be executed many times (many inserts into one table, for example), there can be significant overhead in static SQL because the preparation step has to be executed each time.

In contrast, a dynamic SQL statement contains placeholders. The statement is prepared once, using these placeholders, and the statement can be executed many times without the addition expense of preparing.

In this section we use static SQL. Dynamic SQL is discussed in a later section.

Preparing for the examples

This section describes how to prepare for the examples in the remainder of the chapter.

Sample code

The code fragments in this section are taken from the complete class JDBCExamples.java, included in the jxmp directory under your installation directory.

To install the JDBCExamples class:

If you have not already done so, install the JDBCExamples.class file into the sample database. Once connected to the sample database from Interactive SQL, you can use the following command:
```
INSTALL JAVA NEW 
FROM FILE 'path\jxmp\JDBCExamples.class'
```
where path is the path to your installation directory.

You can also install the class using Sybase Central. While connected to the sample database, open the Java Objects folder and double click Add Class. Then follow the instructions in the wizard.

Inserts, updates, and deletes using JDBC

The Statement object is used for executing static SQL statements. You execute SQL statements such as INSERT, UPDATE, and DELETE, which do not return result sets, using the executeUpdate method of the Statement object. Statements such as CREATE TABLE and other data definition statements can also be executed using executeUpdate.

The following code fragment illustrates how INSERT statements are carried out within JDBC. It uses an internal connection held in the Connection object named conn. The code for inserting values from an external application using JDBC would need to use a different connection, but otherwise would be unchanged.

public static void InsertFixed()  {  
    // returns current connection
    conn = DriverManager.getConnection("jdbc:default:connection");
    // Disable autocommit
    conn.setAutoCommit( false );
    
    Statement stmt = conn.createStatement();
    
    Integer IRows = new Integer( stmt.executeUpdate
      ("INSERT INTO Department (dept_id, dept_name )"
       + "VALUES (201, 'Eastern Sales')" 
        ) );
    // Print the number of rows updated
    System.out.println(IRows.toString() + " row inserted" );
  }

Source code available
This code fragment is part of the InsertFixed method of the JDBCExamples class, included in the jxmp subdirectory of your installation directory.

Notes

The setAutoCommit method turns off the AutoCommit behavior, so that changes are only commited if an explicit COMMIT instruction is executed.
The executeUpdate method returns an integer, which reflects the number of rows affected by the operation. In this case, a successful INSERT would return a value of one (1).
The integer return type is converted to an Integer object. The Integer class is a wrapper around the basic int data type, providing some useful methods such as toString().
The Integer IRows is converted to a string in order to be printed. The output goes to the server window.

To run the JDBC Insert example:

Using Interactive SQL, connect to the sample database as user ID dba.
Ensure the JDBCExamples class has been installed. It is installed together with the other Java examples classes.

For instructions on installing the Java examples classes, see Installing the Java examples.
Call the method as follows:
```
CALL JDBCExamples>>InsertFixed()
```
Confirm that a row has been added to the department table.
```
SELECT *
FROM department
```
The row with ID 201 is not committed. You can execute a ROLLBACK statement to remove the row.

In this example, you have seen how to create a very simple JDBC class. In subsequent examples, we shall expand on this.

Passing arguments to Java methods

We can expand the InsertFixed method to illustrate how arguments are passed to Java methods.

The following method uses arguments passed in the call to the method as the values to be inserted:

public static void InsertArguments( 
                String id, String name)  {
try {
      conn = DriverManager.getConnection( 
                    "jdbc:default:connection" );

String sqlStr =  "INSERT INTO Department "
   + " ( dept_id, dept_name )"
   + " VALUES (" +  id +  ", '"  + name + "')";

      // Execute the statement
      Statement stmt = conn.createStatement();
      Integer IRows = new Integer( stmt.executeUpdate( sqlStr.toString() ) );

      // Print the number of rows updated
      System.out.println(IRows.toString() + " row inserted" );
    }
    catch ( Exception e ) {
      System.out.println("Error: " + e.getMessage());
      e.printStackTrace();
    }
  }

Notes

The two arguments are the department id (an integer) and the department name (a string). Here, both arguments are passed to the method as strings, because they are used as part of the SQL statement string.
The INSERT is a static statement: it takes no parameters other than the SQL itself.
If you supply the wrong number or type of arguments, you receive the Procedure Not Found error.

To use a Java method with arguments:

If you have not already installed the JDBCExamples.class file into the sample database, do so.
Connect to the sample database from Interactive SQL, and enter the following command:
```
call JDBCExamples>>InsertArguments( '203', 'Northern Sales' )
```
Verify that an additional row has been added to the Department table:
```
SELECT *
FROM Department
```
Roll back the changes to leave the database unchanged:
```
ROLLBACK
```

Queries using JDBC

The Statement object is used for executing 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;
  }

Running the example

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()

Notes

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 the 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 SQL-to-Java data type conversion.
Adaptive Server Anywhere 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 Data window.

Using prepared statements for more efficient access

If you use the Statement interface, each statement you send to the database must be parsed, an access plan must be generated, and the statement must be executed. The steps prior to actual execution are called preparing the statement.

You can achieve performance benefits if you use the PreparedStatement interface. This allows you to prepare a statement using placeholders, and then assign values to the placeholders when executing the statement.

Using prepared statements is particularly useful when carrying out many similar actions, such as inserting many rows.

For Info For a general introduction to prepared statements, see Preparing statements.

Example

The example actually inserts a single row, which is not a good use of prepared statements. Nevertheless, it illustrates how to use the PreparedStatement interface.

The following method of the JDBCExamples class carries our a prepared statement:

public static void JInsertPrepared(int id, String name) try {
      conn = DriverManager.getConnection( 
                    "jdbc:default:connection");

      // Build the INSERT statement
      // ? is a placeholder character
      String sqlStr = "INSERT INTO Department "
   + "( dept_id, dept_name ) "
   + "VALUES ( ? , ? )" ;

      // Prepare the statement
      PreparedStatement stmt = conn.prepareStatement( sqlStr );
      
      stmt.setInt(1, id);
      stmt.setString(2, name );
      Integer IRows = new Integer( 
                          stmt.executeUpdate() );

      // Print the number of rows updated
      System.out.println(IRows.toString() + " row inserted" );
    }
    catch ( Exception e ) {
      System.out.println("Error: " + e.getMessage());
      e.printStackTrace();
    }
  }

Running the example

Once you have installed the JDBCExamples class into the sample database, you can execute this example by entering the following statement:

call JDBCExamples>>InsertPrepared( 
                           202, 'Eastern Sales' )

The string argument is enclosed in single quotes, which is appropriate for SQL. If you invoked this method from a Java application you would use double quotes to delimit the string.

Inserting and retrieving objects

As an interface to relational databases, JDBC is designed to retrieve and manipulate traditional SQL data types. Adaptive Server Anywhere also provides abstract data types in the form of Java classes. The way you access these Java classes using JDBC depends on whether you are inserting or retrieving the objects.

Retrieving objects

You can retrieve objects and their fields and methods in the following ways:

Accessing methods and fields Java methods and fields can be included in the select-list of a query. A method or field then appears as a column in the result set, and can be accessed using one of the standard ResultSet methods, such as getInt, or getString.

Retrieving an object If you include a column with a Java class data type in a query select list, you can use the ResultSet getObject method to retrieve the object into a Java class. You can then access the methods and fields of that object within the Java class.

getObject server-side only
You can use getObject only from server-side JDBC. In order to access Java classes from client-side JDBC you must serialize the object. For information, see Creating distributed applications.

Inserting objects

From a server-side Java class, you can use the JDBC setObject method to insert an object into a column with Java class data type.

Inserting objects can be carried out using a prepared statement. For example, the following code fragment inserts an object of type MyJavaClass into a column of table T:

java.sql.PreparedStatement ps = 
    conn.prepareStatement("insert T values( ? )" );
ps.setObject( 1, new MyJavaClass() );
ps.executeUpdate();

setObject server-side only
You can use setObject only from server-side JDBC. In order to access Java classes from client-side JDBC you must serialize the object. For information, see Creating distributed applications.

An alternative is to set up a SQL variable that holds the object and then to insert the SQL variable into the table.

Miscellaneous JDBC notes

Access permissions Like all Java classes in the database, classes containing JDBC statements can be accessed by any user. There is no equivalent of the GRANT EXECUTE statement that grants permission to execute procedures, and there is no need to qualify the name of a class with the name of its owner.
Execution permissions Java classes are executed with the permissions of the connection executing them. This behavior is different to that of stored procedures, which execute with the permissions of the owner.