Java Database Connectivity

Prepared by Aparnathi Bhagu (B.C.A College - Bhacha)

Environment variable for
MYSQL
 Before starting coding we need to set some
environment variable in ov
 er pc. Like…..
Variable Name :- JAVA_HOME
Variable Value :- C:\Program Files\Java\jdk1.6.0

Variable Name :- JAVA_PATH

Variable Value :- C:\Program Files\Java\jdk1.6.0\bin

Variable Name :- CLASS_PATH

Variable Value :- C:\Program Files\Java\jre1.6.0\lib\ext\mysql-
connector-java-5.1.23-bin.jar;
 Before you Know Terms

 JDBC :- Stands for Java Database Connectivity.

 ODBC :- Stands for Open Database
Connectivity.
 API :- Stands for Application programming
Interface .
 History Data Access With Java

 In 1996 JDK 1.0 released which has necessary

classes to implement database access.
 Jdk 1.0 contained core classes like java.net and
java.io which group together to provide the necessary
classes to send-receive data.
 But drawback of jdk 1.0 is that implement a simple
transaction like inserting or selecting data from
database.
 Sun is released JDK 1.1 to solve as above problem.
 History Data Access With Java

 In this version Sun release the JDBC as a separate

package under java.sql.
 Day by day some change in new version.

Prepared by Akshay Sarvaiya (B.C.A College - Bhacha)

 What is ODBC?

 ODBC is an interface to access management

systems and database (DBMS).
 ODBC was developed by programmers of the SQL
Access group in the year 1992 when there was no
standard medium for communicating between a
database and an application.
 It does not depend on a specific programming
language or database system or operating system.
 What is JDBC?

 JDBC is an API developed data for the programming

language Java.
 This was released with JDK 1.1 from Sun
Microsystems (the original owners of Java).
 And its current version is JDBC 4.0 (currently
distributed with Java SE6). Java.sql and javax.sql
packages contain the JDBC classes.
 This is an interface that helps a client to access a
database, providing methods to question and
update data in the databases.
 Characteristics of JDBC

 Its call SQL interface for java.

 No restrict the type of queries passed to DBMS
driver.
 JDBC mechanisms is easy to understand and use.
 Its provides a java interface that stays consistent with
rest of java system.
 Advantage of JDBC

 Its easy to use.

 Development time is short.
 Installation and version control simplified.

Prepared by Akshay Sarvaiya (B.C.A College - Bhacha)

 JDBC API
 JDBC API is a collection of methods, classes and
interfaces that enable java application to
communicate with database.
Java Program and JDBC
 Here java program invokes
the method of the JDBC API.
 Then JDBC API call the
JDBC driver and submit the
queries to it.
 Now JDBC driver converts
the queries to the SQL
statements that a database
can understand.
 Now query is perform in sql.
 After JDBC driver retrieves the results of query form
the database.
 That result convert in to JDBC API classis.
 That classis is use by the java program. And obtains the
result of the query.
JDBC Architecture

Prepared by Akshay Sarvaiya (B.C.A College - Bhacha)

 JDBC JDBC JDBC JDBC
Driver-1 Driver-2 Driver-3 Driver-4

Odbc
SQL
data oracle Mysql
Sever
source
Steps for connection with
the database.

1. Register the driver class.

2. Create the connection object.
3. Create the statement object.
4. Execute the query.
5. Close connection object.
 1-Register the driver
 class.
The forName() method of class is used to register
driver class
 This method is used to dynamically load the driver
class.
 Syntax:-
 forName(“driver name”);
 For example : mysql driver reg.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Prepared by Akshay Sarvaiya (B.C.A College - Bhacha)

2-Create the connection
object.
 Here getConncetion() method of Drivermanager
