JAVA DATABASE

CONNECTIVITY (JDBC)
Introduction
 Java DataBase Connectivity (JDBC) allows us
to access databases through Java programs.
 It provides Java classes and interfaces to fire
SQL and PL/SQL statements, process results
(if any), and perform other operations
common to databases
 It is also possible to access databases from
Java Server Pages.
 The classes and interfaces for database
connectivity are provided as a separate
package, [Link]..
JDBC Drivers
 Can access almost all types of databases
such as relational, object, and object-
relational.
 Accomplished using a set of Java interfaces,
each of which is implemented by different
vendors differently
 A Java class that provides interfaces to a
specific database is called JDBC driver
 Each database has its own set of JDBC
drivers.
 Those drivers are provided (generally freely)
by database vendors.
JDBC Drivers
 JDBC drivers are classified into four
categories depending upon the way they
work.
◦ JDBC-ODBC bridge (Type 1)
◦ Native-API, Partly Java (Type 2)
◦ Middleware, Pure Java (Type 3)
◦ Pure Java Driver (Type 4)
JDBC-ODBC bridge (Type 1)
 Cannot talk to the database directly
 Needs an intermediate ODBC (Open
DataBase Connectivity) driver, with which it
forms a kind of bridge
 The driver translates JDBC function calls to
ODBC method calls. ODBC makes use of
native libraries of the operating system and
is hence platform-dependent
 Type 1 driver is used for experimental
purposes or when no other JDBC driver is
available.
Native-API, Partly Java (Type
2)
 Similar to the Type 1
 Translates JDBC calls to database-specific
native API calls
 Not a pure Java driver, as it interfaces with
non-Java APIs that communicate with the
database
 Little bit faster than the earlier one
 Has limitations similar to the previous one
Middleware, Pure Java (Type 3)
 JDBC driver forwards the JDBC calls to some
middleware server
 Server acts as a gateway for multiple
(possibly different) database servers and
can use different database-specific
protocols to connect to different database
servers
 Hides the details of connections to the
database servers and makes it possible to
change the database servers without
affecting the client.
Pure Java Driver (Type 4)
 Communicate with the database directly by
making socket connections
 Has distinct advantages over other
mechanisms, in terms of performance and
development time.
 Since, it talks to the database server
directly, no other subsidiary driver is
needed. In this book, we shall use only the
Type 4 driver
JDBC Architecture
 The JDBC architecture is sometimes
classified as:
◦ two-tier and
 Type 2 and 4 drivers use two-tier
◦ three-tier.
 Type 1 and 3 use three-tier architecture
JDBC Architecture
Client computer

Application

Network
HTTP, CORBA,
Client computer RMI or other calls
Java Application
Java Application Server(middle-tier)
JDBC JDBC

Network Network
Request Response Database
Proprietary Protoco
Database Server Database Server
DBMS DBMS
JDBC Classes and Interfaces
 Java provides an API for accessing and
processing data stored in a data source
Class/Interface Description
DriverManager The basic service for managing a set of JDBC drivers
Connection A connection (session) to a specific database
Statement The object used for executing a static SQL statement and
returning the results it produces

ResultSet A table of data representing a database result set, which is

usually generated by executing a statement that queries the
database
PreparedStatement An object that represents a precompiled SQL statement
CallableStatement The interface used to execute SQL stored procedures.
DatabaseMetaData Comprehensive information about the database as a whole
ResultSetMetaData An object that can be used to get information about the types
and properties of the columns in a ResultSet object.
Basic Steps
 The following basic steps are followed to
work with JDBC:
◦ Loading a Driver
◦ Making a connection
◦ Executing an SQL statement
Loading a Driver
 Download an appropriate driver depending upon
the database you want to connect.
 The latest version, Type 4 MySQL JDBC driver, can
be downloaded from the following site:
◦ [Link]
s
 Download the .zip or .[Link] file containing the jar
(java archive) file [Link].
 The latest version Type 4 JDBC driver for Oracle
can be downloaded from the following site:
◦ [Link]
java/sqlj_jdbc/[Link]
Loading a Driver
 Once you have downloaded the
appropriate .jar file, put it in Tomcat’s lib
directory and restart the web server.
 If you are developing simple Java database
applications, put this .jar file in the
CLASSPATH environment variable.
 An instance of the driver has to be created
and registered with the DriverManager class
so that it can translate the JDBC call to the
appropriate database call.
Loading a Driver
 JDBC class DriverManager
 Interfaces between the Java application and
the JDBC driver.
 Manages the set of JDBC drivers installed on
the system
 Many different ways to load
 Use static forName() method of Class class
with a driver class name as an argument
◦ [Link]("[Link]");
Loading a Driver
 The instance created in this fashion must
register itself with the DriverManager class.
 For MySQl and Oracle
◦ [Link]("[Link]");
◦ [Link]("[Link]");
 Registration procedure may be done by
explicitly creating an instance and passing it
to the static registerDriver() method of the
DriverManager class.
◦ [Link](new
[Link]());
Making a connection
 The connection to the database can be
established using methods provided by the
DriverManager class.
 All methods return a Connection object on
successful creation of the connection.
◦ public static Connection getConnection(String url,
String login, String passwd)
◦ public static Connection getConnection(String url)
◦ public static Connection getConnection(String url,
Properties)
Connection object
 The Connection object encapsulates the
session/connection to a specific database
 It is used to fire SQL statements as well as
commit or roll back database transactions.
 It also allows us to collect useful information
about the database dynamically and to write
custom applications.
 getConnection() method takes is a database
URL
 URL always starts with jdbc:. The format of the
rest of the JDBC URL varies widely for different
databases.
Making a connection
 The format of the MySQL JDBC URL is as
follows:
◦ jdbc:mysql://[host]:[port]/[database]
 Suppose a MySQL database, test, is
running in a machine, uroy, at port 3306,
the corresponding URL will be
◦ jdbc:mysql://uroy:3306/test
 A database connection can be established
