Michela Crosetto

Antonio Cavaliere

Giuseppe Innamorato

Bartolomeo Bogliolo

Torino - Italy

Using the Java language for accessing relational Databases on Internet based applications has proved to be a very robust and efficient technology.

The environments that can offer such a power tool are several.

In this paper we present two different approaches for accessing the Oracle RDBMS using the Java language: the JDBC e JSQL interfaces.

The advent of the World Wide Web and of Java has brought new and unpredictable possibilities for applications design and development.

These new possibilities can be adequately exploited to access to relational Databases as network resources.

This abstract presents some possible Java architectures and application examples, with a particular focus upon interfacing Oracle RDBMS.

The rapid and widespread diffusion of the World Wide Web has totally changed the way to communicate. In a few years, in fact, a new Virtual World is grown and become boundless, and it can be visited almost by everyone.

The great diffusion and power reached by Internet permits to gain potentially an unlimited number of users, that is to say potential clients for many companies (services and products providers) that are becoming more and more interested in the internet business. The commercial approach to internet for these companies implies the creation of a web server having embedded database enabling capabilities but, at the same time, it must be also very well designed from a graphical point of view. It is strategic to use fast and simple tools for accessing Databases whenever the user needs to use the internet (or the intranet) technology to manage orders, to manage clients or to address any other customer care activities, to realise helpdesk functions, etc.

This change has not been fully recognised and accepted by everyone, but in the meanwhile a second revolution has already come, thanks to the Java technology.

In this context the future applications, but also the traditional client-server applications, should be redesigned in order to exploit the huge capabilities offered by the new technology, which allows the user to access transparently either to data and to applications residing in the intranet or in the internet.

The present abstract presents some experiences made using Java for building applications accessing Oracle Databases. In particular, it describes the JDBC and JSQL architecture.

If You are intrested on developing Your own JDBC driver see the document Developing Custom and Standard Interface to Oracle RDBMS in Java.

The JDBC API

The JDBC API, included in the Java Development Kit from version 1.1, represents the Java language interfaces for accessing whatever kind of database.

The combination of the intrinsic potentials of Java and the access to a data allows you to build very powerful web-based applications. This paradigm allows you to write mission-critical applications, either in intranet environment or in internet environment, without any need to modify the code (Write once, run everywhere).

The JDBC access functions offer the possibility to manage completely the information stored in a database. We can identify three ways in which JDBC can be realised:

1. Direct Access (or 1-Tier): the database access is managed by one or more custom libraries and is limited to a local database.
2. Two levels access (or 2-Tier): the database access is done using a classical client/server model, where the client communicates with the server in whitch the database resides. This method allows you to work with a remote database.
3. The three levels access (or 3-Tier): the database access is still done using the client/server model. The client doesn’t communicate directly with the database but trough an intermediate level that is responsible of managing the database in terms of access and requests.

This model is the closest to the Java paradigm of ‘Write once, run everywhere’, because it permits you to write applets or applications that are business oriented and so to hide the underlying Database specific characteristics. The role of the Middleware layer is to optimise the Database access, to provide security, to manage the concurrent accesses and to improve the development time, by providing a standard interface to multiple Databases.

At the protocol level, the database communication is realised on the basis of JDBC standards by different database suppliers (i.e. Oracle). We can depict four categories:

1. JDBC-ODBC bridge

Provided with JDK 1.1. It consist of libraries written in ‘C’ language which implement the JDBC interfaces for communicating with an ODBC driver manager and then with an ODBC driver. In a three-levels structure, the ODBC drivers and the driver manager reside at the Middleware level, and therefore they will not be installed on each client system.

2. Library to Java

It consists of the implementation of the JDBC using ‘C’ libraries, thus provide to access and manage the calls to a specific database.

3. Network-protocol driver

It permits the conversion of JDBC calls in a DBMS independent standard, which is connected via socket to a Middleware layer able to manage the clients requests towards the database.

4. Java Native driver

The Oracle Company has focused mainly upon making network protocol driver which allow a major usage of the JDBC, even on an internet network.

The JDBC interfaces set is divided into two main layers:

1. Driver Layer
2. Application Layer

The Driver Layer manages the interaction with the database and must implement the following interfaces:

• JDBC Driver
• Connection
• Statement
• ResultSet

The Driver interface is the one used to represent, inside the application, the database to be connected to. Because it is possible to be connected to several types of databases at the same time, the DriverManager interface is able to manage the right driver according to the request. This interface is able to choose the right driver to be used on the basis of the syntax of the connection request, which has a structure very similar to a common URL one:

jdbc:<subprotocol>:<subname>

Where:

<subprotocol> identifies the driver type

<subname> identifies the database name and the type of the database we want to connect to.

For instance, for an Oracle connection the possible string could be the following:

In case the driver is not available (meaning that the DriverManager is unable to establish the connection) you can load at run-time the needed driver in one of the following ways:

• Directly load the driver using the method Class.ForName():

Class.ForName("oracle.jdbc.dnlddriver.OracleDriver")

• Add the driver in the list of the available drivers, contained in the system property "jdbc.drivers".

• Register directly the class using the following method (belonging to DriverManager Interface):

The Application layer is linked to the application and is responsible of the user interaction and of the Driver Layer. In fact it manages the database data accessed by using the driver.

Oracle provides a JDBC implementation for accessing to an Oracle Database. The JDBC implementation is included inside the Oracle package:

There are two different implementations of the JDBC Oracle Driver:

1. JDBC OCI

It uses Java native methods for using "C" functions provided by OCI libraries. It is platform dependent and is developed for Solaris and Windows (Windows 95, Windows NT 3.51 and 4.0). It requires the SQL*Net product installed on the client (from version 2.3).

 

2. JDBC THIN

