Introduction

As a Java developer, I find Java Database Connectivity (JDBC) to be an essential tool for interacting with relational databases from Java applications. JDBC is a Java API that offers a standardized approach to accessing databases, allowing me to use SQL commands to insert, update, retrieve and delete data in a database.

With JDBC, I can develop Java applications that can interact with databases on any platform that supports JDBC, irrespective of the underlying database management system. This platform-independent interface to databases is a significant advantage, as it means that my Java applications can be developed and run on any platform.

Overall, JDBC provides a reliable and efficient way for me to connect Java applications to databases, allowing me to access and manipulate data easily.

So without wasting any time, let's understand this.

Connecting to a Database using JDBC

To connect to a database using JDBC, the following steps are typically followed:

Load the Driver Class

If you're developing a Java application that needs to interact with a database, you'll need to load the JDBC driver first. The JDBC driver is what enables your application to communicate with the database, so it's a critical component of the process.

To load a JDBC driver into the Java Virtual Machine (JVM), you can use the Class.forName() method. For instance, if you're using the MySQL database, you can load the MySQL JDBC driver using the following code:

// used for MySQL versions prior to 8.0
Class.forName("com.mysql.jdbc.Driver"); 

//used for MySQL 8.0 and later
Class.forName("com.mysql.cj.jdbc.Driver");

This code loads the MySQL JDBC driver into the JVM, making it available for use in your application. Once the driver is loaded, you can establish a connection to the database and start interacting with it using JDBC API methods.

---

Establish a connection

I know that once I have loaded the JDBC driver, I need to establish a connection to the database to start interacting with it. This is typically done using the DriverManager.getConnection() method, which takes three parameters: the URL of the database, the username, and the password.

For example, suppose I want to connect to a MySQL database named "mydatabase" with the username "root" and password "password." In that case, I can use the following code to establish the connection:

String url = "jdbc:mysql://localhost/mydatabase";
String user = "root";
String password = "password";
Connection connection = DriverManager.getConnection(url, user, password);

or you can use this:

Connection con = null;
con = DriverManager.getConnection("jdbc:mysql://localhost:3306?user=root&password=password");

This code creates a Connection object, which represents the connection to the database. The URL parameter specifies the location of the database, and the username and password parameters are used to authenticate the user.

Once the connection is established, I can start using JDBC API methods to interact with the database, such as executing SQL queries and modifying data. However, it's essential to remember to close the connection when I'm finished with it to free up resources and prevent potential memory leaks.

---

Create a statement

To execute SQL commands against a database, a Statement object must be created after establishing a connection to the database using JDBC. This can be achieved by using the Connection.createStatement() method which returns a Statement object. The following code demonstrates how to create a Statement object using an established connection object in Java:

Statement statement = connection.createStatement();

With the Statement object created, Now you can execute SQL commands such as SELECT, INSERT, UPDATE, and DELETE to interact with the database.

---

Execute an SQL query

Once a Statement object has been created, the next step is to execute SQL queries against the database. This can be achieved using the Statement.executeQuery() method, which takes an SQL query as a parameter and returns a ResultSet object that contains the results of the query.

For instance, suppose I have a Statement object named "statement" that was created using an established connection to a MySQL database named "mydatabase" with the username "root" and password "password." In that case, I can execute an SQL query that retrieves all the rows from a table named "users" using the following code:

ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

Or you can do

String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);

---

Process the results

I understand that processing the results of an SQL query is an essential step in interacting with databases. After executing an SQL query using the Statement.executeQuery() method, the results can be processed using the ResultSet object.

The ResultSet.next() method is used to move the cursor to the next row in the ResultSet. Once the cursor is positioned at a row, the ResultSet.getXXX() methods can be used to retrieve the values of the columns in that row, where XXX is the data type of the column.

For example, suppose I have a ResultSet object named "resultSet" that was created by executing an SQL query to retrieve the values of the "id" and "name" columns from a table named "users." I can retrieve the values of the "id" and "name" columns in the current row using the following code:

int id = resultSet.getInt("id");
String name = resultSet.getString("name");

This code retrieves the value of the "id" column as an integer using the ResultSet.getInt() method and the value of the "name" column as a string using the ResultSet.getString() method.

Once the values have been retrieved, they can be processed as needed. It's important to note that the ResultSet object is forward-only, which means that the cursor can only move forward through the ResultSet. If it's necessary to move backward through the ResultSet, a different type of ResultSet object, such as a ScrollableResultSet, should be used.

---

Close the resources

It's important to close the resources used by the ResultSet, Statement, and Connection objects after they've been used. This ensures that database resources are released and prevents resource leaks.

To close the ResultSet object, I can call the ResultSet.close() method. To close the Statement object, I can call the Statement.close() method. And to close the Connection object, I can call the Connection.close() method. For example, suppose I have created a ResultSet object named "resultSet", a Statement object named "statement", and a Connection object named "connection". After processing the results, I can close the resources using the following code:

resultSet.close();
statement.close();
connection.close();

---

Conclusion

JDBC is an essential API for Java developers who need to access databases from their applications. With JDBC, developers can easily interact with databases using SQL commands and retrieve or update data as needed.

In this blog post, we covered the steps involved in using JDBC to connect Java applications to databases. We discussed how to load the JDBC driver, establish a connection to the database, create a statement, execute an SQL query, and process the results. We also emphasized the importance of closing resources to prevent resource leaks and ensure efficient database interactions.

By following the steps outlined in this blog post, you can leverage the power of JDBC to develop robust, scalable applications that interact with databases efficiently and reliably. Whether you're working on a small project or a large enterprise application, JDBC provides a versatile and powerful toolset for working with databases in Java.

Here is the full code for the example:

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

public class JDBCDemo {

  public static void main(String[] args) {
    try {
      // Load the MySQL JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      // Establish a connection to the database
      String url = "jdbc:mysql://localhost/mydatabase";
      String user = "root";
      String password = "password";
      Connection connection = DriverManager.getConnection(url, user, password);

      // Create a statement
      Statement statement = connection.createStatement();

      // Execute an SQL query
      String sql = "SELECT * FROM users";
      ResultSet resultSet = statement.executeQuery(sql);

      // Process the results
      while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        System.out.println("id: " + id + ", name: " + name);
      }

      // Close the resources
      resultSet.close();
      statement.close();
      connection.close();
    } catch (ClassNotFoundException e) {
      System.out.println("JDBC driver not found");
    } catch (SQLException e) {
      System.out.println("SQL exception: " + e.getMessage());
    }
  }

}

Or you can do it in this way also:

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

public class StatementDemo {
    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // Load the MySQL JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Driver class loaded");

            // Establish a connection to the database
            con = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase?user=root&password=password");
            System.out.println("Established connection");

            // Create a statement object
            stmt = con.createStatement();
            System.out.println("Statement object created");

            // Execute an SQL query and get the result set
            String sql = "SELECT id, username FROM users";
            rs = stmt.executeQuery(sql);

            // Process the result set
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                System.out.println("id: " + id + ", username: " + username);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // Close the result set
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            // Close the statement
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            // Close the connection
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            System.out.println("All resources closed");
        }
    }
}

See you in the next blog, Until then keep developing and solving.