Basic JDBC Syntax

Basic JDBC Usage

Example 12-2 used a Connection object, representing a physical connection to the database. Now you can use this Connection object to create Statement objects. Statement objects are JDBC's way of getting SQL statements to the database.

There are three main types of Statement objects: the base class Statement, the PreparedStatement, and the CallableStatement.

To create a Statement object, use the createStatement method as shown in Example 12-3:

Statement s = c.createStatement();

Example 12-3 creates a Statement object named s, from the Connection object c. You can now use this Statement object to execute queries and updates on the database.

There are two main methods in the Statement class that are important. The first is executeQuery. This method takes one argument, the SQL statement to be executed, and returns an object of type ResultSet, which is discussed later. This method is used for executing queries which will return a set of data back, for instance, a SELECT statement. The ResultSet object returned represents the data resulting from the query.

Example 12-4 retrieves some data from the booktown database:

Statement s = null;
try {
  s = c.createStatement();
} catch (SQLException se) {
  System.out.println("We got an exception while creating a statement:" +
                     "that probably means we're no longer connected.");
  se.printStackTrace();
  System.exit(1);
}
ResultSet rs = null;
try {
  rs = s.executeQuery("SELECT * FROM books");
} catch (SQLException se) {
  System.out.println("We got an exception while executing our query:" +
                     "that probably means our SQL is invalid");
  se.printStackTrace();
  System.exit(1);
}

int index = 0;

try {
  while (rs.next()) {
      System.out.println("Here's the result of row " + index++ + ":");
      System.out.println(rs.getString(1));
  }
} catch (SQLException se) {
  System.out.println("We got an exception while getting a result:this " +
                     "shouldn't happen: we've done something really bad.");
  se.printStackTrace();
  System.exit(1);
}

Example 12-4 creates a Statement object, and then uses that Statement object's executeQuery method to execute the query SELECT * FROM books. You get back a ResultSet, and use that ResultSet to print out some of the information you got back.

The other important method is executeUpdate. This method, again, takes one argument, which is the SQL statement to be executed. This statement, unlike the last one, is a statement which changes data in the database: for example, a CREATE, an INSERT, or an UPDATE statement. The difference is that this returns an int, and this corresponds to the number of records that were modified.

Example 12-5 uses the executeUpdate method to insert a new row into the books table.

Statement s = null;
try {
  s = c.createStatement();
} catch (SQLException se) {
  System.out.println("We got an exception while creating a statement:" +
                     "that probably means we're no longer connected.");
  se.printStackTrace();
  System.exit(1);
}

int m = 0;

try {
  m = s.executeUpdate("INSERT INTO books VALUES " + 
                      "(41472, 'Practical PostgreSQL', 1212, 4)");
} catch (SQLException se) {
  System.out.println("We got an exception while executing our query:" +
                     "that probably means our SQL is invalid");
  se.printStackTrace();
  System.exit(1);
}

System.out.println("Successfully modified " + m + " rows.\n");

The ResultSet object is our primary interface for fetching information from the database. It has two main features. It can step through the set of rows returned, and it can return values for a specific column in that row. It works in a similar fashion to a standard Java Enumeration: it starts before the first element, and you use the next method to step through the rest of the elements.

next returns true if the ResultSet was able to step to the next results; that is to say, there are results to be read. The while loop in Example 12-4 will print out the first column of each of the rows returned. If no rows were returned, next will return false initially, representing this fact, and therefore nothing will be printed.

ResultSet can return values of all sorts of different types; Example 12-4 treats the first column as if it were a String. Fortunately, all standard SQL data types can be represented as String, so regardless of the type of the first column, you will be able to fetch the value of the first column and print it out. There are many other methods available on ResultSet, including methods for fetching all the various SQL data types and converting them to native Java types. Consult the API documentation on ResultSet for more information.

Using Advanced JDBC Features

As mentioned earlier, besides the basic Statement object, there are two additional types of statements available in JDBC: PreparedStatements and CallableStatments. These two types are described in this section.

In addition to these statements, this section also describes the use of the ResultSetMetaData and DatabaseMetaData objects. You can use these last two objects to interrogate JDBC for information about a given set of query results, or for information about your database. The ability to get such information at run-time enables you to dynamically execute any SQL statement, even one that is unknown when you write your program.

PreparedStatement ps = null;

try {
  ps = c.prepareStatement("INSERT INTO authors VALUES (?, ?, ?)");
  ps.setInt(1, 495);
  ps.setString(2, "Light-Williams");
  ps.setString(3, "Corwin");
} catch (SQLException se) {
  System.out.println("We got an exception while preparing a statement:" +
                     "Probably bad SQL.");
  se.printStackTrace();
  System.exit(1);
}

try {
  ps.executeUpdate();
} catch (SQLException se) {
  System.out.println("We got an exception while executing an update:" + 
                     "possibly bad SQL, or check the connection.");
  se.printStackTrace();
  System.exit(1);
}

ResultSetMetaData

You can interrogate JDBC for detailed information about a query's result set using a ResultSetMetaData object. ResultSetMetaData is a class that is used to find information about the ResultSet returned from a executeQuery call. It contains information about the number of columns, the types of data they contain, the names of the columns, and so on.

Two of the most common methods in the ResultSetMetaData are getColumnName and getColumnTypeName. These retrieve the name of a column, and the name of its associated data type, respectively, each in the form of a String.

The getColumnType method is not the same as the getColumnTypeName. getColumnType returns an int corresponding to a data type's internal JDBC identification code, whereas getColumnTypeName returns the name as a String.

Example 12-7 is an example of using the ResultSetMetaData to get the name and data type of the first column in a ResultSet called rs. This code could logically followed the acquisition of the ResultSet named rs in Example 12-4.

Example 12-7. JDBC ResultSetMetaData

ResultSetMetaData rsmd = null;
try {
  rsmd = rs.getMetaData();
} catch (SQLException se) {
  System.out.println("We got an exception while getting the metadata:" +
                     "check the connection.");
  se.printStackTrace();
  System.exit(1);
}

String columnName = null,
       columnType = null;
try {
  columnName = rsmd.getColumnName(1);
  columnType = rsmd.getColumnTypeName(1);
} catch (SQLException se) {
  System.out.println("We got an exception while getting the column name:" +
                     "check the connection.");
  se.printStackTrace();
  System.exit(1);
}

System.out.print("The name of the first column is: '");
System.out.print(columnName);
System.out.println("'");
System.out.print("The data type of the first column is: ");
System.out.println(columnType);

There are many other such methods in the ResultSetMetaData class, all of which are well documented in the JDK API documentation.

DatabaseMetaData dbmd = null;

try {
    dbmd = c.getMetaData();
} catch (SQLException se) {
    System.out.println("We got an exception while getting the metadata:" +
                       " check the connection.");
    se.printStackTrace();
    System.exit(1);
}

String username = null;
try {
    username = dbmd.getUserName();
} catch (SQLException se) {
  System.out.println("We got an exception while getting the username:" +
                     "check the connection.");
  se.printStackTrace();
  System.exit(1);
}

String url = null;
try {
  url = dbmd.getURL();
} catch (SQLException se) {
  System.out.println("We got an exception while getting the username:" +
                     "check the connection.");
  se.printStackTrace();
  System.exit(1);
}

System.out.println("You are connected to '" + url +
                   "' with user name '" + username + "'");