The connection string to an Oracle Database differs depending on the driver you want to use:

In the following example it will be used the Thin Oracle Driver from a Java Applet.

// Import of Java JDBC package & used classes
import jdbc.sql.*;
import java.applet.Applet;

public class JdbcApplet extends Applet {
// Driver class name to load and Oracle driver name
static final String oracle_driver = "oracle.jdbc.dnlddriver.OracleDriver";
// Connection String
static final String connect_string = "jdbc:oracle:dnldthin:@oraserver:1521:oracl";
// User Name and password
String User;
String Passwd;
// Query to execute
static final String query = "select 'Hello World: ' || sysdate from dual";
// Button to push in order to execute the query
Button execute_button;
// Query result will be showed here
TextArea output;
// Database Connection
Connection conn = null;
// Environment Set Up
public void init () {
    // Graphics Layout
    setLayout (new BorderLayout ());
    execute_button = new Button ("Hello JDBC");
    add ("North", execute_button);
    output = new TextArea (10, 80);
    add ("Center", output);
    // Login and password comes from HTML
    User = getParameter("Login");
    Passwd = getParameter("Passwd");
}
// Each execution makes a connection and disconnection
public boolean action (Event ev, Object arg) {
if (ev.target == execute_button) {
    try {
        // Clear the output storage area
        output.setText ("");
        // See if we need to open the connection to the database
        if (conn == null) {
          // Load the JDBC driver
          output.appendText("Loading driver\n");
          Driver d = (jdbc.sql.Driver)Class.forName (oracle_driver).newInstance();
          // Connect to the database
          output.appendText ("Connecting...\n");
          conn = DriverManager.getConnection(connect_string, User, Passwd);
          output.appendText ("Connected\n");
        }
        // Create a statement & Execute the query
        Statement stmt = conn.createStatement ();
        output.appendText ("Executing query…\n");
        ResultSet rset = stmt.executeQuery (query);
        // Dump the result
        while (rset.next ())
          output.appendText(rset.getString(1)+"\n");
          // We're done
          output.appendText ("done.\n");
    } catch (Exception e) {
        // Error
        output.appendText (e.getMessage()+"\n");
    }
    return true;
}
return false;
}

The HTML page is the following:

<html>
<head>
<title>JDBC applet</title>
</head>
<body>
<h1>JDBC applet and Oracle database</h1>
<applet code="JdbcApplet" width=500 height=200>
<param name="Login" value="scott">
<param name="Passwd" value="tiger">
</applet>
</body>
</html>

Oracle provides another interface, besides JDBC, for accessing a Database: JSQL.

JSQL is a technology that permits to write SQL code inside a normal Java application. It uses a pre-compiler in order to translate the SQL calls in Java methods calls. The pre-compiler produces Java code with calls to JDBC.

The statements inside the syntax are: SELECT for data retrieval, INSERT, UPDATE, DELETE for data manipulation and calls to store procedures.

The possibility to embed SQL statements inside a programming language like "C", FORTRAN, COBOL, ADA has now been extended to the Java language. Nevertheless some differences exist between Java and the other languages:

• Java has a garbage-collection mechanism for correctly manage the memory
• With Java there is the possibility to represent the SQL NULL state with the Java NULL indicator
• The Java portability is extended to the applications using SQL.

JSQL translator is an utility that is able to transform JSQL clauses in standard Java code for accessing a Database using calls to a JDBC interface.

The result provided from a pre-compiler is a Java generic program that can be compiled in order to provide the byte-code.

The disadvantage of JSQL is represented by the introduction of static code inside a Java program. It is possible to manipulate dynamic SQL statements using the JDBC.

Each JSQL clause is associated to a connection type that represents the Database schema where the SQL statement will be executed. The pre-compiler ensures that each statement is valid inside the proper schema.

Each JSQL clause begin with the #sql token,

Each JSQL clause identifies, explicitly or implicitly, an object of connection-context type, which contains all the useful parameters that identify the connection type and the environment where the statement will be executed.

import jsql.runtime.*;
#sql context connection;

public class example
{
  static connection conn; // for a connection-context object
  public static void main (String argv[]) 
  throws SQLException {
    conn = new connection("scott/tiger"); // open database connection 
    int num_dept; 
    /* Function call*/
    #sql (conn) num_dept = { VALUES( EVALUATION(10) ) }; 
    stampa(num_dept, 2);
  }

  static void stampa (int num_dept, int num_emp)
      throws SQLException  {
    #sql iterator iterazione (Int dept, String ename);
    /* We use the variable iter as a result container */
    iterazione iter;

#sql (recs) iter =
               { SELECT DEPTNO AS "DEPT" ,ENAME AS "NAME"
                  FROM EMP 
                  WHERE DEPTNO = :num_dept 
                   AND EMPNO   = :num_emp 
                  ORDER BY DEPTNO};
while (iter.next()) {
      System.out.println( iter.dept() + " has name " + iter.name() ); 
    }
  }
}

There are several advantages compared to the JDBC:

• JSQL is more concise compared to Java calls to JDBC
• It is easier to write and to maintain JSQL programs
• The data type matching between Java and SQL is done efficiently by the pre-compiler
• The JSQL programs client side are truly "Run Everywhere"
• The JSQL is built on top of the JDBC standard and connects to all the servers supporting JDBC.

JDBC technology allows for building powerful Internet multi-tier applications. Although it has been only recently introduced to the market, the continuous evolution of the Java environment has made possible to develop robust network applications.

In this paper we presented some of the JDBC architectures using the Oracle driver implementation.

In conclusion, we can say that the Database access using JDBC is simple and is becoming a standard (at least in the Java world). Furthermore the possibility to manage easily the Database could realise the Java ambition to be the standard internet programming language.