Java in Adaptive Server Enterprise

Chapter 4 Data Access Using JDBC

Chapter 4

Data Access Using JDBC

This chapter describes how to use Java Database Connectivity (JDBC) to access data.

Overview

JDBC provides a SQL interface for Java applications. If you want to access relational data from Java, you must use JDBC calls.

You can use JDBC with the Adaptive Server SQL interface in either of two ways:

JDBC on the client - Java client applications can make JDBC calls to Adaptive Server using the Sybase jConnect JDBC driver.
JDBC on the server - Java classes installed in the database can make JDBC calls to the database using the JDBC driver internal to Adaptive Server.

The use of JDBC calls to perform SQL operations is essentially the same in both contexts.

This chapter provides sample classes and methods that describe how you might perform SQL operations using JDBC. These classes and methods are not intended to serve as templates, but as general guidelines.

JDBC Concepts and Terminology

JDBC is a Java API and a standard part of the Java class libraries that control basic functions for Java application development. The SQL capabilities that JDBC provides are similar to those of ODBC and dynamic SQL.

The following sequence of events is typical of a JDBC application:

Create a Connection object - Call the getConnection( ) class method of the DriverManager class to create a Connection object. This establishes a database connection.
Generate a Statement object - Use the Connection object to generate a Statement object.
Pass a SQL statement to the Statement object - If the statement is a query, this action returns a ResultSet object.
The ResultSet object contains the data returned from the SQL statement, but provides it one row at a time (similar to the way a cursor works).
Loop over the rows of the results set - Call the next( ) method of the ResultSet object to:
- Advance the current row (the row in the result set that is being exposed through the ResultSet object) by one row.
- Return a Boolean value (true/false) to indicate whether there is a row to advance to.
For each row, retrieve the values for columns in the ResultSet object - use the getInt( ), getString( ), or similar method to identify either the name or position of the column.

Differences Between Client- and Server-Side JDBC

The difference between JDBC on the client and in the database server is in how a connection is established with the database environment.

Client-side JDBC - Requires the Sybase jConnect JDBC driver to establish a connection. The connection is established by passing arguments to the DriverManager.getConnection( ) method. The database environment is an external application from the perspective of the Java client application.
Server-side JDBC - When JDBC is used within the database server, a connection already exists. A value of "jdbc:default:connection" is passed to DriverManager.getConnection( ), which provides the JDBC application the ability to work within the current user connection. This is a safe and efficient operation because the client application has already passed the database security to establish the connection.
You can write JDBC classes to run both at the client and at the server by employing a single conditional statement for constructing the URL.
An external connection requires the machine name and port number, while the internal connection requires one of these values:
- jdbc:default:connection
- jdbc:sybase:ase
- jdbc:default

Connections and Permissions

Connection defaults - From server-side JDBC, only the first call to getConnection( "jdbc:default:connection" ) creates a new connection with the default values.
Subsequent calls return a wrapper of the current connection with all connection properties unchanged.
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 from that of stored procedures, which execute with the permissions of the owner.

Using JDBC to Access Data

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

This section describes how you can use JDBC to perform the typical operations of a SQL application. The examples are extracted from the class JDBCExamples, which is described in "The JDBCExamples Class" and in $SYBASE/$SYBASE_ASE/sample/JavaSql (UNIX) or %SYBASE%\Ase-12_0\sample\JavaSql (Windows NT).

JDBCExamples illustrates the basics of a user interface and shows the internal coding techniques for SQL operations.

Overview of the JDBCExamples Class

The JDBCExamples class uses the Address class described in "Sample Java Classes " . To execute these examples on your machine, install the Address class on the server and include it in the Java CLASSPATH of the jConnect client.

You can call the methods of JDBCExamples from either a jConnect client or Adaptive Server.

You must create or drop stored procedures from the jConnect client. The Adaptive Server internal driver does not support create procedure and drop procedure statements.

JDBCExamples class methods perform the following SQL operations:

