What is JDBC Architecture?

JDBC stands for Java Database Connectivity is a Java API(Application Programming Interface) used to interact with databases. JDBC is a specification from Sun Microsystems and it is used by Java applications to communicate with relational databases. We can use JDBC to execute queries on various databases and perform operations like SELECT, INSERT, UPDATE and DELETE.

JDBC API helps Java applications interact with different databases like MSSQL, ORACLE, MYSQL, etc. It consists of classes and interfaces of JDBC that allow the applications to access databases and send requests made by users to the specified database.

Purpose of JDBC

Java programming language is used to develop enterprise applications. These applications are developed with intention of solving real-life problems and need to interact with databases to store required data and perform operations on it. Hence, to interact with databases there is a need for efficient database connectivity, ODBC(Open Database Connectivity) driver is used for the same.

ODBC is an API introduced by Microsoft and used to interact with databases. It can be used by only the windows platform and can be used for any language like C, C++, Java, etc. ODBC is procedural.

JDBC is an API with classes and interfaces used to interact with databases. It is used only for Java languages and can be used for any platform. JDBC is highly recommended for Java applications as there are no performance or platform dependency problems.

Components of JDBC

JDBC has four main components that are used to connect with a database as follows:

1. JDBC API
2. JDBC Driver Manager
3. JDBC Test Suite
4. JDBC-ODBC Bridge Drivers

JDBC API

JDBC API is a collection of various classes, methods, and interfaces for easy communication with a database. JDBC API provides two different packages to connect with different databases.

JDBC Driver Manager

JDBC DriverManager is a class in JDBC API that loads a database-specific driver in Java application and establishes a connection with a database. DriverManager makes a call to a specific database to process the user request.

JDBC Test Suite

JDBC Test Suite is used to test operations being performed on databases using JDBC drivers. JDBC Test Suite tests operations such as insertion, updating, and deletion.

JDBC-ODBC Bridge Drivers

As the name suggests JDBC-ODBC Bridge Drivers are used to translate JDBC methods to ODBC function calls. JDBC-ODBC Bridge Drivers are used to connect database drivers to the database. Even after using JDBC for Java Enterprise Applications, we need an ODBC connection for connecting with databases.

JDBC-ODBC Bridge Drivers are used to bridge the same gap between JDBC and ODBC drivers. The bridge translates the object-oriented JDBC method call to the procedural ODBC function call. It makes use of the sun.jdbc.odbc package. This package includes a native library to access ODBC characteristics.

Architecture of JDBC

Let's take a look at the JDBC architecture in java.

As we can see in the above image the major components of JDBC architecture are as follows:

1. Application
2. The JDBC API
3. DriverManager
4. JDBC Drivers
5. Data Sources

Application

Applications in JDBC architecture are java applications like applets or servlet that communicates with databases.

JDBC API

The JDBC API is an Application Programming Interface used to create Databases. JDBC API uses classes and interfaces to connect with databases. Some of the important classes and interfaces defined in JDBC architecture in java are the DriverManager class, Connection Interface, etc.

DriverManager

DriverManager class in the JDBC architecture is used to establish a connection between Java applications and databases. Using the getConnection method of this class a connection is established between the Java application and data sources.

JDBC Drivers

JDBC drivers are used to connecting with data sources. All databases like Oracle, MSSQL, MYSQL, etc. have their drivers, to connect with these databases we need to load their specific drivers. Class is a java class used to load drivers. Class.forName() method is used to load drivers in JDBC architecture.

Data Sources

Data Sources in the JDBC architecture are the databases that we can connect using this API. These are the sources where data is stored and used by Java applications. JDBC API helps to connect various databases like Oracle, MYSQL, MSSQL, PostgreSQL, etc.

Types of JDBC Architecture

The JDBC Architecture can be of two types based on the processing models it uses. These models are

1. 2-tier model
2. 3-tier model

2 Tier Model

• 2-tier JDBC architecture model is a basic model.
• In this model, a java application communicates directly to the data sources. JDBC driver is used to establish a connection between the application and the data source.
• When an application needs to interact with a database, a query is directly executed on the data source and the output of the queries is sent back to the user in form of results.
• In this model, the data source can be located on a different machine connected to the same network the user is connected to.
• This model is also known as a client/server configuration. Here user's machine acts as a client and the machine on which the database is located acts as a server.

