JDBC Connection in Java

Key Components in JDBC

1. DriverManager: Manages a list of database drivers and establishes a connection between the Java application and the database.
2. Connection: Represents a session with a specific database and is used to execute SQL statements.
3. Statement: Used to execute static SQL queries without parameters.
4. PreparedStatement: Used to execute precompiled SQL queries with or without input parameters, improving performance and security.
5. ResultSet: Holds the data returned by executing a SQL query and allows navigating through the retrieved records.
6. Driver: Interface that handles the communication with the database server. Specific implementations exist for different databases.

Types of JDBC Drivers

JDBC is part of Java SE and uses drivers to connect Java applications to databases. There are four main types of JDBC drivers:

1. JDBC-ODBC Bridge Driver (Type 1)
   Uses the ODBC driver to connect to the database. It acts as a bridge between JDBC and ODBC but is platform-dependent and less efficient.
2. Native-API/Partly Java Driver (Type 2)
   Converts JDBC calls into native database API calls. Requires database-specific client libraries on the client machine.
3. Network Protocol Driver (Type 3)
   Uses a middleware server that translates JDBC calls into the database-specific protocol. It enables access to multiple databases through a network.
4. Thin Driver/Pure Java Driver (Type 4)
   A 100% Java driver that directly converts JDBC calls to the database’s native protocol. It is platform-independent and commonly used for its efficiency.

Establishing Connection in JDBC

To establish the JDBC Connection, we need to follow a specific process. Let us now understand what that process is by going through the steps involved in it one by one:

1. First of all, we do need to Import the database. 

Before working with databases in Java, you need to import the JDBC package which contains all the necessary classes and interfaces. This allows your program to use JDBC API features like Connection, Statement, and ResultSet.

Example:

import java.sql.*;

This statement imports all the core JDBC classes required to establish connections and execute SQL queries.

2. Then we have to load the driver using the forName () method. 

The JDBC driver acts as a bridge between the Java application and the database. To use the driver, you must load its class into memory. This is done using the Class.forName() method, which dynamically loads the driver class at runtime and registers it with the DriverManager.

Example:

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

This tells Java to load the MySQL JDBC driver class so the application can communicate with the MySQL database. Without this step, the DriverManager won't know which database driver to use.

You must first load or register the driver before using it in your program. We need to load the driver before using it in our program. We can register the driver in one of two ways: 

i. Class.forName ():
This method loads the driver class files into memory during the run time. You don't have to use new objects or create objects. In the following example uses Class.forName () to load the Oracle driver as  follows:
The syntax for this is Syntax: Class.forName(“oracle.jdbc.driver.OracleDriver”)

ii.  DriverManager.registerDriver () 

DriverManager is a Java built-in class with static member registers. Here, we have called the constructor of the driver class at the compile time. In the following example uses DriverManager.registerDriver () to register the Oracle driver. Let us have a look at it.

Syntax: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())

3. Now, we have to register the driver in DriverManager. 

4. Next, form a Connection using a Connection class object 

After loading the driver, we can establish a connection in the following ways

Connection con = DriverManager.getConnection(url,user,password)

It requires the following parameters:

1. user: The user name that can access the SQL prompt. 
2. Password: The password you can use to access the SQL prompt.
3. Con: This is a reference to the connection interface. 
4. URL: Uniform Resource Locator  

We have looked at all the parameters required to maintain the connection. Let us see an example to understand it better:

Example :

String url = “ jdbc:oracle:thin:@localhost:5421:xe”

5. We now need to create a statement. 

Since we have established the connection, we can work with the database. The CallableStatement, JDBCStatement and PreparedStatement interfaces define the methods we can use to send SQL commands and receive data from the database. The usage of the  JDBC statement is as follows:

Statement st = con.createStatement();

6. Execution of the query 

The query here is a SQL query. Now you know that you can execute multiple types of queries, like queries to update/insert a table in the database. A query to get the data. We use the executeQuery () method of the Statement interface to execute a query to get a value from the database. This method returns an object in the  ResultSet. We can use the ResultSet object to extract the records from the table. We use the executeUpdate (SQL query) method to execute update/insert queries.

7. Closing the connection

In the end, we have sent the data to the specified location and are nearing the task's completion. When you close the connection, the Statement and ResultSet objects close automatically. We use the close () method of the Connection interface to close the connection. 

It will be as follows: 

con.close ();

Implementation

// A Java Program for Establishing Connection in JDBC

// Importing the database
import java.sql.*;
// Importing the required classes
import java.util.*;

// Main class of Java
class Main
{

    // The Main driver method
public
    static void main(String a[])
    {

        // Creating a connection using Oracle DB
        String url = "jdbc:oracle:thin:@localhost:5241:xe";

        // Username and password to access DB
        // Custom initialization
        String user = "superuser";
        String pass = "54321";

        // Entering data
        Scanner k = new Scanner(System.in);

        System.out.println("enter employee_name");
        String employee_name = k.next();

        System.out.println("enter employee_id");
        int employee_id = k.nextInt();

        System.out.println("enter department");
        String department = k.next();

        // Inserting data using SQL query
        String sql = "insert into employee1 values('" + employee_name + "'," + employee_id + ",'" + department + "')";

        // Connection class object
        Connection connectionn = null;

        // Try block to check for exceptions
        try
        {

            // Registering drivers
            DriverManager.registerDriver(
                new oracle.jdbc.OracleDriver());

            // Reference to connection interface
            connectionn = DriverManager.getConnection(url, user,
                                                      pass);

            // Creating a statement
            Statement st = connectionn.createStatement();

            // Executing query
            int m = st.executeUpdate(sql);
            if (m == 1)
                System.out.println(
                    "inserted successfully : " + sql);
            else
                System.out.println("insertion failed");

            // Closing the connections
            connectionn.close();
        }

        // Catch block to handle exceptions
        catch (Exception ex)
        {
            // Display message when exceptions occurs
            System.err.println(ex);
        }
    }
}

Output:

Enter employee_name
Ninja
Enter employee_id
6523
Enter department
12A
inserted successfully:  insert into employee1 values(‘Ninja',6523, '12A')

Practice by yourself on java online compiler.

Java Program to Connect MySQL Database Using JDBC

To connect a Java program with a MySQL database using JDBC, follow these steps:

Example:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MySQLConnectionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase"; // Replace 'mydatabase' with your database name
        String username = "root";  // Replace with your MySQL username
        String password = "password"; // Replace with your MySQL password

        try {
            // Load MySQL JDBC Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish the connection
            Connection connection = DriverManager.getConnection(url, username, password);

            if (connection != null) {
                System.out.println("Connected to MySQL database successfully!");
                connection.close();
            }
        } catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC Driver not found.");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Failed to connect to MySQL database.");
            e.printStackTrace();
        }
    }
}

Output:

Connected to MySQL database successfully!

Java Program to Connect to Oracle Database Using JDBC

Connecting to an Oracle database is similar but requires the Oracle JDBC driver and a different connection URL.

Example:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class OracleConnectionExample {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:xe"; // Replace with your Oracle DB details
        String username = "system";  // Replace with your Oracle username
        String password = "oracle";  // Replace with your Oracle password

        try {
            // Load Oracle JDBC Driver
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // Establish the connection
            Connection connection = DriverManager.getConnection(url, username, password);

            if (connection != null) {
                System.out.println("Connected to Oracle database successfully!");
                connection.close();
            }
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC Driver not found.");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Failed to connect to Oracle database.");
            e.printStackTrace();
        }
    }
}

Output:

Connected to Oracle database successfully!

Frequently Asked Questions

What are the main steps in Java to establish JDBC Connection?

The main steps are as follows:
a. Load the driver.
b. Make the Connection
c. Get the object of statement
d. Execution of query
e. Close the connection

What are the different types of JDBC statements?

There are basically three types of JDBC statements:
a. Statement 
b. PreparedStatement
c. CallableStatement

Conclusion

JDBC (Java Database Connectivity) is a crucial API for connecting and executing queries with databases in Java. It simplifies database interactions, supports a variety of database systems, and enables seamless integration of database operations into Java applications. JDBC enhances data handling capabilities, promotes code portability, and is essential for building robust, data-driven applications. Proper management of JDBC resources ensures efficient and reliable database access.