1190-1193

Previous Table of Contents Next

Page 1190

segment to gather column heading and sizing information to assist in displaying the results of a query in a grid:

 // rsinfo references a valid java.sql.ResultSetMetaData int NumCols = rsinfo.getColumnCount(); String ColNames[] = new String[NumCols]; int ColSizes[] = new int[NumCols]; int ColTypes[] = new int[NumCols]; for (int i = 0; i < NumCols; i++) {     ColNames[i] = rsinfo.getColumnLabel(i + 1);             // JDBC column indexes are 1-based     ColSizes[i] = rsinfo.getColumnDisplaySize(i + 1);     ColTypes[i] = rsinfo.getColumnType(i + 1); } 

The getColumnType() method returns an integer representing a SQL datatype. The SQL datatype constants are defined in java.sql.Types. In the context of the example, you can use this information to determine whether a column should be displayed left-justified or right-justified. The following code segment extends this example and retrieves the column values for each row:

 // rs references a valid java.sql.ResultSet // rsinfo references a valid java.sql.ResultSetMetaData int NumCols = rsInfo.getColumnCount(); String ColVals[] = new String[NumCols]; int i = 0; while (rs.next()) {     for (i = 0; i < NumCols; i++) {         ColVals[i] = rs.getString(i + 1); // do something to display this column value     } } 

The preceding code segment used getString()for all column values. You can usually use this method regardless of a column's datatype because most drivers support conversion of any datatype to its string representation. Alternatively, an application can use datatype information to store column values in a more appropriate structure and use the corresponding get() method for each column based on its datatype. This might be necessary when the data is used in mathematical computations , but it is often more convenient to retrieve column values as strings for display purposes.

When using getInt() and the other numeric methods , special handling might be required for null values. You can use the wasNull() method to determine if the last column value read was null:

 double comm = rs.getDouble("COMM"); if (rs.wasNull)     // handle null values (e.g. set an indicator) 

By default, a driver returns 0 for a numeric get() method when the database value is null. The wasNull() method is provided for situations where the default behavior is not acceptable for an application's purposes.

Page 1191

Applying DML Statements

You can apply DML statements through the same interfaces as queries. You can use the Statement interface for direct execution and the PreparedStatement interface to prepare parameterized DML statements. This code segment demonstrates direct execution of a DML statement based on tables in the well-known Oracle scott schema:

 // DbConn is a valid Connection reference String DeptSQL = "INSERT INTO dept VALUES (50, `IS', `CHICAGO')"; String EmpSQL1 = "INSERT INTO emp VALUES (8000, `SMITH', `MANAGER', 7839, sysdate, 3500, 0, 50)"; String EmpSQL2 = "INSERT INTO emp VALUES (8001, `DILBERT', `P/A', 8000, sysdate, 3000, 0, 50)"; DbConn.setAutoCommit(false); java.sql.Statement stmt = DbConn.createStatement();int RowsInserted = stmt.executeUpdate(DeptSQL); if (RowsInserted == 1) {     RowsInserted = stmt.executeUpdate(EmpSQL1);     if (RowsInserted == 1) {         RowsInserted = stmt.executeUpdate(EmpSQL2);         if (RowsInserted == 1)             DbConn.commit();         else             DbConn.rollback();     }     else         DbConn.rollback(); } else     DBConn.rollback(); 

This admittedly contrived example illustrates two important points. First, to manage transactions, autocommit must be disabled. (It is enabled by default.) Secondly, the use of DBMS-specific functions, constants, and pseudo- columns is supported by most RDBMS-specific drivers. If an application must be portable across a broad range of database engines and JDBC driver implementations , one set of SQL and DML statements might not be possible. Although the SQL escape syntax solves many database interoperability problems for outer joins and other common inconsistencies, it is not supported by all drivers. Applications with portability requirements can use the DatabaseMetaData interface to work around these problems, generating RDMS- or driver-specific syntax when necessary.

The previous code segment also shows that you can use the executeUpdate() method for all DML statements, not just updates. In each case, it returns the number of rows affected. The following code segment demonstrates the same transaction using prepared execution for the inserts into the emp table:

 DbConn.setAutoCommit(false); Statement stmt = DbConn.createStatement(); String DeptSQL = "INSERT INTO DEPT VALUES (`50', `IS', `CHICAGO')"; String EmpSQL = "INSERT INTO emp VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement pstmt = DbConn.prepareStatement(EmpSQL); 

