Full HTML for

Basic foilset Overview of JDBC: Java Database Connectivity

Given by Nancy McCracken,(Sangetta Aggarwal, Meryem Ispirli, Udayan Parvate, Tom Pulikal, Chao-Wei Ou) at Basic Information Track Computational Science Course CPS616 on Spring Semester 1999. Foils prepared May 19 99
Outside Index Summary of Material

JDBC provides a set of classes for Java with a standard SQL database access interface.
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: Java Database Connectivity

 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 JDBC use of SQL types for Foil 12 Data from ResultSets
13 Result Sets can be Scrollable
14 PreparedStatement class
15 CallableStatement class
16 Generalized example of getting results
17 A typical JDBC API usage sequence
18 Standard Extension Features coming in 2.0
19 2 tier person database example for Foil 19 Simple JDBC 2 Tier Applet - An Example
20 JDBC 2 Tier Applet
21 JDBC 2 Tier Applet
22 More Details on JDBC Driver Architectures
23 Driver Classification- I
24 Driver Classification - II
25 JDBC Driver API - I
26 JDBC Driver API - II
27 JDBC Driver API - III
28 List of vendors
29 Remote Database Access Current Options
30 mSQl jdbc driver for mSQl database
31 Weblogic jdbcKona driver for Oracle
32 3 tier Remote JDBC driver Architecture
33 PPT Slide
34 Remote jdbc Driver Client-side Description
35 Remote jdbc Driver Server-side Description - I
36 Remote jdbc Driver Server-side Description - II
37 JDBC 2+ Tier Architecture
38 Advantages of JDBC 2+ Tier Architecture
39 Java WebServer with Servlets
40 Programming Java Servlets
41 JDBC 3 Tier Servlet Example
42 JDBC Servlet Example (contd.)
43 JDBC Servlet Example (contd.)
44 Suns servlet database example for Foil 44 JDBC Servlet Example (contd.)
45 JDBC 3 Tier Example using RMI
46 3 tier person database example using RMI for Foil 46 The RMI Example
47 3-Tier JDBC Example Using CORBA
48 3 tier person database example using CORBA IDL for Foil 48 JDBC Example using CORBA
49 Using JDBC-ODBC Bridge
50 Accessing a Desktop Database

Outside Index Summary of Material

HTML version of Basic Foils prepared May 19 99

Foil 1 JDBC Java Database Connectivity

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
Nancy McCracken
Assisted by Sangeeta Agrawaal, Meryem Ispirli
Udayan Parvate, Tom Pulikal, and Chao-Wei Ou
updated January 1999
http://www.npac.syr.edu/projects/tutorials/JDBC
HTML version of Basic Foils prepared May 19 99

Foil 2 JDBC Main Features

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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.
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 May 19 99

Foil 3 Traditional Web/RDBMS access Compared to using Java

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 4 JDBC Software Architecture

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 5 Driver Access Architecture

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
Java application or applet
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 May 19 99

Foil 6 JDBC API is Package java.sql

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 7 Driver Manager

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 8 Driver class creates Connection

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 9 SQL Statements

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
createStatement makes an instance of Statement class
  • used for simple SQL statements with no parameters
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 May 19 99

Foil 10 Statement Class

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 11 ResultSet class

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 12 Data from ResultSets

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index JDBC use of SQL types for Foil 12
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 May 19 99

Foil 13 Result Sets can be Scrollable

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
New in JDBC 2.0 is that the statement can be given one of the scrollable constants: Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
Scrolling can also be insensitive depending on whether you plan to make changes to the Result Set. You can update, insert and delete rows into the result set and it will take place back in the database.
A scrollable Result Set, in addition to the method "rs.next", also has a method "rs.previous" to go back rows. You can set the cursor at the end with "rs.afterLast", and you can move the cursor to particular row numbers with "rs.absolute(int)".
HTML version of Basic Foils prepared May 19 99

Foil 14 PreparedStatement class

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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. stmt.setString(1, "bill"); ResultSet rs = stmt.executeQuery("select * from tb1 where name = ?");
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 May 19 99

Foil 15 CallableStatement class

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 16 Generalized example of getting results

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 17 A typical JDBC API usage sequence

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 18 Standard Extension Features coming in 2.0

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
Rowsets - a JavaBean component that can contain a set of rows from a result set.
JNDI - the Java Naming and Directory Interface will use logical names to connect to a database and driver.
Connection pooling - a cache of open connections.
Distributed Transaction Support - allows a JDBC driver to support a standard two-phase commit protocol used by the Java Transaction API. This will enable transactions to be put into JavaBeans.
HTML version of Basic Foils prepared May 19 99

