Connection, Statement, and ResultSet

In JDBC (Java Database Connectivity), the Connection, Statement, and ResultSet are key components used to interact with relational databases. Each plays a specific role in the process of executing SQL queries and processing the results. Let’s explore each of these components:

1. Connection:

The Connection interface represents a connection to a relational database. It is responsible for establishing a communication link between the Java application and the database. The DriverManager class is used to get a connection to the database.

Establishing a Connection:

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

public class DatabaseConnection {

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

        try {
            // Establishing a connection
            Connection connection = DriverManager.getConnection(url, username, password);

            // Perform database operations using the connection

            // Closing the connection
            connection.close();

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

Note:

• The getConnection method is static and part of the DriverManager class.
• The URL specifies the database type, location, and other parameters.

2. Statement:

The Statement interface is used for executing SQL queries against the database. There are three main types of statements: Statement, PreparedStatement, and CallableStatement.

Creating a Statement:

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

public class DatabaseStatement {

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);

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

            // Perform database operations using the statement

            // Closing the statement
            statement.close();
            connection.close();

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

Note:

• The createStatement method is called on the Connection object to create a Statement.
• The Statement is used for executing simple SQL queries without parameters.

PreparedStatement:

PreparedStatement is a subinterface of Statement and is used for executing parameterized SQL queries. It helps prevent SQL injection.

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/mydatabase";
        String username = "root";
        String password = "password";

        try {
            Connection connection = DriverManager.getConnection(url, username, password);

            // Creating a PreparedStatement
            String sql = "INSERT INTO employees (employee_name, salary) VALUES (?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            // Setting parameters
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setDouble(2, 50000.0);

            // Execute the query
            preparedStatement.executeUpdate();

            // Closing the PreparedStatement
            preparedStatement.close();
            connection.close();

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

3. ResultSet:

The ResultSet interface represents the result set of a database query. It provides methods to iterate over the rows and retrieve data from the query results.

Processing Results with ResultSet:

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

public class ResultSetExample {

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);

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

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

            // Processing the results
            while (resultSet.next()) {
                int id = resultSet.getInt("employee_id");
                String name = resultSet.getString("employee_name");
                double salary = resultSet.getDouble("salary");
                System.out.println("Employee ID: " + id + ", Name: " + name + ", Salary: " + salary);
            }

            // Closing the ResultSet and Statement
            resultSet.close();
            statement.close();
            connection.close();

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

Note:

• The next method of ResultSet moves the cursor to the next row.
• The getXXX methods retrieve data of different types (e.g., getInt, getString, getDouble).

4. Conclusion:

The Connection, Statement, and ResultSet components are fundamental to JDBC programming in Java. They provide the means to establish a connection to a database, execute SQL queries, and process the results. Understanding how to use these components is essential for developing database-driven Java applications.