10.3 Executing SQL

SQL (structured query language) is the language used to retrieve data from a relational database. In the JDBC API the Statement object is used to send SQL statements to a database. The createStatement() method on the Connection class will create a valid Statement object for the connection.

Following on from the previous example:

Statement dbStatement = dbConnection.createStatement();

int updareRowCount = dbStatement.executeUpdate("UPDATE EMPLOYEE SET NAME='Jonathan' WHERE EMPLOYEE_ID = 12345");

The code above creates a Statement object and then executes an UPDATE SQL command. The executeUpdate() method can be used to execute INSERT, DELETE and UPDATE SQL commands. It returns the number of database rows affected by the SQL command as an int.

A Statement object can also be used to execute SELECT SQL statements. For example:

ResultSet result = dbStatement.executeQuery("SELECT * FROM EMPLOYEE");

The results of the SQL SELECT are returned as a ResultSet object. The ResultSet class provides various methods for navigating the database rows returned by a query and methods for extracting the column values in a row. The code below displays the EMPLOYEE_ID and NAME columns for each of the rows returned from the query above.

while ( result.next() )
 {
  System.out.println( result.getString("EMPLOYEE_ID") + " - " + result.getString("NAME") );
 }

result.close();

The next() method moves the current row pointer of the result set to the next row. When the result set is first returned the current row pointer is pointing to a non-existing row before the first row. This means that you need to call the next() method before you can access the first row in a newly created ResultSet.

The getString() method will return the value of the specified column as a String. The ResultSet class provides methods for extracting columns as all the various data types.

ResultSet and Statement objects consume database resources. To make the most efficient use of the database resources you should free them as soon as your are finished with them. The close() method on the ResultSet and Statement classes, frees the database resources consumed by the object. The database resources will also be freed when the objects are garbage collected.