Now we are at the point where we can start issuing SQL commands to our database and getting back results. We do this through a Statement object that we get from the connection object described in the previous section. Table 24-4 shows several methods in Connection.
Table 24-4. Some methods of java.sql.Connection
|
Method |
Purpose |
|---|---|
|
|
Returns a statement object that is used to send SQL to the database. |
|
|
Returns an object that can be used for sending parameterized SQL statements. |
|
|
Returns an object that can be used for calling stored procedures. |
|
|
Gets an object that supplies database configuration information. |
|
|
Reports whether the database is currently open or not. |
|
|
Restores/removes read-only mode, allowing certain database optimizations. |
|
|
Makes all changes permanent since the previous commit/rollback. |
|
|
Undoes and discards all changes done since the previous commit/rollback. |
|
|
Restores/removes auto-commit mode, which does an automatic commit after each statement. |
|
|
Closes the connection and releases the JDBC resources for it. |
You will invoke these methods on the java.sql.Connection object that you get back from the JDBC driver manager, as shown in an upcoming example. You use a connection to create a Statement object. The statement object has methods that let you send SQL to the database. Thankfully, statements are blissfully simple. You send SQL queries as Strings. In other words, the JDBC designers did not try to force-fit object-oriented programming onto SQL, perhaps by creating a Select class. Here's how you send a select query to the database:
Statement myStmt = connection.createStatement();
ResultSet myResult;
myResult= myStmt.executeQuery( "SELECT * FROM Person;" );
The executeQuery() method takes a string as an argument. The string contains the SQL statement that you want to execute. In the code fragment show previously, the SQL asks for all data to be returned from the Person table. There is an object that holds your result set. Here, we've called it myResult and it belongs to the ResultSet class. We'll talk more about ResultSet in a minute. Once you have a Statement object, you call one of its methods, shown in Table 24-5, to send SQL to the database. Statement has more methods than these, but these are the ones you'll use most.
Table 24-5. java.sql.statement methods to execute SQL
|
SQL statement |
JDBC statement to use |
Type of its return value |
Comment |
|---|---|---|---|
|
SELECT |
|
|
The return value will hold the data extracted from the database. |
|
INSERT, UPDATE, DELETE, CREATE, DROP |
|
|
The return value will give the count of the number of rows changed (for insert, update, or delete statements), or zero otherwise. |
|
Stored procedure with multiple results |
|
|
The return value is true if the first result is a ResultSet, false otherwise. You get the actual results by calling another method of the statement class. |
Standard SQL has an optional ";" at the end of each SQL statement. You can leave it off. It is omitted in all the tutorials at Javasoft.
The different SQL statements have different return values. Some of them have no return value, some of them return the number of rows they affected, and the select statement returns all the data it pulled out of the database. To cope with these different possible results, you need to call a different method depending on what kind of SQL statement you are executing. The most interesting case is the select statement that gets back an entire result set of data. The next section, esult Sets, describes how this data is conveyed to your Java program.
Almost every JDBC interaction with a database can throw an exception, and you need to handle it appropriately in your code. JDBC defines four exceptions at present: SQLException (the most common), SQLWarning, BatchUpdateException, and DataTruncation. It is very important to write each handler so it outputs meaningful error messages for every exception it gets. If you don't pay attention to this, you will find it much harder to debug database problems and error situations.
Older databases sometimes have support for asynchronous SQL operations, meaning that you can start another SQL statement before you get the results back from the past one. Java doesn't need to use this kind of SQL because you can get the same effect by issuing the statements in separate Java threads. Your JDBC programs will be more portable if you use Java threads (supported everywhere) and not asynchronous SQL (may or may not be supported).
When you write multithreaded Java code that uses JDBC, you must synchronize all your accesses to all shared data. Shared data means any data that is accessed in more than one thread and also written by at least one of the threads. If you do not properly synchronize data access, the data can be updated or read inconsistently (with a value partly from one thread and partly from another). That leaves your code with hard-to-debug data races and data corruption problems.