using this URL as follows:
◦ Connection con =
[Link]("jdbc:mysql://uro
y:3306/test", "root", "nbuser");
Making a connection
 Similarly, the following code segment creates a
database connection to the Oracle database mirora
running in the machine miroracle at port 1521.
◦ Connection con =
[Link]("jdbc:oracle:thin:@miroracl
[Link]mirora", "scott", "tiger");
 The second overloaded version of the
getConnection() method takes only a string
argument containing user name and password.
◦ basicURL?param1=value1&param2=value2…
 The following is an example of such a string
argument for the MySQL database.
◦ jdbc:mysql://uroy:3306/test?
user=root&password=nbuser
Making a connection
 Alternatively, parameters can be put in a
[Link] object and the object can
be passed to the getConnection() method.
 The following is an example using Properties.
◦ String url = "jdbc:mysql://uroy:3306/test";
◦ [Link] p = new
[Link]();
◦ [Link]("user", "root");
◦ [Link]("password", "nbuser");
◦ Connection con =
[Link](url, p);
Execute SQL statement
 The Connection interface provides methods for
obtaining different statement objects that are
used to fire SQL statements via the established
connection
 The Connection object can be used for other
purposes such as gathering database
information, and committing or rolling back a
transaction.
 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.
Simple Statements
 A Statement object is instantiated using the
createStatement() method on the Connection
object as follows:
◦ Statement stmt = [Link]();
 The following code segment first creates a table
named accounts.
◦ String create = "CREATE TABLE accounts (
" + " accNum integer primary key,
" + " holderName varchar(20),
" + " balance integer "
+ ")";
◦ [Link](create);

◦ Once the table is created, data can be inserted into it

using the following code segment.
Simple Statements
◦ String insert = "INSERT INTO accounts
VALUES(1,'Uttam K. Roy', 10000)";
◦ [Link](insert);
◦ insert = "INSERT INTO accounts
VALUES(2,'Bibhas Ch. Dhara', 20000)";
◦ [Link](insert);
executeQuery()
 Used to read data from database
 Result of the executeQuery method is
stored in an object of type ResultSet.
 To retrieve data from a column, methods of
the form getX() are used,
◦ String query = "SELECT * FROM accounts";
◦ ResultSet rs = [Link](query);
◦ while([Link]()) {
◦ [Link]([Link]("accNum"));
◦ [Link]([Link]("holderName"));
◦ [Link]([Link]("balance"));
◦}
execute()
 Used if type of statements (DDL, DML, DCL, or
DQL) is not known in advance
 Result can either be a ResultSet object or an
integer.
 Returns a Boolean value, which indicates the
return type.
 The return value true indicates that the result is a
ResultSet object, which can be obtained by
calling its getResultSet() method.
 On the other hand, if the return value is false, the
result is an update count, which can be obtained
by calling the getUpdateCount() method.
Atomic transaction
 executeUpdate() method is committed
automatically.
 This may lead to data inconsistency if a
series of related statements are executed
 This problem can be solved using the
autoCommit() method available on the
Connection object.
 First the autoCommit flag of the Connection
object is set to false.
 At the end of execution of all related
statements, the transaction is committed
Atomic transaction
 Looks as follows:
◦ [Link](false);
◦ //execute some statement
◦ //execute another statement
◦ //execute other statement
◦ [Link]();
executeBatch()
 Execute a set of related commands as a
whole
 Safe to clear the Statement object using the
method clearBatch() before adding any
command
 Once all commands are added,
executeBatch() is called to send them as a
unit to the database.
 The DBMS executes the commands in the
order in which they were added
Pre-compiled Statement
 When an SQL statement is fired to the database
for execution using the Statement object the
following steps get executed:
◦ DBMS checks the syntax of the statement being
submitted.
◦ If the syntax is correct, it executes the statement.
 DBMS compiles every statement unnecessarily,
even if users want to execute the same SQL
statement repeatedly with different data items.
 This creates significant overhead, which can be
avoided using the PreparedStatement object.
Pre-compiled Statement
 A PreparedStatement object is created using the
prepareStatement() method of the Connection
object.
 An SQL statement with placeholders (?) is supplied
 This SQL statement, together with the
placeholders is sent to the DBMS
 DMBS, in turn, compiles the statement and if
everything is correct, a PreparedStatement object
is created.
 This SQL statement is then fired repeatedly, with
placeholders substituted by different data items
Example
◦ PreparedStatement ps =
[Link]("INSERT INTO questions
values(?,?)");
◦ BufferedReader br = new BufferedReader(new
InputStreamReader(new FileInputStream("[Link]")));
◦ String line = [Link]();
◦ while (line != null) {
◦ StringTokenizer st = new StringTokenizer(line, ":");
◦ String qno = [Link]();
◦ String question = [Link]();
◦ [Link](1, qno);
◦ [Link](2, question);
◦ [Link]();
◦ line = [Link]();
◦}
Pre-compiled Statement
 PreparedStatement has another important
role in executing parameterized SQL
statements.
◦ String query = "INSERT INTO questions
values("+qno+",'"+question+"')";
 If question is “3:What’s JDBC?”, query will be
“INSERT INTO questions values(3,'What's
JDBC?')”
 An invalid statement due the “’” character in
the word “What’s”
 Pre-compiled statement can eliminate this
problem
Stored procedures
 Allows the calling of stored procedures that
are stored in the database server
 This is done using the CallableStatement
object.
 Created using the prepareCall() method on
a Connection object.
 Here is an example:
◦ String proCall = "{call changePassword(?, ?, ?)}";
◦ CallableStatement cstmt =
[Link](proCall);
Examples
 JDBC API allows the following syntax to call
stored procedures:
◦ {call procedure-name [(?, ?, ...)]}
 No parameter and no return type
◦ {call procedure-name}
◦
 Single parameter: and no return type
◦ {call procedure-name(?)}
◦
 Two parameters and a return type
◦ {? = call procedure-name (?, ?)}
Retrieving result
 A table of data is represented in the JDBC by
the ResultSet interface.
 A pointer points to a particular row of the
ResultSet object at a time.
 This pointer is called cursor. The cursor is
positioned before the first row when the
ResultSet object is generated.
 To retrieve data from a row of ResultSet, the
cursor must be positioned at the row.
 The ResultSet interface provides methods to
move this cursor.
next()
 next()
◦ This method on the ResultSet object moves the cursor
to the next row of the result set.
◦ It returns true/false depending upon whether there are
more rows in the result set.
 Since the next() method returns false when
there are no more rows in the ResultSet object, it
can be used in a while loop to iterate through
the result set as follows:
◦ String query = "SELECT * from users";
◦ ResultSet rs = [Link](query);
◦ while([Link]()) {
◦ //process it
◦ }
Getting Database information
 Sometimes, it is necessary to know the capabilities
of DataBase Management System (DBMS) before
dealing with it.
 The DatabaseMetaData interface provides methods
to collect comprehensive information about a
DBMS.
 We can discover features a DBMS supports and
develop our application accordingly.
 For example, before creating a table, one may want
to know what data types are supported by this
DBMS.
 The user may also want to know whether the
underlying DBMS supports batch update.
Example
◦ <!--[Link]-->
◦ <%@page import="[Link].*, [Link].*"%>
◦ <%
◦ new [Link]();
◦ String url = "jdbc:mysql://uroy:3306/test";
◦ Connection con = [Link](url, "root", "nbuser");
◦ DatabaseMetaData md = [Link]();
◦ Method[] methods = [Link]().getMethods();
◦ Object[] param = new Object[0];
◦ [Link]("<table border=\"1\">");
◦ for (int i = 0; i < [Link]; i++) {
◦ if (methods[i].getParameterTypes().length == 0) {
◦ if (methods[i].getReturnType() == [Link] ||
methods[i].getReturnType() == [Link]) {
◦ [Link]("<tr>");
◦ [Link]("<td>"+methods[i].getName() + "</td>");
◦ try {
◦ [Link]("<td>" + methods[i].invoke(md, param)+"</td>");
◦ }catch(Exception e) {[Link]("<td>" + e+"</td>");}
◦ [Link]("</tr>");
◦ }
◦ }
◦ }
◦ [Link]("</table>");
◦ %>
Scrollable and Updatable
ResultSet
 Result sets can be scrollable in the sense that
the cursor can be moved backward and
forward.
 Additionally, a result set can be updatable,
such that any change to the result set reflects
in the database immediately.
 The createStatement() and prepareStatement()
methods take extra parameters that specify
the type of result returned by subsequent
execution of SQL statements.
◦ Statement createStatement(int resultSetType, int
resultSetConcurrency)
Scrollability type
 The parameter resultSetType can assume the
following static integer constants defined in
ResultSet.
 Their meaning is as follows:
 TYPE_FORWARD_ONLY
◦ If this constant is used, the cursor starts at the first row
and can only move forward.
 TYPE_SCROLL_INSENSITIVE
◦ All cursor positioning methods are enabled; the result set
does not reflect changes made by others in the underlying
table.
 TYPE_SCROLL_SENSITIVE
◦ All cursor positioning methods are enabled; the result set
reflects changes made by others in the underlying table.
Concurrency Type
 The parameter resultSetConcurrency can
assume the following static integer
constants defined in ResultSet.
 The following is a brief description:
 CONCUR_READ_ONLY
◦ The result set is not updatable.
 CONCUR_UPDATABLE
◦ Rows can be added and deleted; columns can be
updated and are visible to others.
Scrollability
TYPE_SCROL
TYPE_FORWAR TYPE_SCROL
Scroll Type L_INSENSITIV
D_ONLY L_SENSITIVE
E

DELETE No Yes Yes

Internal UPDATE Yes Yes Yes

INSERT No No No

DELETE No No No

External UPDATE No Yes No

INSERT No No No
Examples
 The following example creates a Statement
object, whose methods will return scrollable,
update insensitive, and read-only result
sets.
◦ Statement stmt =
[Link](ResultSet.TYPE_SCROLL_IN
SENSITIVE, ResultSet.CONCUR_READ_ONLY);
◦ ResultSet rs = [Link]("SELECT *
FROM questions");
 The ResultSet object rs is now scrollable,
but update insensitive.
More examples
 Move the cursor forward by one row.
◦ [Link]();
◦ //or
◦ [Link](1);
 Move the cursor backward by one row.
◦ [Link]();
◦ //or
◦ [Link](-1);
 Set the cursor before the first row.
◦ [Link]();
 Set the cursor after the last row.
◦ [Link]();
 Set the cursor at the first row (row 1).
◦ [Link]();
◦ //or
◦ [Link](1);
More examples
 Set the cursor at the last row.
◦ [Link]();
◦ //or
◦ [Link](-1);
 Set the cursor at the second row.
◦ [Link](2);
 Set the cursor at the second last row.
◦ [Link](-2);
 Move the cursor forward six rows from the current position.
◦ [Link](6);
◦ //Sets the cursor after the last row, if it goes beyond the last row
 Move the cursor backward four rows from the current position.
◦ [Link](-4);
◦ //Sets the cursor before the first row, if it goes before the first row
Updatable ResultSet
 The following example creates a Statement
object, whose methods will return scrollable
as well as external-update-insensitive and
updatable result sets.
◦ Statement stmt =
[Link](ResultSet.TYPE_SCROLL
_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
◦ ResultSet rs = [Link]("SELECT *
FROM users");
Updatable ResultSet
◦ //Updating existing row
◦ [Link](4);
◦ [Link]("password","newPassword");
◦ [Link]();
◦
◦ //inseting new row
◦ [Link]();
◦ [Link](1, "anik");
◦ [Link](2, "anik123");
◦ [Link]();
◦
◦ //Deleting a row
◦ [Link]()
Updatable ResultSet
Examples

 [Link]
 [Link]
Result Set Metadata
 The ResultSetMetaData object is used to
retrieve information about the types and
properties of the columns and other meta
information about a ResultSet object.
 ResultSet rs = [Link]("SELECT
* FROM questions");
◦ ResultSetMetaData rsmd = [Link]();
 It can be used to get some useful
information such as number of rows,
number of columns, column names, and
their type