JDBC Program in Java

Last Updated on August 21, 2023 by Mayank Dham

What is JDBC in Java?

JDBC in Java stands for Java Database Connectivity, representing a progression from ODBC (Open Database Connectivity). JDBC serves as a standardized API specification designed to facilitate the transfer of data from the frontend to the backend. This API encompasses Java-written classes and interfaces, essentially serving as a conduit – distinct from the Java programming concept of an interface – that bridges your Java program and databases. This bridge establishes a connection between the two entities, enabling programmers to transmit data from Java code and securely store it within the database for subsequent utilization.

• JDBC-ODBC Bridge Driver,
• Native Driver,
• Network Protocol Driver, and
• Thin Driver

Steps to implement JDBC in Java

1) Import Packages

We need to import the existing packages for the JDBC program so that the required APIs are available for the program.
Irrespective of the JDBC Driver, add the following import statement in the Java program.

import java.sql.*;
Import the other classes based on the functionality which you will use in the program. 

2) Load Driver

First, we should load/register the driver in the program before connecting to the Database. You need to register it only once per database in the program.
We can load the driver in the following 2 ways:

1. Class.forName()
2. DriverManager.registerDriver()

3) Establish Connection

After loading the driver, the next step is to create and establish the connection. Once required, packages are imported and drivers are loaded and registered, then we can go for establishing a Database connection.

DriverManager class has the getConnection method, we will use this method to get the connection with Database. To call getConnection() method, we need to pass 3 parameters. The 3 parameters are string data type URL, a username, and a password to access the database.

The getConnection() method is an overloaded method. The 2 methods are:

• getConnection(URL,username,password); – It has 3 parameters URL, username, password.
• getConnection(URL); – It has only one parameter. URL has a username and password also.

4) Create And Execute Statement

Once the connection has established, we can interact with the connected Database. First, we need to create the statement to perform the SQL query and then execute the statement.

(i) Create Statement

Now we will create the statement object that runs the query with the connected database. We use the createStatement method of the Connection class to create the query.

There are 3 statement interfaces are available in the java.sql package. These are explained below:

a) Statement
This interface is used to implement simple SQL statements with no parameter. It returns the ResultSet object.

Statement statemnt1 = conn.createStatement();

b) PreparedStatement
This PreparedStatement interface extends the Statement interface. So, it has more features than the Statement interface. It is used to implement parameterized and precompiled SQL statements. The performance of the application increases because it compiles the query only once.

It is easy to reuse this interface with a new parameter. It supports the IN parameter. Even we can use this statement without any parameter.

String select_query = “Select * from states where state_id = 1”;
PreparedStatement prpstmt = conn.prepareStatement(select_query);

c) CallableStatement
CallableStatement interface extends the PreparedStatement interface. So, it has more features than the PreparedStatement interface. It is used to implement a parameterized SQL statement that invokes procedure or function in the database. A stored procedure works like a method or function in a class. It supports the IN and OUT parameters.

The CallableStatement instance is created by calling the prepareCall method of the Connection object.

CallableStatementcallStmt = con.prepareCall("{call procedures(?,?)}");

(ii) Execute The Query

There are 4 important methods to execute the query in Statement interface. These are explained below:

• ResultSet executeQuery(String sql)
• int executeUpdate(String sql)
• boolean execute(String sql)
• int []executeBatch()

a) ResultSet executeQuery(String sql)
The executeQuery() method in Statement interface is used to execute the SQL query and retrieve the values from DB. It returns the ResultSet object. Normally, we will use this method for the SELECT query.

b) executeUpdate(String sql)
The executeUpdate() method is used to execute value specified queries like INSERT, UPDATE, DELETE (DML statements), or DDL statements that return nothing. Mostly, we will use this method for inserting and updating.

c) execute(String sql)
The execute() method is used to execute the SQL query. It returns true if it executes the SELECT query. And, it returns false if it executes INSERT or UPDATE query.

d) executeBatch()
This method is used to execute a batch of SQL queries to the Database and if all the queries get executed successfully, it returns an array of update counts. We will use this method to insert/update the bulk of records.

5) Retrieve Results

When we execute the queries using the executeQuery() method, the result will be stored in the ResultSet object. The returned ResultSet object will never be null even if there is no matching record in the table. ResultSet object is used to access the data retrieved from the Database.

ResultSet rs 1= statemnt1.executeQuery(QUERY));

We can use the executeQuery() method for the SELECT query. When someone tries to execute the insert/update query, it will throw SQLExecption with the message
“executeQuery method can not be used for update”.

A ResultSet object points to the current row in the Resultset. To iterate the data in the ResultSet object, call the next() method in a while loop. If there is no more record to read, it will return FALSE.

ResultSet can also be used to update data in DB. We can get the data from ResultSet using getter methods such as getInt(), getString(), getDate(). We need to pass the column index or column name as the parameter to get the values using Getter methods.

We will get to know more about the ResultSet in the next tutorial.

6) Close Connection

Finally, we are done with manipulating data in DB. Now we can close the JDBC connection. We need to make sure that we have closed the resource after we have used it. If we don’t close them properly we may end up out of connections.

When we close the connection object, Statement and ResultSet objects will be closed automatically.

conn.close();

From Java 7 onwards, we can close the JDBC connections automatically using a try-catch block. JDBC connection should be opened in the parenthesis of the try block. Inside the try block, you can do the database connections normally as we do.

Once the execution exits the try block, it will automatically close the connection. In this case, we don’t need to close the connection by calling conn.close method in the Java program.

try(Connection conn = DriverManager.getConnection(url, user, password))
{  
    //database connection and operation
}

JDBC Program in Java connection Example

In this example, you will see how to implement the 6 basic steps to connect with database using JDBC in Java program.

Create Table

Before that, first, create one table and add some entries into it.

Below is the SQL query to create a table.

create table employee_details (empNum number(10), lastName varchar(50), firstName varchar(50), email varchar(255) , deptNum number(10), salary number(10));

Created the “employee_details” table in Oracle DB.

Insert Data Into Table

Using the following queries, insert the data into the “employee_details” table.

insert into employee_details values (1001, 'Luther', 'Martin', 'ml@gmail.com', 1, 13000);
insert into employee_details values (1002, 'Murray', 'Keith', 'km@gmail.com', 2, 25000);
insert into employee_details values (1003, 'Branson', 'John', 'jb@gmail.com', 3, 15000);
insert into employee_details values (1004, 'Martin', 'Richard', 'rm@gmail.com', 4, 16000);
insert into employee_details values (1005, 'Hickman', 'David', 'dh@gmail.com', 5, 17000);

Java JDBC Program

Download the JDBC jar file and import it into the Java project.

package com.STH.JDBC;
    // import sql package to use it in our program
    import java.sql.*;
 
    public class Sample_JDBC_Program {
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // store the SQL statement in a string
        String QUERY = "select * from employee_details";
        //register the oracle driver with DriverManager
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //Here we have used Java 8 so opening the connection in try statement
        try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
        {
                Statement statemnt1 = conn.createStatement();
                //Created statement and execute it
                ResultSet rs1 = statemnt1.executeQuery(QUERY);
                {  
                    //Get the values of the record using while loop
                    while(rs1.next())
                    {
                        int empNum = rs1.getInt("empNum");
                        String lastName = rs1.getString("lastName");
                        String firstName = rs1.getString("firstName");
                        String email = rs1.getString("email");
                        String deptNum = rs1.getString("deptNum");
                        String salary = rs1.getString("salary");
                        //store the values which are retrieved using ResultSet and print it
                    System.out.println(empNum + "," +lastName+ "," +firstName+ "," +email +","+deptNum +"," +salary);
                    }
                }
        }
        catch (SQLException e) {
            //If exception occurs catch it and exit the program
            e.printStackTrace();
        }
       }
    }

Output

Key points to be noted:

• First, we will import the necessary packages in our Java program to establish a JDBC connection. These packages contain classes, subclasses, and interfaces that we will need to interact with the database.
• Next, we will register or load the driver with the DriverManager class, which is responsible for managing the JDBC drivers that are available to our program. This step is necessary before we can establish a connection to the database.
• Once the driver is registered, we can establish a connection and begin performing database operations. To create and execute SQL queries, we can use the Statement interface for simple queries, or the PreparedStatement interface for insert, update, and delete operations.
• The results of a SQL query are stored in a ResultSet object, which we can use to retrieve the data from the database. The next() method of the ResultSet class is used to retrieve more than one record from the results.
• Finally, when we are finished with our database operations, it is important to close the connection to free up the resources for other programs to use.

Conclusion
In conclusion, JDBC (Java Database Connectivity) is a pivotal technology that empowers Java programs to seamlessly interact with databases. By providing a standardized API consisting of Java classes and interfaces, JDBC establishes a robust link between the frontend Java application and the backend database. This facilitates the efficient transfer of data, enabling programmers to store, retrieve, and manipulate information within databases, thereby enhancing the functionality and versatility of Java applications.

Frequently Asked Questions (FAQs) related to JDBC program in Java

Here are some FAQs related to JDBC Program in Java:

1. What is JDBC, and why is it important for Java developers?
JDBC stands for Java Database Connectivity, and it is vital for Java developers as it offers a standardized mechanism to connect and interact with databases. It enables seamless data exchange between Java applications and databases, facilitating tasks such as data retrieval, storage, and manipulation.

2. How does JDBC differ from ODBC?
JDBC is an evolution of ODBC (Open Database Connectivity) with a focus on Java applications. While both provide database connectivity, JDBC is tailored specifically for Java programming, utilizing Java classes and interfaces, whereas ODBC is a more general API that can be used with various programming languages.

3. What components are involved in a JDBC program?
A JDBC program involves several key components, including the JDBC driver (specific to the database), the DriverManager class for managing drivers, Connection objects for database connections, Statement/PreparedStatement objects for executing SQL queries, and ResultSet objects for handling query results.

4. What are the steps to establish a JDBC connection to a database?
To establish a JDBC connection, you typically load the appropriate JDBC driver, use the DriverManager to create a Connection object by providing the database URL, username, and password, and then use this Connection object to execute SQL queries and interact with the database.

5. What are the advantages of using PreparedStatement over Statement in JDBC?
PreparedStatement offers advantages like improved performance and security. It precompiles the SQL query, reducing database workload, and provides parameter binding to prevent SQL injection attacks.