Transcript Slide 1 - IT Knowledge Base

JDBC
• JDBC stands for Java Data Base Connectivity.
• JDBC is different from ODBC in that
– JDBC is written in Java (hence is platform
independent, object oriented robust etc.), while
– ODBC is written in C, is not object oriented.
• However, both JDBC and ODBC are based on
the X/Open SQL Command Level Interface
(CLI).
• Sun provides a JDBC ODBC bridge, which
enables one to connect painlessly to ODBC data
sources.
JDBC /ODBC Bridge
• We use MS Access, a 32 bit ODBC driver.
• First create an access database file e.g.
filename.mdb.
• Create a 32-bit ODBC driver via which
JAVA will communicate with the database
tables.
Steps to create 32-bit Driver
• Select the ODBC data source from control
panel; ODBC administrator interface will
open up.
• This allows the creation of several types of
Data Source Name (DSN).
• Select System DSN.
• Add Microsoft Access Driver.
• Name your DSN.
• Select the database.
• DSN is aware of the existence of MS Access
Database.
• Java cant communicate directly with ODBC
driver as this driver has been created using
different techniques other than Java.
• Hence, using appropriate java code and
associated Java driver’s a bridge must be set up
between 32-bit ODBC driver and JAVA UI.
• This is a JDBC:ODBC bridge.
Java Application UI
32-bit
ODBC
Access Driver
Java Driver Manager
Responsible for setting up
JDBC:ODBC bridge
Java Connectivity Model
Database
and
Tables
• Java application communicates with a
driver manager using java code.
• The DriverManager is a class that belongs
to Java.sql package.
• The driver manager communicates with
32-bit ODBC driver.
• Then 32-bit ODBC driver communicates
with Access database.
Java
Driver Manager
ODBC
System DSN
Database
and Tables
Creates a Connection Object which communicates with ODBC driver and
in turns spawns a Result Set object
to hold the record set returned by the query made to database table.
The ResultSet Object holds the recoreds retrieved from the database table.
Data from this result set object used in java application.
Driver Manager
• Java’s Driver Manager controls interaction
between UI and database.
• It can support multiple drivers to different
DBMS systems.
• It loads the requested driver and provides
support for managing database
connections.
•
•
•
•
It can performLocate a driver for a particular database.
Process JDBC initialization calls.
Provides entry point to JDBC functions for
each specific driver.
• Performs validation for JDBC calls.
• Once an ODBC driver has been created
and registered in Windows registry on the
computer, the java DriverManager class
can be used to invoke this driver.
• The DriverManager class works as the
interface between UI and ODBC driver.
• The DriverManager provides method to establish a connection to
specified database• getConnection(JDBC:ODBC:DSN Name,<userName>,<pwd>)
• On success it spawns a Connection object which represent a
physical connection to the database.
• After physical connection establishment, initialize the SQL queries.
• Recoreds retrieved from database which will be held in ResultSet
object.
• To see the data held within this object its each row must be read.
• next() in loop fetches one row at a time. It returns false when there
are no more rows in ResultSet object.
Java.sql package
• DriverManager class is in java.sql package.
• getConnection(URL,user,pwd) establish a connection.
On success, it returns a Connection object.
• A connection object represents a connection with a
database.
• Methods used with this objects are• void close()
to release database resources.
• Statement CreateStatement() returns a new statement
object that can be used to execute SQL statements.
• On error both throws SQLException.
Statement Interface
• Statement object execute SQL statements that
are static and obtain results in ResultSet object.
• Some methods are• ResultSet executeQuery(String SQLquery)
– executes a SQL statement that returns a single
ResultSet object. On error SQLException is thrown.
• int executeUpdate(String SQLquery)
– It executes SQL UPDATE, DELETE, or INSERT
statement. A row count is returned by these
statement. Zero returned if no row is returned. On
error SQLException is thrown.
PreparedStatement Interface
• It extends Statement interface.
• If an application requires multiple executions of any SQL
statement, it is pre-compiled and stored in a
PreparedStatement object. This object can then be used
by many times to execute the statement.
• Some methods are• executeQuery()
• executeUpdate()
– SQL statements that don’t return anything such as DDL can also
be executed.
• On Error SQLException is thrown
ResultSet Interface
• ResultSet object watches a single row of data at a time.
• This row of data is called its current row.
• When a SQL statement is re-executed the current
ResultSet is closed and a new ResultSet object is
created.
• Some methods are• void close()
• void getString()
– Gets the value of a column in the current row as a Java String.
• next()
– A ResultSet is initially positioned before its first row. The first call
to next() makes the first row the current row. The second call
makes the second row the current row, etc.
1. Choosing an appropriate driver
• For every specific database connection
established, an appropriate driver is
required to allow JDBC API to manage the
connection.
• Java program code is used to force a
particular driver to be loaded.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
2. Create a connection
• getConnection() of DriverManager class is
used.
• String url = “jdbc:odbc:DSNName”;
• Connection conn =
DriverManager.getConnection(url);
3. Perform database query
• A connection object needs to be used to send
SQL statement to database.
• After creating the connection, a SQL statement
has to be created.
• Statement stmt = conn.createStatement();
• The SQL statement will be sent to DBMS where
it is optimized and executed.
• ResultSet rs = stmt.executeQery(“Select * From
Emp”);
• int rows =stmt.executeUpdate(“Update Emp Set
Ename=“Pk” Where Ecode=‘101’”);
• The current row’s content are accessible
via getXXX() methods that allow extraction
of values from current row in ResultSet
object.
• Accessing columns of current row is
possible in 2 ways• By index number
• By name
By index
•
•
•
•
•
•
•
•
String getString(int columnIndex);
Boolean getBoolean(int columnIndex);
byte getByte(int columnIndex);
short getShort(int columnIndex);
int getInt(int columnIndex);
long getLong(int columnIndex);
Float getFloat(int columnIndex);
Double getDouble(int columnIndex);
By Name
•
•
•
•
•
•
•
•
String getString(String columnName);
Boolean getBoolean(String columnName);
byte getByte(String columnName);
short getShort(String columnName);
int getInt(String columnName);
long getLong(String columnName);
Float getFloat(String columnName);
Double getDouble(String columnName);
• Example
• Example
• Example