class is used to establish connection with the
database.
 Sysntex :-
 Connection con=DriverManager.getConnection( datase
detail)
 For example:-
 con=DriverManager.getConnection("jdbc:MySql://
localhost:3306/student","root","root");
3- Create the statement
object.
 CreateStatement() method of connection
interface is used to create statement.
 The object of statement is responsible to execute
queries with the database.
 Syntax:- con.CreateStatement();
 Example :-
 Statement stmt=con.createStatement();
 4- Execute the query.
 ExecuteQuery () methos of statement interface is
used to execute queries to the database.
 This method returns the object of result set that
can be used to get all the record of table.
 For Example :-
 ResultSet rs=stmt.executeQuery("select * from
dem");
 4-Close connection object.
 Close () method is use to close all the connetion.
 For example:-
 Con.close();
Type of driver in jdbc

 There are 4 type of driver

1. JDBC-ODBC BRIDGE DRIVER
2. Native-API
3. Network-Protocol
4. Native Protocol
JDBC-ODBC BRIDGE DRIVER

Java Application
JDBC API

Bridge Driver

DSN
ODBC
Driver

Database
 Dsn means data source name.
 It is use for local connection.
 Here jdbc-odbc driver converts all jdbc calls
into ODBC calls and send them .
 Then ODBC driver forwards the call to database
server.
 In short we can say
 Translates query obtained by JDBC into
corresponding ODBC query, which is then handled
by the ODBC driver.
 Advantages
 Almost any database for which ODBC driver is installed,
can be accessed.

 DisAdvantage
 The ODBC driver needs to be installed on the client
machine.
 Considering the client-side software needed, this
might not be suitable for applets.
Native-API
Java Application
JDBC APT

Native API Driver

Native API

Database
 TYPE-2 diver known as Native-API
 Native-API driver is a database driver
implementation that uses the client-side libraries
of the database.
 A native-API partly Java technology-enabled
driver converts JDBC calls into calls on the client
API for Oracle, mysql and other DBMS.
 Its communicates directly with database server.
 Type 2 drivers use a native API to communicate
with a database system.
 Java native methods are used to invoke the
API functions that perform database operations.
 Type 2 drivers are generally faster than Type 1
drivers.
Prepared by Akshay Sarvaiya (B.C.A College - Bhacha)
 Advantages
 Better performance than Type 1 since no jdbc to
odbc translation is needed.

 DisAdvantage
 The vendor client library needs to be installed on the
client machine.
 Cannot be used in internet due the client side
software needed.
 It is not suitable for distributed application.
Network-Protocol
Java Application
JDBC APT

Net Protocol Driver

Middleware
component

Database
 TYPE-3 diver known as Network-Protocol
 Its follows three tiered applroach
 Here database request passed to a middle-tier
server .
 Middle-tier server then translates the request and
passes to specific database native connectivity
interface and also forwards request to database
server.
 If middle-tier is written in java that can be use
type-1 and type-2 driver for forward request to the
database.
 Advantages
 Better performance than Type 1 since no jdbc to
odbc translation is needed.

 DisAdvantage
 The vendor client library needs to be installed on the
client machine.
 Cannot be used in internet due the client side
software needed.
 It is not suitable for distributed application.

Prepared by Akshay Sarvaiya (B.C.A College - Bhacha)

 Statement interface
 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:
 1) public ResultSet executeQuery(String sql): is used to execute
SELECT query. It returns the object of ResultSet.
 2) public int executeUpdate(String sql): is used to execute
specified query, it may be create, drop, insert, update, delete etc.
 3) public boolean execute(String sql): is used to execute queries
that may return multiple results.
 4) public int[] executeBatch(): is used to execute batch of
commands.
 Statement Example
 import java.sql.*;
 class FetchRecord{
 public static void main(String args[])throws Exception{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","sys
tem","oracle");
 Statement stmt=con.createStatement();

 //stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");
 //
int result=stmt.executeUpdate("update emp765 set name='Vimal',salary=10000 where id=3
3");
 int result=stmt.executeUpdate("delete from emp765 where id=33");
 System.out.println(result+" records affected");
 con.close();
 }}
 PreparedStatement
 PreparedStatement allows you to write dynamic
query in Java.
 PreparedStatement in Java allows you to write
parametrized query which gives better performance
than Statement.
 "?" is also called placeholder or IN parameter in Java.
 All JDBC Driver doesn't support pre compilation of
SQL query in that case query is not sent to database
when you call prepareStatement(..) method instead
they would be sent to database when you
execute PreparedStatement query.
Method Summary
Void setBoolean(int parameterIndex, boolean x)
Void setDouble(int parameterIndex, double x)
Void setFloat(int parameterIndex, float x)
Void setInt(int parameterIndex, int x)
Void setLong(int parameterIndex, long x)
Void setNull(int parameterIndex, int sqlType)
void setNull(int parameterIndex, int sqlType, String typeName)
void setObject(int parameterIndex, Object x)
void setObject(int parameterIndex, Object x, int targetSqlType)

setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength)

void

void setShort(int parameterIndex, short x)

