Java Database Connectivity (JDBC)

Java is known as king of programming and 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.

What is JDBC?

Java Database Connectivity (JDBC) is an API specification for connecting applications written in Java to data in popular databases. The JDBC API lets you encode access request statements in Structured Query Language (SQL) that are then passed to the application that manages the database. It returns the results through a similar interface.

JDBC Overview

JDBC is an API developed by Sun Microsystems to enable a standards-based method to access data using the Java language. As is the case with ODBC, JDBC enables a single JDBC application to access a number of data sources and can run on any machine with a Java Virtual Machine (JVM). The JDBC data standard defines a set of Java interfaces to enable application developers abstract data access functionality including:

• Establish Connection with a data source
• Execute SQL queries
• Process result sets

Need of JDBC

It is important to understand why we need Java Database connectivity. Java applications are required to connect with databases. 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 interfaces and classes which helps to connect Java applications to the database.

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 four types of JDBC Drivers in use:

• JDBC-ODBC Bridge
  A JDBC-ODBC Bridge provides application developers with a way to access JDBC drivers via the JDBC API. Type 1 JDBC drivers translate the JDBC calls into ODBC calls and then send the calls to the ODBC driver. Type 1 JDBC drivers are generally used when the database client libraries need to be loaded on every client machine.
• Native API/Partly Java Driver
  A Native API/Partly Java Driver is a partial Java driver because it converts JDBC calls into database specific calls. Type 2 Native API/Partly Java Driver communicates directly with the database server.
• Pure Java Driver
  A Pure Java Driver works in a three tiered architecture. The JDBC calls are passed via the network to the middle tier server. This server translates the calls to the database specific native interface to further request the server. JDBC drivers available from Simba are Type 3 and Type 4 drivers.
• Native Protocol Java Driver
  A Native Protocol Java Driver converts JDBC calls into the database specific calls so that the client applications can communicate directly with the server.

JDBC Working

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.

JDBC Architecture and Components

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

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.

2] 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.

However, JDBC architecture has two layers as listed below.

• JDBC API: This layer supports connecting Java applications 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 the driver. This driver manager takes care that the correct driver is being used in accessing each of the data sources.

JDBC components:

There are 6 main components of JDBC:

1. Driver Manager: Driver Manager is a class which manages all the database drivers. The Driver Manager 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. Result Set: Result Set contains the results that are retrieved from the database after the execution of SQL statements or queries.
6. SQL Exception: SQL Exception class is used to handle any of the errors that occur in a database application.

Java Database Connectivity with 5 steps

There are 5 steps to connect any java application with the database using JDBC. These steps are as follows:

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

2] Create the connection object

The getConnection() method of DriverManager class is used to establish connection with the database.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException

2) public static Connection getConnection(String url,String name,String password) throws SQLException

3] Create the Statement object

The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.

Syntax of createStatement() method

public Statement createStatement()throws SQLException

4] Execute the query

The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method

public ResultSet executeQuery(String sql)throws SQLException

5] Close the connection object

By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

Syntax of close() method

public void close()throws SQLException

Java Database Connectivity with MySQL

To connect Java application with the MySQL database, we need to follow 5 following steps. In this example we are using MySql as the database. So we need to know following informations for the mysql database:

• Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.
• Connection URL: The connection URL for the mysql database is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the database, localhost is the server name on which mysql is running, we may also use IP address, 3306 is the port number and sonoo is the database name. We may use any database, in such case, we need to replace the sonoo with our database name.
• Username: The default username for the mysql database is root.
• Password: It is the password given by the user at the time of installing the mysql database. In this example, we are going to use root as the password.

Let’s first create a table in the mysql database, but before creating table, we need to create database first.

create database sonoo;

use sonoo;

create table emp(id int(10),name varchar(40),age int(3));

Example to Connect Java Application with mysql database

In this example, sonoo is the database name, root is the username and password both.

Code:

import java.sql.*;
class MysqlCon{
public static void main(String args[]){
try{
Class.forName(“com.mysql.jdbc.Driver”);
Connection con=DriverManager.getConnection(
“jdbc:mysql://localhost:3306/sonoo”,”root”,”root”);
//here sonoo is database name, root is username and password
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(“select * from emp”);
while(rs.next())
System.out.println(rs.getInt(1)+” “+rs.getString(2)+” “+rs.getString(3));
con.close();
}catch(Exception e){ System.out.println(e);}
}
}

JDBC connection in Java with Oracle:

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 jdbc 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 the oracle database is “system”.
• Password: During the installation of the oracle database password is given by the username.

Advantages of JDBC:

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 procedures.
5. JDBC supports both types of processing i.e. Synchronous and Asynchronous processing.

Summary

JDBC is a very important concept which every Java developer needs to understand to develop projects. This module explored the role of JDBC in providing solution developers with enterprise-wide database connectivity capabilities and the JDBC architecture.

This blog helps to study about the relationship between JDBC and Java. You learned about various database systems and how JDBC was designed to work with relational DBMSs and the relational command language, SQL. You learned about two and n-tier application models. Finally, you learned about how JBDC was designed to leverage the power of Java.

References

What is JDBC in Java | JDBC Tutorial - Great Learning

JDBC Tutorial | What is Java Database Connectivity(JDBC) - javatpoint

Java Database Connectivity - Wikipedia