To execute SQL queries using JDBC (Java Database Connectivity), you typically use the Statement or PreparedStatement objects. Here’s a general guide on how to execute different types of SQL queries:

1. Setup JDBC Connection

Ensure you have a connection to the database before executing any queries. Here’s a basic setup:

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

public class DatabaseConnection {
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/dbname"; // Update with your database URL
        String user = "username"; // Update with your database username
        String password = "password"; // Update with your database password
        
        return DriverManager.getConnection(url, user, password);
    }
}

2. Executing SQL Queries

a. Executing a Query (SELECT)

Use the Statement object to execute SELECT queries.

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

public class QueryExample {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnection.getConnection();
             Statement statement = connection.createStatement()) {

            String query = "SELECT * FROM tablename"; // Replace with your SQL query
            ResultSet resultSet = statement.executeQuery(query);
            
            while (resultSet.next()) {
                // Process the result set
                int id = resultSet.getInt("id"); // Replace "id" with your column name
                String name = resultSet.getString("name"); // Replace "name" with your column name
                System.out.println("ID: " + id + ", Name: " + name);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

b. Executing an Update (INSERT, UPDATE, DELETE)

Use the Statement or PreparedStatement object to execute INSERT, UPDATE, or DELETE queries.

Using Statement:

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

public class UpdateExample {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnection.getConnection();
             Statement statement = connection.createStatement()) {

            String updateQuery = "UPDATE tablename SET columnname = 'value' WHERE condition"; // Replace with your SQL query
            int rowsAffected = statement.executeUpdate(updateQuery);
            
            System.out.println("Rows affected: " + rowsAffected);
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Using PreparedStatement:

  import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedStatementExample {
    public static void main(String[] args) {
        String updateQuery = "UPDATE tablename SET columnname = ? WHERE condition"; // Replace with your SQL query

        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(updateQuery)) {

            preparedStatement.setString(1, "value"); // Set value for the placeholder
            int rowsAffected = preparedStatement.executeUpdate();
            
            System.out.println("Rows affected: " + rowsAffected);
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. Handle Exceptions and Resource Management

Ensure you handle SQL exceptions properly and close resources to avoid leaks. The try-with-resources statement (used above) automatically closes the resources.

Summary

  • Statement: Use for executing simple queries or updates.
  • PreparedStatement: Use for executing queries with parameters or when you want to avoid SQL injection risks.