![]() |
|||
![]() ![]() |
![]() |
![]()
|
![]() |
Getting the Number and Label of Columns A row is usually composed of table data that may be organized in different columns of different types. It may be important to discover the properties of the result sets rows, the number of columns, and the type of data in each column. We will see later how to get such information about result sets. Only the column number and column labels will be used now. ResultSet int getMetaData().getColumnCount(); String getMetaData().getColumnLabel(int i); The getMetaData() method returns a ResultSetMetaData object that is explained later because it is quite complex. Calling getColumnCount() on this object returns the expected value. int getMetaData().getColumnCount(); The return type is integer and is the number of columns in the rows composing this result set. String getMetaData().getColumnLabel(int i); The parameter is the column index where a value of 1 indicates the first column. The method obviously returns the label for the column at this index. It may be more efficient to store the ResultSetMetaData object once instead of calling the method to create it each time it is necessary to access a property. The driver may provide caching, but it is often preferable not to abuse such features when not really needed. Listing 5-16 shows how to call these methods. Listing 5-16: Getting the number and label of columns. ... Connection myConnection = DriverManager.getConnection(url, javauser, hotjava); Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(sqlQuery); int maxColumns = rs.getMetaData().getColumnCount(); for (int i = 1; i <= maxColumns; i++) { if (i > 1) System.out.print(, ); System.out.print(rs.getMetaData().getColumnLabel(i)); } System.out.println(====================================); while (rs.next()) { // we got a row } myStatement.close(); myConnection.close(); ... ... Accessing Columns As shown in Figure 5-8, columns must be fetched one by one, in left-to-right order. Fetching may be done in a loop using the column indexes or column names discovered by the ResultSetMetaData object.
The rows contents are accessible via getXXX() methods that allow the extraction of the various values of the columns of rows in the result set. There are two ways of accessing columns: by column index or by column name. Accessing a column by name is more convenient, but less efficient because it internally needs many comparisons of strings before finding the column. Certain SQL statements return tables without column names or with multiple identical column names. It is absolutely necessary to use column numbers in these cases. Figure 5-9 illustrates the access to a rows columns.
All columns within a row must be read in left-to-right order, and each column must only be read once. This rule may not be true with some DBMSs, but it is preferable to observe it to ensure maximum portability. By Column Indexes Below is the list of getXXX() methods available to fetch columns in a row: Result Set String getString(int columnIndex); boolean getBoolean(int columnIndex); byte getByte(int columnIndex); short getShort(int columnIndex); int getInt(int columnIndex); long getLong(int columnIndex); float getFloat(int columnIndex); double getDouble(int columnIndex); java.math.BigDecimal getBigDecimal(int columnIndex, int scale); byte[] getBytes(int columnIndex); java.sql.Date getDate(int columnIndex); java.sql.Time getTime(int columnIndex); java.sql.Timestamp getTimestamp(int columnIndex); java.io.InputStream getAsciiStream(int columnIndex); java.io.InputStream getUnicodeStream(int columnIndex); java.io.InputStream getBinaryStream(int columnIndex); Object getObject(int columnIndex); return_type getXXX(int columnIndex); All these methods return the column value in the current row. Column indexes are integers. This example shows how to execute a SQL statement and retrieve the results using column indexes. Note that the rows are always read from left-to-right and that columns are only read once. Listing 5-17 shows the statement using column indexes. Listing 5-17. Using column indexes. ... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(SELECT name, title, salary FROM employees); while (rs.next()) { // print the columns of the row that was retrieved String empName = rs.getString(1); String empTitle = rs.getString(2); long empSalary = rs.getLong(3); System.out.println(Employee + empName + is + empTitle + and earns $ + empSalary); } ... ... By Column Names Column names may be more convenient to use. Here are the getXXX() methods supporting column names: ResultSet String getString(String columnName); boolean getBoolean(String columnName); byte getByte(String columnName); short getShort(String columnName); int getInt(String columnName); long getLong(String columnName); float getFloat(String columnName); double getDouble(String columnName); java.math.BigDecimal getBigDecimal(String columnName, int scale); byte[] getBytes(String columnName); java.sql.Date getDate(String columnName); java.sql.Time getTime(String columnName); java.sql.Timestamp getTimestamp(String columnName); java.io.InputStream getAsciiStream(String columnName); java.io.InputStream getUnicodeStream(String columnName); java.io.InputStream getBinaryStream(String columnName); Object getObject(String columnName); return_type getXXX(String columnName); The parameter should match exactly with the rows column name that needs to be accessed. The same example using column names is shown in Listing 5-18. Listing 5-18: Using column names. ... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(SELECT name, title, salary FROM employees); while (rs.next()) { // print the columns of the row that was retrieved String empName = rs.getString(name); String empTitle = rs.getString(title); long empSalary = rs.getLong(salary); System.out.println(Employee + empName + is + empTitle + and earns $ + empSalary); } ... ...
|
![]() |
|