Types of JDBC drivers and MySQL database connection example

JDBC drivers for MySQL databaseJava 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

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:

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

 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:

 

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

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:

Disadvantages of the JDBC Native API Driver

The disadvantages of using the JDBC Native API Driver are as follows:

 

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

JDBC Net Protocol Driver

Advantages of the JDBC Net Protocol Driver

The advantages of using the JDBC Net Protocol Driver are as follows:

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

Pure Java driver

Advantage of Java drivers

The advantage of Java drivers is as follows:

Disadvantages of the Java Driver

The disadvantage of the Java Drivers is as follows:

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:

  1. Register Type 4 driver of MYSQL.
  2. Get a Connection object.
  3. Fire the query using PreparedStatement to insert a record.
  4. Close all resources.
  5. Add the required jar files for MySQL.

Let's start the development:

  1. Create a new Java application Ch04_ContactManagement.
  2. Create or copy the Contact and Gender POJOs which we used in previous examples.
  3. Create a new interface as ContactDAO in com.packt.ch04.dao package as follows:
public interface ContactDAO {
   int addContact(Contact contact);
}
  1. 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.
  2. Create a class ContactDAOImpl in package com.packt.ch04.dao which implements ContactDAO.
  3. Add a data member Connection in ContactDAOImpl 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.

  1. 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");
    1. Implement the method addContactdecalred in the ContactDAO interface. In this we will fire the query to insert a record in the Contact_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;
  }
  1. Let's now create ContactMain with a main 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.

  1. Now add the mysql-connector jar file. You can download it from this or this link.
  2. 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)).
  1. The same schema will be used throughout my blog.
  2. 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:

 MYSQL output

 

Вас заинтересует / Intresting for you:

Getting Started with Java prog...
Getting Started with Java prog... 1621 views Doctor Thu, 02 Aug 2018, 04:05:33
Introducing Java Data Access T...
Introducing Java Data Access T... 1814 views Анатолий Sun, 16 Sep 2018, 09:44:35
Creating TinyCalculator web ap...
Creating TinyCalculator web ap... 9051 views Zero Cool Sun, 23 Sep 2018, 11:10:19
J2ME Specifications and other ...
J2ME Specifications and other ... 1662 views Максим Николенко Sun, 10 Jun 2018, 16:30:17
Print