Create and drop an example table, xmp:

          create table xmp (id int, name varchar(50),  home Address)

Create and drop a sample stored procedure, inout:

create procedure inout @id int, @newname  varchar(50), 
             @newhome  Address, @oldname varchar(50) output, @oldhome
             Address  output as

select @oldname = name, @oldhome = home  from xmp 
             where id=@id

update xmp set name=@newname,  home = @newhome 
             where  id=@id

Insert a row into the xmp table.
Select a row from the xmp table.
Update a row of the xmp table.
Call the stored procedure inout, which has both input parameters and output parameters of datatypes java.lang.String and Address.

JDBCExamples operates only on the xmp table and inout procedure.

The main( ) and serverMain( ) Methods

JDBCExamples has two primary methods:

main( ) - is invoked from the command line of the jConnect client.
serverMain( ) - performs the same actions as main( ), but is invoked within Adaptive Server.

All actions of the JDBCExamples class are invoked by calling one of these methods, using a parameter to indicate the action to be performed.

Using main( )

You can invoke the main( ) method from a jConnect command line as follows:

java  JDBCExamples  "server-name:
     port-number?user=user-name&password=password" action

You can determine server-name and port-number from your interfaces file. user-name and password are your user name and password. If you omit &password=password, the default is the empty password. Here are two examples:

"antibes:4000?user=smith&password=1x2x3"

"antibes:4000?user=sa"

Make sure that you enclose the parameter in quotation marks.

The action parameter can be create table, create procedure, insert, select, update, or call. It is case insensitive.

You can invoke JDBCExamples from a jConnect command line to create the table xmp and the stored procedure inout as follows:

java JDBCExamples "antibes:4000?user=sa" CreateTable

java JDBCExamples "antibes:4000?user=sa" CreateProc

You can invoke JDBCExamples for insert, select, update, and call actions as follows:

java JDBCExamples "antibes:4000?user=sa" insert

java JDBCExamples "antibes:4000?user=sa" update

java JDBCExamples "antibes:4000?user=sa" call

java JDBCExamples "antibes:4000?user=sa" select

These invocations display the message "Action performed."

To drop the table xmp and the stored procedure inout, enter:

java  JDBCExamples "antibes:4000?user=sa" droptable

java JDBCExamples "antibes:4000?user=sa" dropproc

Using serverMain( )

Because the server-side JDBC driver does not support create procedure or drop procedure, create the table xmp and the example stored procedure inout with client-side calls of the main( ) method before executing these examples. Refer to "Overview of the JDBCExamples Class" .

After creating xmp and inout, you can invoke the serverMain( ) method as follows:

select JDBCExamples.serverMain('insert')

go

select  JDBCExamples.serverMain('select')

go

select  JDBCExamples.serverMain('update')

go

select  JDBCExamples.serverMain('call')

go

Server-side calls of serverMain( ) do not require a server-name:port-number parameter; Adaptive Server simply connects to itself.

Obtaining a JDBC Connection: the Connecter( ) Method

Both main( ) and serverMain( ) call the connecter( ) method, which returns a JDBC Connection object. The Connection object is the basis for all subsequent SQL operations.

Both main( ) and serverMain( ) call connecter( ) with a parameter that specifies the JDBC driver for the server- or client-side environment. The returned Connection object is then passed as an argument to the other methods of the JDBCExamples class. By isolating the connection actions in the connecter( ) method, JDBCExamples' other methods are independent of their server- or client-side environment.

Routing the Action to Other Methods: the doAction( ) Method

The doAction( ) method routes the call to one of the other methods, based on the action parameter.

doAction( ) has the Connection parameter, which it simply relays to the target method. It also has a parameter locale, which indicates whether the call is server- or client-side. Connection raises an exception if either create procedure or drop procedure is invoked in a server-side environment.

Executing Imperative SQL Operations: the doSQL( ) Method

The doSQL( ) method performs SQL actions that require no input or output parameters such as create table, create procedure, drop table, and drop procedure.

