Java Database Connectivity
JDBC
JDBC is an API with set of classes and interfaces
present in package java.sql
Thick Client
JAVA
APP DRIVER Supporting Software DB
Thin CLIENT SERVER
Client
JAVA
DRIVER DB
APP
CLIENT SERVER
JDBC
JDBC supports 4 types of Drivers
Thick Drivers:
JDBC-ODBC Bridge Driver.
Native-API Partly Java Driver
Thin Drivers:
Net-Protocol Fully Java Driver
Native-Protocol Fully Java Driver
JDBC-ODBC Bridge Driver (TYPE – I)
JDBC API access via one or more ODBC drivers.
Binary code be loaded on each client machine
JDBC-ODBC BRIDGE
J O
JAVA D D
DB
APP B B
C C
SERVER
CLIENT
ODBC
The Open Database Connectivity (ODBC) interface by Microsoft allows
applications to access data in database management systems (DBMS)
using SQL as a standard for accessing the data.
connection
DRIVER 1
DB
Request connection
CLIENT DRIVER 2
Application DB
DRIVER 3 connection
DB
ODBC Driver
Manager SERVER
Native-API Partly Java Driver (TYPE – II)
Binary code should be loaded on each client machine
Converts JDBC calls into calls on the client API for all databases
J ORACLE
OCI
JAVA D
App B
DB Library SQL
C Server
CLIENT SERVER
Net-Protocol Fully Java Driver (TYPE – III)
The specific protocol used depends on the vendor
Type 1 DB1
Middle
Protocol 1 ware Type 2 DB2
JAVA Type 3
Appli
App Driver
cation
Type 4
DB3
APPLICATION SERVER
CLIENT SERVER
Native-Protocol Fully Java Driver(TYPE – IV)
Direct call from the client to the DBMS server and is a practical solution
for Intranet access.
protocols are proprietary to database vendors, primary source for this
style of driver.
JAVA JDBC Data Base
App Driver
PROTOCOL
User Sockets & Streams
JDBC
The 4 steps to connect to database are:
Loading the Driver.
Getting the Connection to database.
Executing the SQL Query.
Closing the Connection.
Loading Driver
Syntax for loading the Class file
Class.forName(“Driver Class Name”);
Example for Jdbc-Odbc bridge driver:
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Class.forName("oracle.jdbc.driver.OracleDriver");
JDBC drivers by various Vendors
Jdbc-Odbc Bridge : sun.jdbc.odbc.JdbcOdbcDriver
Oracle : oracle.jdbc.driver.OracleDriver
DB2 : com.ibm.db2.jdbc.app.DB2Driver
Pointbase : com.pointbase.jdbc.JdbcUniversalDriver
Sybase : com.sybase.jdbc2.jdbc.SybDriver
SQL-Server : weblogic.jdbc.mssqlserver4.Driver
Data Source Name (DSN)
User DSN
Available for user who creates and stores in
registry
System DSN
Available for all users and stores in registry
Various ways of getting database connection
Connection con =
DriverManager.getConnection(“jdbc:odbc:dsnname”);
DriverManager.getConnection(“jdbc:odbc:dsnname”, “username”,
“password”);
DriverManager.getConnection(“jdbc:oracle:oci”, “username”,
“password”);
DriverManager.getConnection(“jdbc:oracle:thin:@ipaddress:port:s
erviceId”, “username”, “password”)
Statement
Statement
It is used to execute SQL statements
Prepared Statement
Used to prepare statements with place holders(?) to set
the values at run time
Callable Statement
Used to execute functions or procedures available in
data base
Statement
A Statement object is used for executing a static SQL
statement and obtaining the results produced by it.
Statement smt = con.createStatement();
ResultSet rs = smt.executeQuery(“Select_Queries”);
int n = smt.executeUpdate(“DML_Queries”);
boolean b = smt.execute(“Any_Query”);
ResultSet
Is an Object which stores data of the select statement result in
records and fields form.
By default it is Forward Only and Read Only.
Result Set Navigation and updating is possible from new API
version 1.2 onwards.
ResultSet
Navigating from one record to another
boolean b = rs.next()
Extracting values from ResultSet is possible either by Field Name or Field
Index
Column Index
int n=rs.getInt(1);
or
Column Name
int n = rs.get(“empID”);
String s=rs.getString(1);
or
String s=rs.getString(“empName”);
ResultSetMetaData
It is Data about Data of ResultSet like field names, no.
of Columns etc.
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
String fname = rsmd.getColumnName(int index);
String dn = rsmd.getColumnTypeName(int index);
PreparedStatement
A PreparedStatement object is used when an application plans to reuse a
statement multiple times.
The application prepares the SQL Statement it plans to use. Once prepared,
the application can specify values for parameters (if any) in the prepared
SQL statement.
PreparedStatement ps = con.prepareStatement(“Query with Place
Holders”);
Examples:
PreparedStatement ps = con.prepareStatement(“select * from emp
where empno=?”);
Place Holder
PreparedStatement
PreparedStatement ps = con.prepareStatement(“insert into emp
(empno,ename) values(?,?)”);
ps.setInt(1,102); Set values for
ps.setString(2, “Scott”); place holders
Executing the PreparedStatement
ResultSet rs=ps.executeQuery();
int n=ps.executeUpdate();
boolean b=ps.execute();
Statement vs PreparedStatement
A Statement object
Doesn’t contain a SQL statement at the time of creation.
Compiled and executed every time
A PreparedStatement object
Is a precompiled Statement
Contains a SQL statement at the time of creation.
Support for Place holders, (Ex: For data type blob, clob,
binaryStream)
Function
create or replace function DemoFunction (id number)
return varchar2 is
temp varchar2(20);
begin
select name into temp from Student where
rollno=id;
return temp;
end DemoFunction;
Procedure
create or replace procedure DemoProcedure(sname
varchar2, rno number) is
begin
insert into Student values(sname,rno);
end DemoProcedure;
CallableStatement
A CallableStatement is used to call stored procedures that return values. The
CallableStatement has methods for retrieving the return values of the stored
procedure.
CallableStatement cs = con.prepareCall(“{call prod(?,?)}”);
= con.prepareCall("{?=call fun(?)}");
Example:
CallableStatement cs = con.prepareCall(“{call emp.insert(?,?)}”);
CallableStatement
CallableStatement cs = con.prepareCall(“{?=call empSal(?)}”);
cs.registerOutParameter(1, java.sql.Types.INTEGER);
cs.setInt(2,102);
cs.executeUpdate();
int result = cs.getInt(1);
CallableStatement
java.sql.Types
java.sql.Types.DATE
java.sql.Types.DOUBLE
java.sql.Types.TIME
java.sql.Types.FLOAT
java.sql.Types.VARCHAR
java.sql.Types.INTEGER
java.sql.Types.TIMESTAMP
java.sql.Types.NUMERIC
java.sql.Types. CHAR
java.sql.Types.BOOLEAN
java.sql.Types.BLOB
java.sql.Types.CLOB
Example Program
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl",
"scott", "tiger");
CallableStatement cs = con.prepareCall("{?=call empSal(?)}");
cs.setInt(2, 7369);
cs.registerOutParameter(1,Types.INTEGER);
cs.executeUpdate();
int x=cs.getInt(1);
System.out.println(" The Value is: "+ x);
con.close();
