IndianTechnoEra: Java Database Connectivity | Advance Java

Advance Java | IndianTechnoEra

JDBC Introduction:

Java Database Connectivity or (JDBC) is a standard API that provides a platform-independent method for connecting to databases and executing SQL queries. 

It is a part of JavaSE (Java Standard Edition).

JDBC Advance Java | IndianTechnoEra 

JDBC allows Java applications to interact with a wide variety of relational databases, such as Oracle, MySQL, SQL Server, and PostgreSQL.

The current version of JDBC is 4.3. It is the stable release since 21st September, 2017. It is based on the X/Open SQL Call Level Interface.

Why JDBC?

Before the JDBC, there was the ODBC API 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).

JDBC architecture:

The JDBC architecture consists of two main components:

1. JDBC API
2. JDBC Driver

JDBC Architecture | IndianTechnoEra 

JDBC API:

The JDBC API provides a set of classes and interfaces that allows Java applications to connect to databases, send SQL statements, and retrieve results.

We can use JDBC API to access tabular data stored in any relational database. 

By the help of JDBC API, we can save, update, delete and fetch data (basically CRUD) from the database. 

The java.sql package contains classes and interfaces for JDBC API. 

JDBC API Classes & Interfaces:

JDBC API Classes:

A class is a blueprint for creating objects. It defines the properties and behavior of objects of a certain type. A class can contain fields, methods, constructors, and other members.

1. DriverManager class
2. Blob class
3. Clob class
4. Types class

DriverManager class - manages the set of database drivers and provides methods for establishing connections to a database; 

Blob class - represents a binary large object in a database and provides methods for accessing and manipulating the data in the BLOB; 

Clob class - represents a character large object in a database and provides methods for accessing and manipulating the data in the CLOB; 

Types class - defines constants that represent the SQL types supported by the JDBC API.

**A Blob is typically used to store images, audio, or other multimedia objects, and sometimes binary executable code is also stored as a blob. We can convert this datatype to string or from string using the toString() and valueOf() methods.

**An SQL CLOB is a built-in type that stores a Character Large Object as a column value in a row of a database table. By default drivers implement a Clob object using an SQL locator(CLOB) , which means that a Clob object contains a logical pointer to the SQL CLOB data rather than the data itself.

JDBC API Interfaces :

An interface, on the other hand, is a collection of abstract methods** that define a contract for a class to implement. An interface can also contain constants and default methods.

**Don't require implementation for its declaration. These methods don't have a body which means no implementation. A few properties of an abstract method are: An abstract method in Java is declared through the keyword “abstract”.

1. Driver interface
2. Connection interface
3. Statement interface
4. PreparedStatement interface
5. CallableStatement interface
6. ResultSet interface
7. ResultSetMetaData interface
8. DatabaseMetaData interface
9. RowSet interface

Driver interface - defines methods for a database driver to be registered and to establish a connection to a database; 

Connection interface - represents a connection to a database and provides methods for managing transactions; 

Statement interface: defines methods for executing SQL queries and updates, and for managing the statement's lifecycle.

PreparedStatement interface: extends the Statement interface and adds methods for setting parameters in a prepared statement and executing the statement.

CallableStatement interface - extends PreparedStatement and adds methods for executing stored procedures; 

ResultSet interface - represents a set of results from executing a query and provides methods for accessing the data in the result set; 

ResultSetMetaData interface: It provides methods for obtaining metadata about the columns in a result set, such as the number of columns, column names, and column types.

DatabaseMetaData interface: It provides methods for obtaining metadata about the database, such as the database name, version, and supported SQL syntax.

RowSet interface - represents a set of rows that can be scrolled through and updated.

**The executeQuery() method is used for executing SQL queries that return data, 

** The executeUpdate() method is used for executing SQL statements that modify data in the database.

JDBC API Key Features:

Here are some key features of the JDBC API:

1. Connection management: The JDBC API provides a set of interfaces and classes for managing connections to databases. 

Applications can create a connection to a database using a JDBC driver, and then use that connection to execute SQL statements and retrieve results.

2. Statement execution: The JDBC API provides a set of interfaces and classes for executing SQL statements, such as SELECT, INSERT, UPDATE, and DELETE. 

Applications can execute SQL statements using a Statement object or a PreparedStatement object.

