Executing SQL commands with executeUpdate() or through PrepareStatement objects v22.214.171.124
In the previous example,
ListEmployees executed a
SELECT statement using the
executeQuery() was designed to execute query statements so it returns a
ResultSet that contains the data returned by the query. The
Statement class offers a second method that you use to execute other types of commands (
DELETE, and so forth). Instead of returning a collection of rows, the
executeUpdate() method returns the number of rows affected by the SQL command it executes.
The signature of the
executeUpdate() method is:
Provide this method a single parameter of type
String containing the SQL command that you want to execute.
The example that follows shows using the
executeUpdate() method to add a row to the
The following example isn't a complete application, only a method. These code samples don't include the code required to set up and tear down a
Connection. To experiment with the example, you must provide a class that invokes the sample code.
updateEmployee() method expects a single argument from the caller, a
Connection object that must be connected to an EDB Postgres Advanced Server database:
executeUpdate() method returns the number of rows affected by the SQL statement (an
INSERT typically affects one row, but an
DELETE statement can affect more).
executeUpdate() returns without an error, the call to
System.out.println displays a message to the user that shows the number of rows affected.
The catch block displays an appropriate error message to the user if the program encounters an exception:
You can use
executeUpdate() with any SQL command that doesn't return a result set. However, you probably want to use
PrepareStatements when the queries can be parameterized.
Many applications execute the same SQL statement over and over again, changing one or more of the data values in the statement between each iteration. If you use a
Statement object to repeatedly execute a SQL statement, the server must parse, plan, and optimize the statement every time. JDBC offers another
Statement derivative, the
PreparedStatement, to reduce the amount of work required in such a scenario.
The following shows invoking a
PreparedStatement that accepts an employee ID and employee name and inserts that employee information in the
Instead of hard coding data values in the SQL statement, you insert placeholders to represent the values to change with each iteration. The example shows an
INSERT statement that includes two placeholders (each represented by a question mark):
With the parameterized SQL statement in hand, the
AddEmployee() method can ask the
Connection object to prepare that statement and return a
At this point, the
PreparedStatement has parsed and planned the
INSERT statement, but it doesn't know the values to add to the table. Before executing
PreparedStatement, you must supply a value for each placeholder by calling a
setObject() expects two arguments:
- A parameter number. Parameter number one corresponds to the first question mark, parameter number two corresponds to the second question mark, etc.
- The value to substitute for the placeholder.
AddEmployee() method prompts the user for an employee ID and name and calls
setObject() with the values supplied by the user:
It then asks the
PreparedStatement object to execute the statement:
If the SQL statement executes as expected,
AddEmployee() displays a message that confirms the execution. If the server encounters an exception, the error handling code displays an error message.
Some simple syntax examples using
PreparedStatement sending SQL commands follow:
To use the
UPDATE command to update a row:
To use the
DROP TABLE command to delete a table from a database:
To use the
CREATE TABLE command to add a new table to a database:
To use the
ALTER TABLE command to change the attributes of a table: