Chapter 4:

Database Connectivity
4.1 JDBC Architecture

• The term JDBC stands for Java Database Connectivity

• JDBC is a specification from Sun microsystems
• JDBC is an API(Application programming interface) in Java that helps users to interact or
communicate with various databases
• The classes and interfaces of JDBC API allow the application to send the request to the
specified database
• Using JDBC, we can write programs required to access databases
• JDBC API uses JDBC drivers to connect with the database.
• JDBC and the database driver are capable of accessing databases and spreadsheets
• JDBC API is also helpful in accessing the enterprise data stored in a relational database(RDB)
4.1 JDBC Architecture
4.1 JDBC Architecture

1. Application
• Application in JDBC is a Java applet or a Servlet that communicates with a data source

2. JDBC API
• JDBC API provides classes, methods, and interfaces that allow Java programs to execute SQL
statements and retrieve results from the database
• By the help of JDBC API, we can save, update, delete and fetch data from the database
• It is like Open Database Connectivity (ODBC) provided by Microsoft
• JDBC API to handle database using Java program and can perform 1) Connect to the database
2) Execute queries and update statements to the database and 3) Retrieve the result received
from the database
• Some important classes and interfaces defined in JDBC API are as follows:
DriverManager Driver Connection Statement
PreparedStatement CallableStatement ResultSet SQL data
4.1 JDBC Architecture

3. Driver Manager
• The Driver manager uses some database-specific drivers that effectively connect enterprise
applications to databases
• The JDBC API uses a driver manager and database-specific drivers
to provide transparent connectivity to heterogeneous databases
• The JDBC driver manager ensures that the correct driver
is used to access each data source
• The driver manager is capable of supporting multiple
concurrent drivers connected to multiple heterogeneous databases
4.JDBC drivers
• JDBC drivers help us to communicate with a data source through JDBC
• We need a JDBC driver that can intelligently interact with the respective data source
4.1 JDBC Architecture

There are two types of processing models in JDBC architecture: two-tier and three-tier
1. Two-tier model
• In this model, a Java application directly communicates with the data source
• JDBC driver provides communication between the application and the data source
• When a user sends a query to the data source, the answers to those queries are given to
the user in the form of results
2. Three-tier model
• In the three-tier model, the query of the user queries goes to the middle-tier services
• From the middle-tier service, the commands again reach the data source
• The results of the query go back to the middle tier
4.1.2 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:
1. JDBC-ODBC bridge driver
2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)
4.1.2 JDBC Driver Types

1. JDBC-ODBC bridge driver

• The JDBC-ODBC bridge driver uses ODBC driver to connect to the database
• The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls
• This is now discouraged because of thin driver
• Oracle does not support the JDBC-ODBC Bridge
from Java 8
• Oracle recommends that you use JDBC drivers
provided by the vendor of your database
instead of the JDBC-ODBC Bridge
Advantages:
• easy to use.
• can be easily connected to any database
Disadvantages:
• Performance degraded because JDBC method call is converted into the ODBC function
calls
• The ODBC driver needs to be installed on the client machine
4.1.2 JDBC Driver Types

2. Native-API driver
• The Native API driver uses the client-side libraries of the database
• The driver converts JDBC method calls into native calls of the database API
• It is not written entirely in java

Advantages:
• performance upgraded than JDBC-ODBC
bridge driver
Disadvantages:
• The Native driver needs to be installed on the each client machine
• The Vendor client library needs to be installed on client machine
4.1.2 JDBC Driver Types

3. Network Protocol driver

• The Network Protocol driver uses middleware (application server) that converts JDBC
calls directly or indirectly into the vendor-specific database Protocol
• It is fully written in java

Advantages:
• No client side library is required because of
application server that can perform many
tasks like auditing, load balancing, logging etc
Disadvantages:
• Network support is required on client machine
• Requires database-specific coding to be done in the middle tier
• Maintenance of Network Protocol driver becomes costly because it requires database-
specific coding to be done in the middle tier
4.1.2 JDBC Driver Types

4. Thin driver
• The thin driver converts JDBC calls directly into the vendor-specific database protocol
• That is why it is known as thin driver
• It is fully written in java

