Chapter 12 Introduction to JDBC

1
Chapter 12 Introduction to JDBC

• Type of JDBC driver
• Data Type/Method in JDBC
• setup Driver Manager and Connection for Database
• Setup ODBC-JDBC driver for Database
• lab

2
JDBC Architecture

• It consists of two layers of APIs
• DriverManager API
• DriverManager class is used to manage the driver
  the JDBC drivers that are installed on your
  system
• Driver API

3
JDBC Architecture
Java Application/Applet
JDBC DriverManager
JDBC-ODBC Bridge Driver
JDBC Driver
JDBC-Net Driver
ODBC Driver
4
JDBC Driver Types

• Can be classified into the following four types
• Type 1 JDBC-ODBC bridge driver
• do not access databasees directly, but they
  depend on ODBC drivers for actual access to
  databases
• Type 2 Native-API, partly-Java driver
• implemented using JAVA native methods written in
  C, which call a database-system native API
• Tyoe 2 drivers are not binary portable to other
  platforms.
• Example DB2 JDBC Application Driver

5
JDBC Driver Types

• Type 3 Net-protocol, all-Java driver
• implemented using pure Java methods, which use a
  database-system independent, network protocol for
  client/server commuication.
• Type 3 drivers are binary portable to other
  platform, callable from Java applets, and work in
  a client/server environment.
• If the network protocol used is supported by Web
  servers for example HTTP then Type 3 can used for
  Internet,otherwise they can only be used for
  intranet

6
JDBC Driver Types

• Type 4 Native-protocol, all-Java driver
• implemented using pure Java methods, which use a
  database-system independent, network protocol for
  client/server commuication.
• Type 4 drivers are binary portable to other
  platform, callable from Java applets, and work in
  a client/server environment.
• If the network protocol used is unlikely to be
  supported by Web Server,so type 4 can only be
  used for intranet access

7
SQL to JAVA Mapping
Standard SQL to Java Data Type Mapping ___________
__________________________________________________
__________ SQL Data Type Java Data
Type _____________________________________________
__________________________ TINYINT byte or
Integer SMALLINT short or Integer INTEGER int
or Integer BIGINT long or Long REAL float or
Float FLOAT double or Double DOUBLE double or
Double DECIMAL(P,S) java.math.BigDecimal
8
SQL to JAVA Mapping
Standard SQL to Java Data Type Mapping ___________
__________________________________________________
__________ SQL Data Type Java Data
Type _____________________________________________
__________________________ NUMERIC(P,S) java.math
.BigDecimal CHAR(N) String VARCHAR(N) String LO
NGVARCHAR String BIT boolean or
Boolean BINARY(N) byte VARBINARY(N) byte LO
NGVARBINARY byte
9
SQL to JAVA Mapping
Standard SQL to Java Data Type Mapping ___________
__________________________________________________
__________ SQL Data Type Java Data
Type _____________________________________________
__________________________ DATE java.sql.Date TI
ME java.sql.Time TIMESTAMP java.sql.Timestamp
10
JAVA to SQL Mapping
Standard Java to SQL Data Type Mapping ___________
__________________________________________________
__________ Java Data Type SQL Data
Type _____________________________________________
__________________________ byte TINYINT short
SMALLINT int or Integer INTEGER long or Long
BIGINT float or Float REAL double or
Double DOUBLE
11
JAVA to SQL Mapping
Standard Java to SQL Data Type Mapping ___________
__________________________________________________
__________ Java Data Type SQL Data
Type _____________________________________________
__________________________ java.math.BigDecimal
NUMERIC(P,S) String VARCHAR(N) or
LONGVARCHAR boolean or Boolean
BIT byte VARBINARY(N) or
LONGVARBINAR java.sql.Date DATE java.sql.Ti
me TIME java.sql.Timestamp TIMESTAMP
12
JDBC API
DriverManager
getConnection(url)
Connection
Connection
createStatement()
Statement
Statement
executeQuery(sql)
ResultSet
getXXX(index)
13
JDBC Architecture

• DriverManager.getConnection(url) return a
  Connection object that represents the connection
  established to a database identified by the url
  string
• Connection.createStatement() returns a Statement
  object that can be used to execute simple SQL
  statements.
• Statement.executeQuery(sql) executes the sql
  string that is a SQL SELECT statement and return
  a ResultSet object that represents the result of
  the query
• ResultSet.getXXX(index) retrieves the filed data
  from the current row for the column represented
  by index. XXX is the desired data type.

14
DriverManager Class

• DriverManager class maanges JDBC drivers and
  provides a uniform interface for establishing
  connections to databases.

while(driver.hasMoreElements()) Driver driver
(Driver)drivers.nextElement()
System.out.println(Driver driver.getClass()
.getName()) catch(Exception
e) System.out.println(e) System.exit(0)
import java.sql. import java.util. class
DriverApp public static void main(String
args) try Class.forName(ids.sql.IDSDriver)
Class.forName(sun.jdbc.odbc.JdbcOdbcDriver)
Emueration drivers DriverManager.getDrivers()
System.out.println(Avaliable drivers )
15
Driver Class

• .Driver class
• JDBC allows for multiple database drivers. Each
  driver must supply a class that implements the
  Driver interface.
• When a Driver class is first loaded, it must
  create an instance of itself and register with
  the DriverManager.
• The DriverManager will try to load as many
  drivers as it can find

16
Driver Class
import java.sql. import java.util. class
ConnectApp public static void main(String
args) try Class.forName(sun.jdbc.odbc.JdbcO
dbcDriver) String url jdbcodbcbank) Con
nection con DriverManager.getConnection(url,
sa, ) DatabaseMetaData meta
con.getMetaData()

• System.out.println(Database
• meta.getDatabaseProductName())
• System.out.println(version
• meta.getDatabaseProductVersion())
• System.out.println(User name
• meta.getUserName())
• con.close()
• catch(Exception e)
• System.exit(0)

17
Setting Up ODBC Drivers to Work with Microsoft
Access Databases

• Choose ODBC icon from the Control Panel
• click System DSN tab and then Add button to add
  new data source
• Enter Human as the name for the data source in
  the Data Source Name field.
• Click the Create Button to create database
  (human.mdb)

18
Setting Up ODBC Drivers to Work with Microsoft
Access Databases
19
Lab12

• Lab 12