MODULE 5 Advanced Java

Module – 5: JDBC

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; ResultSet; Transaction Processing; Metadata, Data

types; Exceptions.

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 1

MODULE 5 Advanced Java

Introduction

✓ J2EE application saves, retrieves, and manipulates information stored in a

database using web services provided by a J2EE component

✓ A J2EE component supplies database access using Java data objects contained

in the JDBC API.

✓ Java data objects have methods that open a connection to a DBMS and then

transmit messages to insert, retrieve, modify or delete stored in a database

✓ DBMS uses the same connection to send messages back to the J2EE component

What is ODBC?

✓ Abbreviation of Open DataBase Connectivity, a standard database access

method developed by Microsoft Corporation.

✓ The aim of ODBC is to make it possible to access any data from any application,

regardless of which database management system (DBMS) is handling the data.

✓ ODBC manages this by inserting a middle layer, called a database driver,

between an application and the DBMS.

✓ The purpose of this layer is to translate the application's data queries into

commands that the DBMS understands.

✓ For this to work, both the application and the DBMS must be ODBC-compliant -

- that is, the application must be capable of issuing ODBC commands and the

DBMS must be capable of responding to them

The Concept of JDBC

✓ Language barrier -each DBMS defined its own low-level way to interact with

programs to access data stored in its databases.

✓ JDBC driver developed by Sun Microsystems- is a specification described the

detail functionality of a JDBC driver

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 2

MODULE 5 Advanced Java

✓ JDBC driver need be a translator that should convert DBMS messages to

lowlevel messages and vice versa

✓ Java programmer could use Java data objects defined in the JDBC API to write

a routine that interact with the DBMS

✓ Java data objects convert the routine into low level messages that confirm to

the JDBC driver specification.

✓ The JDBC driver translates the routine into low-level messages that are

understood and processed by the DBMS

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. Type1: JDBC-ODBC bridge driver

2. Type2: Native-API driver (partially java driver)

3. Type 3: JDBC/Network Protocol driver (fully java driver)

4. Type 4: JDBC/Thin driver (fully java driver)

1) Type1: 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.

Advantages:

✓ easy to use.

✓ can be easily connected to any database.

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 3

MODULE 5 Advanced Java

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.

2) Type2: Java/Native-API Code 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

Advantage:

✓ performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

✓ The Native driver needs to be installed on the each client machine.

✓ The Vendor client library needs to be installed on client machine.

2) Type 3 JDBC/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.

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 4

MODULE 5 Advanced Java

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.

4)Type 4 JDBC /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 language.

Advantage:

✓ Better performance than all other drivers

✓ No software is required at client side or server side.

Disadvantage:

✓ Drivers depends on the Database.

JDBC Packages

✓ JDBC API contained in two packages

o [Link]-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. Not part of J2SE

o [Link]: extends [Link]-part of J2SE

▪ Interact with Java Naming and Directory Interface(JNDI)

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 5

MODULE 5 Advanced Java

▪ Java data objects that manage connection pooling , among other

advanced JDBC features

A Brief Overview of the JDBC process

The JDBC Process (5 steps)

✓ Loading the JDBC Driver

✓ Connect to the DBMS

✓ Creating and executing a statement

✓ Processing data returned by the DBMS

✓ Terminating the connection with the DBMS

Note: Consider oracle/mysql as a database

Loading the JDBC Driver

✓ The [Link]() method is used to load the JDBC driver.

✓ Suppose a developer wants to work offline and write a J2EE component that

interacts with Microsoft Access on the developer’s PC

✓ Developer must write a routine that loads the JDBC/ODBC Bridge driver called

“[Link].

✓ [Link](“[Link]”);

Connect to the DBMS

✓ [Link](url,UserId,Password):

o Connects DBMS component to the DBMS

o To this URL,UserID and Password are passed

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 6

MODULE 5 Advanced Java

o It returns Connection interface –used throughout the process to

reference the database.

o [Link](): Responsible for managing driver information.

o [Link]: sends statements to the DBMS for processing

