Java is one of the powerful languages used in the IT industry to develop a number of software projects. To be a successful Java programmer, a developer needs to understand one of the important concepts of Java i.e. JDBC – Java Database Connectivity or JDBC.

Below is the list of topics that we will be covering in this Java Database Connectivity discussion:

• Whatis JDBC?

• What are the different types of drivers?

• How JDBC works and why it is  needed

• JDBC architecture and its various components

• Steps involved in connecting Java application with the database using JDBC

• JDBC connection in Java with Oracle

• Connectivity with MySQL using JDBC

• Java Database Connectivity packages

• Which database is best used for Java?

• Advantages of Java Database Connectivity

• Difference between JDBC and ODBC drivers.

To understand Java Database Connectivity, the developer needs to have prior knowledge of Java programming. You can take help of online Java courses to learn the basics of Java program.

What is JDBC?

JDBC stands for Java Database Connectivity. It is a step by step procedure to communicate with the database from the Java application in order to perform database operation. Basically, JDBC is an Application Programming Interface (API) that makes it possible to standardize and simplify the process of connecting Java applications to a database.

DBC is considered to be a part of Java platform, Standard Edition (Java SE) uses the Structured Query Languages (SQL) for performing different database queries like access, storage, update or delete.

Relational Database Management System (RDBMS) supports Structured Query Language (SQL). Since Java is a platform independent programming language which runs on most platforms, JDBC helps to write a single database application which can run on different platforms and interact with different Database Management Systems.

What are the different types of JDBC drivers?

As we have understood, JDBC is used to connect Java applications with the database. We should also know that JDBC uses different JDBC drivers to perform this task. Basically, a JDBC driver is a software component which enables Java applications to interact with the database.

There are 4 different types of JDBC drivers. These are:

• JDBC-ODBC Bridge Driver
• Native-API Driver (partially Java driver)
• Network Protocol Driver (fully Java driver)
• Thin Driver (fully Java driver).

Let us discuss each of these drivers independently:

• JDBC-ODBC Bridge Driver/Type-1 Driver: There are some databases which provide ODBC driver to connect their databases. Here, ODBC stands for Open Database Connectivity.

A Java application which needs to communicate with a database containing ODBC driver will use JDBC API to communicate with JDBC-ODBC Bridge driver. Here, this JDBC-ODBC bridge driver will communicate with the ODBC driver and then further establish a connection with its specific database.

Advantage: 1)It helps to easily connect with the database.

Disadvantage: 1)Performance decreases as JDBC method call needs to be converted into ODBC function call.

• Native-API driver/Type-2 Driver: Some database vendors provide only Native APIs, written in C/C++ programming languages, in order to access their database.

A Java application which wants to communicate with such databases which provide only Native APIs uses Native API driver to establish a communication. Java application is programmed using JDBC API, makes JDBC calls. Then, these JDBC calls are converted into database specific native calls using Native-API driver. Further, these native calls are passed over to database specific native library which communicates with the database.

Advantage: 1) Performance is upgraded compared to JDBC-OBDC/Type 1 driver.

Disadvantage: 2) Need to install Native API driver on each of the client machine.

• Network Protocol Driver/Type-3 Driver: The Network Protocol Driver uses middleware to convert JDBC calls directly or indirectly into vendor-specific database protocol.

Advantage: 1) No client libraries required to install on client machines.

Disadvantage: 1) Performance is slower as the number of network calls to an intermediate middleware server.

• Thin Driver/Type-4 Driver: Thin Driver directly communicates with the database. This driver doesn’t require any native database library or middleware server to communicate with the database. Here, the thin driver converts JDBC calls made by Java application into vendor-specific database protocol.

Advantage: It doesn’t require any software at client side or server side.

Disadvantage: It is a database dependent driver.

How JDBC works

Let’s quickly look at how JDBC works. JDBC establishes a connection with a data source, sending queries, updating statements and processing results. JDBC  helps developers with the following aspects:

• It helps to establish a connection with a data source
• It allows to send queries and updates statements
• It helps to fetch the data from the database and process the fetched results.

