The Statement Interface

The Statement Interface

The Statement interface is the base interface in the JDBC API. A Statement interface is implemented by the JDBC driver to provide the ability to execute SQL statements from Java applications using the JDBC API and the JDBC driver. A Statement object is created from a database connection obtained either by loading and initializing the JDBC driver, or from the connection pool configured in the WebLogic Server.

Yesterday you saw all the ways a WebLogic Server configures connection pools for databases using the JDBC driver. You can create and close any number of Statement objects in your JDBC application.

A programmer uses the Statement object as a handle to interact with the database. The SQL statement types that the Statement object can execute with the database can be categorized as inserts, updates, deletes, and queries. The JDBC API provides methods in the Statement interface that enable programmers to carry out these actions with the database.

You should use the Statement object to execute SQL statements that do not accept parameters. Consequently, the use of the Statement object is restricted to simple SQL statements. For more complex SQL statements, the JDBC API provides two additional interfaces: the PreparedStatement interface and the CallableStatement interface. You will study these interfaces next.

Before you look into the important methods of the Statement interface, study this code snippet for creating a Statement object:

 Statement myStmt = myConnection.createStatement();  

This code snippet shows how a Statement object is created using the java.sql.Connection object. If a Statement object cannot be created for some reason, the createStatement() method throws an SQL exception.

Take a look at some of the important methods in the Statement interface.

The execute() Method

The execute() method is the easiest method to execute SQL statements in Java applications that use the Statement interface. The execute() method takes the SQL statement as a String parameter and returns a Boolean value (true/false) indicating whether or not the SQL statement was successfully executed. The execute() method can be used to execute SQL statements that perform inserts, updates, deletes, and queries. When a query SQL statement is executed, a result set (a tabular structure of retrieved data) is returned in the Statement object. The result set object can be retrieved from the Statement object using the getResultSet() method of the Statement interface. A code snippet follows:

 boolean retVal = myStmt.execute(""SELECT BOOK_NAME FROM book_tbl");  

The executeQuery() Method

The executeQuery() method of the Statement interface can be used only to execute SQL query statements. The executeQuery() method accepts the SQL query statement as a String and returns the retrieved data wrapped in a ResultSet object. A code snippet is provided here:

 ResultSet myRSet = myStmt.executeQuery(""SELECT BOOK_NAME FROM book_tbl");  

The executeUpdate() Method

To perform database operations like insert, update, and delete, the Statement interface contains the executeUpdate() method. The executeUpdate() method takes the SQL statement as a String parameter and returns an integer value. The returned value can be the number of rows inserted, updated, deleted, or none, depending on the SQL statement executed. The SQL statement can be an INSERT, UPDATE, or DELETE. A code snippet follows:

 int retVal =  myStmt.executeUpdate("INSERT INTO book_tbl VALUES(1234, \""book title"", 39.99""); 

Executing the above SQL statement causes a row containing the passed data to be inserted into the book_tbl table. The retVal will contain a value of 1 to indicate the number of rows inserted.

The getResultSet() Method

The getResultSet() method is used only if an SQL query statement has been executed using the execute() method of the Statement object. Because the execute() method returns a Boolean value, the ResultSet object of the retrieved data can be accessed using the getResultSet() method.

 Boolean retVal = myStmt.execute("SELECT BOOK_NAME FROM book_tbl"");  ResultSet myRSet = myStmt.getResultSet(); 

The close() Method

The close() method closes the reference of the Statement object and frees up resources for the JDBC driver. You must always call the close() method after you have finished using the Statement object. To close the Statement object, use the following code:

 myStmt.close();