10.2 Executing SQL Statements Explained

Executing SQL statements in Java SE 11 is a fundamental skill for interacting with databases. This section will cover the key concepts and techniques for executing SQL statements using JDBC.

Key Concepts

1. Establishing a Connection

Before executing any SQL statements, you need to establish a connection to the database. This is typically done using the DriverManager.getConnection() method, which returns a Connection object.

Example

        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";
        Connection connection = DriverManager.getConnection(url, user, password);
    

2. Creating a Statement

Once a connection is established, you can create a Statement object to execute SQL queries. The Statement interface provides methods like executeQuery() for SELECT statements and executeUpdate() for INSERT, UPDATE, and DELETE statements.

Example

        Statement statement = connection.createStatement();
    

3. Executing a Query

To execute a SQL query, you can use the executeQuery() method, which returns a ResultSet object containing the query results. This method is used for SELECT statements.

Example

        String query = "SELECT * FROM employees";
        ResultSet resultSet = statement.executeQuery(query);
    

4. Processing the ResultSet

The ResultSet object contains the data returned by the query. You can iterate through the results using the next() method and retrieve individual column values using methods like getString(), getInt(), etc.

Example

        while (resultSet.next()) {
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            System.out.println("Name: " + name + ", Age: " + age);
        }
    

5. Executing an Update

For SQL statements that modify the database (INSERT, UPDATE, DELETE), you use the executeUpdate() method. This method returns the number of rows affected by the statement.

Example

        String updateQuery = "UPDATE employees SET age = 30 WHERE name = 'John'";
        int rowsAffected = statement.executeUpdate(updateQuery);
        System.out.println(rowsAffected + " rows updated.");
    

6. Using PreparedStatement

The PreparedStatement interface is used to execute precompiled SQL statements with parameters. This is more efficient and secure, as it helps prevent SQL injection attacks.

Example

        String sql = "INSERT INTO employees (name, age) VALUES (?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "Jane");
        preparedStatement.setInt(2, 25);
        preparedStatement.executeUpdate();
    

7. Closing Resources

It is important to close the ResultSet, Statement, and Connection objects after use to free up resources. This can be done using the close() method.

Example

        resultSet.close();
        statement.close();
        connection.close();
    

Examples and Analogies

Think of executing SQL statements as placing an order at a restaurant. Establishing a connection is like walking into the restaurant and being seated. Creating a statement is like placing your order with the waiter. Executing a query is like receiving your meal, and processing the ResultSet is like eating your meal. Executing an update is like asking for a modification to your order. Using PreparedStatement is like ordering from a pre-set menu, ensuring accuracy and efficiency. Closing resources is like paying the bill and leaving the restaurant.

By mastering the execution of SQL statements in Java SE 11, you can efficiently interact with databases, retrieve and manipulate data, and build robust database-driven applications.