jdbc_presentation.Java Database Connectivity

JDBC

1. JDBC API

2. Introduction
JDBC is a set of programming APIs that allows easy
connection to a wide range of databases
JDBC is a database-independent API for accessing a
relational database
You pass SQL to Java methods in the JDBC classes (the
packages java.sql and javax.sql) and get back JDBC objects
JDBC is designed so simply that most database
programmers need learn only a few methods to
accomplish most of what they need to do

3. Types of Drivers
There are 4 types of JDBC Drivers
Type 1 - JDBC-ODBC Bridge
Type 2 - JDBC-Native Bridge
Type 3 - JDBC-Net Bridge
Type 4 - Direct JDBC Driver
Type 1 only runs on platforms where ODBC is available
ODBC must be configured separately
Type 2 Drivers map between a proprietary Database API and the
JDBC API
Type 3 Drivers are used with middleware products
Type 4 Drivers are written in Java
In most cases, type 4 drivers are preferred

4. Introduction
java.sql provides the API for accessing and processing
data stored in a data source (usually a relational database)
using the Java programming language
this package provides classes and interfaces to get both
database and result set metadata from the database server
javax.sql provides the API for server-side data source
access
This package supplements the java.sql package and, as of the
version 1.4 release, is included in the JDK

5. Architecture
The DriverManager loads JDBC
drivers in memory, and can also
be used to create
java.sql.Connection objects to
data sources
You can have more than one
driver and therefore more than
one database

6. JDBC Class Usage
DriverManager
Driver
Connection
Statement
ResultSet

7. Architecture
DriverManager: It is a “factory” class and can also
be used to create java.sql.Connection objects to
data sources (such as Oracle, MySQL, etc.).
Connection: This interface represents a connection
with a data source. The Connection object is used
for creating Statement, PreparedStatement, and
CallableStatement objects.
DatabaseMetaData: This interface provides detailed
information about the database as a whole. The
Connection object is used for creating
DatabaseMetaData objects.
Statement: This interface represents a static SQL
statement. It can be used to retrieve ResultSet
objects.
PreparedStatement: This interface extends
Statement and represents a precompiled SQL
statement. It can be used to retrieve ResultSet
objects.

8. Architecture
CallableStatement: This interface
represents a database stored
procedure. It can execute stored
procedures in a database server.
ResultSet: This interface represents a
database result set generated by using
SQL’s SELECT statement.
ResultSetMetaData: This interface
provides information about the types
and properties of the columns in a
ResultSet object.
SQLException: This class is an
exception class that provides
information on a database access
error or other errors.

9. JDBC Class Diagram
C
o
p
y
r
i
g
h
t
©
1
9
9
7
A
l
e
x

C
h
a
f
f
e
e
Whoa!

10. Data Types
Type JDBC Type JAVA Type
Suggested
Method
Boolean BIT boolean
Integer Data TINYINT byte
SMALLINT short getShort()
INTEGER int getInt()
BIGINT long
Floating-point
Data
REAL float
FLOAT
DOUBLE
double getDouble()
Binary Data BINARY
VARBINARY
LONGVARBINARY
byte[]
Textual Data CHAR
VARCHAR
LONGVARCHAR
String getString()
Date & Time DATE java.sql.Date getDate()
TIME
TIMESTAMP
java.sql.Time
java.sql.Timestamp
getTime()
getTimestamp()
Other types NUMERIC
DECIMAL
BigDecimal
CLOB Clob*
BLOB Blob*
ARRAY Array*
DISTINCT Mapping of
underlying type

STRUCT Struct*
REF Ref*
JAVA_OBJECT Underlying java class

11. Steps
JDBC programming can be explained in the following
simple steps:
Importing required packages
Registering the JDBC drivers
Opening a connection to a database
Creating a Statement/PreparedStatement/CallableStatement
object
Executing a SQL query and returning a ResultSet object
Processing the ResultSet object
Closing the ResultSet and Statement objects
Closing the connection

12. Step 1: Import the Required Packages
Before you can use the JDBC driver to get and use a
database connection, you must import the following
packages: java.sql and javax.sql.
import java.sql.*;
import javax.sql.*;

13. Step 2: Register the JDBC Drivers
A JDBC driver is a set of database-specific
implementations for the interfaces defined by JDBC
Oracle: First, the main driver class must be loaded into
the Java virtual machine (VM):
Class.forName("oracle.jdbc.driver.OracleDriver");
The specified driver (i.e., the
oracle.jdbc.driver.OracleDriver class) must implement
the java.sql.Driver interface
A class initializer (a static code block) within the
oracle.jdbc.driver.OracleDriver class registers the driver
with the java.sql.DriverManager

14. Step 2: Register the JDBC Drivers
MySQL:
Class.forName("com.mysql.jdbc.Driver");
A class initializer (a static code block) within the
com.mysql.jdbc.Driver class registers the driver with the
java.sql.DriverManager
Note that when loading a JDBC driver, you must make
sure that the database-specific driver API (usually a JAR
file) is in your CLASSPATH environment variable

15. Step 3: Opening a Connection to a Database
Next, we need to obtain a connection to the database
To get a connection object to a database, you need at least
three pieces of information: the database URL, the database
username, and the database user’s password
import java.sql.Connection;
import java.sql. DriverManager;
…
String dbURL = "jdbc:oracle:thin:@localhost:1521:kitty";
String dbUsername = "scott";
String dbPassword = "tiger";
Connection conn = DriverManager.getConnection(dbURL,
dbUsername, dbPassword);

16. Step 3: Opening a Connection to a Database
There is an alternate method for creating a database connection: first get a JDBC
driver, then use that driver to get a connection
import java.sql.Connection;
import java.sql. Driver;
import java.sql. DriverManager;
import java.util.Properties;
…
String dbURL = "jdbc:oracle:thin:@localhost:1521:kitty";
String dbUsername = "scott";
String dbPassword = "tiger";
Properties dbProps = new Properties();
String driverName = "oracle.jdbc.driver.OracleDriver";
Driver jdbcDriver = (Driver) Class.forName(driverName).newInstance();
dbProps.put("user", dbUsername);
dbProps.put("password", dbPassword);
Connection conn = jdbcDriver.connect(databaseURL, dbProps);

17. Step 3: Opening a Connection to a Database
Once you have a valid java.sql.Connection object, you
can create statement objects (such as Statement,
PreparedStatement, and CallableStatement)
Connection conn = <get-a-valid-Connection-object>;
Statement stmt = conn.createStatement();

18. Step 3: Opening a Connection to a Database
Example Database
create table MyEmployees (
id INT PRIMARY KEY,
firstName VARCHAR(20),
lastName VARCHAR(20),
title VARCHAR(20),
salary INT
);
insert into MyEmployees(id, firstName, lastName, title, salary)
values(60, 'Bill', 'Russel', 'CTO', 980000);
insert into MyEmployees(id, firstName, lastName, title, salary)
values(70, 'Alex', 'Baldwin', 'Software Engineer', 88000);

19. Step 5: Executing a Query and Returning a
ResultSet Object
Invoking the execute() method of the statement object
will execute the database-specific code necessary to issue
a SQL statement against the database and retrieve the
results (as a table)
String query = "SELECT id, lastName FROM MyEmployees";
ResultSet result = stmt.executeQuery(query);
The result is a table (as a ResultSet object) returned by
executing the SELECT statement
By iterating the result, we can get all of the selected
records

20. Step 6: Processing the ResultSet Object
ResultSet.next() returns a boolean: true if there is a next
row or record and false if not (meaning the end of the
data or set has been reached)
Conceptually, a pointer or cursor is positioned just
before the first row when the ResultSet is obtained
Invoking the next() method moves to the first row, then
the second, and so on

