New York University

Computer Science Department

Courant Institute of Mathematical Sciences

 

Java and Database Technology - JDBC

 

Course Title: Application Servers                                           Course Number: g22.3033-011

Instructor: Jean-Claude Franchitti                                            Session: 6

 

 

 


Introduction

 

 

The "Write Once, Run AnywhereTM" JavaTM 2 Platform is a safe, flexible, and complete cross-platform solution for developing robust Java applications for the Internet and corporate intranets.

 

The open and extensible Java Platform APIs are a set of essential interfaces that enable developers to build their Java applications and applets.

 

The Java 2 Platform provides uniform, industry-standard, seamless connectivity and interoperability with enterprise information assets.

 

The JDBCTM API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases.

 

The JDBC API provides a call-level API for SQL-based database access.

 

 


JDBC API Overview

 

 

The JDBC API makes it possible to do three things:

 

·        Establish a connection with a database or access any tabular data source

·        Send SQL statements

·        Process the results

 

 


JDBC Architecture

 

 

The JDBC API contains two major sets of interfaces: the first is the JDBC API for application writers, and the second is the lower-level JDBC driver API for driver writers.

 


Applications and applets can access databases via the JDBC API using pure Java JDBC technology-based drivers, as shown in the following figure:

 


                                                                (1)                                           (2)

 

 

1.     Direct-to-Database Pure Java Driver: This style of driver converts JDBC calls into the network protocol used directly by DBMSs, allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access.

 

2.     Pure Java Driver for Database Middleware: This style of driver translates JDBC calls into the middleware vendor's protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases.

 

 


JDBC Architecture (continued)

 

 


The graphic below illustrates JDBC connectivity using ODBC drivers and existing database client libraries.

 

 

 

 

 

 

 

 


JDBC-ODBC Bridge plus ODBC Driver:

 

This combination provides JDBC access via ODBC drivers. ODBC binary code--and in many cases, database client code-- must be loaded on each client machine that uses a JDBC-ODBC Bridge.

 

Sun provides a JDBC-ODBC Bridge driver, which is appropriate for experimental use and for situations in which no other driver is available.

 

Native-API Partly-Java Driver:

 

This style of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMSs, and requires that some binary code be loaded on each client machine.

 

 


JDBC - Java Database Connectivity Standard API

 

 

Necessity:

 

·        Java applications and applets

·        Open Standard

 

Based on X/Open SQL CLI (Call Level Interface)

Low-level API that supports basic SQL functionality

In order to pass JDBC compliance tests, a driver must support at least ANSI SQL92 Entry Level standard

 

 

JDBC API is expressed as a series of abstract Java interfaces that allow an application programmer to open connections to particular databases, execute SQL statements, and process the results.

 


 


 


JDBC - Java Database Connectivity Standard API (continued)

 

 

The most important JDBC API interfaces are:

 

 

·        java.sql.DriverManager which handles loading of drivers and provides support for creating new database connections

·        java.sql.Connection which represents a connection to a particular database

·        java.sql.Statement which acts as a container for executing a SQL statement on a given connection

·        java.sql.ResultSet which controls access to the row results of a given Statement

 

The java.sql.Statement interface has two important sub-types: java.sql.PreparedStatement for executing a pre-compiled SQL statement, and java.sql.CallableStatement for executing a call to a database stored procedure.

 

 

 


 



JDBC Package java.sql.*

 

 

·        JDBC drivers simply need to provide implementations of the abstract classes defined as in JDBC API.

 

·        JDBC Interfaces

·        Driver

·        Connection

·        Statement

·        ResultSet

·        PreparedStatement

·        CallableStatement

·        ResultSetMetaData

·        DatabaseMetaData

 

·        JDBC Classes

·        DriverManager

·        Types

·        DriverPropertyInfo

·        Date

·        Time

·        Timestamp

 

·        JDBC Exceptions

·        SQLException

·        SQLWarning

·        DataTruncation

 


