Data Manipulation
After a connection is made to the data source, the JDBC API is furnished with comprehensive operations. Both DDL (Data Definition Language) and DML (Data Manipulation Language) operations are available. Metadata (information about the database itself) or the result set can also be queried.
Note
We assume that the reader is familiar with SQL fundamentals. For more coverage of SQL, we recommend you to refer to the book, Sams Teach Yourself SQL in 10 Minutes (ISBN: 0672321289).
A Statement object represents a SQL statement, and must be created from the Connection object. A Statement sends simple SQL statements to the database:
// Create a Statement object java.sql.Statement stmt = conn.createStatement();
One of the powerful methods of the Statement object is the execute() method. All DDL and DML database operations can be performed using the execute() method.
Caution
All DDL operations, such as creating and dropping objects, can be performed by the execute() method of the Statement object. However, creating a database instance is DBMS-specific, and is not available to all JDBC drivers.
// Using the execute method for some DDL operations
try {
stmt.execute("DROP TABLE Student ");
} catch (SQLException e) {
System.out.println("Table Student already exists.");
}
stmt.execute("CREATE TABLE Student
(id integer, fname varchar(15), lname varchar(15), ssn varchar(12))");
System.out.println("Table Student is created...");
// Using the execute method for some DML operations
stmt.executeUpdate
("INSERT into Student values (1, 'Lillian', 'Ghaly' , '111-000-1111')");
stmt.executeUpdate
("INSERT into Student values (2, 'Raj', 'Talla' , '222-000-2222')");
stmt.executeUpdate
("INSERT into Student values (3, 'Tony', 'Hunter' , '333-000-3333')");
stmt.executeUpdate
("INSERT into Student values (4, 'John', 'Smith' , '444-000-4444')");
// close statements when done
stmt.close();
Note
When creating a Statement, resources will be allocated to the application in order to execute the SQL. It is vital to release these resources, by closing the Statement when execution is complete, using the close() method.
The execute() method returns a boolean: true if the next result is a ResultSet object, or false if it is an update count or there are no more results. The following code gets a ResultSet object, which holds the result of the last query:
stmt.execute("SELECT * from DAY09_STUDENTS");
// Obtain the result of the last query
ResultSet rs = stmt.getResultSet();
The ResultSet is initially positioned before the first row. Table 9.4 gives a summary of the ResultSet methods.
| Method | Purpose |
|---|---|
| Boolean next() | Scrolls the cursor to the next available row |
| String getString(int columnIndex) | Returns data at the current cursor, under a particular column number or index |
| String getString(String columnName) | Returns data at the current cursor, under a particular column name |
| Boolean isFirst() | Returns true if the cursor is at the first row |
| Boolean isLast() | Returns true if the cursor is at the last row |
| int getFetchSize() | Returns the default fetched number of rows |
| setFetchSize(int rows) | Set the required number of rows to be fetched |
| ResultSetMetaData getMetaData() | Returns data about the ResultSet, such as number of columns and the properties of data elements |
Now we can scroll through the ResultSet to display the retrieved data using getString() method. From the earlier example, the ResultSet contains four columns and four rows. Columns can be identified by column name or column number. This example uses the column name:
// Display the ResultSet data on the screen using column name
while (rs.next())
System.out.println(rs.getString("student_id") + " ," +
rs.getString("first_name") + " ," +
rs.getString("last_name") + " ," +
rs.getString("address"));
When using the column number, you pass an integer value to getString() that starts with 1, which represents the first column:
// Display the ResultSet data using column number
while (rs.next())
System.out.println(rs.getString(1) + " ," +
rs.getString(2) + " ," +
rs.getString(3) + " ," +
rs.getString(4));
// close the result set after done.
rs.close();
In both cases, the output of the last println should look like this:
1, LILLIAN, GHALY, 15 DIAMOND ST, BOSTON, MA 3, SAM, LE, 12 APPLEBEE RD, LOS ANGELES, CA
Much like the Statement object, the ResultSet object can be tuned to the optimum number of fetched rows. To do this, use the getFetchSize() and setFetchSize() methods of ResultSet. This increases the performance when a large number of rows is retrieved during search operations. Close the ResultSet object when you're done to release allocated resources.