JDBC
Java JDBC
• JDBC stands for Java Database Connectivity.
• JDBC is a Java API to connect and execute the query with the
database.
• JDBC API uses JDBC drivers to connect with the database.
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 (Network Protocol driver )
• Type -4 driver (Thin driver (fully java driver))
1. Type -1 driver (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 method calls.
• ODBC driver convert ODBC method calls into database specific
method calls.
Database
Machine
Cont…
• Advantages:
• It is easy to use.
• It is database independent driver.
• Disadvantages:
• It is slowest driver because JDBC method calls is converted into the ODBC method
calls and ODBC method calls into database specific method calls .
• The ODBC driver needs to be installed on the client machine.
• This driver internally depends on ODBC driver which will work only on window
machine so this driver is platform dependent driver.
• No support from JDK 1.8 version onwards.
2. Type -2 driver (Native-API driver)
(Partially java driver)
• The Native API driver uses the client-side libraries of the database.
• This driver converts JDBC method calls into database specific native
libraries calls.
• Database specific native libraries calls can be understandable directly
by database.
• It is not written entirely in java.
Database
Machine
Cont…
• Advantage:
• performance upgraded than JDBC-ODBC bridge driver.
• No ODBC driver requirement.
• Disadvantage:
• The Native driver needs to be installed on the each client machine.
• The Vendor database library needs to be installed on client machine.
• Database dependent driver.
• Platform dependent driver.
3. Type -3 driver (Network Protocol driver)
• The Network Protocol driver uses middleware (application server).
• This driver converts JDBC method calls into middleware specific
method calls and middleware converts middleware specific method
calls into the database specific method calls.
• It is fully written in java.
Middleware server
Machine
Database
Machine
Cont…
• Advantage:
• No client side library is required because of application server.
• It is a database independent driver.
• It is a platform independent driver.
• Disadvantages:
• Network support is required on client machine.
4. Type -4 driver (Thin driver)
• The thin driver converts JDBC methods calls into the database specific
method calls directly.
• That is why it is known as thin driver.
• It is fully written in Java language.
Database
Machine
Cont…
• Advantage:
• Better performance than all other drivers.
• No software is required at client side or server side.
• It is a platform independent driver.
• Disadvantage:
• It is a database dependent driver.
Java Database Connectivity
• There are 5 steps to connect any java application with the database
using JDBC.
• These steps are as follows:
• Register the Driver class
• Create connection
• Create statement
• Execute queries
• Close connection
1. Register the driver class
• The forName() method of Class class 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 the OracleDriver class
• Here, Java program is loading oracle driver to esteblish database connection.
[Link]("[Link]");
where OracleDriver = Driver class for the oracle database
2. Create the connection
object
• The getConnection() method of DriverManager class is used to establish
connection with the database.
Syntax of getConnection() method
• public static Connection getConnection(String url,String name,String password) throws SQLException
Example to establish connection with the Oracle 10g database
• Connection con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","system","password");
• jdbc:oracle:thin:@localhost:1521:xe -> Connection url
• where jdbc is the API, oracle is the database, thin is the driver, localhost is
the server name on which oracle is running, we may also use IP address, 1521
is the port number and XE is the Oracle service name.
3. Create the Statement object
• The createStatement() method of Connection interface is used to
create statement.
• This method returns the object of Statement interface.
• The object of Statement interface 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 = [Link]();
4. Execute the query
• The executeQuery() method of Statement interface is used to execute
select query to the database.
• This method returns the object of ResultSet interface 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 executeQuery() method
• ResultSet rs=[Link]("select * from emp");
while([Link]())
{
[Link]([Link](1)+" "+[Link](2));
}
5. Close the connection object
• 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
[Link]();
Connectivity JSP Page to Data Base
(Connect JSP Page with Oracle 10g database)
<%@ page import="[Link].*"%>
<%
try
{
[Link]("[Link]");
Connection con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","system","password");
Statement stmt = [Link]();
ResultSet rs=[Link]("select * from emp");
while([Link]())
{
[Link]([Link](1)+" "+[Link](2));
}
[Link]();
}
catch (Exception e)
{
[Link](e);
}
%>
Method of Statement
Interface
• public int executeUpdate(String sql) throws SQLException : This method is used to execute specified query,
it may be insert, update, delete etc .
• This method returns no of rows that are affected by the execution of query
Example to executeUpdate() method
• [Link]("insert into emp values(3,'Irfan')");
• [Link]("update emp set ename='Vimal‘ where id=2");
• [Link]("delete from emp where id=3");
Connectivity JSP Page to Data Base
(Connect JSP Page with Oracle 10g database)
<%@ page import="[Link].*"%>
<%
try
{
[Link]("[Link]");
Connection con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","system","password");
Statement stmt = [Link]();
[Link]("insert into emp values(3,'Irfan')");
[Link]();
}
catch (Exception e)
{
[Link](e);
}
%>
<%@ page import="[Link].*"%>
<%
try
{
[Link]("[Link]");
Connection con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","system","password");
Statement stmt = [Link]();
[Link]("update emp set ename='Vimal‘ where id=2");
[Link]();
}
catch (Exception e)
{
[Link](e);
}
%>
<%@ page import="[Link].*"%>
<%
try
{
[Link]("[Link]");
Connection con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","system","password");
Statement stmt = [Link]();
[Link]("delete from emp where id=3");
[Link]();
}
catch (Exception e)
{
[Link](e);
}
%>
Cont…
• public boolean execute(String sql) throws SQLException : This method used
for all types of SQL statements.
• This method returns true if ResultSet object is returned or return false if int
value is returned
Example to execute() method
• [Link](“create table emp (id number(10), name varchar2(20))");
<%@ page import="[Link].*"%>
<%
try
{
[Link]("[Link]");
Connection con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","system","password");
Statement stmt = [Link]();
[Link](“create table emp (eid number(10), ename varchar2(20))");
[Link]();
}
catch (Exception e)
{
[Link](e);
}
%>