Full HTML for

Basic foilset Overview of JDBC and its use with CORBA or Java RMI

Given by Nancy McCracken(Sangetta Aggarwal, Udayan Parvate, Tom Pulikal) at CPS616 Technologies of Information Age on Spring 1998. Foils prepared 4 Feb 98
Outside Index Summary of Material

JDBC provides a set of classes for Java with a standard SQL database access interface.
  • Goal is uniform access to a wide range of relational databases
Provides an API for database "drivers" to make actual connections and transactions to database products.
  • includes JDBC-ODBC bridge to standard ODBC drivers, a common interface for relational databases from C
JDBC is "low-level" interface, calling SQL commands directly but is meant to be a base for higher-level interfaces.
This talk has three sections:
  • Using JDBC in Java, with a 2 tier example
  • JDBC drivers
  • 3 tier examples

Table of Contents for full HTML of Overview of JDBC and its use with CORBA or Java RMI

 Denote Foils where Image Critical
Denote Foils where Image has important information
Denote Foils where HTML is sufficient
denotes presence of Additional linked information which is greyed out if missing
1 JDBC Java Database Connectivity
2 Link to Sun web page on JDBC JDBC Main Features
3 Traditional Web/RDBMS access Compared to using Java
4 JDBC Software Architecture
5 Driver Access Architecture
6 Suns API for JDBC JDBC API is Package java.sql
7 Driver Manager
8 Driver class creates Connection
9 SQL Statements
10 Statement Class
11 ResultSet class
12 Data from ResultSets
13 PreparedStatement class
14 CallableStatement class
15 Generalized example of getting results
16 A typical JDBC API usage sequence
17 Simple JDBC 2 Tier Applet - An Example
18 JDBC 2 Tier Applet
19 2 tier person database example for Foil 19 JDBC 2 Tier Applet
20 More Details on JDBC Driver Architectures
21 Driver Classification- I
22 Driver Classification - II
23 JDBC Driver API - I
24 JDBC Driver API - II
25 JDBC Driver API - III
26 List of vendors
27 Remote Database Access Current Options
28 mSQl jdbc driver for mSQl database
29 Weblogic jdbcKona driver for Oracle
30 3 tier Remote JDBC driver Architecture
31 PPT Slide
32 Remote jdbc Driver Client-side Description
33 Remote jdbc Driver Server-side Description - I
34 Remote jdbc Driver Server-side Description - II
35 JDBC 2+ Tier Architecture
36 Advantages of JDBC 2+ Tier Architecture
37 Java WebServer with Servlets
38 Programming Java Servlets
39 JDBC 3 Tier Servlet Example
40 JDBC Servlet Example (contd.)
41 JDBC Servlet Example (contd.)
42 Suns servlet database example for Foil 42 JDBC Servlet Example (contd.)
43 JDBC 3 Tier Example using RMI
44 3 tier person database example using RMI for Foil 44 The RMI Example
45 3-Tier JDBC Example Using CORBA
46 CORBA IDL Structure
47 Mapping CORBA IDL to Java - I
48 Mapping CORBA IDL To Java - II
49 An IDL Example - hello.idl
50 Compile An IDL file
51 Example Server - Hello
52 Example Server I - HelloServant
53 Methods in Server Class
54 Example Server II - HelloServer
55 Example Application Client
56 Example Client - Hello
57 3 tier person database example using CORBA IDL for Foil 57 JDBC Example using CORBA
58 Using JDBC-ODBC Bridge

Outside Index Summary of Material

HTML version of Basic Foils prepared 4 Feb 98

Foil 1 JDBC Java Database Connectivity

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Nancy McCracken
Assisted by Sangeeta Agrawaal,
Udayan Parvate, Tom Pulikal ,
and Chao-Wei Ou
updated January 1998
http://www.npac.syr.edu/projects/tutorials/JDBC
HTML version of Basic Foils prepared 4 Feb 98

Foil 2 JDBC Main Features

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index Link to Sun web page on JDBC
JDBC provides a set of classes for Java with a standard SQL database access interface.
  • Goal is uniform access to a wide range of relational databases
