Unit V

Interacting with
Database
 ODBC
 ODBC stands for Open Database Connectivity

 A standard or open application programming interface (API) for accessing a

database.

 ODBC provides a C interface for database access on Windows environment.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

2 Engineering
 JDBC
 JDBC stands for Java Database Connectivity.

 It is a standard Java API for connecting programs written in Java to the data in
relational databases.

 JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and
the various versions of UNIX.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

3 Engineering
 JDBC Driver
 Java Database Connectivity (JDBC) is an application programming
interface (API), which defines how a client may access any kind of tabular data,
especially relational database.
 It acts as a middle layer interface between java applications and database.
 The JDBC classes are contained in the Java Package java.sql and javax.sql.
 JDBC helps you to write Java applications that manage these three programming
activities:
 Connect to a data source, like a database.
 Send queries and update statements to the database
 Retrieve and process the results received from the database in answer to your
query

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

4 Engineering
 Types of JDBC Driver
 JDBC Driver is a software component that enables java application to interact
with the database. There are 4 types of JDBC drivers:

 Type-1 driver / JDBC-ODBC bridge driver

 Type-2 driver / Native-API driver (partially java driver)
 Type-3 driver / JDBC-Net pure Java / Network-Protocol driver (fully java
driver)
 Type-4 driver / Pure Java Driver / Thin driver / Database-Protocol
driver(fully java driver)

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

5 Engineering
 JDBC-ODBC bridge driver
 The JDBC type 1 driver, also known as the 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.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

6 Engineering
 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.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

7 Engineering
 Native API driver
 The JDBC type 2 driver, also known as the 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.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

8 Engineering
 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.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

9 Engineering
 JDBC-Net pure Java Driver
 The JDBC type 3 driver, also known as the Pure Java driver for database
middleware. It is a database driver implementation which makes use of a middle
tier between the calling program and the database.
 The middle-tier (application server) converts JDBC calls directly or indirectly into
a vendor-specific database protocol. It is fully written in java.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

10 Engineering
 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.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

11 Engineering
 Thin driver
 The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver,
is a database driver implementation that converts JDBC calls directly into a
vendor- specific database protocol.
 That is why it is known as thin driver. It is fully written in Java language.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

12 Engineering
 Advantage:
 Better performance than all other drivers.
 No software is required at client side or server side.

Disadvantage:
 Drivers depend on the Database.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

13 Engineering
 JDBC Two Tier Model
 In a two-tier model, a Java application communicates directly with the
database, via the JDBC driver.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

14 Engineering
 JDBC Three Tier Model
 In a three-tier model, a Java application communicates with a middle tier
component that functions as an application server. The application server talks
to a given database using JDBC.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

15 Engineering
 Common JDBC Components
The JDBC API provides the following interfaces and classes :

 DriverManager Class
 Driver Interface
 Connection Interface
 Statement Interface
 ResultSet Interface

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

16 Engineering
 Common JDBC Components
DriverManager Class

 The DriverManager class acts as an interface between user and drivers.

 It keeps track of the drivers that are available and handles establishing a
connection between a database and the appropriate driver.

 The DriverManager class maintains a list of Driver classes that have

registered themselves by calling the method
DriverManager.registerDriver().

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

17 Engineering
 Commonly used methods of DriverManager class

Method Description

public static void registerDriver(Driver driver); is used to register the given driver
with DriverManager.

is used to deregister the given driver

public static void deregisterDriver(Driver driver); (drop the driver from the list) with
DriverManager.

public static Connection getConnection(String url); is used to establish the connection

with the specified url.

public static Connection getConnection(String url, String is used to establish the connection
userName, String password); with the specified url, username and
password.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

18 Engineering
 Common JDBC Components (cont’d..)
 Driver Interface
This interface handles the communications with the database server. You will
very rarely interact directly with Driver objects. Instead, you use
DriverManager objects, which manages objects of this type. It also abstracts
the details associated with working with Driver objects.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

19 Engineering
 Common JDBC Components (cont’d..)
 Connection Interface
A Connection is the session between java application and database. The
Connection interface is a factory of Statement, PreparedStatement, and
DatabaseMetaData. The Connection interface provide many methods for
transaction management like commit(),rollback() etc.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

20 Engineering
 Commonly used methods of Connection interface
Method Description

public Statement createStatement(); creates a statement object that can be used

to execute SQL queries.

public void setAutoCommit(boolean status); It is used to set the commit status. By

default it is true.

public void commit(); It saves the changes made since the

previous commit/rollback permanent.

public void rollback(); Drops all changes made since the previous
commit/rollback.

closes the connection and Releases a JDBC

public void close(); resources immediately.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

21 Engineering
 Common JDBC Components (cont’d..)
 Statement Interface
The Statement interface provides methods to execute queries with the
database. It provides factory method to get the object of ResultSet.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

22 Engineering
 Commonly used methods of Statement
interface
Method Description

