What is the difference between Statement, PreparedStatement, and CallableStatement in JDBC?

Java JDBC API includes several interfaces to interact with relational databases. Among them, the Statement, PreparedStatement, and CallableStatement interfaces are the most commonly used. These interfaces share some similarities but also have some differences.

- **Statement**: This is the basic interface for executing SQL statements in JDBC. It provides a simple way to execute static SQL queries and updates. The statement interface is used to execute SQL queries, such as SELECT, INSERT, UPDATE, and DELETE. The example below illustrates how to create, execute, and retrieve data using the Statement interface:

    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");
    while (resultSet.next()) {
        System.out.println(resultSet.getInt("id") + " " + resultSet.getString("name"));
    }

- **PreparedStatement**: This interface extends the Statement interface and provides a more efficient way to execute parameterized SQL queries. The PreparedStatement interface allows you to parameterize the SQL statement using placeholders ’?’ and then bind values to these placeholders at execution time. This improves performance because the database can cache the execution plan for the prepared statement, which reduces the overhead of parsing and optimizing the query each time it is executed. The example below illustrates how to create, execute and retrieve data using the PreparedStatement interface:

    PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM employees WHERE id = ?");
    preparedStatement.setInt(1, 1);
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
        System.out.println(resultSet.getInt("id") + " " + resultSet.getString("name"));
    }

- **CallableStatement**: This interface extends the PreparedStatement interface and provides a way to call stored procedures or functions. The CallableStatement interface allows you to execute database stored procedures by passing input parameters, output parameters, and return values. The example below illustrates how to create, execute and retrieve data from a stored procedure using the CallableStatement interface:

    CallableStatement callableStatement = connection.prepareCall("{call get_employee(?, ?)}");
    callableStatement.setInt(1, 1);
    callableStatement.registerOutParameter(2, Types.VARCHAR);
    callableStatement.execute();
    String name = callableStatement.getString(2);
    System.out.println("Employee name: " + name);

In general, using a PreparedStatement is preferred over a Statement because it improves performance, provides better security against SQL injection attacks, and is more convenient to use. However, when you need to execute stored procedures, the CallableStatement interface becomes the method of choice.