21. Step 6: Processing the ResultSet Object
Once positioned at a row, the application can get the data on a column-by-
column basis using the appropriate ResultSet.getXXX() method
if (rs.next()) {
String firstName = rs.getString(1);
String lastName = rs.getString(2);
String title = rs.getString(3);
int salary = rs.getInt(4);
}
or we may use the column names (instead of column positions):
if (rs.next()) {
String firstName = rs.getString("firstName");
String lastName = rs.getString("lastName");
String title = rs.getString("title");
int salary = rs.getInt("salary");
}

22. Step 7: Closing JDBC Objects
Releasing or closing JDBC resources (such as ResultSet,
Statement, PreparedStatement, and Connection objects)
immediately instead of waiting for it to happen on its own
can improve the overall performance
From a good software engineering point of view, you
should put close() statements in a finally clause

23. Step 7: Closing JDBC Objects
ResultSet has a close() method that releases the
ResultSet object’s database and JDBC resources
Another major reason to close the ResultSet objects
immediately after they are done is that we increase
concurrency; as long as the ResultSet object is open, the
DBMS internally holds a lock

24. Step 7: Closing JDBC Objects
public static void close(java.sql.ResultSet rs) {
if (rs == null) {
return;
}
try {
rs.close();
// result set is closed now
}
catch(Exception ignore) {
// ignore the exception
// could not close the result set
// cannot do much here
}
}

25. Step 7: Closing JDBC Objects
public static void close(java.sql.Statement stmt) {
if (stmt == null) {
return;
}
try {
stmt.close();
// result set is closed now
}
catch(Exception ignore) {
// ignore the exception
// could not close the statement
// can not do much here
}
}

26. Step 7: Closing JDBC Objects
public static void close(java.sql.Connection conn) {
if (conn == null) {
return;
}
try {
if (!conn.isClosed()) {
// close the connection-object
conn.close();
}
// connection object is closed now
}
catch(Exception ignore) {
// ignore the exception
// could not close the connection-object
// can not do much here
}
}

27. JDBC Errors/Exceptions
In JDBC, errors and exceptions are identified by the
java.sql.SQLException class (which extends the
java.lang.Exception class)
SQLException is a checked exception
Checked exceptions have to be caught (and handled
properly) or appear in a method that specifies in its
signature that it throws that kind of exception
When a JDBC object (such as Connection, Statement, or
ResultSet) encounters a serious error, it throws a
SQLException

28. JDBC Errors/Exceptions
JDBC give access to such information by providing
several levels of error conditions:
SQLException: SQLException is an exception that provides
information on a database access error or other errors.
SQLWarning: SQLWarnings are subclasses of SQLException,
but they represent nonfatal errors or unexpected conditions,
and as such, can be ignored.
BatchUpdateException: BatchUpdateException is an exception
thrown when an error occurs during a batch update
operation.
DataTruncation: DataTruncation is an exception that reports a
DataTruncation warning (on reads) or throws a
DataTruncation exception (on writes) when JDBC
unexpectedly truncates a data value

29. Metadata
DatabaseMetaData: Provides information about the
database as a whole.
ResultSetMetaData: Used to identify the types and
properties of the columns in a ResultSet object.
RowSetMetaData: An object that contains information
about the columns in a RowSet object.
ParameterMetaData: An object that can be used to get
information about the types and properties of the
parameters in a PreparedStatement object.
DriverPropertyInfo: Driver properties for making a
connection.

30. Example

31. More Details
The important methods of Statement interface are as
follows:
1) public ResultSet executeQuery(String sql): is used to execute SELECT query.
It returns the object of ResultSet.
2) public int executeUpdate(String sql): is used to execute specified query, it may
be create, drop, insert, update, delete etc.
3) public boolean execute(String sql): is used to execute queries that may return
multiple results.
4) public int[] executeBatch(): is used to execute batch of commands.