Here, the Java application calls the JDBC to submit the SQL statements queries and get the results. The JDBC driver which constitutes a set of classes helps to implement the JDBC API. The database stores all the data that is retrieved by the JDBC driver.

Why JDBC is needed?

It is important to understand  why  we need Java Database connectivity. Java applications are required to connect with database. Java applications are written in Java programming language, but the database only understands Structured Query Language (SQL). In order to establish a connection between Java application and database, JDBC is used. JDBC contains a set of interface and classes which helps to connect Java application to the database.

The latest version of JDBC is 4.3 and is released on 21 September 2017.

JDBC Architecture

The JDBC API supports 2 different types of model to access the database they are:

1. Two-tier model.
2. Three-tier model.

1. Two-tier model:

In the two-tier model, a Java application directly communicates with the database. It requires a JDBC driver to establishes the communication with the particular database.

When the user sends the query to the database, results for those queries are sent back to the user. The database may be present on the same machine or it may be located in any remote machine connected via a network. This type of architecture is called as a client-server architecture.

•  Three-tier model:

In the three-tier model, the user queries are sent to the middle-tier services, from there they are sent to the database. Then, the database processes the queries and results are sent back to the middle tier, and from there they are sent to the user. This type of architecture increases the performance and simplifies the application deployment.

Basic JDBC architectural diagram:

As JDBC supports both two-tier model and three-tier model for database access. However, JDBC architecture has two layers as listed below.

• JDBC API: This layer supports helps to connect Java application to JDBC Driver Manager. It uses driver managers and also database specific drivers to establish transparent connectivity to different databases.

• JDBC Driver API: This layer establishes the connection of JDBC Manager to driver. This driver manager takes care that correct driver is being used in accessing each of the data sources.

JDBC components:

Let us discuss 6 main components of JDBC:

1. DriverManager: DriverManager is a class which manages all the database drivers. The DriverManager is used to load the specific database drivers in an application to establish a connection with the database.
2. Driver: Driver is the interface which manages the communications happening between the application and the database server.
3. Connection: Connection is an interface which contains methods for contacting the database.
4. Statement: Statement is an interface that creates an object to submit SQL queries or statements to the database.
5. ResultSet: ResultSet contains the results that are retrieved from the database after the execution of SQL statements or queries.
6. SQLException: SQLException class is used to handle any of the errors that occur in a database application.       

5 Steps involved to connect Java application with the database using JDBC

There are 5 steps involved in connecting a Java application with the database using JDBC. They are as follows:

1. Load the driver class: Load the JDBC driver class into Java Virtual Machine (JVM). The forName() method of class is used to load the driver class.

• Establish the connection: Here a connection between a Java program with the database is established using getConnection() method provided by DriverManager class.

• Create statement: In this step, the statement object is created using createStatement() method of connection interface. This statement object is used to interact with the database.

• Execute query: This step involves executing queries to the database and getting  the output by using executeQuery() method Statement interface.

• Close the connection: The close() method provided by Connection interface is used to close the connection.

JDBC connection in Java with Oracle:

Let us understand how to connect Java application with Oracle database using JDBC. There are 5 steps involved in performing this task. In this discussion, we will use Oracle 10g as the database.

Let us see some of the information about the Oracle database:

• Driver class: Driver class for oracle is oracle.jdbc.driver.OracleDriver.
• Connection URL: jdbc:oracle:thin:@localhost:1521:xe

In this connection URL jbdc is API, oracle is the database, thin is the driver, @localhost is the name of the server in which oracle is running, 1521 is the post number, xe is the service name oracle database.

• Username: The default username of oracle database is “system”.
• Password: During the installation of oracle database password is given by the username.

Create table:

Initially, we need to create a table in oracle database for fetching all the records from the oracle database using the SQL query shown below.

create table employee(eid number(10), name varchar2(20));

Now, look into a Java program to connect Java application with Oracle Database. Here, we are connecting to an Oracle Database to fetch records from a table.

In this example, “system” is the default user name and “manager” is the password for oracle database.

import java.sql.*;
class JdbcConnectionWithOracle {
public static void main(String args[]){
try{
//Step1 load the driver class
Class.forName(“oracle.jdbc.driver.OracleDriver”);
//Step2 create the connection object
Connection con=DriverManager.getConnection(
“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”manager”);
//Step3 create the statement object
Statement stm=con.createStatement();
//Step4 execute query
ResultSet rs=stm.executeQuery(“select * from employee”);
while(rs.next())
{
System.out.println(rs.getInt(1)+” “+rs.getString(2));
}
//Step5 close the connection object
con.close();
}
catch(Exception e){System.out.println(e);}
}
}

The above program is the example of connecting Java program to Oracle Database using JDBC.

Connectivity with MySQL using JDBC

Now we will understand how to establish a connection between Java application and MySQL database using JDBC. We need to follow 5 steps to perform this task.

We will consider an example to understand how to connect MySQL database with Java using JDBC. Here we will use MySQL as the database. So, let us know some information about the MySQL database.

Driver Class	com.mysql.jdbc.Driver
Connection URL	jdbc:mysql://localhost:3306/organization   Here jdbc is the API, mysql is the database, localhost is the server name in which mysql database is running, 3306 is the port number, “organization” is the database name.
Username	The default username for MySQL database is “root”
Password	Password is given by the user at the time of creating MySQL database. Here in this example, we use “root” as the password.

Create a database and a table:

Initially, we need to create a database and then create a table in MySQL database.

create database organization;

use organization;

create table employee (eid number (10), name varchar number(20));

Now we see a Java program to connect Java application to MySQL database using JDBC.

In this example, “root” is the default username and “root” is the password for MySQL database.

import java.sql.*;
class JdbcConnectionWithMySQL {
public static void main(String args[]){
try{
//Step1 load the driver class
Class.forName(“com.mysql.jdbc.Driver”);
//Step2 create the connection object
Connection con=DriverManager.getConnection(
“jdbc:mysql://localhost:3306/organization”, “root”, “root”);
//Step3 create the statement object
Statement stm=con.createStatement();
//Step4 execute query
ResultSet rs=stm.executeQuery(“select * from employee”);
while(rs.next())
{
System.out.println(rs.getInt(1)+” “+rs.getString(2));
}
//Step5 close the connection object
con.close();
}
catch(Exception e)
{
System.out.println(e);}
}
}

The above program is the example of connecting Java program to MySQL database using JDBC.

Java Database Connectivity Packages:

JDBC packages need to be imported at the beginning of the program. Java Database Connectivity API consists of two packages:

• java.sql
• javax.sql

Which Database is best suited for Java?

As there are number of databases available which help to develop software projects on Java,  it is difficult to say which is the best database among all of them. There are various factors involved while choosing a right database to develop a software application. For example, you have to consider whether you are looking for an open source database or a paid version of database, or if you want to use a cloud based database matters.

Some of the databases which can be used for Java are:

1. Oracle
2. MySQL
3. PostgreSQL
4. IBM-DB2
5. MS-SQL

Advantages of Java Database Connectivity:

Java Database Connectivity comes up with several advantages, some of them are:

1. JDBC itself creates XML format of data from the database automatically
2. JDBC supports modules
3. JDBC provides better security
4. JDBC completely supports query and stored procedure
5. JDBC supports both types of processing i.e. Synchronous and Asynchronous processing
6. JDBC does not require content conversion.

Difference between JDBC and ODBC drivers:

As we know, that Java Database Connectivity (JDBC) is an API for Java Programming Language used to connect the Java application to the database, whereas Open Database Connectivity (ODBC) is a standard application programming interface used for connecting application and database.

Let us look into some of the differences between JDBC and ODBC.

JDBC	ODBC
JDBC is platform independent.	ODBC is platform dependent.
Code written in JDBC is easy to understand.	Code written in ODBC is complex to understand.
JDBC can be used only with Java programming language.	ODBC can be used in C, C++, Java programming language.

Conclusion:

JDBC is a very important concept which every Java developer needs to understand to develop projects. We hope this blog helps our reader to understand various concepts related to JDBC.

You can also find online Java courses which help you to know the list of Java courses we have for our readers.

Let us know which concept from this overall discussion you found more valuable.