Java-JDBC by Kadam R.

R
Jdbc Tutorial Jdbc Architecture

JDBC (Java Database Connectivity) is uses

for connect java application with database. It is
Java SE technology, which is install automatically
with the jdk software. Jdbc ia an API (Application
programming interface) used to communicate
Java application to database in database
independent and platform independent manner. It
provides classes and interfaces to connect or
communicate Java application with database.

JDBC

Jdbc ia an API (Application programming interface)

used to communicate Java application to database in
database independent and platform independent
manner. It provides classes and interfaces to connect
or communicate Java application with database.

Jdbc ia a part of JDK software so no need to install

separate software for jdbc API

Jdbc API consists of two packages Jdbc Introduction

 [Link] package JDBC (Java Database Connectivity) is uses for
 [Link] package connect java application with database. It is Java SE
technology, which is install automatically with the
jdk software. Jdbc ia an API (Application
 Java-JDBC by Kadam R.R
programming interface) used to communicate Java But problem with the above communication is a front
application to database in database independent and end application become as a database dependent
platform independent manner. It provides classes application, because every database vendor give its
and interfaces to connect or communicate Java own set of function for communication.
application with database.
To overcome the database dependent problem ODBC
JDBC API is a Java API that can access any kind of (Open database connectivity) community formed by
data stored in a Relational Database. It enables Java Microsoft with Simba technologies.
programs to execute SQL statements. JDBC works
ODBC community has provided ODBC API, to
with Java on a variety of platforms, such as Windows,
connect with any database in a database independent
Mac OS, and the various versions of UNIX.
manner.
Before reading Jdbc you need basic knowledge of
Why Odbc not use in Java Application ?
Core java and most important some topics are
required like Abstract, Interface, Exception ODBC API is written in C language woth pointer but
Handling, Collection Framework etc. Java application does not contain pointer so
internally non pointers java code is converted to C
Why we use JDBC pointers code this conversion is a time consuming
In yearly days for communicate front end application process so the connectivity is very slow.
to database, front end application used a set of
Java application is platform independent but if it is
function given by database vendor, to connect with a
combined with ODBC then it become platform
database.
dependent but this is against of java motto or
Even today C and C++ application are connecting principal. To solved the above problems Sum
with oracle database using a set of function given by MicroSystem introduced JDBC technology. Jdbc
oracle corporation in a orcl.h header file. technology makes java applications as platform
independent and database independent.
 Java-JDBC by Kadam R.R
Difference between ODBC and JDBC  Time

ODBC JDBC Interfaces In Jdbc

 Connection
Odbc is platform dependent Jdbc is both platform and
1  Statement
and database independent. database independent.
 PreparedStatement
Odbc implemented in C Jdbc implemented in java  CallableStatementResultset
1
language with pointer. without pointer.
 ResultSetMetaData

 DatabaseMetaData
JDBC API  Driver
Jdbc API is a java API that can access any kind of  Blob
tabular data and specific data stored in relational
 Clob
database management system (RDBMS).
Note: All Jdbc Interfaces are Implemented in Jdbc
Packages
Driver
 [Link] package
Data Source Name
 [Link] package
DSN (Data Source Name) is a file which stores a
Classes In Jdbc
database name. An ODBC driver uses a DSN file and
 DriverManager reads a database name then connects with that
 SQLException
database.

 Types Tpyes of DSN

 Date
1. System DSN
 Java-JDBC by Kadam R.R
2. User DSN 1. Register the driver class
3. File DSN
In this step we load the JDBC driver class into JVM.
System DSN: System DSN will be stored in a This step is also called as registering the JDBC driver.
sharable location of registry and this type of DSN is The forName() method of Class class is used to
accessible for multiple user accounts of the system. register the driver class. This method is used to
dynamically load the driver class. This step can be
User DSN: User DSN will be stored in a non- completed in two ways.
sharable location of registry and this type of DSN is
only accessible for one user account of the system.  [Link]("fully qualified classname")

 [Link](object of driver class)

File DSN: File DSN is like user DSN, but the DSN
will be stored in a user given location of hard disk. Syntax of forName() method

Steps To Write JDBC Program

public static void forName(String
There are 6 steps to connect any java application with className)throws ClassNotFoundException
the database using JDBC. They are as follows:

