What is JDBC?
The JDBC ( Java Database Connectivity) defines an application programming interfaces and classes for connecting java programs with database.
Using JDBC programmers can send SQL, PL/SQL statements to almost any relational database. JDBC can be used for executing SQL statements. It supports the basic SQL functionality. It provides RDBMS access by allowing programmers to embed SQL inside Java code.
JDBC Architecture
JDBC drivers
JDBC drivers are divided into four types . The different types of jdbc drivers are:
Type 1: JDBC-ODBC Bridge driver (Bridge)
Type 2: Native-API/partly Java driver (Native)
Type 3: AllJava/Net-protocol driver (Middleware)
Type 4: All Java/Native-protocol driver (Pure)
Type 1: JDBC-ODBC Bridge driver
The Type 1 driver translates all JDBC calls into ODBC calls and sends them to the ODBC driver.
Advantage
The JDBC-ODBC Bridge allows access to almost any database, since the database's ODBC drivers are already available.
Disadvantages
1. Since the Bridge driver is not written fully in Java, Type 1 drivers are not portable.
2. They are the slowest of all driver types.
3. The client system requires the ODBC Installation to use the driver.
4. Not good for the Web.
Type 2: Native-API/partly Java driver
The Type 2 drivers convert JDBC calls into database-specific calls. This driver is specific to a particular database. Example: Oracle will have oracle native API. (native API means Database Library API)
Advantage
The Type 2 JDBC drivers offer better performance than the Type 1.This is because of two reasons
1. The layers of communication in Type 2 are less than that of Type 1 .
2. It uses Native API which is Database specific.
Disadvantage
1. Native API must be installed in the Client System and hence type 2 drivers cannot be used for the Internet.
2. Like Type 1 drivers, it’s not written in Java Language which forms a portability issue.
3. If we change the Database then we have to change the native API as it is specific to a database
Type 3: All Java/Net-protocol driver
Type 3 drivers accepts calls from JDBC and passed through the network to the middle-tier server(middleware component). The middle-tier then translates the request to the database. The middle-tier server can in turn use Type1, Type 2 or Type 4 drivers.
Advantage
1. This driver is server-based, so there is no need for any vendor database library to be present on client machines.
2. This driver is fully written in Java and hence Portable. It is suitable for the web..
3. The net protocol can be designed to make the client JDBC driver very small and fast to load.
4. This driver is very flexible allows access to multiple databases using one driver.
5. They are the most efficient amongst all driver types.
Disadvantage
It requires another server application to install and maintain. Traversing the recordset may take longer, since the data comes through the backend server.
Type 4 : Native-protocol/all-Java driver
The Type 4 driver uses java networking libraries to communicate directly with the database server.
Advantage
1. The major benefit of using a type 4 JDBC drivers are that they are completely written in Java to achieve platform independence
2. It is most suitable for the web.
3. Number of translation layers is very less i.e. Type 4 JDBC drivers don't have to translate database requests to ODBC or a native connectivity interface or to pass the request on to another server.
4. performance is also good.
3. No need to install special software on the client or server. Further, these drivers can be downloaded dynamically.
Disadvantage
With type 4 drivers, the user needs a different driver for each database.
Java Database Connectivity Steps
1. Before creating a java jdbc connection to the database, programmers must first import the java.sql package.
import java.sql.*;
2. . Loading a database driver.
Programmers can load the driver class (java.sql.Driver) by calling the method Class.forName() .This method has Driver class name as an argument. Class is a class in java.lang package.
Syntax for loading driver
try
{
Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);
}
catch(Exception x)
{
System.out.println( “Unable to load the driver class!” );
}
3. Creating a Connection
The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver.DriverManager is considered the backbone of JDBC architecture. DriverManager class manages the JDBC driver.
DriverManager’s getConnection() method is used to establish
a connection to a database.
Syntax:
DriveManager.getConnection(URL). The URL always has three parts.
Protocol:subprotocol:subname
Protocol is always JDBC.
Subprotocol is used for specifying the type of driver .
Example:If the driver is JDBC-ODBC bridge then the subprotocol must be odbc.
Subname is used to identify the database.
4.Creating Connection object
Once a connection is obtained user can interact with the database.For the interaction with the database Java implements Connection interface.The methods defined in the Connection interface helps programmer to interact with the database through the established connection ( DriveManager.getConnection) .
ie Connection c=DriveManager.getConnection(URL)
5.Creating Statement object
Statement is an interface defined in sql package.This interface has createStatement() method.This method helps to creates an SQL statement object from the Connection object .
ie Statement st=c.createStatement();
The Statement object st is used to send and execute SQL statements to a database.
6.Executing SQL statements using Statement interface
Statement interface defines three methods to execute the SQL statements.
They are
1. executeQuery()- executes SELECT statement .
2. executeUpdate()-executes statements that create or modify tables,
3. execute()-executes an SQL statement that is written as String object
ie st.executeQuery("select * from Employees");
executing the SQL statement and sendind to database using st.
7.Resultset
Resultset is an interface defined in sql package. It provides access to a table of data generated by executing a Statement interface. When we execute a SELECT statement with the executeQuery() method, it will return a Resultset object. The data in a ResultSet object is organized in rows and columns.
ie r=st.executeQuery("select * from Employees");
In the above statement r is pointing to the Employees table.
Resultset defines a method next().
r.next() - Move the cursor to the first row
Example :
Connecting MS-Access database using JDBC
import java.sql.*;
public class jdbc1
{
public static void main(String args[])
{
ResultSet r;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c=DriverManager.getConnection("jdbc:odbc:emp");
Statement st=c.createStatement();
r=st.executeQuery("select * from Employees");
while(r.next())
{
System.out.println(r.getInt(1)+"\t"+r.getString(2)+"\t"+r.getInt(3)+"\t"+r.getString(4));
}
}
catch(SQLException e)
{
System.out.println("SQL Error");
}
catch(Exception e)
{
System.out.println("Error" +e);
}
}
}
output:
C:\Program Files\Java\jdk1.5.0_07\bin>javac jdbc1.java
C:\Program Files\Java\jdk1.5.0_07\bin>java jdbc1
1 MCA 122 ee
2 MBA 125 hh
Important points
1. what are the differences between ODBC and JDBC?
1.ODBC(OpenDatabaseConnectivity) is for Microsoft and JDBC(Java DatabaseConnectivity) is for java applications.
2.ODBC can't be directly used with Java because it uses a C
Interface
3.ODBC makes use of pointers which have been removed
totally from java.
4.ODBC requires manual installation of the ODBC driver
manager and driver on all client machines.JDBC drivers are
written in java and JDBC code is automatically
installable,secure,and portable on all platforms.
5.ODBC is procedural oriented and JDBC is object oriented.
2.Explain the different drivers in java?
3.Create databaseconnectivity using JDBC?
Friends I have searched for some e-books and notes of various authors and college i will be soon be posting them here
