Java Database Connectivity (JDBC)
Introduction:
It is used to connect the Java program to the
Database. Java cannot directly interact with the database. So it uses the 3rd
party application to connect and make the software dynamic.
JDBC API (Application Program Interface):
It is the set of classes and interfaces under ‘java.sql’
package that is used to connect a java program to the database and allows to
make the communication between them. It is important to connect the database
to the program because it helps to make
the dynamic programs and make it easier to use the program as well.
Classes under JDBC API:
a) DriverManager:-
It is used to establish the connection to the database.
b) SQLException:-
It represents the error caused by the SQL Query. If
specified database table, column does
not exist or if there id mistake in SQL Query.
c) ClassNotFoundException:-
It represents the exception caused if suitable database
friber is not found.
This is all about classes in java. Now lets see the
INTERFACES of the JDBC API.
Interface under JDBC API:
a) Connection:-
It is used to hold the connection to the database.
b) Statement and PreparedStatement:-
Both are used to execute SQL Query. However
PreparedStatement prevents SQL injection attack. PreparedStatement is mostly
used due to the security purpose.
c) CallableStatement:-
It is used to execute the stored procedure.
d) ResultSet:-
It is used to hold the data from the database data
into the main memory.
e) ResultSetMetaData:-
It is used to get addition information from
ResultSet like, name of columns, number of columns etc.
This is all about the Classes and Interface of the JDBC API
Steps for JDBC Connectivity
a) Load the database driver:-
Class.forName(“DriverName”);
b) Create connection to the database:-
Connection
connect=DriverManager.getConnection(“url”);
c) Create SQL Query and Execute it:-
String sql=”INSERT INTO
…………… “;
Statement
st=connect.createStatement();
st.execute(sql);
d) Close the Connection:-
Connect.close();
For example;
import java.sql.*;
// some codes
String url="jdbc:mysql://localhost:3306/java_db";
String username="root";
String pass="";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection(url, uname, pass);
String sql="INSERT INTO tbl_name(col1,col2,...) VALUES(?,?,...)";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1, val1);
pst.setString(2, val2);
|
|
pst.executeUpdate();
conn.close();
}catch(Exception ex){
System.out.println(ex);
}
This is the basic syntax to establish the connection between the java api and database. Later on we will discuss further more on this topic. Stay tuned for the further lessons here.
*****************************************
Types of JDBC Drivers:
Introduction:
As we know Java alone cannot communicate with the
database so it needs the Java Database Connectivity drivers. There are four
types of JDBC Drivers, they are as follows;
a) Type 1 [JDBC-ODBC Bridge Driver]:
JDBC-ODBC Bridge Driver converts the Java calls to
ODBC (Open Database Connectivity) calls and ODBC to DB (Database) Specific
calls. And in the similar way the, DB (Database) Specific calls are also
converted to ODBC calls and JDBC-ODBC Brigde driver converts it to Java calls. In
this way JDBC-ODBC Bridge Driver is used to make the communication between the
Java Application and Database. From the given figure above you can interpret in
your own ways.
Advantages:
.No need to search for drivers in the internet, it
is already installed.
.Works with any database.
Disadvantages:
.Works only in windows.
.Slow in communication.
b) Type 2 [Native API Driver]:
It uses the client-side libraries of the databse. It
converts JDBC method calls into native calls of database API. Also in similar
way, Native API Driver converts the DB specific calls to the JDBC Specific
calls. It is not entirely written in Java.
Advantages:
.Faster than Type 1
.Can be used in all platform but has separate
versions.
Disadvantages:
.You have to download it separately.
.Database Dependent.
c) Type 3 [Network Protocol Driver]:
Network Protocol Driver (NPD) convertes JDBC API
method calls to Middleware Application Server (MAS) and Middleware Application
Server (MAS) to Database (DB) specific calls. Also, the Middleware Application
Server Converts the DB (Database) Specific calls to Network Protocol Driver and
Network Protocol Driver to JDBC Specific calls.
Advantages:
.Purely Written in Java
.Works with any database.
Disadvantages:
.Economically not feasible.
.Slower in communication.
d) Type 4 [Native Protocol Driver]:
It directly interact with database. It does not
require any native database library. It is not purely written in Java.
Advantages:
.Faster Connection.
.Works in any platform.
Disadvantages:
.Database Dependent.