The example uses a java.io.BufferedReader to read values from the console, then loops while the variable notDone is true. This variable is set to false when the user types “exit” at the prompt. Assuming the user types a valid SELECT statement, we call executeQuery() because we expect a result set back.

---

NOTE:  The Statement method executeQuery() always returns a result set, even if there are no values returned. In that case, ResultSet would have no rows, but it would contain metadata related to the query.

---

To find out what columns are returned, first access ResultSetMetaData, then use that to find the column count. Now iterate through the columns and print each column name. After that, iterate through each row of data, printing the values. As an added example of using metadata, check the data source column type and print a $ if the type of the column is CURRENCY, a Microsoft Access–specific data type. You could also have used the ResultSetMetaData method isCurrency(). This would have been more database independent. getColumnType was used for example purposes only, as shown in Figure 3.12. If the name of a column, but not its index, is known, the method findColumn() can be used to find the column number.

import java.sql.*;
import java.io.*;
public class ResultSetMetaDataExample
{
    public static void main (String args[])
    {
        try
        {
            Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
        } catch (Exception e)
        {
            System.out.println(“JDBC–ODBC driver failed to load.”);
            return;
        }
  
        try
        {  
            Connection con =
              DriverManager.getConnection(“jdbc:odbc:Inventory”,
                                          “”,“”);
          
            Statement stmt = con.createStatement();
  
            boolean notDone = true;
            String sqlStr = null;
            /* In this example we use a buffered reader to read in an
               SQL statement from the command line. This allows the
               user to type in any arbritrary SQL statement.
               Try SELECT * FROM INVENTORY
            */
             BufferedReader br = new BufferedReader(
                new InputStreamReader(System.in));
            // We iterate until the user types “exit”
            while(notDone)
            {
                System.out.print(“Enter SELECT Statement:”);
                sqlStr = br.readLine();
          
                if(sqlStr.startsWith(“SELECT”) ||
                    sqlStr.startsWith(“select”))
                {
                    // If this is a SELECT statment, then process
                    ResultSet rs = stmt.executeQuery(sqlStr);
                    ResultSetMetaData rsmd = rs.getMetaData();
              
                    /* Since we don’t know how many columns will come
                       back from an abritrary query, we need to check
                       the metadata of ResultSet.
                    */
                    int columnCount = rsmd.getColumnCount();

                    // Print all the column names.
                    for(int x =1;x<=columnCount;x++)
                     {
                        String columnName = rsmd.getColumnName(x);
                        System.out.print(columnName +“\t”);
                    }
                    System.out.println(“”);
              
                    // Now print each row of data.
                    while(rs.next())
                    {
                        for(int x =1;x<=columnCount;x++)
                        {
                            if(
                               /*
                                  The metadata returns the data type
                                  so we can adjust our display for  
                                  certain types such as currency.
                               */
                               rsmd.getColumnTypeName(x).
                                 compareTo(“CURRENCY”) == 0
                              )
                            System.out.print(“$”);
                            String resultStr = rs.getString(x);
                            System.out.print(resultStr +“\t”);
                        }
                        System.out.println(“”);
                    }
                }
                else if(sqlStr.startsWith(“exit”))
                    notDone = false;
            }
            stmt.close();
            con.close();
        } catch (Exception e)
        {
            System.out.println(e);
        }
    }
}

As this example shows, there is more to fetching data than just retrieving values. You may also need to query the results themselves for metadata. Use this information to determine the validity of your data and its format.

The next section looks at the process of modifying values and sending data to the data source.

Handling Data

Besides fetching information from a data source, you will want to insert, update, delete, and modify the structure of a data source. All these activities can be accomplished using the executeUpdate() method of a Statement object. This method returns the number of rows affected by the call. For example, the following code creates a table and inserts values into it, updates the values, and then deletes the rows and drops the table.

Figure 3.12  ResultSetMetaDataExample output

....
 Statement stmt = con.createStatement();
stmt.executeUpdate(“CREATE TABLE Temp” +
                   “(id int, name varchar(25), value float)”);
int insCount = stmt.executeUpdate(“INSERT INTO Temp” +
                                  “VALUES (1,”test1“, 5.0)”);
System.out.println(“Inserted ” + insCount + “rows”);
insCount = stmt.executeUpdate(“INSERT INTO Temp ” +
                              “VALUES (2,”test2“, 10.0)”);
System.out.println(“Inserted ” + insCount + “rows”);
int updateCount = stmt.executeUpdate(“UPDATE Temp SET value=1.0”);
System.out.println(“Updated ” + insCount + “rows”);
int deleteCount = stmt.executeUpdate(“DELETE FROM Temp”);
System.out.println(“Deleted ” + insCount + “rows”);
Stmt.executeUpdate(“DROP TABLE Temp”);
Stmt.close();
...