Chapter 2
Database
Programming
Gadekar M.J. 1
2.1 The Design of jdbc and jdbc Configuration :
Java Database Connectivity(JDBC) is an Application Programming
Interface(API) used to connect Java application with Database.
It provides the language with java database connectivity standard.
It is used to write programs required to access databases.
The JDBC API consists of classes and methods that are used to
perform various operations like: connect, read, write and store data
in the database.
Gadekar M.J. 2
Fig : JDBC connect Java Application to the database
Gadekar M.J. 3
Components of JDBC
There are generally four main components of JDBC through which
it can interact with a database.
They are as mentioned below:
JDBC API : It provides various methods and interfaces for easy
communication with the database.
It provides two packages as follows
1. [Link].*;
2. [Link].*;
It also provides a standard to connect a database to a client
application.
Gadekar M.J. 4
JDBC Driver Manager :
It loads database-specific driver in an application to establish a
connection with a database.
It is used to make a database-specific call to the database to process
the user request.
JDBC Test suite :
It is used to test the operation(such as insertion, deletion, updation)
being performed by JDBC Drivers.
JDBC drivers :
To communicate with a data source through JDBC, you need a
JDBC driver that intelligently communicates with the respective data
source.
Gadekar M.J. 5
Why Should We Use JDBC ?
We can use JDBC API to handle database using Java program and
can perform the following activities:
Connect to the database
Execute queries and update statements to the database
Retrieve the result received from the database.
Gadekar M.J. 6
2.2 Types of Drivers :
JDBC drivers are client-side adapters (installed on the client
machine, not on the server) that convert requests from Java programs
to a protocol that the DBMS can understand.
JDBC drivers implement the defined interfaces in the JDBC API, for
interacting with your database server.
The “ [Link] ” package that ships with JDK, contains various
classes with their behaviours defined and their actual implementaions
are done in third-party drivers.
Gadekar M.J. 7
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. Pure Java Driver for direct-to-database or Thin driver (fully
java driver)
Gadekar M.J. 8
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.
Gadekar M.J. 9
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.
Gadekar M.J. 10
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.
Gadekar M.J. 11
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.
Gadekar M.J. 12
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.
Gadekar M.J. 13
Advantage :
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.
Gadekar M.J. 14
4. Pure Java Driver for direct-to-database or Thin
driver (fully java driver)
The thin driver converts JDBC calls directly into the vendor-specific
database [Link] is why it is known as thin driver.
It is fully written in Java language.
Gadekar M.J. 15
Advantage :
Better performance than all other drivers.
No software is required at client side or server side.
Disadvantage :
Drivers depend on the Database.
Gadekar M.J. 16
2.3 Executing sql Statement and Query execution :
Types of Statements in JDBC
The statement interface is used to create SQL basic statements in Java
it provides methods to execute queries with the database.
There are different types of statements as follows:
CreateStatement
PreparedStatement
CallableStatement
Gadekar M.J. 17
Create a Statement :
It is generally used for general–purpose access to databases and is
useful while using static SQL statements at runtime.
Syntax:
Statement statement = [Link]();
Gadekar M.J. 18
Implementation: Once the Statement object is created, there are
three ways to execute it.
boolean execute(String SQL) :
If the ResultSet object is retrieved, then it returns true else false is
returned. Is used to execute SQL DDL statements or for dynamic
SQL.
int executeUpdate (String SQL) :
Returns number of rows that are affected by the execution of the
statement, used when you need a number for INSERT, DELETE or
UPDATE statements.
ResultSet executeQuery(String SQL) :
Returns a ResultSet object. Used similarly as SELECT is used in
SQL
Gadekar M.J. 19
Prepared Statement :
Prepared Statement represents a recompiled SQL statement, that can
be executed many times.
This accepts parametrized SQL queries. In this, “?” is used instead
of the parameter, one can pass the parameter dynamically by using
the methods of PREPARED STATEMENT at run time.
String query = "INSERT INTO people(name, age)VALUES(?, ?)";
Statement pstmt = [Link](query);
[Link](1,“AAA"); // where pstmt is an object name
[Link](2,25);
Gadekar M.J. 20
Callable Statement :
Callable Statement are stored procedures which are a group of
statements that we compile in the database for some task.
They are beneficial when we are dealing with multiple tables with
complex scenario & rather than sending multiple queries to the
database.
We can send the required data to the stored procedure & lower the
logic executed in the database server itself.
Gadekar M.J. 21
The Callable Statement interface provided by JDBC API helps in
executing stored procedures.
To prepare a CallableStatement
Syntax:
CallableStatement cstmt = [Link]("{call
Procedure_name(?, ?}");
Gadekar M.J. 22
2.4 Scrollable and updatable Result Sets :
A scrollable updatable result set maintains a cursor which can both
scroll and update rows.
We can make this object to move forward and backward direction by
passing either TYPE_SCROLL_INSENSITIVE or
TYPE_SCROLL_SENSITIVE in createStatement(int,int)
method as well as we can make this object as updatable by:
Statement stmt = [Link]
(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Gadekar M.J. 23
ResultSet Type Values
You can create a Statement that returns result sets in one of the
following types:
TYPE_FORWARD_ONLY : The result set is not scrollable
(default).
TYPE_SCROLL_INSENSITIVE : The result set is scrollable but
not sensitive to database changes.
TYPE_SCROLL_SENSITIVE : The result set is scrollable and
sensitive to database changes.
Gadekar M.J. 24
Example :
String sql = "SELECT * FROM student";
Statement statement = [Link]();
ResultSet result = [Link](sql);
Gadekar M.J. 25
2.5 Metadata – DatabaseMetadata and
ResultSetMetadata
Metadata is data about the data or documentation about the
information which is required by the users.
OR
The metadata means data about data i.e. we can get further
information from the data.
Gadekar M.J. 26
DatabaseMetaData interface provides methods to get meta data of
a database such as database product name, database product
version, driver name, name of total number of tables, name of total
number of views etc.
ResultSetMetaData interface is useful because it provides
methods to get metadata from the ResultSet object.
Gadekar M.J. 27
Methods of ResultSetMetaData interface
Method Description
public int getColumnCount()throws it returns the total number of
SQLException columns in the ResultSet
object.
public String getColumnName(int it returns the column name of
index)throws SQLException the specified column index.
public String it returns the column type
getColumnTypeName(int name for the specified index.
index)throws SQLException
public String getTableName(int it returns the table name for
index)throws SQLException the specified column index.
Gadekar M.J. 28
Methods of DatabaseMetaData interface
Method Description
public String getDriverName()throws it returns the name of the JDBC driver.
SQLException
public String getDriverVersion()throws it returns the version number of the JDBC
SQLException driver.
public String getUserName()throws it returns the username of the database.
SQLException
public String it returns the product name of the database.
getDatabaseProductName()throws
SQLException
public String it returns the product version of the database.
getDatabaseProductVersion()throws
SQLException
public ResultSet getTables(String catalog, it returns the description of the tables of the
String schemaPattern, String specified catalog. The table type can be
tableNamePattern, String[] types)throws TABLE, VIEW, ALIAS, SYSTEM TABLE,
SQLException SYNONYM etc.
Gadekar M.J. 29
2.6 Transaction – Commit(), rollback(),savePoint :
JDBC Connection is in auto-commit mode, which it is by default,
then every SQL statement is committed to the database upon its
completion.
Transactions enable you to control if, and when, changes are applied
to the database.
It treats a single SQL statement or a group of SQL statements as one
logical unit, and if any statement fails, the whole transaction fails.
Gadekar M.J. 30
JDBC, Connection interface provides methods to manage
transaction
Method Description
void setAutoCommit(boolean status) It is true bydefault means
each transaction is committed
bydefault.
void commit() commits the transaction.
void rollback() cancels the transaction.
Gadekar M.J. 31
Savepoints
Savepoint interface gives you the additional transactional
control.
Modern DBMS, support savepoints within their
environments such as Oracle's, PL/SQL.
When you set a savepoint you define a logical rollback
point within a transaction.
If an error occurs past a savepoint, you can use the
rollback method to undo either all the changes or only the
changes made after the savepoint.
Gadekar M.J. 32
Connection object has two new methods that help you manage
savepoints
Method Description
setSavepoint(String Defines a new savepoint. It also
savepointName) returns a Savepoint object..
releaseSavepoint(Savepoint Deletes a savepoint. Notice that it
savepointName) requires a Savepoint object as a
parameter. This object is usually
a savepoint generated by the
setSavepoint() method..
Gadekar M.J. 33
Example of JDBC
import [Link].*;
import [Link].*;
class seta1
{
public static void main(String[] args) throws
SQLException
{
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
Gadekar M.J. 34
try
{
[Link]("[Link]");
Conn = [Link]
("jdbc:postgresql:great","postgres","");
if(conn==null)
[Link]("Connection Failed");
else
Gadekar M.J. 35
Stmt = [Link]();
rs = [Link]("select * from stud");
while([Link]())
[Link]("Roll:"+[Link](1));
[Link]("\tName:"+[Link](2));
Gadekar M.J. 36
[Link]("\tPercent:"+[Link](3));
[Link]("");
}
[Link]();
}
}
catch(Exception e)
{
[Link](e);
}
}
}
Gadekar M.J. 37
Gadekar M.J. 38