Executing SQL Queries

The most commonly used SQL statement is the SELECT statement. We can execute SELECT statements using either a Statement or a PreparedStatement object. Both Statement and PreparedStatement have a method, executeQuery(), that executes an SQL SELECT command and returns a ResultSet containing the results of the query in the form of a table. Consider this query using Statement:

 Statement stmt = new conn.getStatement(); ResultSet rs = stmt.executeQuery(    "SELECT ENAME, JOB, SAL FROM EMP"); 

The ResultSet that this example returns contains three columns, ENAME, JOB, and SAL, as specified in the select list. Assuming the database hasn't been altered since Oracle was installed, it will contain 14 rows.

Navigating a ResultSet

We access the ResultSet one row at a time, starting at the first row and advancing one row forward at a time. (By default, this is the only way to move through a ResultSet we'll see other options in Chapter 9, "Advanced JDBC Features.") Associated with the ResultSet is a cursor, a kind of pointer, which is initially set to point before the first row. To advance it to the first row and every row after that, we need to call the ResultSet's next() method. The next() method returns a Boolean value: true if it was successful in advancing to the next row, false if there are no more rows. This is the usual way to iterate through a ResultSet:

 while(rs.next()) {   // Process current row, column by column } 

Once we have positioned the cursor at a valid row by calling the next() method, we use the ResultSet's getXXX methods to access the columns in the row. Each of the row's columns can be addressed in one of two ways: by the column's name or by its index, starting at 1. In general, it is most efficient to retrieve data from columns by index, in order. Like the set methods for PreparedStatement, ResultSet has many get methods. (Actually, there are about twice as many get methods because of the two ways of addressing a column.) The most commonly used are those for basic Java types and classes, such as getInt(), getLong(), getFloat(), getDouble(), getString(), getDate(), and getTime(). For each column, we need to choose the most appropriate type.

Our sample SQL statement returned three columns, which we know hold an employee's name, job, and salary. The most appropriate method for retrieving name and job is pretty obviously getString(), because they're both variable-length character fields.

It's not as obvious which get method we should use for salary. Most of the numeric types will work, but getLong() is the best and safest choice, because we know the values are integer values, and we don't want to limit anyone's earning potential unduly.

graphics/note_icon.gif

Another option for retrieving a number from a ResultSet, if we intend to display only a number and don't need to do anything with the value, is to use getString(). In general, JDBC is fairly forgiving and will perform any necessary conversions, if possible.

Here is a more complete query example:

 Statement stmt = new conn.getStatement(); ResultSet rs = stmt.executeQuery(    "SELECT ENAME, JOB, SAL FROM EMP"); String name=null, job=null; long salary=0L; while(rs.next()) {   name = rs.getString(1);   job = rs.getString(2);   salary = rs.getLong(3);   System.out.println("Name: " + name +                      ", Job: " + job +                      ", Salary:" + salary); } 

In some cases, we may want to let the database perform formatting for us most notably, for dates and times and we need to accept a string in those cases, rather than an underlying numerical value. For example, we can obtain year as an apostrophe followed by two digits like this:

 rs = stmt.executeQuery(    "SELECT TO_CHAR(SYSDATE, '''YY') FROM DUAL"); System.out.println("Year: " + rs.getString( 

Which, in 2003, prints out

 Year: '03 

In many cases, we have a choice about using Oracle or Java to perform formatting. As in this case, it is often easier to use Oracle. Java, on the other hand, is more flexible and has greater support for internationalization, such as for culturally dependent date and time formats. The important thing is to be consistent you'll want to choose one or the other, based on the needs of your application.

Using Column Names

In the examples we have seen so far, we have retrieved values for each column by using the column's index. We can also use each column's name that is necessary if we have a query such as this:

 ResultSet rs = stmt.executeQuery("SELECT * FROM EMP"); 

(We shouldn't really write queries like this, because it could return unexpected results, should the table change in the future, and because it could return information we don't need, which is wasteful and inefficient). We don't necessarily know in what order the columns are returned. Even if we did, we shouldn't depend on that order, because it could change. Consequently, we need to get the values by using the names of the columns. We can get the name from ENAME, the job from JOB, and the salary from SAL, like this:

 String name=null, job=null; long salary=0L; while(rs.next()) {    name = rs.getString("ENAME");    job = rs.getString("JOB");    salary = rs.getLong("SAL");    System.out.println("Name: " + name +                       ", Job: " + job +                       ", Salary:" + salary); } 

Using PreparedStatement for Queries

The previous examples used Statement for executing queries, but a PreparedStatement can be used, too, and has the same benefits and drawbacks it has for other types of SQL statements that we saw above. If we will be executing an SQL statement repeatedly with different values, it may be preferable to use a PreparedStatement so that it is parsed only once by the database. The bind variables in a query are most commonly used in a WHERE clause. Here is a query for an employee selected by employee number:

 PreparedStatement ps = conn.prepareStatement(    "SELECT ENAME, JOB, SAL FROM EMP WHERE EMPNO=?"); ps.setInt(1, 7900); String name=null, job=null; long salary=0L; ResultSet rs = ps.executeQuery(); while(rs.next()) {    name = rs.getString(1);    job = rs.getString(2);    salary = rs.getLong(3);    System.out.println("Name: " + name +                       ", Job: " + job +                       ", Salary:" + salary); } 

Bind variables are not limited to WHERE clauses; they can be used anywhere in an SQL statement that a text or numeric constant can be used, as well, such as in a select list expression. This is useful if we want to supply a value for an SQL function, for example. Suppose we want to list each employee's year of hire; we can supply the format string using a bind variable, in case we want to be able to change it based on user input.

 PreparedStatement ps = conn.prepareStatement(    "SELECT ENAME, TO_CHAR(HIREDATE,?) FROM EMP"); ps.setString(1,"YYYY"); String name=null, hireyear=null; ResultSet rs = ps.executeQuery(); while(rs.next()) {    name = rs.getString(1);    hireyear = rs.getString(2);    System.out.println("Name: " + name +                       ", Year: " + hireyear); } 

ResultSetMetaData

ResultSetMetaData is a class that we can use, as the name indicates, to obtain information about a ResultSet itself. This information includes number, names, and properties of the columns in the ResultSet, and the name of the table and schema of each column.

We obtain the ResultSetMetaData from a ResultSet, using the getMetaData() method. Assuming that we have obtained a PreparedStatement object, ps, we can obtain a ResultSet and its associated ResultSetMetaData like this:

 ResultSet rs = ps.executeQuery(); ResultSetMetaData rmd = rs.getMetaData(); 

Since the columns in this query all belong to the single table we can get the table name from any of the columns.

 System.out.println("Tablename: " + rmd.getTableName(1)); 

We can get the column count and iterate through the columns to obtain information about them.

 int count = rmd.getColumnCount(); for(int i=1; i<=count; i++) {   System.out.println("Name: " + rmd.getColumnLabel(i)  +                      "  Type: " + rmd.getColumnTypeName(i)); } 

ResultSetMetaData is useful for getting table and column information when displaying database information in a table format, and is preferable to using hard-coded headings, which require greater diligence to make sure they remain correct when the database or queries are changed.



Java Oracle Database Development
Java Oracle Database Development
ISBN: 0130462187
EAN: 2147483647
Year: 2002
Pages: 71

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net