Object Oriented Design and

Programming with Java

Module 5- Database Access

Department of Computer Science and Engineering, Dayananda Sagar University, Bengaluru

Database Access:

Department of Computer Science and Engineering, Dayananda Sagar University, Bengaluru

Database Access:
▪ The Concept of JDBC;
▪ JDBC Driver Types; JDBC Packages;
▪ A Brief Overview of the JDBC process;
▪ Database Connection;
▪ Associating the JDBC/ODBC Bridge with the Database;
▪ Statement Objects; Result Set
JDBC Concepts
▪ JDBC stands for Java Database Connectivity.
▪ JDBC is a Java API to connect and execute the query with the database.
▪ It is a part of JavaSE (Java Standard Edition).
▪ JDBC API uses JDBC drivers to connect with the database.

▪ J2EE(Java 2 Platform Enterprise Edition) is a platform-independent, Java-centric

environment from Sun/Oracle for developing, building and deploying Web-based
enterprise applications online.
▪ The J2EE platform consists of a set of services, APIs, and protocols that provide the
functionality for developing multi-tiered, Web-based applications.
▪ Open Database Connectivity (ODBC) is an open standard Application Programming
Interface (API) for accessing a database
JDBC API➔
provides
application-to-JDBC
Manager
JDBC Driver API➔ Connection
supports JDBC
Manager-to-Driver
Connection
JDBC is a SQL-level API.

It means that the JDBC allows to construct SQL statements and embed
them inside Java API calls.

JDBC Driver :The JDBC API is an implementation to interact a particular

database engine. This implementation is called.
1. DriverManager:
➢ This class manages a list of database drivers.
➢ Matches connection requests from the java application with the proper
database driver using communication subprotocol.
➢ The first driver that recognizes a certain subprotocol under JDBC will be used
to establish a database Connection.
2. Driver:
➢ This interface handles the communications with the database server.
➢ You will interact directly with Driver objects very rarely
3. Connection:
➢ This interface with all methods for contacting a database.
➢ The connection object represents communication context, i.e., all communication
with database is through connection object only.
4. Statement:
➢ You use objects created from this interface to submit the SQL statements to the
database.
➢ Some derived interfaces accept parameters in addition to executing stored
procedures.
5. ResultSet:
➢ These objects hold data retrieved from a database after you execute an SQL query
using Statement objects.
➢ It acts as an iterator to allow you to move through its data.
6. SQLException:
➢ handles any errors that occur in a database application.
JDBC drivers created by DBMS manufacturers have to:

1. Open a connection between the DBMS and the J2EE component.

2. Translate low-level equivalents of SQL statements sent by the J2EE component
in to messages that can be processed by the DBMS.
3. Return data that conforms to the JDBC specification to the JDBC driver.
4. Return information such as error messages that conforms to the JDBC
specification to the JDBC driver.
5. Provide transaction management routines that conform to the JDBC
specification.
6. Close the connection between the DBMS and the J2EE component.
JDBC Driver Types

JDBC Driver is a software component that enables java application to interact with
the database. There are 4 types of JDBC drivers:
Also known as
1. Type-1: JDBC-ODBC Bridge Driver, 1. Type 1: JDBC-ODBC Bridge Driver
2. Type-2: Native Driver, 2. Type 2: JDBC-Native API
3. Type-3: Network Protocol Driver or JDBC
3. Driver,
Typeand3: JDBC-Net Pure Java
4. Type-4: JDBC Driver 4. Type 4: 100% Pure Java
Type 1: JDBC-ODBC Bridge Driver
➢ a JDBC bridge is used to access ODBC (open Database
Connectivity) drivers installed on each client machine.
➢ Using ODBC, requires configuring on your system a Data Source
Name (DSN) that represents the target database
Type 1: JDBC-ODBC Bridge Driver

• a JDBC bridge is used to access ODBC drivers installed on each client machine.
• Using ODBC, requires configuring on your system a Data Source Name (DSN) that
represents the target database
• The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC
function calls. This is now discouraged because of thin driver.
Type 2: JDBC-Native API

➢ JDBC API calls are converted into native C/C++ API calls, which are unique to
the database.
➢ These drivers are typically provided by the database vendors
Type 2: JDBC-Native API

Disadvantage:
➢ The native driver needs to be installed on the each client
machine
➢ Loss of code portability
Type 3: JDBC-Net Pure Java/ JDBC Driver
a three-tier approach is used to access databases

The JDBC clients use standard network sockets to communicate with a middleware
application server.
Type 3: JDBC-Net Pure Java/ JDBC Driver

The JDBC clients use standard network sockets to communicate with a

middleware application server.
Type 4: JDBC Driver/ 100% Pure Java

➢ a pure Java-based driver communicates

directly with the vendor's database
through socket connection.
➢ This is the highest performance driver
available for the database and is usually
provided by the vendor itself.
Type 4: 100% Pure Java