doSQL( ) has two parameters: the Connection object and the SQL statement it is to perform. doSQL( ) creates a JDBC Statement object and uses it to execute the specified SQL statement.

Executing an update Statement: the UpdateAction( ) Method

The updateAction( ) method performs a Transact-SQL update statement. The update action is:

String sql = "update  xmp set name = ?, home = ? where id = ?";

It updates the name and home columns for all rows with a given id value.

The update values for the name and home column, and the id value, are specified by parameter markers (?). updateAction( ) supplies values for these parameter markers after preparing the statement, but before executing it. The values are specified by the JDBC setString( ), setObject( ), and setInt( ) methods with these parameters:

The ordinal parameter marker to be substituted
The value to be substituted

For example:

pstmt.setString(1,  name);

pstmt.setObject(2,  home);

pstmt.setInt(3, id);

After making these substitutions, updateAction( ) executes the update statement.

To simplify updateAction( ), the substituted values in the example are fixed. Normally, applications would compute the substituted values or obtain them as parameters.

Executing a select Statement: the selectAction( ) Method

The selectAction( ) method executes a Transact-SQL select statement:

String  sql = "select name, home from xmp where id=?";

The where clause has a parameter marker (?) for the row to be selected. Using the JDBC setInt( ) method, selectAction( ) supplies a value for the parameter marker after preparing the SQL statement:

PreparedStatement  pstmt = con.prepareStatement(sql);
pstmt.setInt(1,  id);

selectAction( ) then executes the select statement:

ResultSet  rs = pstmt.executeQuery();

For SQL statements that return no results, use doSQL( ) and updateAction( ). They execute SQL statements with the executeUpdate( ) method. For SQL statements that do return results, use the executeQuery( ) method, which returns a JDBC ResultSet object.

The ResultSet object is similar to a SQL cursor. Initially, it is positioned before the first row of results. Each call of the next( ) method advances the ResultSet object to the next row, until there are no more rows.