3. Result retrieval: The JDBC API provides a set of interfaces and classes for retrieving results from SQL statements. 

Applications can retrieve results using a ResultSet object, which provides methods for accessing the data in the result set.

4. Transaction management: The JDBC API provides support for transaction management, which allows applications to execute multiple SQL statements as a single atomic operation. 

Applications can use the Connection object to start and commit transactions.

5. Metadata retrieval: The JDBC API provides a set of interfaces and classes for retrieving metadata about databases, such as the structure of tables and columns. 

Applications can use the DatabaseMetaData interface to retrieve this information.

JDBC Driver: 

The JDBC driver provides the necessary software to connect to a specific database management system. There are four types of JDBC drivers:

   a. Type 1: JDBC-ODBC bridge driver.

   b. Type 2: Native-API partly Java driver.

   c. Type 3: Network-protocol pure Java driver.

   d. Type 4: Native-protocol pure Java driver.

Type 1: JDBC-ODBC bridge driver

This driver uses the ODBC (Open Database Connectivity) technology to connect to the database. 

It acts as a bridge between the Java application and the database by converting JDBC calls to ODBC calls. 

This driver is platform dependent and requires the ODBC driver to be installed on the client machine. 

It is not recommended for use in production environments due to performance issues.

Type 2: Native-API partly Java driver

This driver uses the native API of the database to connect to the database. 

It is partly written in Java and partly in the native language of the database. 

This driver is platform dependent and requires the database-specific client libraries to be installed on the client machine. 

This driver is faster than the Type 1 driver as it directly communicates with the database, but it may have some compatibility issues with different databases.

Type 3: Network-protocol pure Java driver

This driver uses a middleware component to connect to the database. 

It communicates with the middleware using a network protocol, and the middleware then communicates with the database using the database-specific client libraries. 

This driver is platform independent and does not require any client libraries to be installed on the client machine. 

This driver is slower than the Type 2 driver but is more flexible as it can work with multiple databases.

Type 4: Native-protocol pure Java driver

This driver uses a database-specific protocol to connect to the database. 

It is written in pure Java and does not require any client libraries or middleware components to be installed on the client machine. 

It directly communicates with the database using a native protocol. 

It is also platform independent and can work with multiple databases. This driver is the most commonly used JDBC driver in production environments.

This driver is the fastest and most efficient of all the JDBC drivers as it directly communicates with the database using a native protocol. 

In summary, the Type 1 driver is the least preferred driver due to its performance issues, while the Type 4 driver is the most preferred driver due to its high performance and platform independence.

The choice of the JDBC driver depends on the specific requirements of the application and the database being used.

Differences with JDBC Drivers:

Steps to connect database:

Steps to connect to a database using JDBC:

1. Load the JDBC driver class: Use Class.forName() method to load the driver class for the database you want to connect to. 

Example Code: 

For Oracle:

Class.forName("oracle.jdbc.driver.OracleDriver");  

For MySQL:

Class.forName("com.mysql.jdbc.Driver");

2. Create connection to the database: Use DriverManager.getConnection() method to establish a connection to the database.

Example Code:

For Oracle:

Connection con=DriverManager.getConnection( jdbc:oracle:thin:@localhost:1521:xe","system","oracle"); 

For MySQL:

String url = "jdbc:mysql://localhost/";
String db = "myDB";
String user = "root";
String pass = "1234";
Connection con=DriverManager.getConnection( url+db, user, pass);

3. Create a statement object: Use the Connection.createStatement() method to create a Statement object that can be used to execute SQL statements.

Example Code:

Statement stmt=con.createStatement(); 

4. Execute a SQL statement: Use the Statement.execute() method to execute an SQL statement.

Example Code:

ResultSet rs=stmt.executeQuery("select * from emp");  
while(rs.next())  
System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  

 

5. Process the results and Close connection object: If the SQL statement returns any results, use the ResultSet object to retrieve and process the data.

Example Code:

con.close(); 

Implement to show the JDBC MySQL Connection:

Implement to retrieve table data:

CRUD Operations With JDBC:

CRUD stands for Create, Read, Update, and Delete. These are the four basic operations that can be performed on a database. JDBC provides methods to perform these operations using SQL statements.

Here's an example that demonstrates how to perform CRUD operations using JDBC:

Write from here