Java Database Connectivity with example

Java Database Connectivity (JDBC) is an API that allows Java applications to interact with databases. JDBC provides a standard interface for connecting to relational databases, executing SQL queries, and retrieving results.

What is JDBC?

JDBC is part of the Java Standard Edition (Java SE) and provides methods for querying and updating data in a database. The key features of JDBC include:

Database Independence: JDBC allows developers to write database-agnostic code.
Interoperability: Supports various databases through JDBC drivers.
SQL Execution: Enables the execution of SQL statements to manipulate database data.

JDBC Architecture

The JDBC architecture consists of two main layers:

JDBC API: Provides the application-to-JDBC manager connection.
JDBC Driver API: Manages the communication between the application and the database.

Types of JDBC Drivers

There are four types of JDBC drivers:

Type-1 Driver (JDBC-ODBC Bridge Driver): Converts JDBC calls into ODBC calls. Not recommended for production use.
Type-2 Driver (Native-API Driver): Converts JDBC calls into database-specific native calls. Requires native library installation.
Type-3 Driver (Network Protocol Driver): Uses a middle-tier server to convert JDBC calls into database-specific calls. Provides better flexibility.
Type-4 Driver (Thin Driver): Pure Java driver that communicates directly with the database. Recommended for most applications.

Setting Up JDBC

Step 1: Include JDBC Driver

To use JDBC, you need to include the database-specific JDBC driver in your project. For example, for MySQL, you can download the MySQL Connector/J driver from the MySQL website and include it in your classpath.

Step 2: Configure Database

Ensure you have a running database server. For this guide, we’ll use a MySQL database. Create a database and a sample table:

				
					CREATE DATABASE testdb;

USE testdb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Step 3: Java Development Environment

You can use any IDE like IntelliJ IDEA, Eclipse, or even a simple text editor. Ensure that your Java Development Kit (JDK) is installed and configured.

Basic JDBC Example

Step 1: Establishing a Connection

The first step in any JDBC program is to establish a connection to the database.

Example: Connecting to a MySQL Database

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

public class JDBCExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "root"; // replace with your database user
        String password = "password"; // replace with your database password

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            if (connection != null) {
                System.out.println("Connected to the database!");
            }
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
        }
    }
}

Explanation of Connection Code

DriverManager: Manages the list of database drivers. It attempts to establish a connection using the specified URL.
Connection: Represents the connection to the database.
try-with-resources: Ensures that the connection is closed automatically after use.

Step 2: Executing SQL Statements

Once connected, you can execute SQL statements using the Statement, PreparedStatement, or CallableStatement interfaces.

Example: Creating a Table and Inserting Data

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

public class InsertExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "root"; 
        String password = "password"; 

        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement()) {

            // Create table if it doesn't exist
            String createTableSQL = "CREATE TABLE IF NOT EXISTS users (" +
                                     "id INT AUTO_INCREMENT PRIMARY KEY, " +
                                     "name VARCHAR(100), " +
                                     "email VARCHAR(100))";
            statement.executeUpdate(createTableSQL);

            // Insert data
            String insertSQL = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
            statement.executeUpdate(insertSQL);
            System.out.println("Data inserted successfully!");

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

Explanation of Insert Code

Statement: Used to execute SQL queries.
executeUpdate(): Executes a statement that changes the database (INSERT, UPDATE, DELETE).

Step 3: Retrieving Data

To retrieve data from the database, you use the ResultSet object.

Example: Querying Data

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

public class RetrieveExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "root"; 
        String password = "password"; 

        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement()) {

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

            // Iterate through the ResultSet
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }

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

Explanation of Retrieval Code

ResultSet: Represents the result set of a query. You can iterate through it to retrieve data.
getInt() and getString(): Methods to retrieve values from the current row.

Using PreparedStatement for Secure Queries

Using PreparedStatement is recommended for executing queries, especially when dealing with user input. It helps prevent SQL injection attacks and improves performance.

