Executing SQL Queries

In JDBC, you can execute SQL queries using different types of statements, such as Statement, PreparedStatement, and CallableStatement. Each type of statement has its own use case and advantages. Here, I'll provide examples of how to execute SQL queries using each of these statement types.

Before running these examples, ensure that you have a MySQL database set up, and you have the MySQL JDBC driver in your classpath.

1. Using Statement to Execute SQL Queries

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

public class StatementExample {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "your_username", "your_password");
            Statement statement = connection.createStatement();

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

            // Process the results
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }

            // Close the resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2. Using PreparedStatement to Execute SQL Queries

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

public class PreparedStatementExample {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "your_username", "your_password");

            // Define a SQL query with parameters
            String sql = "SELECT * FROM your_table_name WHERE id = ?";
            int targetId = 1;

            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, targetId);

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

            // Process the results
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }

            // Close the resources
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3. Using CallableStatement to Execute SQL Queries

Callable statements are typically used for calling stored procedures in databases. Here's a basic example:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.Types;

public class CallableStatementExample {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "your_username", "your_password");

            // Define a stored procedure call
            String sql = "{CALL your_stored_procedure(?, ?)}";
            int param1 = 1;
            int outputParam = 0;

            CallableStatement callableStatement = connection.prepareCall(sql);
            callableStatement.setInt(1, param1);
            callableStatement.registerOutParameter(2, Types.INTEGER);

            // Execute the stored procedure call
            callableStatement.execute();

            // Retrieve the output parameter value
            outputParam = callableStatement.getInt(2);
            System.out.println("Output Parameter: " + outputParam);

            // Close the resources
            callableStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Make sure to replace "your_database", "your_username", "your_table_name", "your_stored_procedure", and any placeholders with your actual database information and SQL statements. Handle exceptions properly in a real application for robust error handling.