3 Tier Model

• 3-tier model is a complex and more secure model of JDBC architecture in java.
• In this model the user queries are sent to the middle tier and then they are executed on the data source.
• Here, the java application is considered as one tier connected to the data source(3rd tier) using middle-tier services.
• In this model user queries are sent to the data source using middle-tier services, from where the commands are again sent to databases for execution.
• The results obtained on the database are again sent to the middle-tier and then to the user/application.

Interfaces of JDBC API

JDBC API uses various interfaces to establish connections between applications and data sources. Some of the popular interfaces in JDBC API are as follows:

• Driver interface - The JDBC Driver interface provided implementations of the abstract classes such as java.sql.Connection, Statement, PreparedStatement, Driver, etc. provided by the JDBC API.
• Connection interface - The connection interface is used to create a connection with the database. getConnection() method of DriverManager class of the Connection interface is used to get a Connection object.
• Statement interface - The Statement interface provides methods to execute SQL queries on the database. executeQuery(), executeUpdate() methods of the Statement interface are used to run SQL queries on the database.
• ResultSet interface - ResultSet interface is used to store and display the result obtained by executing a SQL query on the database. executeQuery() method of statement interface returns a resultset object.
• RowSet interface - RowSet interface is a component of Java Bean. It is a wrapper of ResultSet and is used to keep data in tabular form.
• ResultSetMetaData interface - Metadata means data about data. ResultSetMetaData interface is used to get information about the resultset interface. The object of the ResultSetMetaData interface provides metadata of resultset like number of columns, column name, total records, etc.
• DatabaseMetaData interface - DatabaseMetaData interface is used to get information about the database vendor being used. It provides metadata like database product name, database product version, total tables/views in the database, the driver used to connect to the database, etc.

Classes of JDBC API

Along with interfaces, JDBC API uses various classes that implement the above interfaces. Methods of these classes in JDBC API are used to create connections and execute queries on databases. A list of most commonly used class in JDBC API are as follows:

• DriverManager class - DriverManager class is a member of the java.sql package. It is used to establish a connection between the database and its driver.
• Blob class - A java.sql.Blob is a binary large object that can store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data.
• Clob class - The java.sql.Clob interface of the JDBC API represents the CLOB datatype. Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL CLOB (not the data).
• Types class - Type class defined and store constants that are used to identify generic SQL types also known as JDBC types.

Working of JDBC

Java applications need to be programmed for interacting with data sources. JDBC Drivers for specific databases are to be loaded in a java application for JDBC support which can be done dynamically at run time. These JDBC drivers communicate with the respective data source.

Steps to connect a Java program using JDBC API. 1. Load Driver: Load JDBC Driver for specific databases using forName() method of class Class. Syntax: Class.forName("com.mysql.jdbc.Driver")

2. Create Connection: Create a connection with a database using DriverManager class. Database credentials are to be passed while establishing the connection. Syntax: DriverManager.getConnection()

3. Create Query: To manipulate the database we need to create a query using commands like INSERT, UPDATE, DELETE, etc. These queries are created and stored in string format. Syntax: String sql_query = "INSERT INTO Student(name, roll_no) values('ABC','XYZ')"

4. Create Statement: The query we have created is in form of a string. To perform the operations in the string on a database we need to fire that query on the database. To achieve this we need to convert a string object into SQL statements. This can be done using createStatement() and prepareStatement() interfaces. Syntax: Statement St = con.createStatement();

5. Execute Statement: To execute SQL statements on the database we can use two methods depending on which type of query we are executing.

• Execute Update: Execute update method is used to execute queries like insert, update, delete, etc. Return type of executeUpdate() method is int. Syntax: int check = st.executeUpdate(sql);
• Execute Query: Execute query method is used to execute queries used to display data from the database, such as select. Return type of executeQuery() method is result set. Syntax: Resultset = st.executeUpdate(sql);

6. Closing Statement: After performing operations on the database, it is better to close every interface object to avoid further conflicts. Synatx: con.close();

Let's see how to connect a Java program using JDBC API.