Java Database Connectivity (JDBC) is a Java API that enables Java applications to interact with databases. It provides methods for querying and updating data in a database. JDBC is part of the Java Standard Edition platform and is included in the java.sql package.

Key Components of JDBC

  1. DriverManager: Manages a list of database drivers. It matches connection requests from the Java application with the appropriate database driver using communication subprotocol.

  2. Driver: Interfaces that handle the communications with the database server. The DriverManager uses this to connect to the database.

  3. Connection: Represents a session/connection with a specific database. All interactions with the database are done via this connection object.

  4. Statement: Used to execute SQL queries. There are three types:

    • Statement: Used for simple SQL statements with no parameters.
    • PreparedStatement: Used for precompiled SQL statements with or without parameters.
    • CallableStatement: Used to execute stored procedures.

  5. ResultSet: Represents the result set of a query. It provides methods to iterate through and access the results.

  6. SQLException: Exception class that provides information on a database access error or other errors.

Basic JDBC Operations

Loading and Registering the Driver

Before you can connect to a database, you must load and register the appropriate JDBC driver. This can be done using the Class.forName() method.

  try {
    Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

Establishing a Connection

Use the DriverManager.getConnection() method to create a Connection object.

  String url = "jdbc:mysql://localhost:3306/mydatabase";
String user = "username";
String password = "password";

Connection connection = null;

try {
    connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
    e.printStackTrace();
}

Creating a Statement

Use the Connection.createStatement() method to create a Statement object.

  Statement statement = null;

try {
    statement = connection.createStatement();
} catch (SQLException e) {
    e.printStackTrace();
}

Executing a Query

Use the Statement.executeQuery() method to execute a SELECT query and get a ResultSet object.

  ResultSet resultSet = null;

try {
    resultSet = statement.executeQuery("SELECT * FROM users");
    
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String email = resultSet.getString("email");
        System.out.println(id + ", " + name + ", " + email);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Inserting, Updating, and Deleting Data

Use the Statement.executeUpdate() method for INSERT, UPDATE, or DELETE statements.

  try {
    String sqlInsert = "INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')";
    int rowsInserted = statement.executeUpdate(sqlInsert);
    if (rowsInserted > 0) {
        System.out.println("A new user was inserted successfully!");
    }

    String sqlUpdate = "UPDATE users SET email='[email protected]' WHERE name='John Doe'";
    int rowsUpdated = statement.executeUpdate(sqlUpdate);
    if (rowsUpdated > 0) {
        System.out.println("An existing user was updated successfully!");
    }

    String sqlDelete = "DELETE FROM users WHERE name='John Doe'";
    int rowsDeleted = statement.executeUpdate(sqlDelete);
    if (rowsDeleted > 0) {
        System.out.println("A user was deleted successfully!");
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Using Prepared Statements

Prepared statements are used to execute parameterized SQL queries. They are precompiled and provide better performance, especially for repetitive queries.

  String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement preparedStatement = null;

try {
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, "Jane Doe");
    preparedStatement.setString(2, "[email protected]");
    int rowsInserted = preparedStatement.executeUpdate();
    if (rowsInserted > 0) {
        System.out.println("A new user was inserted successfully!");
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Using Callable Statements

Callable statements are used to execute stored procedures.

  String sql = "{call getUser(?, ?)}";
CallableStatement callableStatement = null;

try {
    callableStatement = connection.prepareCall(sql);
    callableStatement.setInt(1, 1); // Set input parameter
    callableStatement.registerOutParameter(2, Types.VARCHAR); // Register output parameter
    callableStatement.execute();

    String userName = callableStatement.getString(2); // Get output parameter
    System.out.println("User Name: " + userName);
} catch (SQLException e) {
    e.printStackTrace();
}

Closing Connections

Always close the ResultSet, Statement, and Connection objects to free up resources.

  try {
    if (resultSet != null) resultSet.close();
    if (statement != null) statement.close();
    if (connection != null) connection.close();
} catch (SQLException e) {
    e.printStackTrace();
}

Example: Complete JDBC Program

Here’s a complete example that demonstrates the basic JDBC operations.

  import java.sql.*;

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

        try {
            // Load and register JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish connection
            Connection connection = DriverManager.getConnection(url, user, password);

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

            // Execute a query
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

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

            // Insert a new record using PreparedStatement
            String sqlInsert = "INSERT INTO users (name, email) VALUES (?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sqlInsert);
            preparedStatement.setString(1, "Jane Doe");
            preparedStatement.setString(2, "[email protected]");
            preparedStatement.executeUpdate();

            // Update a record
            String sqlUpdate = "UPDATE users SET email='[email protected]' WHERE name='Jane Doe'";
            statement.executeUpdate(sqlUpdate);

            // Delete a record
            String sqlDelete = "DELETE FROM users WHERE name='Jane Doe'";
            statement.executeUpdate(sqlDelete);

            // Close resources
            resultSet.close();
            statement.close();
            preparedStatement.close();
            connection.close();

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

By understanding and utilizing these JDBC components and techniques, you can effectively manage database interactions in your Java applications.