Page 1192

 int RowsInserted = stmt.executeUpdate(DeptSQL); if (RowsInserted == 1) { int empno = 8000;               String name = "SMITH";               String job = "MANAGER";               int mgr = 7839;     java.sql.Date hiredate = new java.sql.Date(96, 2, 1);               double sal = 3500.00;               int dept = 50;                 pstmt.setInt(1, empno);               pstmt.setString(2, name);     pstmt.setString(3, job);                 pstmt.setInt(4, mgr);     pstmt.setInt(5, hiredate);                 pstmt.setDouble(6, sal);               pstmt.setNull(7, Types.NUMERIC);     pstmt.setInt(8, dept);     RowsInserted = pstmt.executeUpdate();     if (RowsInserted == 1) { empno = 8001;                       name = "DILBERT";                       job = "P/A";                       mgr = 8000;         hiredate = new java.sql.Date(96,10,28);                       sal = 3000.00;                     pstmt.setInt(1, empno);                        pstmt.setString(2, name);         pstmt.setString(3, job);                     pstmt.setInt(4, mgr);     pstmt.setInt(5, hiredate);                     pstmt.setDouble(6, sal);         RowsInserted = pstmt.executeUpdate();         if (RowsInserted == 1) {             con.commit();         }         else {             con.rollback();         }     }     else {         con.rollback();     } } else con.rollback(); 

Note the special treatment of null values. There are no null representations of the basic Java datatypes, so the setNull() method is provided to indicate a null parameter value. The second argument to this method is the SQL datatype of the parameter and must be one of the constants defined in java.sql.Types. You might need to call the set() methods prior to each execution. In theory, you need to call the set() methods only for values that have changed since the previous execution. In practice, however, not all drivers store parameter values from one execution to the next, which might necessitate setting each parameter prior to each execution. This might be disconcerting to ODBC experts who are accustomed to binding an address and

Page 1193

simply assigning new values to bound variables prior to each execution. Remember that Java does not support pointers to basic datatypes, which makes it impossible to bind an address for a parameter.

The previous example also introduced the java.sql.Date datatype. JDBC has three special classes for dealing with date and time representations because internal representations vary greatly from one RDBMS to another. The java.sql.Date datatype should always be fully qualified to distinguish it from java.util.Date. It is constructed with the year, month, and day as arguments. Note that it adds 1900 to the year passed to the constructor, the month is zero-based (0_11), and the day is one-based (1_31). For example, January 1, 2002 is constructed as

 java.sql.Date newdate = new java.sql.Date(102, 0, 1); 

Dates prior to 1900 can be constructed using a negative number for the year. December 31, 1887 is constructed as

 java.sql.Date newdate = new java.sql.Date(-13, 11, 31); 

The two additional JDBC datatype classes, java.sql.Timestamp and java.sql.Time, are constructed similarly. A java.sql.Timestamp object is constructed with the two-digit year, month, day, hour (0_23), minutes (0_59), seconds (0_59), and nanoseconds (0_999,999,999). A java.sql.Time object is constructed with the hour , minutes, and seconds as arguments (nanoseconds are not supported). The primary advantage of using these types is that they support date-time comparisons through the before() and after() methods. You can also use them in conjunction with the java.util.Calendar class to perform date arithmetic. Consult the JDK documentation for additional information on these classes.

You can employ a more generic approach using the JDBC object equivalents of the basic datatypes using the setObject() method for setting parameter values. The setObject() method of the PreparedStatement interface has three forms:

 setObject(int parmIndex, Object obj); setObject(int parmIndex, Object obj, int SQLdataType); setObject(int parmIndex, Object obj, int SQLdataType, int scale); 

Each form accepts the one-based parameter index as the first argument and a java.lang.Object, which holds the value for the parameter. The second and third forms accept a target SQL type (one of the constants defined in java.sql.Types). The third form also accepts a scale, which is ignored unless the target datatype is Types.NUMERIC or Types.DECIMAL. It is up to the driver to perform the actual conversion of the Java objects to the appropriate SQL datatype. Consult the JavaSoft JDBC documentation for the possible mappings of Java object types to SQL datatypes.

The actual implementation of the setObject() method is driver-specific. Some drivers might use the setObject() method to allow specific abstract datatypes to be bound, and drivers might differ in the way objects are mapped to SQL datatypes. For example, Oracle8 drivers might support abstract datatypes and collections through the setObject() method. However, any such implementation is nonportable to other databases by definition.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

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