Provides an API for database "drivers" to make actual connections and transactions to database products.
  • includes JDBC-ODBC bridge to standard ODBC drivers, a common interface for relational databases from C
JDBC is "low-level" interface, calling SQL commands directly but is meant to be a base for higher-level interfaces.
This talk has three sections:
  • Using JDBC in Java, with a 2 tier example
  • JDBC drivers
  • 3 tier examples
HTML version of Basic Foils prepared 4 Feb 98

Foil 3 Traditional Web/RDBMS access Compared to using Java

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Traditional web access to the database was done with the use of CGI script accessing the database and creating output in the form of HTML document presented in WWW client.
The requests and responses were transmitted using HTTP protocol and there was no session notion and it was not able to preserve the database transaction logic
JDBC expands these options:
Java programs can be executed on client and on server side. In this case it is possible to create three tier client-server application.
Client applications can also access database directly.
HTML version of Basic Foils prepared 4 Feb 98

Foil 4 JDBC Software Architecture

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Java applications
Jdbc API
Jdbc Driver manager
Jdbc Driver
Jdbc Driver
DBMS
DBMS
JDBC API
JDBC Driver API
Jdbc-Odbc
Bridge
ODBC Driver
DBMS
HTML version of Basic Foils prepared 4 Feb 98

Foil 5 Driver Access Architecture

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Java application
Java applet or
HTML browser
Application Server (Java)
Jdbc Driver
DBMS
Two-tier Model
Jdbc Driver
DBMS
Three-tier Model
DBMS Proprietary Protocol
DBMS Proprietary Protocol
HTTP (RMI, CORBA or other) Protocol
HTML version of Basic Foils prepared 4 Feb 98

Foil 6 JDBC API is Package java.sql

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index Suns API for JDBC
The JDBC API is contained in JDK 1. Note that many of the classes such as "Statement" are interfaces, as they have abstract methods which must be supplied by a driver.
Interface Index
Callable Statement
Connection
DatabaseMetaData
Driver
PreparedStatement
ResultSet
ResultSetMetaData
Statement
Class Index
Date
DriverManager
DriverPropertyInfo
Numeric
Time
Timestamp
Types
Exception Index
DataTruncation
SQLException
SQLWarning
HTML version of Basic Foils prepared 4 Feb 98

Foil 7 Driver Manager

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The DriverManager class keeps track of the drivers that are available and handles establishing a connection between a driver and a particular database.
User loads the Driver explicitly Class.forName("acme.db.Driver"); This loads the driver class, which should itself call DriverManager.registerDriver with its instance and thus be available for a creating a connection.
HTML version of Basic Foils prepared 4 Feb 98

Foil 8 Driver class creates Connection

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The user requests the DriverManager class to make a connection to a particular database with user and password. The DriverManager then asks each available driver if it can handle that request until one can; the driver then makes the connection. String url = "jdbc:odbc:fred"; Connection conn = DriverManager.getConnection (url, "userid", "password");
The form of the url is <protocol>:<subprotocol>:<subname>
  • the protocol is always jdbc
  • the subprotocol is a keyword registered by the driver authors, odbc is a standard one
  • the subname format is very flexible and is designed by the driver author - just follow their examples!
HTML version of Basic Foils prepared 4 Feb 98

Foil 9 SQL Statements

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
createStatement makes an instance of Statement class
  • used for simple SQL statements with no paramters
prepareStatement makes an instance of PreparedStatement class, a subclass of Statement
  • used for SQL statements with one ore more IN parameters
  • or simple SQL statements that are executed frequently
prepareCall makes an instance of CallableStatement
  • used for calls to stored procedures in the database
  • inherits methods for IN parameters from PreparedStatement
  • also has methods for OUT and INOUT parameters
All the above statements are executed in "autocommit" mode for individual SQL statements. Transactions can be programmed with explicit methods commit and rollback.
HTML version of Basic Foils prepared 4 Feb 98

Foil 10 Statement Class

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Use connection to send SQL statements to database: Statement stmt = conn.createStatement( ); ResultSet rs = stmt.executeQuery("select * from table1"); The return value of the method executeQuery is a single result set.
The method executeUpdate is used for
  • insert, update and delete statements, which return no. of rows affected by change
  • DDL statements such as create table, drop table, . . .
The method execute is used for SQL statements that may return more than one result set or update count.
HTML version of Basic Foils prepared 4 Feb 98

Foil 11 ResultSet class

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
A ResultSet contains all the rows which satisfied the conditions of an SQL statement.
The cursor of a ResultSet is initially pointed before the first row, the method next( ) moves the cursor down one row. ResultSet r = stmt.executeQuery("select a,b,c from table1"); while (r.next( )) { //print values for current row int i = r.getInt("a"); double f = r.getDouble("b"); String s = r.getString("c"); System.out.println("row" + i + f + s); }
HTML version of Basic Foils prepared 4 Feb 98

Foil 12 Data from ResultSets

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The getxxx methods, where xxx is a Java type, can take column labels or column position numbers for an argument. (findColumn ("label") can return column position.)
JDBC API contains a table telling which getxxx methods can be used with particular SQL types.
ResultSet.getMetaData returns a ResultSetMetaData object which gives the number, types and properties of the ResultSet object's columns.
Can use streams on LONG SQL types.
For optional or multiple ResultSets, there are methods getResultSet, getUpdateCount, and getMoreResults.
HTML version of Basic Foils prepared 4 Feb 98

Foil 13 PreparedStatement class

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
PreparedStatements inherit all functionality of Statements.
It may have an IN parameter, which will be indicated by a "?" in the SQL statement.
Each IN parameter is specified in number order from left to right by a setxxx method. ResultSet rs = stmt.executeQuery("select * from tb1 where name = ?"); stmt.setString(1, "bill");
The setxxx methods will convert parameters to appropriate SQL types, or user may use more general stmt.setObject(1, value, "SQLtype");
HTML version of Basic Foils prepared 4 Feb 98

Foil 14 CallableStatement class

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Calling a stored procedure in the database CallableStatment cstmt = conn.prepareCall ("{call getTestData( ? , ? ) }");
The OUT parameters must be registered before the statement is executed cstmt.registerOutParameter(1, Types.NUM); cstmt.registerOutParameter(2, Types.VARCHAR2); cstmt.executeQuery( ); int I = cstmt.getInt(1); String s = cstmt.getString(2);
HTML version of Basic Foils prepared 4 Feb 98

Foil 15 Generalized example of getting results

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
MetaData classes provide information about the table and the results. String query = "select * from table1"; ResultSet rs = stmt.executeQuery(query); ResultSetMetaData rsmd = rs.getMetaData ( ); int columnCount = rsmd.getColumnCount ( ); for (int i = 1; i <= columnCount; i++) { String s = rsmd.getColumnTypeName (i); System.out.println ("Column" + i + " is type " + s); }
HTML version of Basic Foils prepared 4 Feb 98

Foil 16 A typical JDBC API usage sequence

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
DriverManager
getConnection()
method returns a
Connection Object
Connection
Statement
ResultSet
Column values
from Query results
method returns a
Statement object
createStatement()
executeQuery()
next()
method returns a
ResultSet Object
method gets the
next row
HTML version of Basic Foils prepared 4 Feb 98

Foil 17 Simple JDBC 2 Tier Applet - An Example

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The 2-Tier Applet consists of a Java applet which communicates with an Oracle server via JDBC.
The weblogic JDBC driver for Oracle is used. This driver has native code implementations of the java classes, requiring the applet to be on the same host as the driver.
The important java package to include is java.sql.*
Currently, the weblogic installation is on `osprey7'. The environment variable `classpath' has to include /usr/npac/weblogic/classes
The applet directly calls the java class which has the code for communicating with the Oracle server.
HTML version of Basic Foils prepared 4 Feb 98

Foil 18 JDBC 2 Tier Applet

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Implementation:
  • A new instance of the SQL driver is created.
    • Syntax: java.sql.Driver d = (java.sql.Driver)Class.forName("weblogic.jdbc.oci.Driver").newInstance();
  • A connection to the Oracle server is opened by supplying the user's name, password and the machine to connect to.
    • Syntax: Connection conn = DriverManager.getConnection("jdbc:weblogic:oracle:kayak", "username", "password");
HTML version of Basic Foils prepared 4 Feb 98

Foil 19 JDBC 2 Tier Applet

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index 2 tier person database example for Foil 19
The SQL query is created and executed.
  • Syntax: PreparedStatement pstmt = conn.prepareStatement("select * from datatable where NAME = ?");
  • ResultSet rs = pstmt.executeQuery();
The data is read from the database and passed back to the applet where it is displayed.
At the end of the session, the connection to the database is closed.
  • Syntax: conn.close();
Look at complete example.
HTML version of Basic Foils prepared 4 Feb 98

Foil 20 More Details on JDBC Driver Architectures

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The JDBC API defines Java classes to represent database connections, SQL statements, result sets, database metadata, etc. It allows a Java programmer to issue SQL statements and process the results. JDBC is the primary API for database access in Java.
The JDBC DriverAPI represents (which is also a part of package java.sql) the set of interface classes , any new jdbc compliant driver should implement. This imparts inherent extendibility for different types of databases.
The remote access to a database over the network completely depends upon the particular installation of the jdbc driver for that database.
HTML version of Basic Foils prepared 4 Feb 98

Foil 21 Driver Classification- I

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Following is a broad classification of the driver types :
  • JDBC-ODBC bridge provides JDBC access via most ODBC drivers. Some ODBC binary code and in many cases database client code must be loaded on each client machine that uses this driver, so this kind of driver is most appropriate on a corporate network, or for application server code written in Java in a 3-tier architecture.
  • Native-API partly-Java driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
HTML version of Basic Foils prepared 4 Feb 98

Foil 22 Driver Classification - II

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
A net-protocol all-Java driver translates JDBC calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect its all Java clients to many different databases. The specific protocol used depends on the vendor.
Native-protocol all-Java driver converts JDBC calls into the network protcol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver.
HTML version of Basic Foils prepared 4 Feb 98

Foil 23 JDBC Driver API - I

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Any jdbc driver has to implement the following interfaces as specified under the java.sql package and has to fulfil the specified responsibilities.
  • CallableStatement - Acts to give the effect of a stored procedure and allows to get result-set fields after executing the statement.
  • Connection - It represents a session with a specific database. Within the context of a Connection, SQL statements are executed and results are returned. This is the object returned by the getConnection method of the DriverManager class.
  • DatabaseMetaData - Provides methods to know about the database as a whole by returning a ResultSet object for each of the meta-data related queries.
HTML version of Basic Foils prepared 4 Feb 98

Foil 24 JDBC Driver API - II

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Driver - Responsible for determining whether a given JDBC URL is valid for the concerned database and registers itself with the DriverManager class.
PreparedStatement - A SQL statement is pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
ResultSet - It provides access to a table of data generated by executing a Statement. The table rows are retrieved in sequence. Within a row its column values can be accessed in any order. A ResultSet maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The 'next' method moves the cursor to the next row.
HTML version of Basic Foils prepared 4 Feb 98

Foil 25 JDBC Driver API - III

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
ResultSetMetaData - A ResultSetMetaData object can be used to find out about the types and properties of the columns in a ResultSet.
Statement - A Statement object is used for executing a static SQL statement and obtaining the results produced by it
HTML version of Basic Foils prepared 4 Feb 98

Foil 26 List of vendors

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Some of the leading database, connectivity and tools vendors who have already endorsed the JDBC API and the DBMS's they support are
Borland International Inc. - InterBase 4.0
IBM - DB2 Version 2
Imaginary Software Inc. - mSQL
Intersolv - Oracle and Sybase
Javasoft - Several dozen through ODBC drivers
Sybase Inc. - Sybase SQL server
Weblogic Inc., etc. - Oracle, Sybase, MS SQL server
HTML version of Basic Foils prepared 4 Feb 98

Foil 27 Remote Database Access Current Options

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
To Access a database residing on machine M2 from Machine M1 over the internet/intranet is dependent currently upon the specific database you wish to connect to, and the type of the jdbc driver you at your disposal.
  • In case the driver is a Native-protocol all-Java driver ( e.g mSql jdbc driver for mSql database ) , it can connect to the remote database directly since such kind of driver knows the net-protocol the particular database supports, and thus is able to contact and talk to the database server directly via opening socket connections over the network.
  • In case the driver is a Native-API partly-Java driver (e.g WebLogic jdbcKona driver for Oracle database ) , it can connect to the database if the driver and the database resides on the same machine.This is a limitation of this type of driver.
HTML version of Basic Foils prepared 4 Feb 98

Foil 28 mSQl jdbc driver for mSQl database

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
a lightweight SQL engine that supports a subset of ANSI SQL
For the mSQL implementation of JDBC, it looks for URL's in the form of
  • jdbc:msql://[host_addr]:[port]/[db_name]
This is a Native-protocol all-Java driver.
  • In the implementation of the Driver interface for this driver a direct socket connection is opened at the given host_addr and at the specified port. On success a Connection object is returned. Thus with this driver , a mSQL database on any machine can be accessed by any application running on any other machine provided that they are connected over the internet.
Shareware product that can be built on most Unix OS and Win32 OS
- one of the first database products to be supported by a Java API
- avialable from anonymous ftp ftp://bond.edu.au/pub/Minerva/msql
- More information at
http://www.imaginary.com/Java/
HTML version of Basic Foils prepared 4 Feb 98

Foil 29 Weblogic jdbcKona driver for Oracle

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Weblogic Inc. provides a Native-API partly-Java driver for Oracle RDBMS.
For the Oracle implementation of JDBC, it looks for URL's in the form of jdbc:weblogic:oracle:[host_addr]:[db_name]
Example URL jdbc:weblogic:oracle:t:ss2:DEMO
The driver classes should be present at run-time at every client. The database should reside on the same machine. The communication utility SQL_Net, a part of the Oracle suite, enables routing the database calls from the client to the server only if this utility is running on both the client and the database server which is a limitation in itself.
Test versions of their products can be downloaded from
http://www.weblogic.com
HTML version of Basic Foils prepared 4 Feb 98

Foil 30 3 tier Remote JDBC driver Architecture

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The remote jdbc driver has two parts :
  • Client side implementation ( JDBC driver API)
  • Server side implementation ( Middleware )
At the client side, the implementation will be as a database independent driver (with Router like functionality) which will route the requests made by the Application program with jdbc API to the concerned machine where the server implementation of this driver will be active.
At the database server side the implementation of this driver will consist of a server process which can actually use the specific JDBC driver for the local database on that machine and connect and talk to the database.
HTML version of Basic Foils prepared 4 Feb 98

Foil 31 PPT Slide

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Remote jdbc
Client
Remote jdbc
Server
Java Program
using JDBC API
Database2
Remote jdbc Driver Architecture (Three-Tier)
Local jdbc
Driver
Internet/intranet
M1
M2
Database1
Database3
(Possibly on M3)
HTML version of Basic Foils prepared 4 Feb 98

Foil 32 Remote jdbc Driver Client-side Description

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
It consists of a driver which implements the JDBC driver API (all the interfaces from the java.sql package). Thus the application program can still adhere to the JDBC user API but register and load the remote jdbc driver in case of any database instead of having the specific jdbc driver classes for that database to be present at the client machine.
Communication is both from the client-side to server-side (to request connection, prepare and execute query statements) and from server-side to the client-side (for getting the result-set for any query execution).
HTML version of Basic Foils prepared 4 Feb 98

Foil 33 Remote jdbc Driver Server-side Description - I

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
This will consist of a server process running at a specified port (own java server with socket implementation) or at a specified URL (implemented as a Java Web Server URL).
Will receive the method to be called from the client-side and executes the same with the specific jdbc driver for the concerned database which needs to be present at this side only
Should be able to support multiple connections to multiple databases on the same machine, multiple result sets at any time etc.
HTML version of Basic Foils prepared 4 Feb 98

Foil 34 Remote jdbc Driver Server-side Description - II

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Advantages
  • Allows access to a remote database using JDBC API from a java program transparently with a single driver existence at the client side.
  • Tries to attain uniformity at the client side over the use of the driver and the jdbc API used.
  • Will work with any type of driver (out of 4 categories mentioned previously) which only needs to be at the server side now.
Limitations
  • Implementation of the server side of this driver has many alternatives such as Socket communication/RMI/CORBA.
  • Involves network traffic at a higher magnitude which makes it prone to failure depending upon the reliability of the network.
HTML version of Basic Foils prepared 4 Feb 98

Foil 35 JDBC 2+ Tier Architecture

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
In the absence of pure 3 tier drivers, it is easier to have what is sometimes called "2 tier plus" architectures. In this picture, we have a 3 tier client/server picture, except that the client provides and HTML or Java user interface that can access JDBC user code on the server.
Remote
Client
Remote
Server
Java or HTML
GUI
Database2
Local jdbc
Driver
Internet/intranet
M1
M2
Database1
Database3
(Possibly on M3)
Java JDBC
user methods
HTML version of Basic Foils prepared 4 Feb 98

Foil 36 Advantages of JDBC 2+ Tier Architecture

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Advantages are :
  • avoids installations of any vendor specific libraries at the client side
  • uses general client/server solutions for distributed systems over the Internet/Intranet
From now on, we follow common practice to also call the 2+ tier architecture, a 3 tier architecture.
HTML version of Basic Foils prepared 4 Feb 98

Foil 37 Java WebServer with Servlets

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Java WebServer is an example of a general server solution, providing execution of Java programs called servlets.
Servlets are protocol- and platform-independent server side components that can be dynamically loaded like applets
  • or can be considered as server side counterpart to applets
  • or Java application components which are loaded, on demand
Servlets differ from other Java programs in that they are faceless ( without any graphics )
Servlets can be used for the user JDBC code and can be invoked from the Java or HTML client side interface in many ways, one of which is of the form:
  • http://host-name:port/servlet/servlet-name
HTML version of Basic Foils prepared 4 Feb 98

Foil 38 Programming Java Servlets

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Servlets :
  • must import the java.servlet package
  • must extend the GenericServlet class or HttpServlet class if implementing http features
  • must override the service method in the class
eg Hello World Servlet:
import java.io.*;
import java.servlet.*;
public class HelloWorldServlet extends GenericServlet {
public void service(ServletRequest req, ServletResponseres)
throws ServletException, IOException{
PrintStream out = newPrintStream(res.getOutputStream());
out.println("Hello world!");}
public String getServletInfo() {
return "Hello World Servlet"; }
}
HTML version of Basic Foils prepared 4 Feb 98

Foil 39 JDBC 3 Tier Servlet Example

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Demonstrates how to use servlets to access the database
This example uses Weblogic's JDBC driver to connect to the Oracle database on osprey7
The servlet allows to connect to a user's database and run queries on the same
It displays the query results as well as the driver details
The main java packages imported are java.servlet, java.servlet.http and java.sql among others.
HTML version of Basic Foils prepared 4 Feb 98

Foil 40 JDBC Servlet Example (contd.)

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Implementation :
public class DBServlet extends HttpServlet {
//overriding service method
public void service(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
//fetch the parameters from the form input
String username = req.getParameter("username");
String password = req.getParameter("password");
String query = req.getParameter("query");
PrintStream out = new PrintStream (res.getOutputStream() );
String url = "jdbc:weblogic:oracle"; // JDBC driver url
HTML version of Basic Foils prepared 4 Feb 98

Foil 41 JDBC Servlet Example (contd.)

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
try {
Class.forName("weblogic.jdbc.oci.Driver"); /* load the driver classes and get the connection to the database */
Connection con = DriverManager.getConnection(url,username,password);
//create and execute the query statement
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
dispResultSet(rs,out);
//close connections here
} Catch(SQLException ex) {
//print the exceptions caught
}
}
HTML version of Basic Foils prepared 4 Feb 98

Foil 42 JDBC Servlet Example (contd.)

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index Suns servlet database example for Foil 42
private void dispResultSet(ResultSet rs, PrintStream out)
throws SQLException {
//get the resultset metadata from the result set
ResultSetMetaData rsmd = rs.getMetaData();
//get the # of columns in the table and display the results
while (rs.next() ) {
for (int I;I <= numcols; I++) {
//display the values for each column
}
}
}
Show this example with an HTML page interface.
HTML version of Basic Foils prepared 4 Feb 98

Foil 43 JDBC 3 Tier Example using RMI

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The Java RMI architecture provides a way for Java user interfaces to call Java methods on a remote host.
IIOP
RDBMS
Client's Browser
RMI
RMIServer
User JDBC
DB Driver
RMI
Tier 1
Tier 2
Tier 3
HTML version of Basic Foils prepared 4 Feb 98

Foil 44 The RMI Example

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index 3 tier person database example using RMI for Foil 44
This example is organized so that the user interface is a Java applet on the client side of CORBA. The server side of CORBA is a Java class that has methods to call JDBC to access the database. The methods are structured as typical database "procedures".
This example also uses the WebLogic driver to access an Oracle database server, and the RMI server must be on the same host as the WebLogic driver.
Show example.
HTML version of Basic Foils prepared 4 Feb 98

Foil 45 3-Tier JDBC Example Using CORBA

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
IIOP
RDBMS
Client's Browser
ORB
Server
ORB
JDBC
Tier 1
Tier 2
Tier 3
CORBA provides a server for object-oriented programs. We can use it as the server for JDBC database procedures.
HTML version of Basic Foils prepared 4 Feb 98

Foil 46 CORBA IDL Structure

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
module <identifier>
{
<type declaration>
<constant declaration>
<exception declaration>
interface <identifier> [:<inheritance>]
{
<type declaration>
<constant declaration>
<exception declaration>
[<op_type>] <identifier> (<parameters>) [raises exception] [context];
:
};
interface <identifier> [:<inheritance>]
:
};
CORBA uses the Interface Definition Language (IDL) for the interface specification between the client and server.
HTML version of Basic Foils prepared 4 Feb 98

Foil 47 Mapping CORBA IDL to Java - I

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
boolean
char/wchar
octet
string/wstring
short/unsigned short
long/unsigned long
long long/unsigned long long
float
double
boolean
char
byte
java.lang.String
short
int
long
float
double
IDL Type
Java Type
To use CORBA from Java, the IDL interface specification must
be given a Java implementation.
HTML version of Basic Foils prepared 4 Feb 98

Foil 48 Mapping CORBA IDL To Java - II

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
module
interface
enum
struct
union
sequence/array
any
exception
typedef
package
interface with an additional helper
final class with enum values
final class with the same name
final class with the same name
array
class CORBA.any
class extends CORBA.UserException
Use original name
HTML version of Basic Foils prepared 4 Feb 98

Foil 49 An IDL Example - hello.idl

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
module HelloApp
{
interface hello;
{
string sayHello();
};
};
HTML version of Basic Foils prepared 4 Feb 98

Foil 50 Compile An IDL file

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
To use idltojava to compile the "hello" example IDL code as
idltojava -fclient -fserver hello.idl
Two sets of Java codes will be generated:
  • Client Java codes:
    • _helloStub.java
    • hello.java
    • hellohelper.java
    • helloHolder.java
  • Server Java code - _helloImplBase.java
HTML version of Basic Foils prepared 4 Feb 98

Foil 51 Example Server - Hello

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The example server consists of two classes, the servant and the server.
The servant, HelloServant, is the implementation of the Hello IDL interface.
Each Hello instance is implemented by a HelloServant instance.
The servant is a subclass of _HelloImplBase, which is generated by the idltojava compiler from the example IDL.
The servant contains one method for each IDL operation.
HTML version of Basic Foils prepared 4 Feb 98

Foil 52 Example Server I - HelloServant

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
import HelloApp.*;
import org.omg.CosNaming.*;
import org.omg.CosNaming.NamingContextPackage.*;
import org.omg.CORBA.*;
class HelloServant extends _HelloImplBase
{
public String sayHello()
{
return "\nHello world !!\n";
}
}
HTML version of Basic Foils prepared 4 Feb 98

Foil 53 Methods in Server Class

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Creates an ORB instance
Creates a servant instance (the implementation of one CORBA Hello object) and tells the ORB about it
Gets a CORBA object reference for a naming context in which to register the new CORBA object
Registers the new object in the naming context under the name "Hello"
Waits for invocations of the new object
HTML version of Basic Foils prepared 4 Feb 98

Foil 54 Example Server II - HelloServer

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
public class HelloServer {
public static void main(String args[]) {
try{
// create and initialize the ORB
ORB orb = ORB.init(args, null);
// create servant and register it with the ORB
HelloServant HelloRef = new HelloServant(); orb.connect(HelloRef);
// get the root naming context
org.omg.CORBA.Object objRef = orb.resolve_initial_references("NameService");
NamingContext ncRef = NamingContextHelper.narrow(objRef);
// bind the Object Reference in Naming
NameComponent nc = new NameComponent("Hello", "");
NameComponent path[] = {nc}; ncRef.rebind(path, HelloRef);
// wait for invocations from clients
java.lang.Object sync = new java.lang.Object();
synchronized (sync) {
sync.wait();
}
} catch (Exception e) {
System.err.println("ERROR: " + e);
e.printStackTrace(System.out);
}}}
HTML version of Basic Foils prepared 4 Feb 98

Foil 55 Example Application Client

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The example application client that follows:
Creates an ORB
Obtains a reference to the naming context
Looks up "Hello" in the naming context and receives a reference to that CORBA object
Invokes the object's sayHello() operation and prints the result
HTML version of Basic Foils prepared 4 Feb 98

Foil 56 Example Client - Hello

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
import HelloApp.*;
import org.omg.CosNaming.*;
import org.omg.CORBA.*;
public class HelloClient {
public static void main(String args[]) {
try{ // create and initialize the ORB
ORB orb = ORB.init(args, null);
// get the root naming context
org.omg.CORBA.Object objRef = orb.resolve_initial_references("NameService");
NamingContext ncRef = NamingContextHelper.narrow(objRef);
// resolve the Object Reference in Naming
NameComponent nc = new NameComponent("Hello", "");
NameComponent path[] = {nc};
Hello HelloRef = HelloHelper.narrow(ncRef.resolve(path));
// call the Hello server object and print results
String Hello = HelloRef.sayHello();
System.out.println(Hello);
} catch (Exception e) {
System.out.println("ERROR : " + e) ;
e.printStackTrace(System.out);
}}}
HTML version of Basic Foils prepared 4 Feb 98

Foil 57 JDBC Example using CORBA

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index 3 tier person database example using CORBA IDL for Foil 57
This example is organized in the same way as the previous RMI example. The user interface is the Java applet on the client side of CORBA. The server side of CORBA is a Java class that has methods to call JDBC to access the database.
This example also uses the WebLogic driver to access an Oracle database server, and as before, the CORBA server must be on the same host as the WebLogic driver.
Show example.
HTML version of Basic Foils prepared 4 Feb 98

Foil 58 Using JDBC-ODBC Bridge

From Overview of JDBC and its use with CORBA or Java RMI CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
This solution uses JDBC to connect to any ODBC driver. JDBC calls are translated to ODBC calls in the "JDBC/ODBC bridge" and the ODBC driver proceeds from there to access the database.
Specifics on driver syntax: Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver"); String url = "jdbc:odbc:my-dsn"; Connection con = DriverManager.getConnection (url, "my-user", "my-passwd");
© Northeast Parallel Architectures Center, Syracuse University, npac@npac.syr.edu

If you have any comments about this server, send e-mail to webmaster@npac.syr.edu.

Page produced by wwwfoil on Sun Nov 29 1998