Foil 19 Simple JDBC 2 Tier Applet - An Example

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index 2 tier person database example for Foil 19
The 2-Tier Applet consists of a Java applet which communicates with an Oracle server via JDBC.
The Oracle JDBC driver for Oracle8 is used. This driver has implementations of the java classes that can be downloaded with the applet.
The important java package to include is java.sql.*
When you compile the applet, you must include a path to the driver classes.
The applet directly calls the java class which has the code for communicating with the Oracle server.
HTML version of Basic Foils prepared May 19 99

Foil 20 JDBC 2 Tier Applet

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
Implementation:
  • A new instance of the SQL driver is created.
    • Syntax: java.sql.Driver d = (java.sql.Driver)Class.forName ("oracle.jdbc.driver.OracleDriver").newInstance();
  • A connection to the Oracle server is opened by supplying the user's name, password and the machine to connect to, if more than one is available.
    • Syntax: Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@carver.npac.syr.edu:1521:europe", "username", "password");
HTML version of Basic Foils prepared May 19 99

Foil 21 JDBC 2 Tier Applet

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
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 May 19 99

Foil 22 More Details on JDBC Driver Architectures

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 23 Driver Classification- I

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
Following is the classification of the driver types : (this information is directly from the Sun site at http://www.javasoft.com/products/jdbc)
  • 1. 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.
  • 2. 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 May 19 99

Foil 24 Driver Classification - II

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
3. 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. In general, this is the most flexible alternative.
4. 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 May 19 99

Foil 25 JDBC Driver API - I

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 26 JDBC Driver API - II

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 27 JDBC Driver API - III

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 28 List of vendors

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 (Check the Sun web site for a complete list.)
HTML version of Basic Foils prepared May 19 99

Foil 29 Remote Database Access Current Options

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 (Class 4) ( 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 (Class 2), 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, but remote access can be supplied by RMI or CORBA in a 3-tier solution.
HTML version of Basic Foils prepared May 19 99

Foil 30 mSQl jdbc driver for mSQl database

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 (class 4).
  • 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 May 19 99

Foil 31 Weblogic jdbcKona driver for Oracle

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
Weblogic Inc. provides a Native-API partly-Java driver for Oracle RDBMS (class 2).
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
This company is now merged with BEA.
HTML version of Basic Foils prepared May 19 99

Foil 32 3 tier Remote JDBC driver Architecture

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 33 PPT Slide

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 34 Remote jdbc Driver Client-side Description

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 35 Remote jdbc Driver Server-side Description - I

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 36 Remote jdbc Driver Server-side Description - II

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 37 JDBC 2+ Tier Architecture

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 38 Advantages of JDBC 2+ Tier Architecture

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 39 Java WebServer with Servlets

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
Java WebServer is an example of a general server solution, providing execution of Java programs called servlets.
  • Other web servers, including Apache, can also be configured to have 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 May 19 99

Foil 40 Programming Java Servlets

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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, ServletResponse res)
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 May 19 99

Foil 41 JDBC 3 Tier Servlet Example

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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.
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 May 19 99

Foil 42 JDBC Servlet Example (contd.)

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 43 JDBC Servlet Example (contd.)

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 44 JDBC Servlet Example (contd.)

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index Suns servlet database example for Foil 44
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 May 19 99

Foil 45 JDBC 3 Tier Example using RMI

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
The Java RMI architecture provides a way for Java user interfaces to call Java methods on a remote host.
TCP/IP (java socket)
RDBMS
Client's Browser
RMI
RMIServer
User JDBC
DB Driver
RMI
Tier 1
Tier 2
Tier 3
HTML version of Basic Foils prepared May 19 99

Foil 46 The RMI Example

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index 3 tier person database example using RMI for Foil 46
This example is organized so that the user interface is a Java applet on the client side of RMI. The server side of RMI 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 May 19 99

Foil 47 3-Tier JDBC Example Using CORBA

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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 May 19 99

Foil 48 JDBC Example using CORBA

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index 3 tier person database example using CORBA IDL for Foil 48
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.
CORBA uses the Interface Definition Language (IDL) to define the interface, and it uses the Visigenic ORB to provide the object request broker service between the two hosts.
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 May 19 99

Foil 49 Using JDBC-ODBC Bridge

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
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");
HTML version of Basic Foils prepared May 19 99

Foil 50 Accessing a Desktop Database

From Overview of JDBC: Java Database Connectivity Basic Information Track Computational Science Course CPS616 -- Spring Semester 1999. *
Full HTML Index
Most desktop databases currently require a JDBC solution using the JDBC/ODBC bridge.
The bridge does not operate across the network, so if you want network access to the desktop database, you must use a 3-tier solution.
One way to do this is to use an RMI/JDBC server. This RMI server provides the JDBC methods themselves in the RMI interface to be called from applets or applications on remote clients. An example of this is from the Mediation Project, http://dyade.inrialpes.fr/mediation/download.
© 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 Mon Jul 5 1999