selectAction( ) requires that the ResultSet object have exactly one row. The selecter( ) method invokes the next method, and checks for the case where ResultSet has no rows or more than one row.

        if  (rs.next()) {

            name = rs.getString(1);

           home = (Address)rs.getObject(2);

            if (rs.next()) {

               throw new Exception("Error:  Select returned multiple  rows");

           } else { // No  action

        } else { throw  new Exception("Error:  Select returned no rows");

In the above code, the call of methods getString( ) and getObject( ) retrieve the two columns of the first row of the result set. The expression "(Address)rs.getObject(2)" retrieves the second column as a Java object, and then coerces that object to the Address class. If the returned object is not an Address, then an exception is raised.

selectAction( ) retrieves a single row and checks for the cases of no rows or more than one row. An application that processes a multiple row ResultSet would simply loop on the calls of the next( ) method, and process each row as for a single row.

Calling a SQL Stored Procedure: the callAction( ) Method

The callAction( ) method calls the stored procedure inout:

create  proc inout @id int, @newname varchar(50), @newhome  Address,
         @oldname varchar(50)  output,  @oldhome Address output as
 
     select @oldname = name, @oldhome = home  from xmp where id=@id
     update  xmp set name=@newname, home = @newhome  where id=@id

This procedure has three input parameters (@id, @newname, and @newhome) and two output parameters (@oldname and @oldhome). callAction( ) sets the name and home columns of the row of table xmp with the ID value of @id to the values @newname and @newhome, and returns the former values of those columns in the output parameters @oldname and @oldhome.

The inout procedure illustrates how to supply input and output parameters in a JDBC call.

callAction( ) executes the following call statement, which prepares the call statement:

CallableStatement  cs = con.prepareCall("{call inout (?, ?, ?, ?,  ?)}");

All of the parameters of the call are specified as parameter markers (?).

callAction( ) supplies values for the input parameters using JDBC setInt( ), setString( ), and setObject( ) methods that were used in the doSQL( ), updatAction( ), and selectAction( ) methods:

        cs.setInt(1, id);

        cs.setString(2, newName);

        cs.setObject(3, newHome);

These set methods are not suitable for the output parameters. Before executing the call statement, callAction( ) specifies the datatypes expected of the output parameters using the JDBC registerOutParameter( ) method:

        cs.registerOutParameter(4, java.sql.Types.VARCHAR);

        cs.registerOutParameter(5,com.sybase.jdbc.Param.JAVA_OBJECT);

callAction( ) then executes the call statement and obtains the output values using the same getString( ) and getObject( ) methods that the selectAction( ) method used:

        int  res = cs.executeUpdate();

         String oldName = cs.getString(4);

        Address oldHome = (Address)cs.getObject(5);

The JDBCExamples Class

// An example class  illustrating the use of JDBC facilities
 // with  the Java in Adaptive Server feature.

//    
 // The methods of this class perform  a range of SQL operations.
 // These methods  can be invoked either from a Java client,
 // using  the main method, or from the SQL server, using
 // the  internalMain method.
 //

import java.sql.*;             // JDBC

public class JDBCExamples {

The main( ) Method

// The main method,  to be called from a client-side command line

//

    public static void main(String args[]) {

        if (args.length!=2) {

            System.out.println("\n Usage:     "

                 + "java  ExternalConnect server-name:port-number
                 action  ");

            System.out.println("   The action is connect, createtable,
             " + "createproc,  drop, "
             + "insert,  select, update, or call \n" );

            return;

         try{

            String server = args[0];

           String action = args[1].toLowerCase();

           Connection con = connecter(server);

           String workString = doAction(  action, con, client);

           System.out.println("\n" + workString + "\n");

        }  catch (Exception e) {

           System.out.println("\n Exception:  ");

           e.printStackTrace();

The internalMain( ) Method

// A JDBCExamples  method equivalent to 'main', 
 // to  be called from SQL or Java in the server

    public  static String internalMain(String action) {

        try {

           Connection con = connecter("default");

           String workString = doAction(action,  con, server);

           return  workString;

        }   catch ( Exception e ) {

              if (e.getMessage().equals(null)) {

               return "Exc: " + e.toString();

             } else {

               return "Exc - " + e.getMessage();

The connecter( ) Method

// A JDBCExamples  method to get a connection.  
 // It can  be called from the server with argument 'default',
 // or  from a client, with an argument that is the server name.

public static  Connection connecter(String server) 
         throws  Exception, SQLException, ClassNotFoundException  {

       String  forName="";

        String url="";

       if (server=="default") {  // server  connection to current server
         forName = "sybase.asejdbc.ASEDriver";   
         url = "jdbc:default:connection";

       } else if (server!="default") {  //client  connection to server
         forName= "com.sybase.jdbc.SybDriver";          
         url = "jdbc:sybase:Tds:"+ server;

       String user = "sa";

       String password = "";

       // Load  the driver

       Class.forName(forName);

       // Get a connection

       Connection con = DriverManager.getConnection(url, 
         user,  password);

       return con;

The doAction( ) Method

// A JDBCExamples  method to route to the 'action' to be performed

    public  static String doAction(String action, Connection con,
             String  locale) 
         throws Exception {

        String  createProcScript =

             " create proc inout @id  int, @newname varchar(50), 
             @newhome  Address, "

           + "    @oldname varchar(50) output,  @oldhome Address 
             output  as "

           + "  select @oldname = name, @oldhome = home  from xmp 
             where id=@id  "

           + " update  xmp set name=@newname, home = @newhome 
             where  id=@id ";

         String createTableScript =
         "  create  table xmp (id int, name varchar(50), 
             home  Address)" ;

        String dropTableScript = "drop    table xmp  ";

        String  dropProcScript = "drop  proc inout ";

       String insertScript = "insert   into xmp " 
         + "values (1, 'Joe  Smith', new Address('987 Shore',
         '12345'))";

        String  workString = "Action (" + action + )  ;

        if (action.equals("connect")) {

             workString += "performed";

        } else if (action.equals("createtable")) {

             workString += doSQL(con,  createTableScript );

         } else if (action.equals("createproc")) {

             if (locale.equals(server))  {

                 throw new exception (CreateProc  cannot be performed
                 in  the server);

             }   else {

                  workString += doSQL(con, createProcScript  );

        } else if (action.equals("droptable")) {

             workString += doSQL(con,  dropTableScript );

        } else  if (action.equals("dropproc")) {

             if (locale.equals(server))  {

                 throw new exception (CreateProc  cannot be performed
                 in  the server);

             }   else {

                  workString += doSQL(con, dropProcScript );

        } else if (action.equals("insert")) {

             workString += doSQL(con,  insertScript );

        } else  if (action.equals("update")) {

             workString += updateAction(con);

        } else if (action.equals("select")) {

             workString += selectAction(con);

        } else if (action.equals("call")) {

             workString += callAction(con);

        } else { return "Invalid  action:  " + action ;

         return workString;

The doSQL( ) Method

// A JDBCExamples  method to execute an SQL statement.

    public static String doSQL (Connection con,  String action)

            throws Exception {

        Statement stmt = con.createStatement();

        int res = stmt.executeUpdate(action);

        return "performed";

The updateAction( ) Method

// A method that  updates a certain row of the 'xmp' table.
 // This  method illustrates prepared statements and parameter markers.

    public  static String updateAction(Connection con)

           throws Exception {

        String  sql = "update xmp set name = ?, home = ?  where id = ?";

         int id=1;

         Address home = new Address("123 Main", "98765");

        String name = "Sam Brown";

        PreparedStatement pstmt = con.prepareStatement(sql);

        pstmt.setString(1, name);

        pstmt.setObject(2, home);

        pstmt.setInt(3, id);

        int res = pstmt.executeUpdate();

        return "performed";

The selectAction( ) Method

//  A JDBCExamples  method to retrieve a certain row 
 //   of the 'xmp' table.
 //   This method illustrates prepared statements, parameter markers,
 //   and result sets.

    public static String selectAction(Connection  con)

           throws Exception {

        String  sql = "select name, home from xmp where id=?";

        int id=1;

        Address home = null;

        String name = "";

        String street = "";

        String zip = "";

        PreparedStatement pstmt = con.prepareStatement(sql);

        pstmt.setInt(1, id);

        ResultSet rs = pstmt.executeQuery();

        if (rs.next()) {

           name = rs.getString(1);

           home = (Address)rs.getObject(2);

            if (rs.next()) {

               throw new Exception("Error:  Select returned
                  multiple  rows");

           } else { // No  action

        } else { throw  new Exception("Error:  Select returned no rows");

        return "- Row with id=1:  name("+ name + )

                + "  street(" + home.street + ) zip("+ home.zip + );

The callAction( ) Method

// A JDBCExamples  method to call a stored procedure, 
 // passing  input and output parameters of datatype String
 // and  Address.
 // This method illustrates  callable statements, parameter markers,
 // and  result sets.

    public static String callAction(Connection  con)

           throws Exception {

        CallableStatement cs  = con.prepareCall("{call  inout 
         (?, ?, ?, ?, ?)}");

        int id = 1;

        String newName = "Frank Farr";

        Address newHome = new Address("123  Farr Lane", "87654");

         cs.setInt(1, id);

         cs.setString(2, newName);

         cs.setObject(3, newHome);

         cs.registerOutParameter(4, java.sql.Types.VARCHAR);

        cs.registerOutParameter(5, com.sybase.jdbc.Param.JAVA_OBJECT);

        int res = cs.executeUpdate();

        String oldName = cs.getString(4);

        Address oldHome = (Address)cs.getObject(5);

        return "- Old values of row with id=1:  name("+oldName+ )
           street(" + oldHome.street + ")   zip("+ oldHome.zip + );


	Expand Search