void setString(int parameterIndex, String x)
void setTime(int parameterIndex, Time x)
void setTime(int parameterIndex, Time x, Calendar cal)
void setTimestamp(int parameterIndex, Timestamp x)
void setByte(int parameterIndex, byte x)
void setDate(int parameterIndex, Date x)
void setDate(int parameterIndex, Date x, Calendar cal)

void setArray(int parameterIndex, Array x)

void setBinaryStream(int parameterIndex, InputStream x)

void setBlob(int parameterIndex, Blob x)

public static void main(String ar[])
{
Connection con;
PreparedStatement pstmt;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
jdbc:MySql://localhost:3306/student","root","root");
try{
String sql = "INSERT studdetail VALUES(?,?)";
pstmt = con.prepareStatement(sql);
Scanner sc=new Scanner(System.in);
System.out.println("Enter Name");
System.out.println("Enter movie name:");
String s=sc.nextLine();
String s1=sc.nextLine();
pstmt.setInt(1, s);
pstmt.setString(2, s1);
pstmt.executeUpdate();
con.close();
}
 A java.sql.CallableStatement interface object is used
to call stored procedures from the database.
 It is the standard way to execute stored procedure for
all DBMS/RDBMS.
 A stored procedure is an object stored in a database.
 The database stored procedure can be in following
form
 Without parameter
 With input parameter
 With output parameter
 With input and output parameter
 Syntax

 Without parameter - {call procedure_name}

 With input parameter –
{call procedure_name[(?, ?, ...)]}
 Stored procedure that returns value –
{? = call procedure_name[(?, ?, ...)]}
 Without parameter

DELIMITER //

CREATE PROCEDURE proc1()

BEGIN
insert into info value(1,’abc’)
END; //

DELIMITER ;
 Without parameter

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection
(“jdbc:mysql://localhost:3306/EMP”,”root”,”root”);
CallableStatement stmt =
con.prepareCall("{call proc2}");
stmt.execute();
Stored procedure with two IN
parameter
DELIMITER //

CREATE PROCEDURE
proc3(IN code1 varchar(10),IN name1 varchar(10))
BEGIN
insert into emp values(code1,name1);
END; //

DELIMITER ;
 Stored procedure with two
IN parameter
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection
(“jdbc:mysql://localhost:3306/EMP”,”root”,”root”);
CallableStatement stmt =
con.prepareCall("{call proc3(?,?)}");
stmt.setString(1, code);
stmt.setString(2, name);
Stored procedure with one IN and
OUT parameter
DELIMITER //

CREATE PROCEDURE
proc4(IN code1 varchar(10),OUT name1 varchar(10))
BEGIN
SELECT name from emp where code=code1
INTO name1;
END; //

DELIMITER ;
 Stored procedure with two
IN parameter
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection
(“jdbc:mysql://localhost:3306/EMP”,”root”,”root”);
CallableStatement stmt =
con.prepareCall("{call proc4(?,?)}");
stmt.setString(1, ”abc”);
stmt.registerOutParameter(2, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(2));
ResultSet interface
 The object of ResultSet maintains a cursor pointing to a row of
a table. Initially, cursor points to before the first row.

 But 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 = con.createStatement(ResultSet.TYPE_SCROL
L_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Commonly used methods of ResultSet interface
ResultSet interface Example
 import java.sql.*;
 class FetchRecord{
 public static void main(String args[])throws Exception
 {
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localh
ost:1521:xe","system","oracle");
 Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITI
VE,ResultSet.CONCUR_UPDATABLE);
 ResultSet rs=stmt.executeQuery("select * from emp765");
 //getting the record of 3rd row
 rs.absolute(3);
 System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
 con.close();
 }}
SQL Exception
 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.

The term thrown means that current program execution stops,
and the control is redirected to the nearest applicable catch
clause. If no applicable catch clause exists, then the program's
execution ends.

 Exception handling allows you to handle exceptional

conditions such as program-defined errors in a controlled
fashion.
SQL Exception 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.

 The passed SQLException object has the following methods

available for retrieving additional information about the
exception −
general form of a try block
try
{
// Your risky code goes between these curly braces!!!
}
catch(Exception ex)
{
// Your exception handling code goes between these // curly
braces, similar to the exception clause // in a PL/SQL block.
}
Finally
{
// Your must-always-be-executed code goes between these //
curly braces. Like closing database connection.
}