Differences between execute, executeQuery

Differences between execute, executeQuery

In JDBC, the execute, executeQuery, and executeUpdate methods of the Statement and PreparedStatement interfaces are used to execute SQL statements. However, each method serves a different purpose and is used for different types of SQL statements.

Key Differences

1. execute

  • Purpose: Used for executing any kind of SQL statement (DDL, DML, or DQL).
  • Return Type: Returns a boolean value. true if the result is a ResultSet object; false if the result is an update count or no result.
  • Usage: Suitable for executing dynamic SQL statements when the type of the SQL statement is not known in advance.

2. executeQuery

  •  Purpose: Used for executing SELECT statements.
  •  Return Type: Returns a ResultSet object containing the data produced by the query.
  •  Usage: Suitable for executing queries that retrieve data from the database.

3. executeUpdate

  • Purpose: Used for executing INSERT, UPDATE, DELETE, and DDL statements.
  • Return Type: Returns an int value representing the number of rows affected by the query or statement.
  • Usage: Suitable for executing statements that modify data in the database or alter the database structure.

 execute,  executeQuery

Example

Let’s illustrate the differences with a Java example where we execute different types of SQL statements using execute, executeQuery, and executeUpdate.

Before running the Java code, create a table in your database.

Database Table Creation
sql
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary DOUBLE
);


Java Program Using execute, executeQuery, and executeUpdate

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

public class JDBCExecuteMethodsExample {
    public static void main(String[] args) {
        // JDBC URL, username, and password of MySQL server
        String jdbcURL = "jdbc:mysql://localhost:3306/your_database";
        String username = "root";
        String password = "password";

        // JDBC variables for opening and managing connection
        Connection connection = null;

        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish a connection
            connection = DriverManager.getConnection(jdbcURL, username, password);
            System.out.println("Connected to the database!");

            // 1. Using executeQuery to execute a SELECT statement
            String selectSQL = "SELECT * FROM employees";
            PreparedStatement selectStatement = connection.prepareStatement(selectSQL);
            ResultSet resultSet = selectStatement.executeQuery();

            System.out.println("Employees in the database:");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                double salary = resultSet.getDouble("salary");

                System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
            }
            resultSet.close();
            selectStatement.close();

            // 2. Using executeUpdate to execute an INSERT statement
            String insertSQL = "INSERT INTO employees (name, salary) VALUES ('Alice', 55000.0)";
            PreparedStatement insertStatement = connection.prepareStatement(insertSQL);
            int rowsInserted = insertStatement.executeUpdate();
            System.out.println("Inserted " + rowsInserted + " row(s) using executeUpdate");

            // 3. Using execute to execute a dynamic SQL statement
            String dynamicSQL = "UPDATE employees SET salary = 60000.0 WHERE name = 'Alice'";
            PreparedStatement dynamicStatement = connection.prepareStatement(dynamicSQL);
            boolean isResultSet = dynamicStatement.execute();

            if (isResultSet) {
                ResultSet rs = dynamicStatement.getResultSet();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    double salary = rs.getDouble("salary");

                    System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
                }
                rs.close();
            } else {
                int updateCount = dynamicStatement.getUpdateCount();
                System.out.println("Updated " + updateCount + " row(s) using execute");
            }
            dynamicStatement.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.out.println("Disconnected from the database.");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Explanation of the Example

1. Using executeQuery:

  • PreparedStatement selectStatement = connection.prepareStatement(selectSQL); creates a PreparedStatement object for the SELECT query.
  • ResultSet resultSet = selectStatement.executeQuery(); executes the SELECT query and returns a ResultSet object containing the data.

2. Using executeUpdate

  • PreparedStatement insertStatement = connection.prepareStatement(insertSQL); creates a PreparedStatement object for the INSERT query.
  • int rowsInserted = insertStatement.executeUpdate(); executes the INSERT query and returns the number of rows inserted.

3. Using execute

  • PreparedStatement dynamicStatement = connection.prepareStatement(dynamicSQL); creates a PreparedStatement object for the dynamic SQL query.
  • boolean isResultSet = dynamicStatement.execute(); executes the SQL query and returns a boolean indicating whether the result is a ResultSet object.
  • If isResultSet is true, the result is processed as a ResultSet.
  • If isResultSet is false, the result is processed as an update count.

Homepage

Readmore