[Link](“[Link]”);

o Connection con=[Link](url,UserId,Password);

Create and Execute a SQL

✓ [Link](): used to create a Statement object.

✓ This is used to execute a query and return a ResultSet object that contains the

response from the DBMS.

✓ Usually query is assigned to a String object, which is passed to the statement

object’s executeQuery method

Process data returned by the DBMS

✓ The [Link] object is assigned the results received from the DBMS

after the query is processed.

✓ It consist of methods used to interact with data that is returned by the DBMS

to the J2EE component.

✓ call next() method of ResultSet.

✓ If returned value next() is 0 it indicates that no

✓ getString() of ResultSet object is used to copy the value of a specified in the

current row of the ResultSet to a String object.

Ex:

while([Link]())

[Link]([Link](1));

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 7

MODULE 5 Advanced Java

[Link]([Link](2));

Terminate the Connection to the DBMS

✓ The connection to the DBMS is terminated by using the close()method of the

Connection object once the J2EE component is

✓ The close() throws an exception if problem is encounterd.

[Link]();

Example Program

public class select1

public static void main(String[] args)

try

[Link]("[Link]");
Connection con=[Link]( "jdbc:mysql://localhost:3306/student1","root","");

//here student1 is database name, root is username and password is empty

Statement stmt=[Link]();

ResultSet rs=[Link]("select * from data");

while([Link]())

[Link]([Link](1)+" "+[Link](2));

[Link]();

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 8

MODULE 5 Advanced Java

catch(Exception e)

[Link](“Cannot Connect”);

[Link](e);

Database Connection

✓ J2EE component does not directly connect to a DBMS

✓ J2EE connects with the JDBC driver that is associated with the DBMS.

✓ For JDBC must be loaded and registered with the DriverManager once the

JDBC driver is loaded.

✓ Then it is available to JVM and can be used by J2EE components.

✓ [Link](“[Link]);

Exceptions:

1. ClassNotFoundException: Occurs when loading the JDBC drivers

2. SQLException: Occurs when access is not granted and when Connection object is not

returned by getConnection() method.

Three kinds of exceptions are thrown by JDBC methods are:

✓ SQLEXceptions: SQL syntax error in the query and are thrown by many of the

methods contained in the [Link] package

✓ SQLWarnings: It throws warnings received by the

✓ DataTruncation: It throws whenever data is lost due to truncation of the data

value.

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 9

MODULE 5 Advanced Java

Example Program

public class select1

public static void main(String[] args)

try

[Link]("[Link]");
Connection con=[Link]("jdbc:mysql://localhost:3306/student1","root","");

//here student1 is database name, root is username and password is empty

Statement stmt=[Link]();

ResultSet rs=[Link]("select * from data");

while([Link]())

[Link]([Link](1)+" "+[Link](2));

[Link]();

catch(Exception e)

[Link](e);

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 10

MODULE 5 Advanced Java

Timeout

✓ J2EE application that needs database access requests attempts to connect to the

database

✓ However DBMS may not respond quickly due to various reasons ( unavailable data

base connection)

✓ To avoid wait for uncertain amount of time, J2EE component can set a timeout

period after which the DriverManager will stop to attempts to connect to the

database.

✓ Public static void [Link](int seconds) method can be

used by the J2EE component to establish the maximum time the DriverManager

waits for response from DBMS before timeout.

Associating the JDBC/ODBC Bridge with the Database

1. Click Start->Settings->Control Panel->Administrative Tools(small icons)

2. Click ODBC32 to display the DataSource Administrator (ODBC) .

3. Click on Add button in ODBC DataSource Administrator

4. Select proper driver from create new data source dialog box

5. For MS access Microsoft Access Driver(*.mdb)

6. For Oracle Microsoft ODBC for Oracle

7. Click on Finish

8. Enter Suitable Datasource name (in Microsoft ODBC for Oracle window)

9. Enter User Name say Scott (Enter Host String as for server Name

10. Click on Select (for Microsoft ODBC for Access)

11. Browse the data base path and click OK

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 11

MODULE 5 Advanced Java

Statement Objects

Three Statement objects are used to execute the query:

1. Statement: Executes a query immediately.

2. PreparedStatement: used to execute a compiled query.

3. CallableStatement: Used to execute store procedures.

1) Statement object

✓ The Statement interface provides methods to execute queries with the

database.

✓ The statement interface is a factory of ResultSet i.e. it provides factory

method to get the object of ResultSet.

The important methods of Statement interface are as follows:

a) ResultSet executeQuery(String sql): is used to execute SELECT query.

It returns the object of ResultSet.

Statement stmt=[Link]();

ResultSet rs=[Link]("select * from data");

while([Link]())

[Link]([Link](1)+" "+[Link](2));

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 12

MODULE 5 Advanced Java

b) int executeUpdate(String sql): is used to execute specified query, it may

be create, drop, insert, update, delete etc.

Statement stmt=[Link]();

//for insert

int result=[Link]("insert into emp values(33,'Shashank',50000)");

// for update

int result=[Link]("update empset name='Varun',salary=10000 where

id=33");

// for delete

int result=[Link]("delete from emp where id=33");

[Link](result+" records affected");

[Link]();

c) boolean execute(String sql): is used to execute queries that may return

multiple results.

boolean status = [Link](anyquery);

if(status)

//query is a select query.

ResultSet rs = [Link]()

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 13

MODULE 5 Advanced Java

2) PreparedStatement object

✓ The PreparedStatement interface is a subinterface of Statement. It is used to

execute parameterized query.

✓ Improves performance: The performance of the application will be faster if you

use PreparedStatement interface because query is compiled only once.

// PreparedStatement to insert record

PreparedStatement stmt=[Link]("insert into Emp values(?,?)");

[Link](1,101);//1 specifies the first parameter in the query

[Link](2,"Sharath");

int i=[Link]();

[Link](i+" records inserted");

✓ The setXXX() methods are used to supply values to the parameters

✓ All of the Statement object's methods for interacting with the database
execute(), executeQuery(), and executeUpdate() also work with the
PreparedStatement object.

// PreparedStatement to update record

PreparedStatement stmt=[Link]("update emp set

name=? where id=?"); [Link](1," Ratan ");//1 specifies the first

parameter in the query i.e. name [Link](2,101);

int i=[Link]();

[Link](i+" records updated");

// PreparedStatement to delete record

PreparedStatement stmt=[Link]("delete from emp where id=?");

[Link](1,101);

int i=[Link]();

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 14

MODULE 5 Advanced Java

[Link](i+" records deleted");

3) CallableStatement Objects

✓ CallableStatement interface is used to call the stored procedures and

functions.

✓ Suppose you need the get the age of the employee based on the date of birth,

you may create a function that receives date as the input and returns age of the

employee as the output.

String SQL = "{call getEmpName (?, ?)}";

// stored procedure called cs = [Link] (SQL);

[Link](100);

//resisterOutParameter() used to register OUT type used by stored procedure

[Link](2, VARCHAR);

[Link]();

String Name=[Link](1);

[Link]();

ResultSets

Reading the ResultSets

✓ getString() method is called to retrieve values from each of the columns result
set.
✓ [Link](1); to retrieve first column
✓ The next() method is called in the looping statement to move the virtual cursor
to the next row in the ResultSet and determine if there is data in that row.

Example: Reading data from the result set

Connection con = getConnection();

Statement stmt;

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 15

MODULE 5 Advanced Java

ResultSet rs;
String strSQL;
strSQL = "SELECT * FROM STUD_DB WHERE USN=‘123’'";
try
{
stmt = [Link]();
rs=[Link](strSQL);
while([Link]())
{
[Link]([Link](1)); //denote first column
[Link]( [Link](2)); //denote second column
}
[Link]();
[Link]();
}
catch(SQLException ex)
{
}

Scrollable ResultSet

✓ The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor
points to before the first row.

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 16

MODULE 5 Advanced Java

When you create a ResultSet there are three attributes you can set. These are:

Types
✓ ResultSet.TYPE_FORWARD_ONLY (default type)- TYPE_FORWARD_ONLY

means that the ResultSet can only be navigated forward

✓ ResultSet.TYPE_SCROLL_INSENSITIVE- TYPE_SCROLL_INSENSITIVE

means that the ResultSet can be navigated (scrolled) both forward and

backwards. The ResultSet is insensitive to changes while the ResultSet is open.

That is, if a record in the ResultSet is changed in the database by another

thread or process, it will not be reflected in already opened ResulsSet's of this

type.

✓ ResultSet.TYPE_SCROLL_SENSITIVE- means that the ResultSet can be

navigated (scrolled) both forward and backwards. The ResultSet is sensitive to

changes in the underlying data source while the ResultSet is open.

Updatabale ResultSet

Rows contained in the ResultSet is updatable.

✓ This is possible by passing the createStatement method of the Connection

object the CONCUR_UPDATABLE

✓ This is prevented by passing CONCUR_READ_ONLY to the createStatement

method of Connection object.

✓ ResultSet can be changed by:

o Updating a row

o Inserting a new row

o Deleting a row

Example: Update Row

Connection con = getConnection();

ResultSet rs;
String strSQL = "SELECT SNAME,USN FROM STUD_DB WHERE USN=‘123’'";
Statement stmt;
try
{

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 17

MODULE 5 Advanced Java

stmt=[Link](rs.CONCUR_UPDATABLE);
rs=[Link](strSQL);
}
catch(SQLException ex)
{
}

if([Link]())
{
try
{
[Link](“SNAME”,”XYZ”);
//changes SNAME of student to XYZ whose USN is 123
[Link]();
[Link]();
[Link]();
}

Catch(Exception e)
{
}
}

Delete Row in the resultset

✓ deletRow() method is used to remove a row from a ResultSet.

✓ deleteRow method is passed an integer that contains the number of the row to be

deleted

✓ Ex: [Link]();

Example: Delete a row in resultset

Connection con = getConnection();

ResultSet rs;
String strSQL = "SELECT SNAME,USN FROM STUD_DB WHERE USN=‘123’'";
Statement stmt;
try
{
stmt=[Link](rs.CONCUR_UPDATABLE);
rs=[Link](strSQL);
}
catch(SQLException ex)
{
}

if([Link]())
Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 18
 MODULE 5 Advanced Java

try
{

[Link](0); //0: current row of resultset

[Link]();
[Link]();
}
catch(SQLException ex)
{
[Link](“can not process:”+e);
}

Insert Row in the ResultSet

✓ updatexxx() method requires two parameters
✓ Either name of the column or number of the column of the resultset
✓ New Value that will be placed in the column of the Resultset
✓ insertRow method is called after updateRow method-which causes new row to be
inserted into the resultset

Example: Insert Row