1. Load the JDBC driver class or register the JDBC [Link] is a driver class
driver. provided by Sun MicroSystem and it can be loaded
2. Establish the connection into jvm like the following.
3. Create a statement
4. Execute the sql commands on database and get the Syntax
result
5. Print the result [Link]("[Link]
6. Close the connection r");
 Java-JDBC by Kadam R.R
Syntax
1) public static Connection
getConnection(String url)throws SQLException
[Link] jod=new
[Link](); 2) public static Connection
getConnection(String url,String name,String
[Link](jod);
password)

throws SQLException
2. Create the connection object

In this step connection between a java program and a Example to establish connection with the Oracle
database will be opened. To open the connection, we database
call getConnection() method of DriverManager class.
For getConnection() method we need to pass three
Connection con=new
parameters.
[Link](url, username,
 url password);

 username Example:

 password Connection con=new

[Link](Jdbc:Odbc:< dsn
url: url is used to select one register JDBC driver >", "scott","tiger");
among multiple registered driver by DriverManager
class.
3. Create the Statement object
username and password: username and
password are used for authentication purpose. To transfer sql commands from java program to
database we need statement object. To create a
Syntax of getConnection() method statement object we call createStatement() method of
 Java-JDBC by Kadam R.R
connection interface. 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

public Statement createStatement()throws

SQLException

5. Print the result.

Example to create the statement object
Syntax

Statement stmt=new createStatement();

[Link](output);

4. Executing queries
[Link] connection : Close the connection.
Call any one of the following three methods of
Statement interface is used to execute queries to the By closing connection object statement and ResultSet
database and to get the output. will be closed automatically. The close() method of
Connection interface is used to close the connection.
 executeUpdate(): Used for non-select operations.
Syntax of close() method
 executequery(): Used for select operation.
Syntax of close() method
 execute(): Used for both select or non-select operation.

public void close()throws SQLException

 Java-JDBC by Kadam R.R
Example for close connection
[Link]("connection is
established");
[Link](); //step-3

Statement stmt=[Link]();
Example
[Link]("statement object is
cretaed");
import [Link].*;
//step-4
class CreateTable
int i=[Link]("create table
{ student(sid number(3),sname
public static void main(String[] args) throws varchar2(10),marks number(5))");
Exception //step-5
{ [Link]("Result is="+i);
//step-1 [Link]("table is created");

//step-6
[Link]("[Link] [Link]();
");
con .close();
[Link]("driver is laoded");
}
//step-2
}
Connection
con=[Link]("jdbc:odbc:
ramadsn","system","system");
 Java-JDBC by Kadam R.R
Insert Images in Database  fileInputStream object

you can not Insert a picture in database directly. but  Size of the file
you can store binary data of picture file. To insert
image in database we need a column of type BLOB Find size of Image
(Binary Large Object). To find the size of an image file we need File class
object for that file.

Example

File f=new File("c:/[Link]");

int size=(int)[Link]();

Note: In Java forward slash (/) is allowed but

backward slash (\) is not allowed at the time of
Important Points
writing of path.
 In Jdbc only PreparedStatement support the binary data
Example to Insert Image in database
transfer between a Java application to database.

 Jdbc supports only gif or jpeg or png type of images to

import [Link].*;
insert or read from a database.
import [Link].*;
 To set binary data into a parameter of PreparedStatement
object, you need to call setBinaryStream(). import [Link].*;

class PhotoInsert
Parameters of setBinaryStream()
{
 parameter index
 Java-JDBC by Kadam R.R

Connection con; [Link](2,empname);

[Link]("enter photo file path");
public void openCon()throws Exception
String path=[Link]();
{

[Link]("[Link]");
File f=new File("c:/[Link]");
con=[Link]("jdbc:oracl
e:thin:@rama-pc:1521:xe","system","system"); int size=(int) [Link]();

[Link]("connection is opened"); FileInputStream fis=new FileInputStream(f);

} [Link](3,fis,size);

public void insert()throws Exception int i=[Link]();

{ [Link](i+"row inserted");

Scanner s=new Scanner([Link]); [Link]();

PreparedStatement [Link]();
pstmt=[Link]("insert into }
emp_info values(?,?,?)");
public void closeCon()throws Exception
[Link]("enter emp id");
{
int empid=[Link]();
[Link]();
[Link](1,empid);
}
[Link]("enter emp name");

String empname=[Link]();
 Java-JDBC by Kadam R.R

public static void main(String[] args)throws

Exception

PhotoInsert p1= new PhotoInsert();

[Link]();

[Link]();

[Link](); Important Points

}  Again we need to convert binary data to image, because in

} database binary data of image is store.

 When we select image from database, it will be store in a

ResultSet object..

 From ResultSet object we need to read the binary data

Retrieve Images from Database
and we need to store in a InputStream object.
While retrieving a image from a database, the binary
data of image will be selected from the database. Convert binary data into image

To convert binary data into a image again we write

the data into a file using FileOutputStream object.

Example to Retrieve Image from database

import [Link].*;

import [Link].*;
 Java-JDBC by Kadam R.R

import [Link].*; [Link](1,empid);

ResultSet rs=[Link]();
class PhotoSelect
[Link]();
{
InputStream is=[Link](1);
Connection con;
[Link]();
public void openCon()throws Exception
FileOutputStream fos=new
{ FileOutputStream("c:/[Link]");
[Link]("[Link]"); int k;
con=[Link]("jdbc:oracl while((k=[Link]())!=-1)
e:thin:@rama-pc:1521:xe","system","system");
{
[Link]("connection is opened");
[Link](k);
}
}
public void select()throws Exception
[Link]("picture is ready open
{ c:drive");
Scanner s=new Scanner([Link]); [Link]();
PreparedStatement [Link]();
pstmt=[Link]("select photo
from emp_info where empid=?"); }//end of

[Link]("enter emp id"); public void closeCon()throws Exception

int empid=[Link](); {

[Link]();
 Java-JDBC by Kadam R.R
Need of Save Point ?
}//end of select
By usning SavePoint interface we can divide a large
transaction into logically different [Link] we
public static void main(String[] args)throws want to produce one part of operations of a
Exception transaction from another part of operations of a
transaction then in the middle we put a SavePoint.
{
Advantage of SavePoint
PhotoSelect ps= new PhotoSelect();

[Link](); In operation of a transaction are executed upto the

SavePoint successfully then the operations can be
[Link](); protected even if any failure is occurred in operations
[Link](); after the SavePoint.

}//end of main Create SavePoint

}//end of class To create a SavePoint in the middle of a transaction,

we need to call setSavePoint() method of
Connection interface.

Check SavePoint supported by driver or not

SavePoint Interface
All Jdbc drivers does not provides SavePoint feature,
SavePoint is an interface of [Link] package. It is
because it is an optional feature. To check whether a
introduced in jdbc 3.0 version. A savepoint is a way
jdbc driver has the implementation of this feature or
of implementing subtransactions (also known as
not, we call supportSavePoint() method of
nested transactions) within a relational database
DatabaseMetaData interface.
management system.
Syntax
 Java-JDBC by Kadam R.R

DatabaseMetaData dbmd=[Link](); public static void main(String[] args) throws

Exception
if([Link]()) {
{ [Link]("[Link]");
[Link]("This driver support Connection
SavePoint"); con=[Link]("jdbc:oracl
} e:thin:@rama-pc:1521:xe","system","system");

else Statement stmt=[Link]();

{ //disable auto commit

[Link]("This driver does not [Link](false);

support SavePoint"); DatabaseMetaData dbmd=[Link]();
} if([Link]())

{
Note: Multiple savepoints can exist within a single
//savepoint is supported
transaction. Savepoints are useful for implementing
complex error recovery in database applications try

Syntax {

int i1=[Link]("insert into

import [Link].*; emp_info values(222,'johon','mumbi',69000)");

class SavepointDemo Savepoint point1=[Link]("spoint1");

{ try
 Java-JDBC by Kadam R.R

{ {
try
int i2=[Link]("delete from student
where sid=111"); {

} [Link]();

catch (Exception e1) }

{ catch ( Exception eee)

try {

{ }

[Link](point1); }//end of catch

} }//end of if

catch (Exception ee) else

{ {

} [Link]("this driver dosen't support

savepoints");
}
}//end of else
[Link]();
[Link]();
[Link]("this driver is supported
successfully"); [Link]();

}//end of outer try }

catch ( Exception e2)

 Java-JDBC by Kadam R.R

Batch Processing

Instead of executing a single query, we can execute a

batch (group) of queries using batch processing.

Why need of Batch Processing

In a Jdbc program if multiple sql operations are

Advantage of Batch Processing
there, then each operation is individually transfer to
the database. This approach will increase the number Increase the performance of an application.
of round trips between java program (application)
Method of Batch Processing
and database. If the number of round trips are
increased between an application and database, then The following two methods are used for performing
it will reduce the performance of an application. To Batch Processing. These methods are given by
overcome these problem we use Batch Processing. Statement Interface.

Method Description

void addBatch(String
1 It adds query into batch.
query)

2 int[] executeBatch() It executes the batch of

 Java-JDBC by Kadam R.R

queries.
Statement stmt=[Link]();

Note: In Batch Processing only non-select operations

//create batch
are allowed select operation is not allowed
[Link]("insert into student
If any operation failed in batch processing then values(901,'PQR',788)");
[Link] Exception will be thrown
[Link]("update emp_info set esal=8888
When we want to cancel all the operation of the batch where eno=1012");
when one operation failed then apply batch
[Link]("delete from customer where
processing with transaction management.
custid=111");
Example of Batch Processing

//disabl auto-commit mode

import [Link].*;
[Link](false);
class BatchTest
try
{
{
public static void main(String[] args) throws
Exception int i[]=[Link]();

{ [Link]();

[Link]("[Link]"); [Link]("batch is successfully

executed");
Connection
con=[Link]("jdbc:oracl }
e:thin:@rama-pc:1521:xe","system","system");
catch (Exception e)
 Java-JDBC by Kadam R.R
Transaction Management
{
A transaction is a group of operation used to
try performed one task if all operations in the group are
{ success then the task is finished and the transaction
is successfully completed. If any one operation in the
[Link]();
group is failed then the task is failed and the
[Link]("batch is failed"); transaction is failed.

[Link]("Exception is"+e);

catch (Exception e1)

}//end of outer catch

//cleanup

[Link]();
Suppose a movie ticket booking at online is a
[Link](); transaction. This task contains four operation.

}  Verify the seats

}  Reserve the seats

 Payment

 Issue tickets
 Java-JDBC by Kadam R.R
If all the above four operations are done successfully Consistency: Consistency means, after a
then a transaction is finished successfully. In the transaction completed with successful, the data in the
middle, if any one operation is failed then all datastore should be a reliable data this reliable data
operation are canceled and finally a transaction is is also called as consistent data.
failed.
Isolation: Isolation means, if two transaction are
Properties of Transaction managements going on same data then one transaction will not
disturb another transaction.
Every transaction follows some transaction
properties these are called ACID properties. Durability: Durability means, after a transaction is
completed the data in the data store will be
permanent until another transaction is going to be
performed on that data.

Advantage of Transaction Management

fast performance It makes the performance fast

because database is hit at the time of commit.

Types of Transaction
 Local Transaction

 Distributed or global transaction

Atomicity: Atomicity of a transaction is nothing but
Local Transaction
in a transaction either all operations can be done or
all operation can be undone, but some operations are A local transaction means, all operation in a
done and some operation are undone should not transaction are executed against one database.
occure. For example; If transfer money from first account to
 Java-JDBC by Kadam R.R
second account belongs to same bank then Example
transaction is local transaction.
[Link](false);
Global Transaction

A global transaction means, all operations in a

transaction are executed against multiple database. To commit a transaction, call commit() and to
For Example; If transfer money from first account to rollback a transaction, call rollback() method of
second account belongs to different banks then the connection Interface respectively.
transaction is a global transaction.
Example
Note: Jdbc technology perform only local
transactions. For global transaction in java we need [Link]();
either EJB or spring framework.
[Link]();
Things required for transaction in Jdbc

To do transaction management in Jdbc, we need to Note: In transaction management DDL operation are
follow the below steps. not allowed.

 Step 1: Disable auto commit mode of Jdbc Note: The operation in a transaction management
may be executed on same table or different table but
 Step 2: Put all operation of a transaction in try block.
database should be same.
 Step 3: If all operation are done successfully then commit
in try block, otherwise rollback in catch block. Example

By default in Jdbc autocommit mode is enabled but import [Link].*;

we need to disable it. To disable call
setAutoCommit() method of connection Interface. class TrxaExample

{
 Java-JDBC by Kadam R.R

public static void main(String[] args)throws }//end of try

Exception catch (Exception e)

{ {

[Link]("[Link]"); try

Connection con {
=[Link]("jdbc:oracle:th [Link]();
in:@rama-pc:1521:xe","system","system");
[Link]("Trasaction is failed");
[Link]("driver is loaded");
}
Statement stmt=[Link]();
catch (Exception ex)
[Link](false);
{
try
[Link](ex);
{
}
int i1=[Link]("insert into student
}//end of catch
values(110,'rama',685)");
[Link]();
int i2=[Link]("update customer
set custadd='Hyderabad'where custid=111"); [Link]();

int i3=[Link]("delete from student [Link]("connection is closed");

where sid=101");
} //end of main
[Link]();
} //end of class
[Link]("Transaction is success");
 Java-JDBC by Kadam R.R
Syntax

Properties Files
Properties p=new Properties();
A property file is one type of the file which organizing
the data in the form of (key, value) pair. A property // Here p resides in heap memory.
file is a text file which is to be created in any editors
like-notepad, Editpuls and etc. Property file should
Advantages of Properties class over Hashtable class
be saved on same file name with on extension .prop
or .rbf.  Properties class object is able to read the data from
properties/resource bundle file.
Properties Files Important points
 Properties class always makes us to develop flexible
 A property file is a text file which is to be created in any
application.
editors like-notepad, Editpuls and etc.
Constructor and Method of Properties class
 Property file should be saved on same file name with on
extension .prop or .rbf. Constructor perporties()
 Properties class object organizes the data in the form of
(key, value) pair and it displays in the same order in public void load(FileInputStream);
methods
whichever order it is added. public String get Property(String);

 Property file always resides in secondary memory.

Constructor of properties class is used for creating an
[Link] object of properties class.

It is one of the pre-defined sub-class of Hashtable Syntax

class, so that all the methods of Hashtable are
inherited into properties class. Creating Properties is Properties p=new Properties();
nothing but creating an object of properties class.
 Java-JDBC by Kadam R.R
public void load(FileInputStream); is used for
loading the content of property file ito properties String marks=[Link]("marks");
class object by opening the properties file in read
mode with the help of FileInputStream class.

Example

FileInputStream fis=new
FileInputStream("student properties");
Example of properties file
[Link](fis);

import [Link].*;
public String get Property(String); is used for
import [Link].*;
obtaining the value of value by passing the key.
class Prop

public static void main(String []args)

try
Example {

Scanner s=new Scanner([Link]);

String sno=[Link]("stno");
[Link]("Enter the property file
String sname=[Link]("sname");
name");

String pfile=[Link]();
 Java-JDBC by Kadam R.R

FileInputStream fis=new FileInputStream(); }

}
Properties p=new Properties();
}
[Link](fis);

String sno=[Link]();

String sname=[Link]();
Jdbc Interview Question
String marks=[Link]();
Why use Jdbc ?
[Link]("student number="+sno);
In yearly days for communicate front end application
[Link]("student name="+sname); to database, front end application used a set of
[Link]("student marks="+marks); function given by database vendor, to connect with a
database.
[Link]();
Even today C and C++ application are connecting
}
with oracle database using a set of function given by
catch(FileNotFoundException fe) oracle corporation in a orcl.h header file.
{ But problem with the above communication is a front
[Link]("properties file does not end application become as a database dependent
exists"); application, because every database vendor give its
own set of function for communication.
}
To overcome the database dependent problem ODBC
catch(Exception e)
(Open database connectivity) community formed by
{ Microsoft with Simba technologies. ODBC
[Link](e);
 Java-JDBC by Kadam R.R
community has provided ODBC API, to connect with direction forward and backward
any database in a database independent manner. direction

Why Odbc not use in Java Application ? ?

Slow performance, If we want to Fast performance,
ODBC API is written in C language woth pointer but 1 move nth record then we need to directly move on any
Java application does not contain pointer so n+1 iteration record.
internally non pointers java code is converted to C
pointers code this conversion is a time consuming
Scrollable ResultSet
process so the connectivity is very slow. Non-Scrollable ResultSet cursor
1 cursor can move
can not move randomly
Java application is platform independent but if it is randomly
combined with ODBC then it become platform
dependent but this is against of java motto or Why use ResultSetMetaData ?
principal. To solved the above problems Sum
While executing a select operation on a database, if
MicroSystem introduced JDBC technology. Jdbc
the table structure is already known for the
technology makes java applications as platform
programmer then a programmer of Jdbc can read the
independent and database independent.
data from ResultSet object directly. If the strucure of
In which formate image are store in database ? table is unknown then need ResultSetMetaData.

In database images are store in binary form. Difference between PreparedStatement and Statement ?

Difference between Scrollable ResultSet and Non-Scrollable ResultSet Statement PreparedStatement

?
Statement interface is
Non-Scrollable ResultSet Scrollable ResultSet PreparedStatement interface is
slow because it compile
1 faster, because its compile the
the program for each
1 Cursor move only in forward Cursor can move both command for once.
execution
 Java-JDBC by Kadam R.R

We can not use ? symbol

in sql command so We can use ? symbol in sql
2 setting dynamic value command, so setting dynamic
into the command is value is simple.
complex

We can not use statement We can use PreparedStatement

3 for writing or reading for reading or writing binary
binary data (picture) data.