Categories: JDBC

Java Database Connectivity(JDBC) Tutorial

Java Database Connectivity JDBC is a Java database connectivity technology (Java Standard Edition platform) from Oracle Corporation. This technology is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the JVM host environment.

JDBC drivers:

JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert requests from Java programs to a protocol that the DBMS can understand.

Types:

There are commercial and free drivers available for most relational database servers. These drivers fall into one of the following types:
JDBC technology drivers fit into one of four categories.
1.     JDBC-ODBC bridge
2.     Native-API Driver
3.     Network-Protocol Driver(MiddleWare Driver)
4.     Database-Protocol Driver(Pure Java Driver

   1. JDBC-ODBC bridge:
The JDBC type 1 driver, also known as the JDBC-ODBC bridge, is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls.

 

Sun provides a JDBC-ODBC Bridge driver: sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed source.

Advantages:

  • Almost any database for which an ODBC driver is installed can be accessed, and data can be retrieved.

Disadvantages:

Performance overhead since the calls have to go through the jdbc Overhead bridge to the ODBC driver, then to the native db connectivity interface (thus may be slower than other types of drivers)

2.     Native-API Driver

The JDBC type 2 driver, also known as the Native-API driver, is a database driver implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API.For example: Oracle OCI driver is a Type 2 Driver.

Advantages:

As there is no implementation of jdbc-odbc bridge, its considerably faster than a type 1 driver.

Disadvantages:

The vendor client library needs to be installed on the client machine.

 3 .     Network-Protocol Driver(MiddleWare Driver)

The JDBC type 3 driver, also known as the Pure Java Driver for Database Middleware, is a database driver implementation which makes use of a middle tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol.

 

Advantages:

Since the communication between client and the middleware server is database independent, there is no need for the database vendor library on the client. The client need not be changed for a new database.

Disadvantages:

· The middleware layer added may result in additional latency, but is typically overcome by using better middleware services.

4.     Database-Protocol Driver(Pure Java Driver)

Schematic of the Native-Protocol driver.The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor-specific database protocol.

 

Advantages:

· Completely implemented in Java to achieve platform independence.
· These drivers don’t translate the requests into an intermediary format (such as ODBC).

Disadvantages:

  • Drivers are database dependent, as different database vendors use widely different (and usually proprietary) network protocols.

JDBC Basics – Java Database Connectivity Steps:

Step 1: Loading a database driver: The first thing you need to do before you can open a database connection is to load the JDBC driver for the database.

Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);

Step 2: Opening the Connection
To open a database connection you use the java.sql.DriverManager class.

String url      = "jdbc:h2:~/test";  
String user     = "sa";
String password = "";

Connection connection =
    DriverManager.getConnection(url, user, password);

Step 3: Creating a jdbc Statement object: Once a connection is obtained we can interact with the database.

Statement statement = connection.createStatement();

A statement object is used to send and execute SQL statements to a database.

Statement: Execute simple sql queries without parameters.
Statement createStatement()
Creates an SQL Statement object.

Prepared Statement: Execute precompiled sql queries with or without parameters.
PreparedStatement prepareStatement(String sql)
returns a new PreparedStatement object. PreparedStatement objects are precompiled
SQL statements.

Callable Statement: Execute a call to a database stored procedure.
CallableStatement prepareCall(String sql)
returns a new CallableStatement object. CallableStatement objects are SQL stored procedure call statements.

Step 4: Closing the Connection
Once you are done using the database connection you should close it. This is done by calling the Connection.close() method, like this:

connection.close();

Example-

import java.sql.*;

class TestRetrieve
{
 public static void main(String[] args)
 {
  try
  {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection c=DriverManager.getConnection("Jdbc:Odbc:mydsn");
   Statement s=c.createStatement();
   ResultSet result1=s.executeQuery("select * from emp");
   while(result1.next())
   {
    System.out.println(result1.getString(1));
    System.out.println(result1.getString(2));
   }
  }catch(SQLException e)
     {
      System.out.println(e);
     }
   catch(Exception i)
   {
    System.out.println(i);
   }
  
 }
}

 

Dinesh Rajput

Dinesh Rajput is the chief editor of a website Dineshonjava, a technical blog dedicated to the Spring and Java technologies. It has a series of articles related to Java technologies. Dinesh has been a Spring enthusiast since 2008 and is a Pivotal Certified Spring Professional, an author of a book Spring 5 Design Pattern, and a blogger. He has more than 10 years of experience with different aspects of Spring and Java design and development. His core expertise lies in the latest version of Spring Framework, Spring Boot, Spring Security, creating REST APIs, Microservice Architecture, Reactive Pattern, Spring AOP, Design Patterns, Struts, Hibernate, Web Services, Spring Batch, Cassandra, MongoDB, and Web Application Design and Architecture. He is currently working as a technology manager at a leading product and web development company. He worked as a developer and tech lead at the Bennett, Coleman & Co. Ltd and was the first developer in his previous company, Paytm. Dinesh is passionate about the latest Java technologies and loves to write technical blogs related to it. He is a very active member of the Java and Spring community on different forums. When it comes to the Spring Framework and Java, Dinesh tops the list!

Share
Published by
Dinesh Rajput

Recent Posts

Strategy Design Patterns using Lambda

Strategy Design Patterns We can easily create a strategy design pattern using lambda. To implement…

2 years ago

Decorator Pattern using Lambda

Decorator Pattern A decorator pattern allows a user to add new functionality to an existing…

2 years ago

Delegating pattern using lambda

Delegating pattern In software engineering, the delegation pattern is an object-oriented design pattern that allows…

2 years ago

Spring Vs Django- Know The Difference Between The Two

Technology has emerged a lot in the last decade, and now we have artificial intelligence;…

2 years ago

TOP 20 MongoDB INTERVIEW QUESTIONS 2022

Managing a database is becoming increasingly complex now due to the vast amount of data…

2 years ago

Scheduler @Scheduled Annotation Spring Boot

Overview In this article, we will explore Spring Scheduler how we could use it by…

3 years ago