con = getConnection();
ResultSet rs;
String strSQL = "SELECT SNAME,USN FROM STUD_DB";
Statement stmt;
try
{
rs=[Link](rs.CONCUR_UPDATABLE);
rs=[Link](strSQL);
}
catch(SQLException ex)
{
}
If([Link]())
{
try
{
[Link](1,”RAJ”);
[Link](2,”123”);
[Link]();
[Link]();
[Link]();
}

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 19

MODULE 5 Advanced Java

catch(SQLException ex)
{
}

Transaction processing

✓ It involves several tasks similar to the tasks that are required to complete a

transaction.

✓ A database transaction consists of a set of SQL statements, each of which must

be successfully completed for the transaction to be completed.

✓ If fails, SQL statements that are executed successfully up to that point in the

transaction must be rolledback().

✓ A database transaction isn’t completed until J2EE component calls the commit()

method of the Connection object

✓ All SQL statements executed prior to the call to the commit() method can be

rolledback()

✓ However once commit() is called, none of the SQL statement can be rolled back.

Executing database transaction

Example code:

Connection con = getConnection();

Statement stmt;

String strSQL; strSQL = "DELETE STUD_DB WHERE USN=‘123’ ";

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 20

MODULE 5 Advanced Java

try

{
stmt = [Link]();

[Link](strSQL);

[Link]();

//call this for final commit

//[Link]();

//call this for rollback [Link]();

[Link]();

catch(SQLException ex)

Using Savepoints in a transaction

✓ 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.

✓ The Connection object has two new methods that help you manage savepoints

✓ setSavepoint(String savepointName): Defines a new savepoint.

✓ It also returns a Savepoint object.

✓ releaseSavepoint(Savepoint savepointName): Deletes a savepoint. Notice

that it requires a Savepoint object as a parameter. This object is usually a

savepoint generated by the setSavepoint() method.

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 21
MODULE 5 Advanced Java

try

//Assume a valid connection object conn

[Link](false);

Statement stmt = [Link]();

//set a Savepoint
Savepoint savepoint1 = [Link]("Savepoint1");

String SQL = "INSERT INTO Employees " + "VALUES (106, 20, 'Pramodh', 'naveen')";

[Link](SQL);

//Submit a malformed SQL statement that breaks

String SQL = "INSERTED IN Employees " + "VALUES (107, 22, 'goutam', 'ganesh')";

[Link](SQL);

//If there is no error, commit the changes. [Link]();

catch(SQLException e)

//If there is any error. [Link](savepoint1);

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 22

MODULE 5 Advanced Java

Batching SQL statement into a transaction/Batch processing code (6 Marks)

The required methods for batch processing are given below:

✓ void addBatch(String query): It adds query into batch.

✓ int[ ] executeBatch(): It executes the batch of queries.

✓ clearBatch(): it clears the batch

Example code:

Statement stmt=[Link]();

[Link]("insert into user values(190,'abhi',40000)");

[Link]("insert into user values(191,'arun',50000)");

int[] count = [Link]();

// you can get number of sql stmt that was executed by count[] array.

Metadata

✓ Meta data is data about data

✓ J2EE component can access metadata by using the DatabaseMetaData interface

✓ Database metaData interface is used to retrieve information about databases,

tables, columns, indexes etc.

DatabaseMetaData objects methods are:

✓ getDatabaseProductname() Returns the product name of the database

✓ getUserName(): Returns the userName

✓ getURL(): Returns the URL of the Database

✓ getSchemas() Returns all the scheme name available in this database

✓ getPrimaryKeys() Returns primary Keys

✓ getProcdures() Returns stored procedures names

✓ getTables(): Returns names of th tables in the database

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 23

MODULE 5 Advanced Java

Resultset Mata Data

✓ Describes the result set

✓ Usage: ResultsetMetaData rm=[Link]();

Methods are:

✓ getColumnCount(): Returns the number of columns contained in the ResultSet

✓ getColumnName(int number): Returns the Name of the column specified by the

column number

✓ getColumnType(int number) Returns the Data type of the column specified by

the column number

Data types

List of data types fpr use with setXXX() and getXXX() methods are

SQL TYPES JAVA TYPES

CHAR String

VARCHAR String

LONGVARCHAR String

NUMERIC [Link]

DECIMAL [Link]

BIT Boolean

TINYINT Byte

SMALLINT Short

INTEGER Integer

BIGINT Long

REAL Float

FLOAT Float

DOUBLE double

BINARY Byte[]

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 24

MODULE 5 Advanced Java

QUESTIONS

1. Describe the various steps of JDBC process with code snippets.

2. Explain the four types of JDBC Driver.

3. What is transaction? Write a java program to execute database transaction.

4. Explain the different types of statement object. Give an example for each

5. Describe Database metadata object and ResultSet metadata object.

6. What is meant by Scrollable Result Set? Explain with an example program.

7. What is ResultSet? How to set scroll option to resultset? Explain

8. Explain a) PreparedStatement b) CallabaleStatement

9. Write a program to call stores procedure using callabale statement

10. List SQL DataTypes.

Prepared By Nagamahesh BS,[Link],ISE,SVIT Page 25