➢ The thin driver converts JDBC calls directly into the vendor-specific
database protocol.
➢ That is why it is also known as thin driver. It is fully written in Java
language.
Which Driver should be Used?

➢ If you are accessing one type of database, such as Oracle, Sybase, or

IBM, the preferred driver type is 4.
➢ If your Java application is accessing multiple types of databases at the
same time, type 3 is the preferred driver.
➢ Type 2 drivers are useful in situations, where a type 3 or type 4 driver is
not available yet for your database.
➢ The type 1 driver is not considered a deployment-level driver, and is
typically used for development and testing purposes only.
The JDBC API is contained in two packages.

1. The first package is called java. Sql and contains core Java data objects of the JDBC
API.
➢ These include Java data objects that provide the basics for connecting to the
DBMS and interacting with data stored in the DBMS.
➢ java .sql is part of the J2SE.

2. The other package that contains the JDBC API is javax.sql, which extends java.sql
and is in the J2EE.
➢ Included in the javax.sql package are Java data objects that interact with Java
Naming and Directory Interface (JNDI) and Java data objects that manage
connection pooling, among other advanced JDBC features.
Connecting to Database:
Import JDBC Packages

import java.sql.* ; // for standard JDBC programs import

java.math.* ; // for BigDecimal and BigInteger support

Register/load JDBC Driver

❖ The Java's most common approach to register a driver is to use Class.forName()

method, to dynamically load the driver's class file into memory
Class.forName("oracle.jdbc.driver.OracleDriver");

❖ DriverManager.registerDriver()
when using a non-JDK compliant JVM, such as the one provided by Microsoft.
Driver myDriver = new oracle.jdbc.driver.OracleDriver();

DriverManager.registerDriver( myDriver );
Create Connection Object

1. If you have a host at TCP/IP address 192.0.0.1 with a host name of myhostname, and
2. your Oracle listener is configured to listen on port 1521, and
3. your database name is EMP,
then complete database URL would be:
jdbc:oracle:thin:@myhostname:1521:EMP
String URL = "jdbc:oracle:thin:@myhostname:1521:EMP";
String USER = "username";
String PASS = "password“;
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Using Only a Database URL
jdbc:oracle:driver:username/password@database
jdbc:oracle:thin:username/password@amd:1521:EMP
Closing JDBC Connections

➢ At the end of your JDBC program, it is required explicitly to close all

the connections to the database to end each database session.

➢ However, if you forget, Java's garbage collector will close the

connection when it cleans up stale objects.

conn.close();
Java Database Connectivity with 5 Steps
• Register the Driver class
Class.forName("oracle.jdbc.driver.OracleDriver");

• Create connection
Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@local
host:1521:xe","system","password");

• Create statement
Statement stmt=con.createStatement();

• Execute queries
ResultSet rs=stmt.executeQuery("select * from emp");

while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

• Close connection
con.close();
Database Connection
▪ A J2EE component does not directly connect to a DBMS. Instead, the J2EE component connects
with the JDBC driver that is associated with the DBMS.
▪ However, before this connection is made, the JDBC driver must be loaded and registered with
the DriverManager.
▪ The purpose of loading and registering the JDBC driver is to bring the JDBC driver into the Java
Virtual Machine (JVM). The JDBC driver is automatically registered with the DriverManager once
the JDBC driver is loaded and is therefore available to the JVM and can be used by J2EE
components.
▪ The Class.forName() method is used to load the JDBC driver.
▪ In this example, the JDBC/ODBC Bridge is the driver that is being loaded. You can replace the
JOBC/ODBC Bridge with the appropriate JDBC driver for the DBMS being used in your J2EE
application.
▪ The Class.forName() method throws a ClassNotFoundException if an error occurs when loading
the JDBC driver. Errors are trapped using the catch{} block whenever the JDBC driver is being
loaded.
34
Dept. of CSE, DSU
The Connection Object

▪ After the JDBC driver is successfully loaded and registered, the J2EE component
must connect to the database. The database must be associated with the JDBC
driver, which is usually performed by either the database administrator or the
systems administrator.
▪ The data source that the JDBC component will connect to is defined using URL
format. The URL consists of three parts. These are
▪ JDBC which indicates that the JDBC protocol is to be used to read the URL
▪ <subprotocol> which is the JDBC driver name.
▪ < subname> which is the name of the database.

Dept. of CSE, DSU 35

▪ The connection to the database is established by using one of three
getConnection() methods of the DriverManager object. The getConnection()
method requests access to the database from the DBMS.
▪ It is up to the DBMS to grant or reject access. A Connection object is
returned by the getConnection() method if access is granted; otherwise, the
getConnection() method throws a SQLException

Dept. of CSE, DSU 36

Dept. of CSE, DSU 37
Dept. of CSE, DSU 38
Dept. of CSE, DSU 39
Dept. of CSE, DSU 40
Dept. of CSE, DSU 41
Dept. of CSE, DSU 42
Dept. of CSE, DSU 43
Dept. of CSE, DSU 44
Dept. of CSE, DSU 45