Simple JDBC Select example

 

 

// export CLASSPATH=.:/usr/local/java/lib/classes.zip:/export/coursehome/faculties/dbmsfac/jdbc/sybjdbc.zip

// Driver is com.sybase.jdbc.SybDriver

// URL is jdbc:sybase:Tds:144.16.2.1:2001

 

import java.sql.*;

import java.awt.Graphics;

import java.util.Properties;

 

public class Simplet2 extends java.applet.Applet {

 

   public void init() {

      resize(600,425);

   }

 

   public void paint(Graphics g) {

      try {

        // Connect to the database at that URL.

//        String url = "jdbc:odbc:MSSQL";

        String url = "jdbc:sybase:Tds:144.16.2.1:2001";

        Properties props = new Properties();

        props.put("user", "sudhinp");

        props.put("password", "sudhin123");

 

       // Driver driver = new sun.jdbc.odbc.JdbcOdbcDriver();

Driver driver = (Driver) Class.forName("com.sybase.jdbc.SybeDriver").newInstance();

        Connection con = driver.connect(url, props);

 

         // retrieve data from database

         g.drawString("Let's retrieve some data from the database...", 10, 10);

 

         Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT name,type  FROM sysobjects where name like '%sys%'");

         g.drawString("Received results:", 10, 25);

 

         // display the result set

         // rs.next() returns false when there are no more rows

         g.drawString("type      name", 20, 45);

         g.drawString("----      -----------", 20, 55);

         int y = 80;

           int i = 0;

         while (rs.next() && (i<15)) {

            i++;

            String c1= rs.getString(1);

            String c2 = rs.getString(2);

            g.drawString(c2, 20, y );

            g.drawString(c1, 100, y );

            y = y + 15;

 

                    }

         stmt.close();

 

         con.close();

         } catch( Exception e ) {

         e.printStackTrace();

      }

   }

}

 


Simple Update example

 

 

import java.sql.*;

import java.awt.Graphics;

import java.util.Properties;

 

public class Update extends java.applet.Applet {

 

   public void init() {

      resize(600,425);

   }

 

   public void paint(Graphics g) {

      try {

        // Connect to the database at that URL.

        Driver driver = null;

                   int row_count = 0;

        String url = "jdbc:sybase:Tds:144.16.2.1:2001";

        Properties props = new Properties();

        props.put("user", "sudhinp");

        props.put("password", "");

;

         // register the driver with DriverManager

        driver = (Driver) Class.forName("com.sybase.jdbc.SybDriver").newInstance();

;

 

        Connection con = driver.connect(url, props);

         // retrieve data from database

         g.drawString("Let's update a row", 10, 10);

                   // The prepared statement takes two parameters.

                   PreparedStatement stmt = con.prepareStatement( "UPDATE course SET course_desc = ? WHERE course_id = ?");

 

 

          // First use the prepared statement to update

          stmt.setString(1, "NETWORKS WORKSHOP");

          stmt.setString(2, "CNET");

          row_count = stmt.executeUpdate();

    g.drawString("Number of rows affected : "+row_count, 10, 25);

          System.out.println("Updated \"count\" row OK.");

 

          stmt.close();

          con.close();

 

  } catch (java.lang.Exception ex) {

                   ex.printStackTrace();

  }

 }

}

 


Class DriverManager

 

 

·        Manage JDBC drivers.

·        Load the driver classes referenced in the "jdbc.drivers" system property .

·        This allows a user to customize the JDBC Drivers used by their applications.

·        For example in your ~/.hotjava/properties file you might specify-
jdbc.drivers=foo.bah.Driver:sybase.sql.SybDriver:oracle.sql.OraDriver

·        A program can also explicitly load JDBC drivers at any time using
Class.forName("my.sql.Driver");

·        getConnection()

·        JDBC URL has the format - jdbc:subprotocol:subname
Like - jdbc:sybase:Tds:144.14.2.1:2001

 


Interface Driver

 

