Java is a pure object-oriented programming (OOP) language while relational databases use sequential programming language (SQL). Both of them have their own data type, methods and programming techniques. OOP manages objects where SQL manages tables. It is pretty clear that both of them cannot communicate directly with each other due to the unavailability of compatible language support. Direct communication is not possible so the unit is required to make them communicate even though these two are fundamentally different. The Java Database Connectivity (JDBC) architecture will be managed by JDBC APIs from a Java application which helps to manage the incompetency. The one which manages the language and data type difference is DriverManager
. The following figure illustrates this:
JDBC architecture
The JDBC driver is an implementation that defines the interface in JDBC API for interacting with the database server.
The four types of drivers that facilitate JDBC programming on the variety of platforms and operating systems are as follows:
- JDBC-ODBC bridge driver
- JDBC Native API Driver / Partly JAVA Driver
- JDBC Net Protocol Driver
- All Java drivers
We shall now see all types of driver and their advantages and disadvantages.
JDBC-ODBC bridge driver
The Type 1 driver uses JDBC API to the Open Database Connectivity (ODBC) API to access a database. The Type 1 driver is installed on the client machine which translates all the JDBC calls into ODBC calls. In order to connect to the database it uses an ODBC driver with the help of Data Source Name (DSN).
JDBC-ODBC bridge driver
Advantages of the JDBC-ODBC bridge driver
These types of drivers are freely installed on the system, which allows access to almost all types of databases.
Disadvantages of the JDBC-ODBC bridge driver
The disadvantages of using the JDBC-ODBC bridge driver are as follows:
- When a program executes the JDBC, the call gets converted first of all to JDBC-ODBC and then later from JDBC-ODBC to ODBC. As there are a high number of conversions, the working of the driver is slow, which hampers performance.
- The JDBC-ODBC Driver uses system installations so in order to use a Type 1 driver, the ODBC driver needs to be installed on the client system.
- The Type 1 driver uses DSN, which needs to be created on every client machine to connect with a database. This leads to restricting the use of the application to desktops. It's not useful for web applications.
JDBC Native API Driver/Partly Java Driver
The Type 2 Driver converts JDBC calls to the native calls of the database API, which are database specific. It needs some binary code to be present on the client machine to have communication with the database server.
JDBC Native API Driver/Partly JAVA Driver
Advantages of the JDBC Native API Driver
The advantage of using the JDBC Native API Driver is as follows:
- In comparison with the Type 1 driver, it gives better performance
Disadvantages of the JDBC Native API Driver
The disadvantages of using the JDBC Native API Driver are as follows:
- As a vendor-specific native API must be installed on each client machine, it leads to problems using it for the Internet
- The communication with the database server is dependent upon the native API, which leads to problems when the database changes
JDBC Net Protocol Driver
The Type 3 is useful in the case of a middleware server. The JDBC application will send the request to this middleware server, which translates JDBC calls to the respective database.
JDBC Net Protocol Driver
Advantages of the JDBC Net Protocol Driver
The advantages of using the JDBC Net Protocol Driver are as follows:
- Unlike the Type 2 driver, in the Type 3 driver there is no need for a vendor-specific database library to be present on each and every machine
- It is portable as it is written in Java
- It can be used for web applications as there is no client-specific code or configuration
Disadvantages of the JDBC Net Protocol Driver
As the Type 3 driver uses a middleware server, it's required to install a separate server application where database-specific coding needs to be performed.
All Java drivers
The Type 4 drivers are also called pure Java drivers as they are completely written in Java. The Type 4 driver converts JDBC calls directly to the database-specific protocol with minimum conversions.
Pure Java driver
Advantage of Java drivers
The advantage of Java drivers is as follows:
- The conversion of calls is from JDBC API to database-specific calls, leading to the best performance among all other drivers
Disadvantages of the Java Driver
The disadvantage of the Java Drivers is as follows:
- As Type 4 driver is specific to the database, whenever the database changes the driver has to be changed accordingly
Let's look at a JDBC application which will insert contact details in the MySQL database using Type 4 driver with the help of the following steps:
- Register Type 4 driver of MYSQL.
- Get a
Connection
object. - Fire the query using
PreparedStatement
to insert a record. - Close all resources.
- Add the required jar files for MySQL.
Let's start the development:
- Create a new Java application
Ch04_ContactManagement
. - Create or copy the
Contact
and Gender POJOs which we used in previous examples. - Create a new interface as
ContactDAO
incom.packt.ch04.dao
package as follows:
public interface ContactDAO {
int addContact(Contact contact);
}
- DAO is a Data Access Object which provides an interface to the database or persistence mechanism. It provides data operations without exposing the actual implementations. The examples which we are going to take while explaining how to handle databases will practically show the power of DAOs.
- Create a class
ContactDAOImpl
in packagecom.packt.ch04.dao
which implementsContactDAO
. - Add a data member
Connection
inContactDAOImpl
and initialize it in a constructor. As we are using Type 4 driver for MySQL, the code will be as follows for registering the driver:
Class.forName("com.mysql.jdbc.Driver");
Note
Handle the
ClassNotFoundException
.
- To obtain a
Connection
object, we need to pass the URL, username and password as:
connection=DriverManager.getConnection("jdbc:mysql:3306/localhost/contactDB","root","mysql");
- Implement the method
addContactdecalred
in theContactDAO
interface. In this we will fire the query to insert a record in theContact_CORE
table with runtime values. The code snippet will be as follows:
public int addContact(Contact contact)
{
int record=0;
try {
PreparedStatement ps=connection.prepareStatement("insert
into Contact_CORE values(?,?,?,?,?,?)");
ps.setString(1,contact.getFirstName());
ps.setString(2,contact.getLastName());
ps.setInt(3,contact.getGender());
ps.setString(4,contact.getAddress());
ps.setString(5,contact.getEmail());
ps.setString(6,contact.getPhone_number());
record=ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return record;
}
- Let's now create
ContactMain
with amain
function as follows:
public static void main(String[] args) {
// TODO Auto-generated method stub
ContactDAO contactDAO=new ContactDAOImpl();
Contact contact=new Contact();
contact.setFirstName("John");
contact.setLastName("Ray");
contact.setGender(1);
contact.setAddress("JB Road");
contact.setEmail("This email address is being protected from spambots. You need JavaScript enabled to view it.");
contact.setPhone_number("9845321234");
int record=contactDAO.addContact(contact);
if(record==1)
{
System.out.println("RECORD INSERTED SUCCESSFULLY");
}
else {
System.out.println("RECORD NOT INSERTED PLEASE TRY AGAIN");
}
}
Note
Here we are adding hardcoded values just for checking but we can even write code to take data from the user.
- Now add the
mysql-connector
jar file. You can download it from this or this link. - Finally, set up the MySQL database by creating the database and contact_core table. We can set up the database as follows:
Create database contactDB.
Use contactDB.
Create table Contact_core(firstname varchar(20),
lastname varchar(20),
gender int,
address varchar(100),
email varchar(20),
phone_number varchar(20)).
- The same schema will be used throughout my blog.
- Execute the main code; if there is no exception, one row will be added to the MySQL database. We can check it on the MySQL console as follows: