JDBC
Introduction
JDBC
is a Java API which is used to connect and execute query to the
database.
JDBC
API use JDBC drivers to connect to the database.
JDBC
Driver :
JDBC
driver is a software component that enables Java application to
interact with the database.
4
types of JDBC Drivers -
1.
JDBC-ODBC bridge Driver
The
JDBC-ODBC bridge diver uses ODBC driver to connect to the database.
This
driver converts JDBC method calls into the ODBC function calls.
Advantages
: * easy to use
* can be easily connect to the any database.
Disadvantages
: * Performance degrade since JDBC method calls convert to the ODBC
function calls.
* The ODBC diver needs to install to the client machine.
2.
Native API Driver ( Partially Java 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.
This
is not written entirely in Java.
Advantage
: * Performance upgrade than JDBC-ODBC bridge driver.
Disadvantages
: * The Native driver needs to be installed on each client machine.
* The vendor client libraries needs to be installed on each client
machine.
3.
Network Protocol Driver ( Fully Java Driver )
The
Network Protocol Driver uses middle-ware (application server), that
converts JDBC calls directly or indirectly into the vendor-specific
database protocol.
Advantage
: * No client side library is required, since application server
perform the tasks.
Disadvantages
: * Network support is required on client machine.
* Required database-specific coding to be done in the middle tier.
4.
Thin Driver ( Fully Java Driver )
The
thin driver converts JDBC calls directly into the vendor-specific
database protocol. Therefore this is known as thin driver.
It
is fully written in Java.
Advantages
: * Better performance than all other drivers.
* No software required either client-side or server-side.
Disadvantages
: * Drivers depends on the database.
Steps
connect to the database in Java
Step
1 : Register
the Driver class
By
using forName() method, register to the driver class.
Class.forName("com.mysql.jdbc.Driver");
Step
2 : Create
the connection object.
The
getConnection() method of DriverManager is used to establish
connection with the database.
Connection
connection =
DriverManager
.getConnection("jdbc:mysql://localhost:3306/myworkspace",username,password);
Step
3 : Create
the Statement Object
The
createStatement() method of Connection interface is used to create
statement. The object of Statement is responsible to execute queries
with the datebase.
statement
= connection.createStatement();
Step
4 : Execute
the Query
The
executeQuery() of Statement interface is used to execute queries to
the database.
This
method returns the object of ResultSet, which used to retrieve the
rows of a database table.
ResultSet
rs = statement.executeQuery(selectSQL);
Step
5 : Close
the connection object
The
close() method of Connection Interface is used to close the
connection.
By
closing the connection object Statement and ResultSet will be
automatically closed.
connection.close();
DriverManager
Class
The
DiverManager class act as an interface between user and drivers. It
establish the connection between the database and the appropriate
driver.
registerDriver(Driver
driver) - used to register the given driver
with the DriverManager.
deregisterDriver(Driver
driver) - used to deregister the given driver
(drop the Driver from the Driver list) with the DriverManager.
Connection
getConnection(String url) - used to
establish the connection with the specified url.
Connection
getConnection(String url, String username, String password) -
used to establish the connection with the
specified url, username and password.
Connection
Interface
A
connection is the session between Java application and the database.
The
connection interface provide many methods for transaction management
like commit(), rollback() etc.
By
default, connection commit the changes after executing queries.
Statement
createStatement() - create a statement
object that can use to execute queries.
Statement
createStatement(int resultSetType, int resultSetConcurrency) -
create a statement object that will
generate ResultSet Objects with the given type and concurrency.
void
setAutoCommit(boolean status) - used to
set commit the status. Default is true.
void commit()
- save the changes
void rollback()
- drop the changes
void close()
- close the connection and release the JDBC
resource immediately.
Statement
Interface
The
Statement interface provide methods to execute queries with the
database.
The
Statement interface is a factory of ResultSet.
ResultSet
executeQuery(String sql) - used to
execute “select” queries. It returns the object of ResultSet.
Int
executeUpdate(String sql) - used to
execute specified query. ( create, drop, insert, update, delete )
boolean
execute(String sql) - used to execute
queries that may return multiple results.
Int[]
executeBatch() - used to execute batch
of commands.
ResultSet
Interface
The
object of ResultSet maintains a cursor to pointing to a particular
row of data.
Initially
cursor points to before the first row.
By
default ResultSet object can be move forward only and it is not
updatable.
boolean next()
- used to move the cursor to the one row next
form the current position.
boolean
previous() - used to move the cursor to
the one row previous form the current position.
boolean
first()
- used to move the cursor to the first row in the result set
object.
boolean
last()
- used to move the cursor to the last row in the result set
object.
boolean
absolute(int
row) - used to move the cursor to the specified row number in
the ResultSet.
boolean
relative(int
row) - used to move the cursor to the relative row number in
the ResultSet.
Int
getInt(int
columnIndex) - used to return data of the specified column
index of the current row as int.
int
getInt(String
columnName) - used to return data of the specified column name
of the current row as int.
String
getString(int
columnIndex) - used to return data of the specified column
index of the current row as String.
String
getString(String
columnName) - used to return data of the specified column name
of the current row as String.