Advantages:
• Better performance than all other drivers
• No software is required at client side or server side
Disadvantages:
• Drivers depend on the Database
4.1.2 JDBC Configuration and Managing Connection

There are 5 steps to connect any java application with the database
using JDBC
• Register the Driver class
• Create connection
• Create statement
• Execute queries
• Close connection

1. Register the driver class

• The forName() method of Class class is used to register the driver class
• This method is used to dynamically load the driver class
• Since JDBC 4.0, explicitly registering the driver is optional. We just need to put vender's Jar in
the classpath, and then JDBC driver manager can detect and load the driver automatically
4.1.2 JDBC Configuration and Managing Connection

1. Register the driver class

• Syntax:
• Java program is loading oracle driver to esteblish database connection

2. Create the connection object

• The getConnection() method of DriverManager class is used to establish connection with the
database
• Syntax of getConnection() method

• Example to establish connection with the Oracle database

4.1.2 JDBC Configuration and Managing Connection

3. Create the Statement object

• The createStatement() method of Connection interface is used to create statement
• The object of statement is responsible to execute queries with the database
• Syntax of createStatement() method

• Example to create the statement object

4. Execute the query

• The executeQuery() method of Statement interface is used to execute queries to the database
• This method returns the object of ResultSet that can be used to get all the records of a table
• Syntax of executeQuery() method
4.1.2 JDBC Configuration and Managing Connection

4. Execute the query

• Example to execute query

5. Close the connection object

• By closing connection object statement and ResultSet will be closed automatically
• The close() method of Connection interface is used to close the connection
• Syntax of close() method
• Example to close connection
4.1.2 JDBC Configuration and Managing Connection

Java Database Connectivity with MySQL

1. Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.
2. Connection URL: The connection URL for the mysql database
is jdbc:mysql://localhost:3306/sonoo
3. Username: The default username for the mysql database is root.
4. Password: It is the password given by the user at the time of installing the mysql database

Create a table in the mysql database

4.1.2 JDBC Configuration and Managing Connection

Java Database Connectivity with MySQL

( Code )
4.1.3 Statements

• Once a connection is obtained we can interact with the database

• The JDBC Statement, CallableStatement, and PreparedStatement interfaces define the
methods and properties that enable you to send SQL or PL/SQL commands and receive data
from your database
• They also define methods that help bridge data type differences between Java and SQL data
types used in a database
4.1.3 Statements

Creating Statement Object

• Before you can use a Statement object to execute a SQL statement, you need to create one
using the Connection object's createStatement( ) method
• Once you've created a Statement object, you can then use it to
execute an SQL statement with one of its three execute methods
1. boolean execute (String SQL): Returns a boolean value of true if
a ResultSet object can be retrieved; otherwise, it returns false
- Use this method to execute SQL DDL statements or when you
need to use truly dynamic SQL
2. int executeUpdate (String SQL): Returns the number of rows affected by the execution of the
SQL statement
-Use this method to execute SQL statements for which you expect to get a number of rows
affected - for example, an INSERT, UPDATE, or DELETE statement
4.1.3 Statements

3. ResultSet executeQuery (String SQL): Returns a ResultSet object

-Use this method when you expect to get a result set, as you would with a SELECT statement

Closing Statement Object

• Just as you close a Connection object to save database resources, for the same reason you
should also close the Statement object
4.1.3 Statements

The PreparedStatement Objects

• The PreparedStatement interface extends the Statement interface, which gives you added
functionality with a couple of advantages over a generic Statement object
• This statement gives you the flexibility of supplying arguments dynamically
• All parameters in JDBC are represented by the
? symbol, which is known as the parameter
marker
• You must supply values for every parameter
before executing the SQL statement
• If you forget to supply the values, you will
receive an SQLException
• All of the Statement object's methods for interacting with the database (a) execute(), (b)
executeQuery(), and (c) executeUpdate() also work with the PreparedStatement object
4.1.3 Statements

The PreparedStatement Objects

• However, the methods are modified to use SQL statements that can
input the parameters
• Just as you close a Statement object, for the same reason you should also close the
PreparedStatement object
• If you close the Connection object first, it will close the PreparedStatement object as well
• However, you should always explicitly close the PreparedStatement object to ensure proper
cleanup
4.1.3 Statements

The CallableStatement Objects

• Just as a Connection object creates the Statement and PreparedStatement objects, it also
creates the CallableStatement object, which would be used to execute a call to a database
stored procedure
• The String variable SQL, represents the stored procedure,
with parameter placeholders
• Using the CallableStatement objects is much like using the
PreparedStatement objects
• You must bind values to all the parameters before executing
the statement, or you will receive an SQLException
• Just as you close other Statement object, for the same reason you should also close the
CallableStatement object
4.1.4 Result Set

• The SQL statements that read data from a database query, return the data in a result set
• The SELECT statement is the standard way to select rows from a database and view them in a
result set
• The java.sql.ResultSet interface represents the result set of a database query
• A ResultSet object maintains a cursor that points to the current row in the result set
• The term "result set" refers to the row and column data contained in a ResultSet object
• The methods of the ResultSet interface can be broken down into three categories
1. Navigational methods − Used to move the cursor around
2. Get methods − Used to view the data in the columns of the current row being pointed by the
cursor
3. Update methods − Used to update the data in the columns of the current row. The updates
can then be updated in the underlying database as well
4.1.4 Result Set

• JDBC provides the following connection methods to create statements with desired ResultSet
• createStatement(int RSType, int RSConcurrency);
• prepareStatement(String SQL, int RSType, int RSConcurrency);
• prepareCall(String sql, int RSType, int RSConcurrency)

• Type of ResultSet
4.1.4 Result Set
Concurrency of ResultSet

Example:
try { Statement stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY); }
4.1.4 Result Set
Navigating a Result Set
• There are several methods in the ResultSet interface that involve moving the cursor
1. public void beforeFirst() throws SQLException: Moves the cursor just before the first row
2. public void afterLast() throws SQLException: Moves the cursor just after the last row
3. public boolean first() throws SQLException: Moves the cursor to the first row
4. public void last() throws SQLException: Moves the cursor to the last row
5. public boolean absolute(int row) throws SQLException: Moves the cursor to the specified
row
6. public boolean relative(int row) throws SQLException: Moves the cursor the given number
of rows forward or backward, from where it is currently pointing
7. public boolean previous() throws SQLException: Moves the cursor to the previous row. This
method returns false if the previous row is off the result set
8. public int getRow() throws SQLException: Returns the row number that the cursor is
pointing to
4.1.4 Result Set
Viewing a Result Set
• The ResultSet interface contains dozens of methods for getting the data of the current row

• There are get methods in the ResultSet interface for each of the eight Java primitive types, as
well as common types such as java.lang.String, java.lang.Object, and java.net.URL
• There are also methods for getting SQL data types java.sql.Date, java.sql.Time,
java.sql.TimeStamp, java.sql.Clob, and java.sql.Blob. Check the documentation for more
information about using these SQL data types
4.1.4 Result Set
Updating a Result Set
• The ResultSet interface contains a collection of update methods for updating the data of a
result set

• There are update methods for the eight primitive data types, as well as String, Object, URL,
and the SQL data types in the java.sql package
• Updating a row in the result set changes the columns of the current row in the ResultSet
object, but not in the underlying database
• To update your changes to the row in the database, you need to invoke one of the following
methods
4.1.5 Result Set
Updating a Result Set
1. public void updateRow(): Updates the current row by updating the corresponding row in the
database
2. public void deleteRow(): Deletes the current row from the database
3. public void refreshRow(): Refreshes the data in the result set to reflect any recent changes in
the database
4. public void cancelRowUpdates(): Cancels any updates made on the current row
5. public void insertRow(): Inserts a row into the database. This method can only be invoked
when the cursor is pointing to the insert row
4.1.5 SQL Exceptions
• Exception handling allows you to handle exceptional conditions such as program-defined
errors in a controlled fashion
• When an exception condition occurs, an exception is thrown
• JDBC Exception handling is very similar to the Java Exception handling but for JDBC, the most
common exception you'll deal with is java.sql.SQLException
SQLException Methods
• An SQLException can occur both in the driver and the database
• When such an exception occurs, an object of type SQLException will be passed to the catch
clause
1. getErrorCode( ): Gets the error number associated with the exception
2. getMessage( ): Gets the JDBC driver's error message for an error, handled by the driver or
gets the Oracle error number and message for a database error
3. getSQLState( ): Gets the XOPEN SQLstate string. For a JDBC driver error, no useful information
is returned from this method. For a database error, the five-digit XOPEN SQLstate code is
returned. This method can return null
4.1.5 SQL Exceptions
4. getNextException( ) : Gets the next Exception object in the exception chain
5. printStackTrace( ): Prints the current exception, or throwable, and it's backtrace to a
standard error stream.
6. printStackTrace(PrintStream s) : Prints this throwable and its backtrace to the print
stream you specify
7.printStackTrace(PrintWriter w): Prints this throwable and it's backtrace to the print writer
you specify

• By utilizing the information available from the Exception object, you can catch an
exception and continue your program appropriately
4.2 DDL and DML Operations using Java
• SQL commands are instructions. It is used to communicate with the database. It is also used to
perform specific tasks, functions, and queries of data
• SQL can perform various tasks like create a table, add data to tables, drop the table, modify
the table, set permission for users
Types of SQL Commands
• There are five types of SQL commands: DDL(Data Definition Language), DML(Data
Manipulation Language), DCL, TCL, and DQL

DDL(Data Definition Language)

• DDL changes the structure of the table like creating a table, deleting a table, altering a table,
etc.
• All the command of DDL are auto-committed that means it permanently save all the changes
in the database
4.2 DDL and DML Operations using Java
• Here are some commands that come under DDL: - CREATE, ALTER, DROP, TRUNCATE
• CREATE It is used to create a new table in the database
• Syntax:
• Example:
• DROP: It is used to delete both the structure and record stored in the table
• Syntax:
• Example:
• ALTER: It is used to alter the structure of the database. This change could be either to modify
the characteristics of an existing attribute or probably to add a new attribute.
• Syntax: To add a new column in the table:
• Syntax: To modify existing column in the table:
• Example:
4.2 DDL and DML Operations using Java
• TRUNCATE: It is used to delete all the rows from the table and free the space containing the
table
• Syntax:
• Example:

Data Manipulation Language (DML)

• DML commands are used to modify the database. It is responsible for all form of changes in
the database
• The command of DML is not auto-committed that means it can't permanently save all the
changes in the database. They can be rollback
• Mainly INSERT, UPDATE, DELETE commands are used.
• INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table
• Syntax:
4.2 DDL and DML Operations using Java
• UPDATE: This command is used to update or modify the value of a column in the table
• Syntax:

• DELETE: It is used to remove one or more row from a table.

• Syntax:

• Example:
4.2.2 Updateable Result Sets
• Whenever we create a ResultSet object that never allows us to update the database through
the ResultSet object, it allows retrieving the data only forward. Such type of ResultSet is
known as non-updatable and non-scrollable ResultSet

JDBC Updatable ResultSet

• To make the ResultSet object updatable and scrollable we must use ResultSet interface such as
TYPE_SCROLL_SENSITIVE and CONCUR_UPDATABLE

• We can insert the record in the database through ResultSet object using absolute() method,
but before inserting a record, you need to decide at which position you are inserting, since
the absolute() method takes a position as a parameter where it to be inserted
4.2.2 Updateable Result Sets
• Example:
4.2.3 Row Sets and Cached Row Sets
• A row set contains all data from a result set, but it can be disconnected from the database
• A row set may make a connection with a database and keep the connection open during its
life cycle, in which case it is called connected row set
• A row set may also make connection with a database, get data from it, and then close the
connection
• Such a row set is called disconnected row set.
• You can make changes to data in a disconnected row set, and commit changes to the database
later
• In JDBC, a row set is represented by the RowSet interface which is defined in
the javax.sql package.
• The javax.sql package is an extension of JDBC, besides the primary package java.sql.
• The RowSet interface extends the java.sql.ResultSet interface, which means you can use a row
set just like a result set.
4.2.3 Row Sets and Cached Row Sets
• The javax.sql.rowset package provides the following interfaces that extend
the RowSet interface:
• CachedRowSet stores data in memory so you can work on the data without keeping the
connection open all the time
• FilteredRowSet allows filtering data without having to write SQL SELECT queries
• JoinRowSet combines data from different RowSet objects, which is equivalent to SQL JOIN
queries.
• JdbcRowSet is a thin wrapper around a ResultSet that makes it possible to use the result
set as a JavaBeans component
• WebRowSet can read and write data in XML format, making it possible to transfer the data
through tiers in a web application.
4.2.3 Row Sets and Cached Row Sets
CachedRowSet
• A CachedRowSet object is a container for rows of data that caches its rows in memory, which
makes it possible to operate (scroll and update) without keeping the database connection
open all the time
• A CachedRowSet object makes use of a connection to the database only briefly: while it is
reading data to populate itself with rows, and again while it is committing changes to the
underlying database. So the rest of the time, a CachedRowSet object is disconnected, even
while its data is being modified. Hence it is called disconnected row set.
• Being disconnected, a CachedRowSet object is much leaner than a ResultSet object, making it
easier to pass a CachedRowSet object to another component in an application
• You can modify data in a CachedRowSet object, but the modifications are not immediately
reflected in the database
• You can create a CachedRowSet object either from a reference implementation provided by
JDK (default), or from an implementation of database vendor such as RowSetFactory
4.2.3 Row Sets and Cached Row Sets
Creating a CachedRowSet Object

String url = "jdbc:mysql://localhost:3306/college";

String username = "root";
String password = "password";

RowSetFactory factory = RowSetProvider.newFactory();

CachedRowSet rowset = factory.createCachedRowSet();

rowset.setUrl(url);
rowset.setUsername(username);
rowset.setPassword(password);
rowset.setCommand(sql);

rowset.execute();
4.2.4 Transactions
• Transaction represents a single unit of work
• The ACID properties describes the transaction management well. ACID stands for Atomicity,
Consistency, isolation and durability.
• Atomicity means either all successful or none
• Consistency ensures bringing the database from one consistent state to another consistent
state
• Isolation ensures that transaction is isolated from other transaction
• Durability means once a transaction has been committed, it will remain so, even in the event
of errors, power loss etc
Advantage
• It makes the performance fast because database is hit at the time of commit
4.2.4 Transactions
• In JDBC, Connection interface provides methods to manage transaction

• A transaction is a set of one or more statements that is executed as a unit, so either all of the
statements are executed, or none of the statements is executed
• The way to allow two or more statements to be grouped into a transaction is to disable the
autocommit mode
4.2.4 Transactions

• Example:
4.2.4 Transactions
Disabling Auto-Commit Mode
• When a connection is created, it is in auto-commit mode.
• This means that each individual SQL statement is treated as a transaction and is automatically
committed right after it is executed
• The way to allow two or more statements to be grouped into a transaction is to disable the
autocommit mode
• Example: con.setAutoCommit(false);

Committing Transactions
• After the auto-commit mode is disabled, no SQL statements are committed until you call the
method commit explicitly
• All statements executed after the previous call to the method commit are included in the
current transaction and committed together as a unit
• Example: con.commit();
4.2.4 Transactions
Rollback
• If you group update statements to a transaction, then the transaction either succeeds in its
entirety and it can be committed, or it fails somewhere in the middle
• In that case, you can carry out a rollback and the database automatically undoes the effect of
all updates that occurred since the last committed
Example: conn.rollback();
4.2.5 SQL Escapes
• The JDBC specification (like the ODBC specification) acknowledges the fact that some vendor
specific SQL may be required for certain RDBMS features
• To aid developers in writing portable JDBC applications across multiple database products, a
special escape syntax is used to specify the generic commands the developer wants to be run
• The JDBC driver translates these escape sequences into native syntax for its specific database
• The parsing of the sql statements for these escapes can be disabled
using Statement.setEscapeProcessing(false)
• Connection.nativeSQL(String sql) provides another way to have escapes processed. It
translates the given SQL to a SQL suitable for the PostgreSQL™ backend
• To use the JDBC escapes, you simply write your SQL replacing date/time literal values, outer
join and functions by the JDBC escape syntax
• Eg:
ResultSet rs = st.executeQuery("SELECT {fn week({d '2005-01-24'})}");
Or
ResultSet rs = st.executeQuery("SELECT extract(week from DATE '2005-01-24')");