Establishing JDBC Connection in Java

Before performing any database operations, you first need to establish a connection using JDBC. This connection acts like a communication channel through which SQL queries are sent and results are received. Setting up this connection involves loading the database driver, specifying the database URL and authenticating with a username and password.

The diagram below demonstrates the workings of JDBC by correlating its steps to real-world examples.

We can think of JDBC as a middleman that builds communication between a Java application and a database.

Steps to Establish a JDBC Connection

The process of connecting to a database using JDBC can be broken into the following steps

Step 1: Import the JDBC Package

First, we need to import the packages.

import java.sql.*;

Step 2: Load or Register the Driver 

In order to begin with, you first need to load the driver or register it before using it in the program. Registration is to be done once in your program. You can register a driver in one of two ways mentioned below as follows:

1. Class.forName()

Here, we load the driver’s class file into memory at the runtime. No need to create objects. The following example uses Class.forName() to load the Oracle driver as shown below as follows:

Class.forName(“oracle.jdbc.driver.OracleDriver”);

2. DriverManager.registerDriver()

DriverManager is a Java inbuilt class with a static member register. Here we call the constructor of the driver class at compile time. The following example uses DriverManager.registerDriver() to register the Oracle driver as shown below: 

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

Note:

Since JDBC 4.0 (Java 6+), drivers are auto-loaded if present in the classpath, so manual loading is usually unnecessary.

Step 3: Establish a Connection

After loading the driver, establish connections as shown below as follows: 

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

String url = “jdbc:mysql://localhost:3306/testdb”

• user: Username from which your SQL command prompt can be accessed.
• password: password from which the SQL command prompt can be accessed.
• con: It is a reference to the Connection interface.
• Url: Uniform Resource Locator which is created as shown below:

Step 4: Create a Statement 

Once a connection is established, you can interact with the database. The Statement, PreparedStatement and CallableStatement interfaces define methods that enable you to send SQL commands and receive data from your database.

Use of Statement is as follows: 

Statement st = con.createStatement();

Note:

In real-world application, it is recommended to use PreparedStatement or CallableStatement instead of Statement for executing SQL queries.

Step 5: Execute SQL Query 

Now comes the most important part i.e. executing the query. The query here is an SQL Query. Now, we know we can have multiple types of queries. Some of them are as follows: 

• The query for updating/inserting a table in a database.
• The query for retrieving data.

1. executeQuery(): This method of the Statement interface is used to execute queries of retrieving values from the database. This method returns the object of ResultSet that can be used to get all the records of a table. 
2. executeUpdate(sql query): This method of the Statement interface is used to execute queries of updating/inserting.

Pseudo Code:

ResultSet rs = stmt.executeQuery("SELECT * FROM students");

while (rs.next()) {

System.out.println(rs.getString("name"));

}

Step 6: Close the Connections 

So, finally we have sent the data to the specified location and now we are on the verge of completing our task. By closing the connection, objects of Statement and ResultSet will be closed automatically. The close() method of the Connection interface is used to close the connection. It is shown below as follows:

con.close();

Note:

It is recommended to use try-with-resources to automatically close resource like Connection, Statement and ResultSet.

Java Program for MySQL JDBC Connection

Prerequisites

Before writing JDBC code, make sure you have:

1. Database Installed and Running:

• Install a relational database (e.g., PostgreSQL, MySQL).
• Create a database and a user.

2. JDBC Driver (Connector JAR):

• JDBC requires a driver to connect with the database.
• Download the JAR or add Maven dependency.

Example: The below Java program demonstrates how to connect to a MYSQL database, execute a Query, retrieve data and display it.

import java.sql.*;

class Geeks {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/database_name"; // Database details
        String username = "rootgfg"; // MySQL credentials
        String password = "gfg123";
        String query = "select * from students"; // Query to be run

        // Load and register the driver
        Class.forName("com.mysql.cj.jdbc.Driver");

        // Establish connection
        Connection con = DriverManager.getConnection(url, username, password);
        System.out.println("Connection Established successfully");

        // Create a statement
        Statement st = con.createStatement();

        // Execute the query
        ResultSet rs = st.executeQuery(query);

        // Process the results
        while (rs.next()) {
            String name = rs.getString("name"); // Retrieve name from db
            System.out.println(name); // Print result on console
        }

        // Close the statement and connection
        st.close();
        con.close();
        System.out.println("Connection Closed....");
    }
}

Output:

Java Program for Oracle Database Connection

Example: The below Java program demonstrates how to establish a JDBC Connection with an Oracle database.

import java.sql.*;
import java.util.Scanner;

public class Geeks{
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "system";
        String password = "yourpassword";

        String insertSql = "INSERT INTO student1 (name, roll, class) VALUES (?, ?, ?)";

        try (Connection con = DriverManager.getConnection(url, user, password);
             Scanner sc = new Scanner(System.in)) {

            con.setAutoCommit(false); // manage transaction manually

            System.out.print("Enter name: ");
            String name = sc.nextLine().trim();

            System.out.print("Enter roll no: ");
            int roll = Integer.parseInt(sc.nextLine().trim());

            System.out.print("Enter class: ");
            String cls = sc.nextLine().trim();

            try (PreparedStatement ps = con.prepareStatement(insertSql)) {
                ps.setString(1, name);
                ps.setInt(2, roll);
                ps.setString(3, cls);

                int rows = ps.executeUpdate();
                if (rows == 1) {
                    con.commit();
                    System.out.println("Inserted successfully.");
                } else {
                    con.rollback();
                    System.out.println("Insertion failed, rolled back.");
                }
            } catch (SQLException e) {
                con.rollback();
                System.err.println("Insert error, rolled back: " + e.getMessage());
                e.printStackTrace();
            }

        } catch (SQLException e) {
            System.err.println("DB connection error: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Output: