In this post, I will demonstrate what are fundamental steps involved in the process of connecting to a database and executing a query using JDBC API.

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

Learn complete JDBC at https://www.javaguides.net/p/jdbc-tutorial.html

Technologies used

We use below technologies in this post:

JDK - 1.8 or later
MySQL - 5.7.12
IDE - Eclipse Neon
JDBC - 4.2

Fundamental Steps in JDBC

The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:

Import JDBC packages
Load and register the JDBC driver // This step is not required in Java 6 and in JDBC 4.0
Open a connection to the database.
Create a statement object to perform a query.
Execute the statement object and return a query resultset.
Process the resultset.
Close the resultset and statement objects. // This step is not required because we use a try-with-resource statement to auto-close the resources
Close the connection. // This step is not required because we use a try-with-resource statement to auto-close the resources

From the above steps, we actually require below five steps to connect a Java application to the database (example: MySQL):

Import JDBC packages
Open a connection to the database.
Create a statement object to perform a query.
Execute the statement object and return a query resultset.
Process the resultset.

Key points

From JDBC 4.0, we don't need to include 'Class.forName()' in our code to load JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.
We have used try-with-resources statements to automatically close JDBC resources.

1. Import JDBC packages

This is for making the JDBC API classes immediately available to the application program. The following import statement should be included in the program irrespective of the JDBC driver being used:

import java.sql.*;

For individual JDBC API classes imports:

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

2. Open a connection to the database

We use the getConnection() method of the DriverManager class to connect to the database.

Syntax: Here are overloaded getConnection() methods available:

Connection java.sql.DriverManager.getConnection(String url) throws SQLException
Connection java.sql.DriverManager.getConnection(String url, String username, String password) throws SQLException
Connection java.sql.DriverManager.getConnection(String url, Properties info) throws SQLException

Example: The following lines of code illustrate using the getConnection() method to connect to a MySQL database:

try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

3. Create a statement object to perform a query

We can use the createStatement() method is invoked on the current Connection object to create a SQL Statement.

Syntax:

public Statement createStatement() throws SQLException

Example: Let's create a Statement object using a connection.createStatement() method:

// Step 3:Create a statement using connection object
 Statement stmt = connection.createStatement();

4. Execute the statement object and return a query resultset

Let's use the executeQuery() method of Statement interface is used to execute SQL statements.

Syntax:

public ResultSet executeQuery(String query) throws SQLException

Example:

// Step 4: Execute the query or update query
ResultSet rs = stmt.executeQuery(QUERY));

5. Process the resultset

Below snippet shows how to process ResultSet object using while loop:

// Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }

Complete Code

Let's put all the steps together and here is the complete example with output:

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

/**
 * Select Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class SelectStatementExample {
    private static final String QUERY = "select id,name,email,country,password from Users";

    public static void main(String[] args) {

        // using try-with-resources to avoid closing resources (boilerplate code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            Statement stmt = connection.createStatement();

            // Step 3: Execute the query or update query
            ResultSet rs = stmt.executeQuery(QUERY)) {

            // Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

Output:

1,Ram,tony@gmail.com,US,secret
3,Pramod,pramod@gmail.com,India,123
4,Deepa,deepa@gmail.com,India,123
5,Tom,top@gmail.com,India,123

Learn complete JDBC at https://www.javaguides.net/p/jdbc-tutorial.html

Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours

Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course

Java Guides

Search This Blog

Steps to Connect a Java Application to Database (JDBC and MySQL)