Establishing JDBC Connection in Java

Before Establishing a JDBC Connection in Java (the front end, i.e., your Java Program, and the back end, i.e., the database), we should learn what precisely a JDBC is and why it came into existence. Now, let us discuss what exactly JDBC stands for and why it is essential, and how to establish a database connection step-by-step with the help of a real-world example.

What is JDBC? 

JDBC stands for Java Database Connectivity. JDBC is a Standard API that enables Java applications to interact with databases like MYSQL, PostgreSQL, etc. This API consists of classes and interfaces written in Java. In other words, we can also say that JDBC acts as a bridge between your Java application (frontend) and the database (backend), allowing you to send and retrieve data between the two systems.

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

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

Steps to Establish a JDBC Connection

Below are the steps that explains how to connect to Database in Java:

• Step 1: Import the Packages
• Step 2: Load the drivers using the forName() method 
• Step 3: Register the drivers using DriverManager 
• Step 4: Establish a connection using the Connection class object
• Step 5: Create a statement
• Step 6: Execute the query
• Step 7: Close the connections

Java Database Connectivity

Let us discuss these steps in brief before implementing by writing suitable code to illustrate connectivity steps for JDBC.

Step 1: Import the JDBC Package

First, we need to import the packages.

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:

Class.forName()

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

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

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: From JDBC 4.0 (Java 6 or later), driver loading is done automatically, it means we don not have to manually load or register the driver using Class.forName() or DriverManager.registerDriver().

Step 3: Establish a Connection

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

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

• 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:

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

Where,

• oracle is the database used, thin is the driver used
• @localhost is the IP Address where a database is stored
• 1521 is the port number and xe is the service provider. 

All 3 parameters above are of String type and are to be declared by the programmer before calling the function. Use of this can be referred to form the final code.

Step 4: Create a Statement 

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

Use of JDBC Statement is as follows: 

Statement st = con.createStatement();

Note: Here, con is a reference to Connection interface used in previous step.

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.

The executeQuery() 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. 
The executeUpdate(sql query) method of the Statement interface is used to execute queries of updating/inserting.

Pseudo Code:

int m = st.executeUpdate(sql);

if (m==1)

System.out.println("inserted successfully : "+sql);

else

System.out.println("insertion failed");
on

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

Note: Here, sql is SQL query of the type String.

// This code is for establishing connection with MySQL
// database and retrieving data
// from db Java Database connectivity

/*
 *1. import --->java.sql
 *2. load and register the driver ---> com.jdbc.
 *3. create connection
 *4. create a statement
 *5. execute the query
 *6. process the results
 *7. close
 */

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:

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

Example:

import java.sql.*;

public class MySQLConnect {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "yourusername";
        String password = "yourpassword";

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

        // Connect to database
        try (Connection con = DriverManager.getConnection(url, user, password);
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM students")) {

            System.out.println("Connection Successful!");

            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Note: Never hardcode database credentials in your production code.

Java Program for Oracle Database Connection

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

// Java Program to Establish Connection 
// in JDBC with Oracle Database

// Importing database
import java.sql.*;

// Importing required classes
import java.util.*;

// Main class
class Main {
    public static void main(String a[]) {
       
        String url = "jdbc:oracle:thin:@localhost:1521:xe"; // Database details

        // Username and password to access DB
        String user = "system";
        String pass = "12345";

        // Entering the data
        Scanner k = new Scanner(System.in);
        System.out.println("Enter name:");
        String name = k.next();
        System.out.println("Enter roll no:");
        int roll = k.nextInt();
        System.out.println("Enter class:");
        String cls = k.next();

        // Inserting data using SQL query
        String sql = "insert into student1 values('" + name + "'," + roll + ",'" + cls + "')";

        // Connection class object
        Connection con = null;

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

            // Establish connection
            con = DriverManager.getConnection(url, user, pass);

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

            // Execute the query
            int m = st.executeUpdate(sql);
            if (m == 1)
                System.out.println("Inserted successfully: " + sql);
            else
                System.out.println("Insertion failed");

            // Close the connection
            con.close();
        } catch (Exception ex) {
            
            // Handle exceptions
            System.err.println(ex);
        }
    }
}
}

Output: