JDBC API Components

JDBC API Components

The JDBC (Java Database Connectivity) API provides a standard interface for connecting to relational databases from Java applications. The main components of the JDBC API are:

1. DriverManager: Manages a list of database drivers. It establishes a connection between a database and the appropriate driver.

2. Driver: Handles the communications with the database server. It interacts with the database management system (DBMS).

3. Connection: Represents a connection with a specific database. It provides methods for creating statements, managing transactions, and closing connections.

4. Statement: Used for executing SQL queries against the database. There are three types of statements:

  • Statement: Used for general-purpose access to the database.
  • PreparedStatement: Used for executing precompiled SQL queries with input parameters.
  • CallableStatement: Used for executing stored procedures.

5. ResultSet: Represents the result set of a database query. It provides methods for retrieving data from the result set row by row.

6. SQLException: Handles errors and exceptions that occur during database access.

JDBC API

Let’s illustrate these components with a Java example where we connect to a MySQL database, execute a query, and process the results.

Example
Database Table Creation
Before running the Java code, create a table in your database.
sql
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    price DOUBLE
);

Java Program Using JDBC Components
java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCComponentsExample {
    public static void main(String[] args) {
        // JDBC URL, username, and password of MySQL server
        String jdbcURL = "jdbc:mysql://localhost:3306/your_database";
        String username = "root";
        String password = "password";

        // JDBC variables for opening and managing connection
        Connection connection = null;

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

            // Establish a connection
            connection = DriverManager.getConnection(jdbcURL, username, password);
            System.out.println("Connected to the database!");

            // Create a SQL query
            String sql = "SELECT * FROM products";

            // Create a Statement
            PreparedStatement statement = connection.prepareStatement(sql);

            // Execute the query
            ResultSet resultSet = statement.executeQuery();

            // Process the results
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                double price = resultSet.getDouble("price");

                System.out.println("ID: " + id);
                System.out.println("Name: " + name);
                System.out.println("Price: " + price);
                System.out.println("-----------");
            }

            // Close the resources
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.out.println("Disconnected from the database.");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Explanation of the Example

  • DriverManager:
  • DriverManager.getConnection(jdbcURL, username, password); establishes a connection to the database using the appropriate driver.
  • Driver:
  • Class.forName(“com.mysql.cj.jdbc.Driver”); loads the MySQL JDBC API driver, registering it with the DriverManager.
  • Connection:
  • DriverManager.getConnection(jdbcURL, username, password); creates a Connection object representing the connection to the database.
  • Statement:
  • connection.prepareStatement(sql); creates a PreparedStatement object for executing the SQL query.
  • ResultSet:
  • statement.executeQuery(); executes the query and returns a ResultSet object containing the results.
  • SQLException:
  • Errors and exceptions during database access are caught and handled using the SQLException class.

Homepage

Readmore