Sending a Query via JDBC
In this phrase, we create a JDBC statement using the Connection object's createStatement() method and use it to execute a query that returns a Java ResultSet. To create a connection, see the previous phrase, "Connecting to a Database via JDBC." When performing a SELECT query, we use the executeQuery() method of the Statement object. If we wanted to perform an UPDATE operation rather than a SELECT query, we would use the executeUpdate() method of the Statement object, instead of the executeQuery() method. The executeUpdate() method is used with SQL INSERT, UPDATE, and DELETE statements. The executeUpdate() method returns either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing. Here is an example of how we would execute an UPDATE statement: Statement stmt = conn.createStatement( ); int result = stmt.executeUpdate( "UPDATE users SET name='tim' where id='1234'"); It is important to remember that only one ResultSet object per Statement object can be open at the same time. All the execution methods in the Statement interface will close the current ResultSet object if there is an open one. This is important to remember if you are nesting database connections and queries. JDBC 3.0 introduced a feature called result set holdability. Holdability allows you to keep more than one result set open if you specify this option when the statement object is created. To learn more about the new features that were provided by JDBC 3.0, I'd recommend reading this article available on IBM's DeveloperWorks site: http://www-128.ibm.com/developerworks/java/library/j-jdbcnew/ When working with statements and results, it is important to always close the Connection, the Statement, and the ResultSet objects when finished with them. Each of the Connection, the Statement, and the ResultSet objects have a close() method used for performing a close operation to free up memory and release resources. Not closing these objects is a frequent cause of memory leaks in Java applications. Not closing a connection can also cause deadlock scenarios in multithreaded applications. If you have a SQL statement that will be executed many times, it is more efficient to use a PreparedStatement query. See the next phrase "Using a Prepared Statement." |
