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