·        Specific for a particular DBMS

·        acceptsURL(String)

·        connect(String url,Properties info)

·        getPropertyInfo()

·        jdbcCompliant()

·        getMajorVersion

·        getMinorVersion

 


Interface Connection

 

·        Represents a session with a specific database

·        createStatement()

·        prepareStatement()

·        prepareCall()

·        getMetaData()

·        Default auto commit

 


Interface Statement

 

·        Execute a SQL statement without parameters and obtaining the results produced by it.

·        executeQuery(String)

·        executeUpdate(String)

·        execute(String)

·        getResultSet,getMoreResults,getUpdateCount

·        setCursorName(String)

·        setMaxRows(int)

 


Interface PreparedStatement extends Statement

 

 

·        Pre-compile , store re-execute multiple times.

·        Support IN parameters -
PreparedStatement stmt = conn.prepareStatement( "UPDATE table3 SET m =? WHERE x = ?");

·        Set the IN parameters -
stmt.setString(1, "Hi");
stmt.setInt(2, 25);

·        Parameters are refered to sequentially, by number. The first parameter is 1

·        executeQuery,executeUpdate,execute

·        setABC methods setByte,setBinaryStream,setDouble,setLong,setTimestamp .. etc.

·        Method setObject(int parameterIndex, Object x)

 


Interface CallableStatement extends PreparedStatement

 

·        Execute SQL stored procedures with IN and OUT parameters.

·        sqlstring {?= call [,, ...]} or {call [,, ...]}
CallableStatement stmt = conn.prepareCall( "{call getTestData(?, ?)}");

·        setABC to set IN parameters

·        Register OUT parameters using registerOutParameter prior to executing the stored procedure.
stmt.registerOutParameter(1,java.sql.Types.TINYINT);

·        Use getABC to retrieve OUT values after execution.
byte x = stmt.getByte(1);

 


Interface ResultSet

 

·        ResultSet 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.

·        next()

·        getABC(int) and getABC(String)

·        getCursorName()

·        getMetaData()

 

 


Interface ResultSetMetaData

 

·        ResultSetMetaData object can be used to find out about the types and properties of the columns in a ResultSet.

·        getCatalogName(int)

·        getColumnCount()

·        getColumnDisplaySize(int)

·        getColumnName(int)

·        getColumnType(int)

·        getColumnTypeName(int)

·        getTableName(int)

·        getColumnLabel(int)

 


Interface DatabaseMetaData

 

·        Provides information about the database as a whole

·        Returns ResultSets

·        getTables()

·        getColumns()

·        getProcedures()

·        getProcedureColumns()

·        getTableTypes, getTablePrivileges, getCatalogs etc

·        getStringFunctions, getSystemFunctions, getTimeDateFunctions etc

 

 

Example :

 

DatabaseMetaData mymetadata = myconnection.getMetaData();

ResultSet rs= mymetadata.getColumns(null,null,"emp",null);

Choice col_names = new Choice();

while (rs.next()) {

 

 col_names.add(rs.getString(4));

 

 }

 

class Types

class Time extends java.util.Date

class Date extends java.util.Date

class Timestamp extends java.util.Date

JDBC Exceptions

 

·        class SQLException

·        class SQLWarning

·        class DataTruncation extends SQLWarning

 

Types of JDBC drivers

 

 

All Java driver , proprietary DBMS protocol

 

·        Uses proprietary network protocol of the particular DBMS

·        Client and server

·        Machine independent

·        Practical solution for Intranet

 


 



Types of JDBC drivers (continued)

 

 

Native-API partly-Java driver

·        The Java driver consists of some binary code be loaded on each client machine.

·        A shared library created for Java native methods using proprietary client API.

·        Machine dependent.

 


 


 


Types of JDBC drivers (continued)

 

Net-protocol all-Java driver

·        Translates JDBC calls into a DBMS-independent net protocol which is then translated to a particular DBMS protocol by a server.

·        Net server middleware

·        Standard JDBC driver

·        More secure

·        Less load on DBMS server

 

 


 

 



Types of JDBC drivers (continued)

 

JDBC-ODBC bridge

 

·        The 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.

 

Applets

 

·        Advantage: Local computation.

·        Constraints

·        User Security

·        Make Applets trustworthy

·        Performance considerations

·        Three-tier access to databases may be used, make calls to a "middle tier" of services on the net whose implementations in turn access databases. These calls might be made through RPC (remote procedure call) or through an ORB (object request broker)

 

 


Simple select (w/Applet)

 

 

// export CLASSPATH=.:/usr/local/java/lib/classes.zip:/export/coursehome/faculties/dbmsfac/jdbc/sybjdbc.zip

// Driver is com.sybase.jdbc.SybDriver

// URL is jdbc:sybase:Tds:144.16.2.1:2001

 

import java.sql.*;

import java.awt.Graphics;

import java.util.Properties;

 

public class Simplet2 extends java.applet.Applet {

 

   public void init() {

      resize(600,425);

   }

 

   public void paint(Graphics g) {

      try {

        // Connect to the database at that URL.

//        String url = "jdbc:odbc:MSSQL";

        String url = "jdbc:sybase:Tds:144.16.2.1:2001";

        Properties props = new Properties();

        props.put("user", "sudhinp");

        props.put("password", "sudhin123");

 

       // Driver driver = new sun.jdbc.odbc.JdbcOdbcDriver();

Driver driver = (Driver) Class.forName("com.sybase.jdbc.SybeDriver").newInstance();

        Connection con = driver.connect(url, props);

 

         // retrieve data from database

         g.drawString("Let's retrieve some data from the database...", 10, 10);

 

         Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT name,type  FROM sysobjects where name like '%sys%'");

         g.drawString("Received results:", 10, 25);

 

         // display the result set

         // rs.next() returns false when there are no more rows

         g.drawString("type      name", 20, 45);

         g.drawString("----      -----------", 20, 55);

         int y = 80;

      int i = 0;

         while (rs.next() && (i<15)) {

            i++;

            String c1= rs.getString(1);

            String c2 = rs.getString(2);

            g.drawString(c2, 20, y );

            g.drawString(c1, 100, y );

            y = y + 15;

 

             }

         stmt.close();

 

         con.close();

         } catch( Exception e ) {

         e.printStackTrace();

      }

   }

}

 


Simple Update (w/Applet)

 

 

import java.sql.*;

import java.awt.Graphics;

import java.util.Properties;

 

public class Update extends java.applet.Applet {

 

   public void init() {

      resize(600,425);

   }

 

   public void paint(Graphics g) {

      try {

        // Connect to the database at that URL.

        Driver driver = null;

            int row_count = 0;

        String url = "jdbc:sybase:Tds:144.16.2.1:2001";

        Properties props = new Properties();

        props.put("user", "sudhinp");

        props.put("password", "");

;

         // register the driver with DriverManager

        driver = (Driver) Class.forName("com.sybase.jdbc.SybDriver").newInstance();

;

 

        Connection con = driver.connect(url, props);

         // retrieve data from database

         g.drawString("Let's update a row", 10, 10);

            // The prepared statement takes two parameters.

            PreparedStatement stmt = con.prepareStatement( "UPDATE course SET course_desc = ? WHERE course_id = ?");

 

 

      // First use the prepared statement to update

      stmt.setString(1, "NETWORKS WORKSHOP");

      stmt.setString(2, "CNET");

      row_count = stmt.executeUpdate();

    g.drawString("Number of rows affected : "+row_count, 10, 25);

      System.out.println("Updated \"count\" row OK.");

 

      stmt.close();

      con.close();

 

  } catch (java.lang.Exception ex) {

            ex.printStackTrace();

  }

 }

}

 


Industry Momentum

 

 