32. More Details
Example
import java.sql.*;  
class FetchRecord{  
public static void main(String args[])throws Exception{  
Class.forName("oracle.jdbc.driver.OracleDriver");  
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","system","oracle");  
Statement stmt=con.createStatement();  
  
//stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");  
//
int result=stmt.executeUpdate("update emp765 set name='Vimal',salary=10000 wh
ere id=33");  
int result=stmt.executeUpdate("delete from emp765 where id=33");  
System.out.println(result+" records affected");   con.close();  
}
}  

33. More Details
ResultSet

34. More Details
The PreparedStatement interface is a sub-interface of
Statement. It is used to execute parameterized query.
The important methods of PreparedStatement interface
are given below:

35. More Details
Example
PreparedStatement stmt=con.prepareStatement("insert into E
mp values(?,?)");  
stmt.setInt(1,101);
//1 specifies the first parameter in the query  
stmt.setString(2,"Ratan");    
int i=stmt.executeUpdate();  
System.out.println(i+" records inserted"); 

36. More Details
Example
PreparedStatement stmt=con.prepareStatement("update emp 
set name=? where id=?");  
stmt.setString(1,"Sonoo");
//1 specifies the first parameter in the query i.e. name  
stmt.setInt(2,101);  
  
int i=stmt.executeUpdate();  
System.out.println(i+" records updated");  

37. More Details
Example
PreparedStatement stmt=con.prepareStatement("delete from e
mp where id=?");  
stmt.setInt(1,101);  
  
int i=stmt.executeUpdate();  
System.out.println(i+" records deleted");  

38. More Details
CallableStatement interface is used to call the stored
procedures and functions
The differences between stored procedures and
functions are given below:

39. More Details
Syntax
CallableStatement stmt=con.prepareCall("{call myprocedur
e(?,?)}");  

40. More Details
Example
CallableStatement stmt=con.prepareCall("{call insertR(?,?)}"); 
 
stmt.setInt(1,1011);  
stmt.setString(2,"Amit");  
stmt.execute();   create or replace procedure "INSERTR"  
(id IN NUMBER,  name IN VARCHAR2)  
is  
begin  
insert into user420 values(id,name);  
end;  
/   

41. More Details
Example
CallableStatement stmt=con.prepareCall("{?
= call sum4(?,?)}");  
stmt.setInt(2,10);  
stmt.setInt(3,43);  
stmt.registerOutParameter(1,Types.INTEGER);  
stmt.execute(); 
create or replace function sum4  
(n1 in number,n2 in number)  
return number is
temp number(8);  
begin  
temp :=n1+n2;  
return temp;  
end;  
/ 

42. More Details
Instead of executing a single query, we can execute a
batch (group) of queries.
It makes the performance fast.
The java.sql.Statement and java.sql.PreparedStatement
interfaces provide methods for batch processing.
The required methods for batch processing are given
below:

43. More Details
Example
Statement stmt=con.createStatement();  
stmt.addBatch("insert into user420 values(190,'abhi',40000)");  
stmt.addBatch("insert into user420 values(191,'umesh',50000)");
    
stmt.executeBatch();
//executing the batch    
con.commit();

44. More Details
Example
PreparedStatement ps=con.prepareStatement("insert into user420 values(?,?,?)");    
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));  
while(true){    
System.out.println("enter id");  String s1=br.readLine();  
int id=Integer.parseInt(s1);    
System.out.println("enter name");  String name=br.readLine();    
System.out.println("enter salary");  String s3=br.readLine();  
int salary=Integer.parseInt(s3);    
ps.setInt(1,id);  ps.setString(2,name);  ps.setInt(3,salary);    
ps.addBatch();  
System.out.println("Want to add more records y/n");  
String ans=br.readLine();  
if(ans.equals("n")){  break;  }    
}  
ps.executeBatch();

45. Thank You!
Questions?