JDBC Statement Objects

The purpose of the Statement object is to send SQL Statements to a database. The string containing the text of the SQL request is supplied as the argument to one of the execute methods of the Statement object.

• Statement objects
  You now know how to connect to a database and how to get information about that database. A Statement object allows you to communicate with a database. You send commands to the database using this mechanism. These SQL Statements are interpreted and executed at the database. Here is a sample of how to create a Statement:

Statement stmt = con.createStatement();

This should look familiar from the first Example program you created. In our Example program, you used the executeQuery() method. The object returned from that call was one of the most important types in JDBC. It was a ResultSet. A ResultSet is an object that represents a subset of data chosen from the target database.It can be thought of as a temporary table that holds the database rows that were selected based on the SQL criteria supplied to the database. The results that Statement methods return vary, depending on the method. Not every Statement method returns a ResultSet. The execute series are the most often used of Statement's methods and are described in the following table.

executeQuery()	Used to execute SQL Statements that return a single ResultSet
executeUpdate()	Used to execute SQL Statements that modify a table or values of columns in a table and return the number of rows affected (the value may be zero in the case of certain SQL Statements that return nothing)
execute()	Used to execute any type of SQL Statement, but is intended for those that can return multiple results or values; returns a boolean value indicating the success or failure of the request

To allow the most flexibility to work with various databases and data sources, JDBC places no restriction on the kinds of SQL expressions a Statement can send. A driver is not required to check the syntax of the SQL phrase sent to it. In fact, if the database can understand it (and this is a programmer responsibility), the Statements do not have to be SQL. However, a driver that claims to be JDBC-compliant must support at least ANSI SQL-92 Entry Level capabilities.
A Statement should automatically be closed when the connection is garbage collected, but you should close it yourself as soon as it is no longer needed. The JDBC recommendation is to always close the Statement explicitly. This releases the resources used on the database and allows them to be reused immediately by another application. You saw this in the Example program you used previously. There the line stmt.close() closed the Statement. There you also saw examples of how to close a Result Set (rs.close()) and a connection (con.close()).

The executeUpdate() method is used to request that the database execute SQL commands modify the data stored in its tables (such as INSERT, UPDATE, DELETE Statements) as well as SQL commands that modify the tables themselves (such as CREATE TABLE, DROP TABLE, and ALTER TABLE). Use of the term "Update" is a bit of a misnomer and reflects more that a change of some sort is requested. The executeUpdate() returns an int containing the number of rows affected for INSERT, UPDATE, or DELETE Statements, or zero for certain types of SQL expressions that do not return any value.
An example will help illustrate this. View the code below to see a snippet of code that illustrates the use of an executeUpdate() method (where "con" is a working connection).

Statement stmt = con.createStatement();
int updates = stmt.executeUpdate("INSERT INTO table1 (column1, column2, column3, column4) 
VALUES(99999, 'Updated Info', 'More info', 9.0)");

The snippet adds a record to the database table TABLE1. The SQL expression includes the appropriate values for the columns of a row in this table. The return value is not a ResultSet, however. It is a count of the records that were changed or modified. In this case, you would expect this update to return the value "1." In the next lesson, you will learn about the ResultSet features.

Click the Exercise link below to check your ability to update an existing database.
JDBC Statement- Exercise