Leading database, middleware and tool vendors have been building support for JDBC technology into many new products. This ensures that customers can build portable Java applications while choosing from a wide range of competitive products for the solution best suited to their needs.

 

The following list is a partial listing of companies that are shipping products with support for JDBC technology:

 

 

Vendor

DBMS Supported

Agave Software Design

Oracle, Sybase, Informix, others via ODBC

Asgard Software

Unisys A series DMSII database

Borland

InterBase 4.0

Caribou Lake Software

Ingres

Connect Software

Sybase, MS SQL Server

DataRamp

Several dozen through ODBC drivers

IBM

IBM DB2 Version 2

IDS Software

Oracle, Sybase, MS SQL Server, MS Access, Informix, Watcom, and others via ODBC

Imaginary

MSQL

InterSoft

Essentia

Intersolv

DB2, Ingres, Informix, Oracle, Microsoft SQL Server, Sybase

OpenLink

Oracle, Informix, Sybase, MS SQL Server, CA-Ingres, Progress, Unify, and Postgress95

SAS

SAS, and via SAS/ACCESS, Oracle, Informix, Ingres, and ADABAS

SCO

Informix, Oracle, Ingres, Sybase, Interbase

StormCloud Development

Any DBMS that is accessible via ODBC

Sybase, Inc

Sybase SQL Server, SQL Anywhere, Sybase IQ,Replication Server and more than 25 enterprise and Legacy database servers via Sybase

Symantec

Oracle, Sybase, MS SQL Server, MS Access, Watcom and others via ODBC

Sun/JavaSoft

Several dozen through ODBC drivers

Visigenic

Several dozen through ODBC drivers

WebLogic

Oracle, Sybase, MS SQL Server

Yard Software GmbH

YARD-SQL Database

 

 


Advantages of JDBC Technology

 

 

Leverage Existing Enterprise Data

 

With JDBC technology, businesses can continue to use their installed databases and access information easily — even it it is stored on different database management systems.

 

Reduced Development Time

The combination of the Java API and the JDBC API makes application development easy and economical. The JDBC API is simple to learn, easy to deploy, and inexpensive to maintain.

 

Zero Configuration for Network Computers

With the JDBC API, no configuration is required on the client side. With a driver written in the Java programming language, all the information needed to make a connection is completely defined by the JDBC URL or by a DataSource object registered with a Java Naming and Directory InterfaceTM (JNDI) naming service. Zero configuration for clients supports the network computing paradigm and centralizes software maintenance.

 


Key Features

 

 

Full Access to Metadata

The JDBC API provides metadata access that enables the development of sophisticated applications that need to understand the underlying facilities and capabilities of a specific database connection.

 

No Installation

A Pure JDBC technology-based driver does not require special installation; it is automatically downloaded as part of the applet that makes the JDBC calls.

 

Database Connection Identified by URL

JDBC technology exploits the advantages of Internet-standard URLs to identify database connections. The new JDBC 2.0 API adds an even better way to identify and connect to a data source, using a DataSource object, that makes code even more portable and easier to maintain.

 

Included in the Java Platform.

As a core part of the Java 2 Platform, the JDBC 2.0 core API is available anywhere that the platform is. This means that Java applications can truly write database applications once and access data anywhere.

 

Requirements

Software: The Java 2 Platform (either the Java 2 SDK, Standard Edition, or the Java 2 SDK, Enterprise Edition), an SQL database, and a JDBC technology-based driver for that database.

Hardware: Same as the Java 2 Platform.

 

Availability

The JDBC API specification is available on the Java Software web site. The JDBC API and a reference implementation of the JDBC-ODBC Bridge and related documentation are shipping now with the Java 2 SDK, Standard Edition, and the Java 2 SDK, Enterprise Edition.

 

 


References

 

 

·        JDBC Home http://splash.javasoft.com/jdbc

·        JDBC Driver http://www.connectsw.com

·        JDBC Driver http://www.openlinksw.com

·        JDBC-ODBC http://www.intersolv.com