public ResultSet executeQuery(String sql); used to execute SELECT query. It returns

the object of ResultSet.

public int executeUpdate(String sql); used to execute specified query, it may be

create, drop, insert, update, delete etc.

public boolean execute(String sql); used to execute queries that may return
multiple results.

public int[] executeBatch(); used to execute batch of commands.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

23 Engineering
 Prepared Statement
 The PreparedStatement interface is a subinterface of Statement. It is used to
execute parameterized query.
 Example:
String sql="insert into emp values(?,?,?)";
Here, we are passing parameter (?) for the values. Its value will be set by calling the
setter methods of PreparedStatement.
 to get the instance of PreparedStatement the prepareStatement() method of
Connection interface is used to return the object of PreparedStatement.
 Syntax:
public PreparedStatement prepareStatement(String query)throws SQLException{
}

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

24 Engineering
 Methods of PreparedStatement interface
Method Description
public void setInt(int paramIndex, int value) sets the integer value to the given parameter
index.
public void setString(int paramIndex, String sets the String value to the given parameter
value) index.
public void setFloat(int paramIndex, float value) sets the float value to the given parameter index.

public void setDouble(int paramIndex, double sets the double value to the given parameter
value) index.
public int executeUpdate() executes the query. It is used for create, drop,
insert, update, delete etc.

public ResultSet executeQuery() executes the select query. It returns an instance

of ResultSet.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

25 Engineering
 Common JDBC Components (cont’d..)
 ResultSet Interface
The object of ResultSet maintains a cursor pointing to a
particular row of data. Initially, cursor points to before the first
row.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

26 Engineering
 Commonly used methods of ResultSet interface
Method Description

public boolean next(); is used to move the cursor to the one row next from the
current position.
public boolean previous(); is used to move the cursor to the one row previous from
the current position.
public boolean first(); is used to move the cursor to the first row in result set
object.
is used to move the cursor to the last row in result set
public boolean last(); object.
public boolean absolute(int row); is used to move the cursor to the specified row number in
the ResultSet object.
public int getInt(int columnIndex); is used to return the data of specified column index of the
current row as int.
public int getInt(String is used to return the data of specified column name of the
columnName); current row as int.
public String getString(int is used to return the data of specified column index of the
columnIndex); current row as String.
public String getString(String is used to return the data of specified column name of the
columnName); current row as String.

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

27 Engineering
 Connecting to Database
 There are 5 steps to connect any java application with the database in java
using JDBC. They are as follows:

1. Register the driver class

2. Creating connection
3. Creating statement
4. Executing queries
5. Closing connection

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

28 Engineering
 1. Register the driver class
 The Class.forName() method is used to register the driver class. This method
is used to dynamically load the driver class.

 Syntax of forName() method

public static void forName(String className)throws ClassNotFoundException

 Example to register with JDBC-ODBC Driver

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

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

29 Engineering
 2. Creating connection
 The DriverManager .getConnection() method is used to establish
connection with the database.

 Syntax of getConnection() method

public static Connection getConnection(String url)throws SQLException

public static Connection getConnection(String url,String name,String password)

throws SQLException

 Example establish connection with Oracle Driver

Connection con = DriverManager.getConnection
("jdbc:odbc:DemoDB","username","password");

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

30 Engineering
 3. Creating statement
 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

 Example to create the statement object

Statement stmt=con.createStatement();

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

31 Engineering
 4. Executing queries
 The executeQuery() method of Statement interface is used to execute queries to
the database. This method returns the object of ResultSet that can be used to get
all the records of a table.

 Syntax of executeQuery() method

public ResultSet executeQuery(String sql)throws SQLException

 Example to execute query

ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

32 Engineering
 5. Closing connection
 By closing connection object statement and ResultSet will be closed
automatically. The close() method of Connection interface is used to close
the connection.

 Syntax of close() method

public void close()throws SQLException

 Example to close connection

con.close();

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

33 Engineering
 Example to Connect Java Application with
MySQL Database

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

34 Engineering
 import java.sql.*;
class MysqlCon{
public static void main(String args[]){
try {
Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sun","root",“123
"); //here sun is database name, root is username and password
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
con.close();
}
catch(Exception e) {
System.out.println(e);
}
} }//C:\Program Files\Java\jre1.6.0\lib\ext
AJP- Unit-V Mrs.Chavan P.P. Department of Computer
35 Engineering
 Prepared Statement Example
import java.sql.*;
class InsertPrepared{
public static void main(String args[]){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sun","root",“123");
preparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");
stmt.setInt(1,101); //1 specifies the first parameter in the query
stmt.setString(2,"Ratan"); //2 specifies the second parameter in the query
int i=stmt.executeUpdate();
System.out.println(i+" records inserted");
con.close();
}catch(Exception e){ System.out.println(e);}
}
}

AJP- Unit-V Mrs.Chavan P.P. Department of Computer

36 Engineering