The executeQuery(String sql) method

In this section, we will try to execute the same statements (as a query) that we used when demonstrating the execute() method in The execute(String sql) method section. We'll start with the INSERT statement, as follows:

String sql = "insert into person (first_name, last_name, dob) " +
                         "values ('Bill', 'Grey', '1980-01-27')";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    st.executeQuery(sql);                  //PSQLException
} catch (SQLException ex) {
    ex.printStackTrace();                  //prints: stack trace 
}
System.out.println(selectAllFirstNames()); //prints: Bill

The preceding code generates an exception with the No results were returned by the query message because the executeQuery() method expects to execute the SELECT statement. Nevertheless, the selectAllFirstNames() method proves that the expected record was inserted. 

Now let's execute the SELECT statement, as follows:

String sql = "select first_name from person";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    ResultSet rs1 = st.executeQuery(sql);
    System.out.println(rs1 == null);          //prints: false
    ResultSet rs2 = st.getResultSet();
    System.out.println(rs2 == null);          //prints: false
    System.out.println(st.getUpdateCount());  //prints: -1
    while (rs1.next()) {
        System.out.println(rs1.getString(1)); //prints: Bill
    }
    while (rs2.next()) {
        System.out.println(rs2.getString(1)); //prints:
    }
} catch (SQLException ex) {
    ex.printStackTrace();
}

The preceding code selects all the first names from the person table. The returned false value indicates that executeQuery() always returns the ResultSet object, even when no record exists in the person table. As you can see, there appear to be two ways of getting a result from the executed statement. However, the rs2 object has no data, so, while using the executeQuery() method, make sure that you get the data from the ResultSet object.

Now let's try to execute an UPDATE statement as follows:

String sql = "update person set first_name = 'Adam'";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    st.executeQuery(sql);                  //PSQLException
} catch (SQLException ex) {
    ex.printStackTrace();                  //prints: stack trace
}
System.out.println(selectAllFirstNames()); //prints: Adam

The preceding code generates an exception with the No results were returned by the query message because the executeQuery() method expects to execute the SELECT statement. Nevertheless, the selectAllFirstNames() method proves that the expected change was made to the record. 

We are going to get the same exception while executing the DELETE statement:

String sql = "delete from person";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    st.executeQuery(sql);                  //PSQLException
} catch (SQLException ex) {
    ex.printStackTrace();                  //prints: stack trace
}
System.out.println(selectAllFirstNames()); //prints: 

Nevertheless, the selectAllFirstNames() method proves that all the records of the person table were deleted.

Our demonstration shows that executeQuery() should be used for SELECT statements only. The advantage of the executeQuery() method is that, when used for SELECT statements, it returns a not-null ResultSet object even when there is no data selected, which simplifies the code since there is no need to check the returned value for null.