![]() |
|||
![]() ![]() |
![]() |
![]()
|
![]() |
Note that it may be convenient to convert all SQL types to string when retrieving data to display it in tabular format. The getString() method accepts any type and will always do the implicit conversion to a Java string. Chapter 6 provides more information on SQL data types and Java types. Receiving BLOBs Retrieving pictures, sounds, and movies from a database is an expected JDBC function. It makes sense in the context of applets delivered via Web pages, but because Java has a fair set of multimedia facilities, it makes sense in stand-alone Java applications as well. As for sending such binary large objects, Java streams are used to retrieve LONGVARBINARY or LONGVARCHAR data. However, the possibility exists to retrieve the data in fixed-size chunks. The limits are imposed by the Statement.getMaxFieldSize() value. Another limitation due to underlying implementation constraints is that each stream must be accessed immediately after the get method. Indeed, they will be closed on successive get calls on the result set. Three separate methods support the retrieval of streams:
These methods were listed in the earlier section describing how to scan the rows columns of a result set. Listing 5-23 shows how to retrieve binary large objects from a database. The fields containing a BLOB are emp_pict and emp_welcome. Listing 5-23: How to retrieve BLOBs. ... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(SELECT name, emp_pict, emp_welcome FROM employees); // we retrieve in 4K chunks byte[] buffer = new byte[4096]; int size; while (rs.next()) { // fetch employees name String empName = rs.getString(name); // fetch employees picture java.io.InputStream strin = rs.getBinaryStream(emp_pict); for (;;) { size = strin.read(buffer); if (size == 0) { break; } // Send the buffer to some output stream output.write(buffer, 0, size); } // fetch employees voicemail welcome message java.io.InputStream strin2 = rs.getBinaryStream(emp_welcome); for (;;) { size = strin2.read(buffer); if (size == 0) { break; } // Send the buffer to some output stream output.write(buffer, 0, size); } } ... ... Adjusting properties Properties that affect result sets may be set or queried through various methods. Always verify that the settings are as independent of the data source as possible. Data truncation on reads: Data truncation may happen when reading data from a database. How it is handled depends on the circumstances. Normally, data truncation results in a warning. However, if the maximum field size is set to a certain value, and if the application attempts to read a field larger than the limit, the data will be silently truncated to the maximum limit. As a reminder, the setMaxFieldSize() and getMaxFieldSize() were explained in the section on sending SQL statements. Listing 5-24 shows data truncation on reads. Statement: void setMaxFieldSize(int max); int getMaxFieldSize(); Listing 5-24: Data truncation on reads. ... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(SELECT name, title, salary FROM employees); myStatement.setMaxFieldSize(128); 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); } ... ... Limiting the number of rows: Under some circumstances, it may not be useful or preferable to retrieve millions of rows of data. One such circumstance is, for example, if the data destination is the users screen. While you should always build and send queries that make sense to be able to exploit their result, it may happen that the number of returned rows is unpredictable. In this case, it is possible to set a limit. Statement: void setMaxRows(int max); int getMaxRows(); void setMaxRows(int max); This code sets the maximum limit to max. In other words, max is the maximum number of rows that a result set may contain. If the limit is exceeded, the excess rows will be silently dropped from the result set. int getMaxRows(); This code returns the current value of the limit. A value of zero means no limit at all. Listing 5-25 shows how to limit the number of rows returned by a query. Listing 5-25: Limiting the number of rows returned by a query. ... ... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(SELECT name, title, salary FROM employees); // we do not want more than 1000 rows of data retrieved myStatement.setMaxRows(1000); 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); } ... ... Examining the results: The next two methods are useful for examining and navigating through a resulting row of data. ResultSet: boolean wasNull(); int findColumn(String columnName); boolean wasNull(); In case a column has the value of SQL NULL, this method returns true. Note that it should be called after calling the getXXX() method to access the column. int findColumn(String columnName);
|
![]() |
|