esm_deitel_javahtp_5|Java Database Connectivity with JDBC|Tips

Below is a list of Tip Summaries available for this chapter.: Common Programming Errors; Performance Tips; Portability Tips; Software Engineering Observations

Common Programming Errors

23.1	Not providing a value for every column in a primary key breaks the Rule of Entity Integrity and causes the DBMS to report an error.
23.2	Providing the same value for the primary key in multiple rows causes the DBMS to report an error.
23.3	Providing a foreign-key value that does not appear as a primary-key value in another table breaks the Rule of Referential Integrity and causes the DBMS to report an error.
23.4	If a programmer assumes that the columns in a result are always returned in the same order from an SQL statement that uses the asterisk (`*`), the program may process the result incorrectly. If the column order in the table(s) changes, the order of the columns in the result would change accordingly.
23.5	In a query, failure to qualify names for columns that have the same name in two or more tables is an error.
23.6	It is an error to specify a value for an autoincrement column.
23.7	SQL statements use the single-quote (') character as a delimiter for strings. To specify a string containing a single quote (such as O'Malley) in an SQL statement, the string must have two single quotes in the position where the single-quote character appears in the string (e.g., `'O''Malley'`). The first of the two single-quote characters acts as an escape character for the second. Not escaping single-quote characters in a string that is part of an SQL statement is an SQL syntax error.
23.8	Initially, a `ResultSet` cursor is positioned before the first row. Attempting to access a `ResultSet`'s contents before positioning the `ResultSet` cursor to the first row with method `next` causes an `SQLException`.
23.9	Specifying column number 0 when obtaining values from a `ResultSet` causes an `SQLException`.
23.10	Attempting to manipulate a `ResultSet` after closing the `Statement` that created the `ResultSet` causes an `SQLException`. The program discards the `ResultSet` when the corresponding `Statement` is closed.
23.11	Attempting to update a `ResultSet` when the database driver does not support updatable `ResultSet`s causes `SQLException`s.
23.12	Attempting to move the cursor backwards through a `ResultSet` when the database driver does not support backwards scrolling causes an `SQLException`.

Back to Tips

Performance Tips

23.1 If the order of columns in a result is unknown, a program must process the columns by name. Specifying the column names to select from tables enables the application receiving the result to know the order of the columns in advance. In this case, the program can process the data more efficiently, because columns can be accessed by column number.

23.2 If a query specifies the exact columns to select from the database, the ResultSet contains the columns in the specified order. In this case, using the column number to obtain the column's value is more efficient than using the column name. The column number provides direct access to the specified column. Using the column name requires a linear search of the column names to locate the appropriate column.

Back to Tips

Portability Tips

23.1	See the documentation for your database system to determine whether SQL is case sensitive on your system and to determine the syntax for SQL keywords (i.e., should they be all uppercase letters, all lowercase letters or some combination of the two?).
23.2	Not all database systems support the `LIKE` operator, so be sure to read your database system's documentation carefully.
23.3	Some databases use the `*` character in place of the `%` character in a pattern.
23.4	Some databases use the `?` character in place of the `_` character in a `pattern`.
23.5	Some JDBC drivers do not support scrollable `ResultSet`s. In such cases, typically the driver returns a `ResultSet` in which the cursor can move only forward. For more information, see your database-driver documentation.
23.6	Some JDBC drivers do not support updatable `ResultSet`s. In such cases, typically the driver returns a read-only `ResultSet`. For more information, see your database-driver documentation.
23.7	Although the syntax for creating stored procedures differs across database management systems, interface `CallableStatement` provides a uniform interface for specifying input and output parameters for stored procedures and for invoking stored procedures.
23.8	According to the Java API documentation for interface `CallableStatement`, for maximum portability between database systems, programs should process the update counts or `ResultSet`s returned from a `CallableStatement` before obtaining the values of any output parameters.

Back to Tips

Software Engineering Observations

23.1	For most SQL statements, the asterisk (`*`) should not be used to specify column names. In general, programmers process results by knowing in advance the order of the columns in the result—e.g., selecting `authorID` and `lastName` from table `authors` ensures that the columns will appear in the result with `authorID` as the first column and `lastName` as the second column. Programs typically process result columns by specifying the column number in the result (starting from number 1 for the first column).
23.2	Specifying the column names to select guarantees that the columns are always returned in the specified order and also avoids returning unneeded columns, even if the actual order of the columns in the table(s) changes.
23.3	If an SQL statement includes columns from multiple tables that have the same name, the statement must precede those column names with their table names and the dot operator (e.g., `authors.authorID`).
23.4	Most major database vendors provide their own JDBC database drivers, and many third-party vendors provide JDBC drivers as well. For more information on JDBC drivers, visit the Sun Microsystems JDBC Web site, `java.sun.com/products/jdbc`.
23.5	On the Microsoft Windows platform, most databases support access via Open Database Connectivity (ODBC). ODBC is a technology developed by Microsoft to allow generic access to disparate database systems on the Windows platform (and some UNIX platforms). The Java 2 Software Development Kit (J2SDK) comes with the JDBC-to-ODBC-bridge database driver to allow any Java program to access any ODBC data source. The driver is class `JdbcOdbcDriver` in package `sun.jdbc.odbc`.
23.6	Most database management systems require the user to log in before accessing the database contents. `DriverManager` method `getConnection` is overloaded with versions that enable the program to supply the user name and password to gain access.
23.7	Metadata enables programs to process `ResultSet` contents dynamically when detailed information about the `ResultSet` is not known in advance.
23.8	Each `Statement` object can open only one `ResultSet` object at a time. When a `Statement` returns a new `ResultSet`, the `Statement` closes the prior `ResultSet`. To use multiple `ResultSet`s in parallel, separate `Statement` objects must return the `ResultSet`s.

Back to Tips