Example: Using PreparedStatement

				
					import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedStatementExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "root"; 
        String password = "password"; 

        String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {

            preparedStatement.setString(1, "Alice Smith");
            preparedStatement.setString(2, "alice@example.com");
            preparedStatement.executeUpdate();
            System.out.println("Data inserted successfully!");

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

Explanation of PreparedStatement Code

PreparedStatement: Precompiles the SQL statement, which can be executed multiple times with different parameters.
setString(): Sets the value for the placeholder in the SQL statement.

Updating and Deleting Records

You can also update and delete records using PreparedStatement.

Example: Updating and Deleting Records

				
					import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateDeleteExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "root"; 
        String password = "password"; 

        // Update example
        String updateSQL = "UPDATE users SET email = ? WHERE name = ?";
        
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement updateStatement = connection.prepareStatement(updateSQL)) {

            updateStatement.setString(1, "alice.new@example.com");
            updateStatement.setString(2, "Alice Smith");
            int rowsUpdated = updateStatement.executeUpdate();
            System.out.println(rowsUpdated + " rows updated!");

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

        // Delete example
        String deleteSQL = "DELETE FROM users WHERE name = ?";
        
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement deleteStatement = connection.prepareStatement(deleteSQL)) {

            deleteStatement.setString(1, "Alice Smith");
            int rowsDeleted = deleteStatement.executeUpdate();
            System.out.println(rowsDeleted + " rows deleted!");

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

Explanation of Update/Delete Code

UPDATE Statement: Modifies existing records.
DELETE Statement: Removes records from the database.

Error Handling in JDBC

Proper error handling is crucial in JDBC applications. Common exceptions include:

SQLException: Indicates a database access error or other errors.
ClassNotFoundException: Thrown when the JDBC driver class is not found.

Example: Handling Exceptions

				
					try {
    // Database operations...
} catch (SQLException e) {
    System.err.println("SQL error: " + e.getErrorCode() + " - " + e.getMessage());
} catch (ClassNotFoundException e) {
    System.err.println("JDBC Driver not found: " + e.getMessage());
}

Connection Pooling

Connection pooling is a technique to manage database connections efficiently. It improves performance by reusing existing connections rather than creating new ones for each request.

Example: Basic Connection Pooling with HikariCP

Add the HikariCP dependency to your project:

				
					<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

Example Code

				
					import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.SQLException;

public class ConnectionPoolExample {
    private static HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/testdb");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(10);
        dataSource = new HikariDataSource(config);
    }

    public static void main(String[] args) {
        try (Connection connection = dataSource.getConnection()) {
            System.out.println("Connected to the database using connection pool!");
        } catch (SQLException e) {
            System.err.println("Connection pool error: " + e.getMessage());
        }
    }
}

Explanation of Connection Pooling Code

HikariCP: A fast and simple JDBC connection pool.
HikariConfig: Configuration class to set up the connection parameters.
HikariDataSource: Manages the pool of connections.

Riven

Riven

Java Database Connectivity with example

What is JDBC?

JDBC Architecture

Types of JDBC Drivers

Setting Up JDBC

Step 1: Include JDBC Driver

Step 2: Configure Database

Step 3: Java Development Environment

Basic JDBC Example

Step 1: Establishing a Connection

Example: Connecting to a MySQL Database

Explanation of Connection Code

Step 2: Executing SQL Statements

Example: Creating a Table and Inserting Data

Explanation of Insert Code

Step 3: Retrieving Data

Example: Querying Data

Explanation of Retrieval Code

Using PreparedStatement for Secure Queries

Example: Using PreparedStatement

Explanation of PreparedStatement Code

Updating and Deleting Records

Example: Updating and Deleting Records

Explanation of Update/Delete Code

Error Handling in JDBC

Example: Handling Exceptions

Connection Pooling

Example: Basic Connection Pooling with HikariCP

Example Code

Explanation of Connection Pooling Code

Related topic

Riven

Let's get social