JDBC Connectivity

Introduction

• JDBC(Java Database Connectivity)is part of the Java Standard Edition platform, from Oracle Corporation.
• The stable release of JDBC is  JDBC 4.3 / September 21, 2017
• The current developer of JDBC is Oracle Corporation.
• The JDBC API was modeled after ODBC API.
• JDBC is a core part of the Java platform/JavaSE (Java Standard Edition).

Definition

• JDBC is a standard Java API for connecting programs written in Java to the data in relational databases(RDBMS), independently.
• JDBC is used to connect Java applications with databases. It provides Java classes and interfaces to connect or communicate Java applications with databases.
• JDBC API  enables to access any kind of data stored in a Relational Database. It enables Java programs to execute required SQL statements to fetch the data.
• JDBC is a software component that enables a Java application to interact with a database.
• The function of JDBC is to help the Java-based application access different types of databases.
• JDBC Driver is a software component that enables Java applications to interact with the database. 

Features

• It is a Java-based data access technology used for Java database connectivity.
• JDBC provides a secure connection to the database.
• To connect with individual databases, JDBC requires drivers for each database.
• Before JDBC, ODBC API was the database API to connect and execute the query with the database. But, ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).
• There are two major components of JDBC: Connection Pooling and Data Sources.
• JDBC API uses JDBC drivers to connect with the database. With the help of JDBC API, we can save, update, delete, and fetch/search data from the database.
• The java.sql package contains classes and interfaces for JDBC API.
• JDBC is built on the basis of ODBC and hence, some basics of ODBC retain in JDBC.
• The major difference between java. util.Date and java.sql.Date is that, java.sql.Date represents a date without time information whereas, java. util. Date represents both date and time information.

JDBC Architecture

• The JDBC architecture supports two-tier and three-tier processing models for accessing a database. In the two-tier model, a Java applet or application communicates directly to the data source.In the three-tier model, the user’s commands or queries are sent to middle-tier services, from which the commands are again sent to the data source. The results are sent back to the middle tier, and from there to the user. This type of model is found very useful in management information systems.

JDBC Drivers

• It is a set of Java classes that implement the JDBC interfaces to process JDBC calls and return result sets to a Java application.
• The database that stores the data, is retrieved by the application using the JDBC Driver.
• A JDBC driver is a small piece of software that allows JDBC to connect to different databases.
• Essentially, a JDBC driver makes it possible to do three things: – Establish a connection with a data source, Send queries, and update statements to the data source.
• A JDBC driver uses the JDBC  API developed by Sun Microsystems initially but is now part of Oracle.

Types of JDBC Drivers – 

• There are four types of JDBC drivers:

• • JDBC-ODBC Bridge Driver
    • The JDBC-ODBC bridge driver uses the ODBC driver to connect to the database.
    • The JDBC-ODBC bridge driver converts JDBC method calls into ODBC function calls.
    • It is easy to use and can be easily connected to any database.
  • Native – API Driver (Partially Java Driver)
    • The Native API driver uses the client-side libraries of the database.
    • The driver converts JDBC method calls into native calls of the database API.
    • It is not written entirely in Java. Its performance is better than the JDBC-ODBC bridge driver. However, the native driver must be installed on each client machine.
  • Network Protocol Driver (Fully Java Driver)
    • The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or indirectly into the vendor-specific database protocol.
    • It is entirely written in Java.
    • There is no requirement for the client-side library because the application server can perform many tasks like auditing, load balancing, logging, etc.
  • Thin Driver (Fully Java Driver)
    • The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is why it is known as the thin driver.
    • It is entirely written in Java language.
    • Its performance is better than all other drivers however these drivers depend upon the database.

JDBC API Components

• The java.sql package contains the following interfaces and classes for JDBC API which are fully responsible for JDBC connectivity.

Classes:

A list of common classes of JDBC API that involves in JDBC connectivity are – 

• DriverManager Class : – 
  • This class manages a list of database drivers, which are used to connect to a database.
  • It provides methods for registering drivers and creating connections to a database.
  • The DriverManager class acts as an interface between the user and drivers.
  • It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver.
  • It contains several methods to keep the interaction between the user and drivers.

• Types Class : –
  • Java does not have a direct equivalent to type classes, the concept is similar to Java’s interfaces and abstract classes.
  • A type class is a type system construct that defines a set of operations that can be performed on a certain type.
  • It allows for ad-hoc polymorphism, which means that a function or method can be defined to work with any type that satisfies the constraints of the type class.
  • In Java, the closest equivalent to a type class is probably an interface.
• Blob Class : – 
  • Blob stands for the binary large object.
  • It represents a collection of binary data stored as a single entity in the database management system.

• Clob Class : –
  • Clob stands for Character large object.
  • It is a data type that is used by various database management systems to store character files.
  • It is similar to Blob except for the difference that BLOB represents binary data such as images, audio and video files, etc. whereas Clob represents character stream data such as character files, etc.

• SQLException Class : – 
  • This class represents an exception that occurred during a JDBC operation.
  • It is an Exception class that provides information on database access errors.

Interfaces:

A list of common interfaces of JDBC API that involves in JDBC connectivity are – 

• Driver Interface : 

• • the Driver interface is a key component of the JDBC API, providing a standard way for Java applications to connect to databases and allowing for interoperability between different databases and drivers.
  • The Driver interface in Java is a part of the Java Database Connectivity (JDBC) API.
  • It is an interface that defines the contract between the JDBC driver and the Java application.
  • The Driver interface has a single method, connect(), which is used to establish a connection to a database.
  • The connect() method takes a JDBC URL as its parameter and returns an Connection object. The JDBC URL contains the necessary information to connect to the database, such as the database type, the host name, the port number, the database name, and any other required parameters.
  • JDBC drivers that implement the Driver interface is typically loaded using the Class.forName() method, which loads the driver class into the JVM’s memory. Once the driver is loaded, the application can use the DriverManager.getConnection() method to obtain a Connection object to the database.
  • The Driver interface is implemented by the JDBC driver provided by the database vendor. Each vendor’s driver will implement the connect() method to handle the specific details of connecting to their database. The Driver interface provides a standardized way for Java applications to connect to any database that has a JDBC driver available.

• Connection Interface : 
  • This interface represents a connection to a database.
  • It provides methods for creating statements, managing transactions, and accessing metadata about the database.
  • The Connection object is created by using the getConnection() method of DriverManager class. DriverManager is the factory for connection.

• ResultSet Interface : 
  • This interface represents the results of a query executed on a database.
  • It provides methods for iterating over the results and accessing the data in each row.
  • The object of ResultSet maintains a cursor pointing to a row of a table.
  • Initially, the cursor points before the first row.
  • The executeQuery() method of the Statement interface returns the ResultSet object.

• ResultSetMetaData Interface : 
  • This interface provides metadata about the columns in a ResultSet.
  • It can be used to determine the number of columns, the type of each column, and other information about the result set.
  • The object of the ResultSetMetaData interface contains the information about the data (table) such as the number of columns, column name, column type, etc.
  • The getMetaData() method of ResultSet returns the object of ResultSetMetaData.

• RowSet Interface : 
  • The RowSet interface provides a more flexible and easier-to-use alternative to the ResultSet interface, and is a powerful tool for working with database data in Java.
  • The RowSet interface in Java is a part of the Java Database Connectivity (JDBC) API.
  • It extends the ResultSet interface and provides a way to make the ResultSet object more flexible and easier to use.
  • The RowSet interface represents a set of rows from a database table, and it provides methods to navigate, manipulate, and update the rows.
  • The RowSet interface provides several advantages over the ResultSet interface such as:-
    • Portability: RowSet objects can be used independently of the database and can be serialized to be transported over a network.
    • Automatic Connection Management: RowSet objects automatically manage database connections and are designed to work in a disconnected environment.
    • Updatable: RowSet objects can be updated directly, without needing to go through a PreparedStatement.
    • Scrollable: RowSet objects can be scrolled both forward and backward, unlike ResultSet objects which can only be scrolled forward.
• DatabaseMetaData Interface: 
  • This interface provides metadata about the database itself.
  • It can be used to determine information such as the database name, version, and supported features.
  • DatabaseMetaData interface provides methods to get metadata of a database such as a database product name, database product version, driver name, name of the total number of tables, the name of the total number of views, etc.
  • The getMetaData() method of the Connection interface returns the object of DatabaseMetaData.

(NB: In JDBC, the Statements interface is mainly used to send SQL commands to the database and receive respective data from the database, in a different way. There are various methods provided by JDBC statements such as execute(), executeUpdate(), executeQuery, etc. which help to interact with the database to receive data as per the situation. The statement may be – statement, PreparedStatement, and CallableStatement which may have different modes of the procedure with the database.)  

• Statement Interface : 
  • This interface represents a SQL statement that can be executed on a database.
  • It provides methods for executing queries and updates, as well as for accessing the results of a query.
  • The Statement object is created by using the createStatement() method of the Connection class.
  • The Connection interface is the factory for Statement.
  • The Statement interface provides methods to execute queries with the database. The statement interface is a factory of ResultSet; i.e., it provides the factory method to get the object of ResultSet.
  • In the case of a Statement, the query is compiled each time we run the program.
  • The Statement is mainly used in the case when we need to run the static query at runtime.
  • The statement is the factory for the resultset. It is used for general-purpose access to the database. It executes a static SQL query at runtime.

• PreparedStatement Interface : 
  • This interface extends Statement and provides a way to execute parameterized SQL statements.
  • It is used when the same SQL statement is executed multiple times with different parameter values.
  • The PrepareStatement object is created by using the prepareStatement() method of the Connection class.
  • It is used to execute the parameterized query.
  • The PreparedStatement interface is a subinterface of Statement. It is used to execute the parameterized query.
  • In the case of PreparedStatement, the query is compiled only once.
  • PreparedStatement is used when we need to provide input parameters to the query at runtime.
  • The PreparedStatement is used when we need to provide input parameters to the query at runtime.
• CallableStatement Interface: 
  • The callableStatement interface is used to call the stored procedures and functions.
  • We can have business logic on the database through the use of stored procedures and functions that will make the performance better because these are precompiled.
  • The prepareCall() method of the Connection interface returns the instance of CallableStatement.
  • CallableStatement is used when we need to access the database-stored procedures. It can also accept runtime parameters.

• The Benefits of using PreparedStatement over the Statement interface are given below-

• • The PreparedStatement performs faster as compared to Statement because the Statement needs to be compiled every time we run the code whereas the PreparedStatement compiles once and then executes only on runtime.
  • PreparedStatement can execute Parameterized queries whereas statements can only run static queries.
  • The query used in PreparedStatement appeared to be similar every time. Therefore, the database can reuse the previous access plan whereas, Statement inline the parameters into the String, therefore, the query doesn’t appear to be the same every time which prevents cache usage.

Difference between execute(), executeQuery() and executeUpdate() method : –

Difference between ResultSet and Rowset Interface: –

Steps of JDBC Connection

• There are the following fundamental steps to connect Java applications with any database using JDBC. These are – 
  • Import JDBC packages.
  • Load and register the JDBC driver class.
  • Open & Create a connection to the database.
  • Create a statement object to perform a query.
  • Execute the statement object and return a query resultset.
  • Process the resultset.
  • Close the resultset and statement objects.
  • Close the connection.