Java sql where null

T his chapter describes Oracle extensions ( oracle.sql.* formats) and compares them to standard Java formats ( java.sql.* ). Using Oracle extensions involves casting your result sets and statements to OracleResultSet , OracleStatement , OraclePreparedStatement , and OracleCallableStatement , as appropriate, and using the getOracleObject , setOracleObject , get XXX , and set XXX methods of these classes, where XXX corresponds to the types in the oracle.sql package.

This chapter covers the following topics:

11.1 Data Type Mappings

The Oracle JDBC drivers support standard JDBC types as well as Oracle-specific data types. This section documents standard and Oracle-specific SQL-Java default type mappings. This section contains the following topics:

11.1.1 Table of Mappings

The following table shows the default mappings between SQL data types, JDBC type codes, standard Java types, and Oracle extended types.

The SQL Data Types column lists the SQL types that exist in Oracle Database 12 c Release 1 (12.1). The JDBC Type Codes column lists data type codes supported by the JDBC standard and defined in the java.sql.Types class or by Oracle in the oracle.jdbc.OracleTypes class. For standard type codes, the codes are identical in these two classes.

The Standard Java Types column lists standard types defined in the Java language. The Oracle Extension Java Types column lists the oracle.sql.* Java types that correspond to each SQL data type in the database. These are Oracle extensions that let you retrieve all SQL data in the form of an oracle.sql.* Java type.

In general, the Oracle JDBC drivers are optimized to manipulate SQL data using the standard JDBC types. In a few specialized cases, it may be advantageous to use the Oracle extension classes that are available in the oracle.sql package. But, Oracle strongly recommends to use the standard JDBC types instead of Oracle extensions, whenever possible.

Table 11-1 Default Mappings Between SQL Types and Java Types

TIMESTAMP WITH LOCAL TIME ZONE

Starting from Oracle Database 12 c Release 1 (12.1), the oracle.sql.BLOB class is deprecated and replaced with the oracle.jdbc.OracleBlob interface.

Starting from Oracle Database 12c Release 1, the oracle.sql.CLOB class is deprecated and is replaced with the oracle.jdbc.OracleClob interface.

Starting from Oracle Database 12 c Release 1 (12.1), the oracle.sql.STRUCT class is deprecated and replaced with the oracle.jdbc.OracleStruct interface.

Starting from Oracle Database 12 c Release 1 (12.1), the oracle.sql.REF class is deprecated and replaced with the oracle.jdbc.OracleRef interface.

Starting from Oracle Database 12 c Release 1 (12.1), the oracle.sql.ARRAY class is deprecated and replaced with the oracle.jdbc.OracleArray interface.

For database versions, such as 8.1.7, which do not support the TIMESTAMP data type, TIMESTAMP is mapped to DATE .

Related Topics

11.1.2 Notes Regarding Mappings

This section provides further details regarding mappings for NUMBER and user-defined types.

For the different type codes that an Oracle NUMBER value can correspond to, call the getter routine that is appropriate for the size of the data for mapping to work properly. For example, call getByte to get a Java tinyint value for an item x , where -128 < x < 128.

User-defined types, such as objects, object references, and collections, map by default to weak Java types, such as java.sql.Struct , but alternatively can map to strongly typed custom Java classes. Custom Java classes can implement one of two interfaces:

Related Topics

11.2 Data Conversion Considerations

When JDBC programs retrieve SQL data into Java, you can use standard Java types, or you can use types of the oracle.sql package. This section covers the following topics:

11.2.1 Standard Types Versus Oracle Types

The Oracle data types in oracle.sql store data in the same bit format as used by the database. In versions of the Oracle JDBC drivers prior to Oracle Database 10 g , the Oracle data types were generally more efficient. Starting from Oracle Database 10 g , the JDBC drivers were substantially updated. As a result, in most cases the standard Java types are preferred to the data types in oracle.sql.* . In particular, java.lang.String is much more efficient than oracle.sql.CHAR .

In general, Oracle recommends that you use the Java standard types. The exceptions to this are:

Use the oracle.jdbc.OracleData rather than the java.sql.SqlData if the OracleData functionality better suits your needs.
Use oracle.sql.NUMBER rather than java.lang.Double if you need to retain the exact values of floating point numbers. Oracle NUMBER is a decimal representation and Java Double and Float are binary representations. Conversion from one format to the other can result in slight variations in the actual value represented. Additionally, the range of values that can be represented using the two formats is different. Use oracle.sql.NUMBER rather than java.math.BigDecimal when performance is critical and you are not manipulating the values, just reading and writing them.
Use oracle.sql.DATE or oracle.sql.TIMESTAMP if you are using a JDK version earlier than JDK 6. Use java.sql.Date or java.sql.Timestamp if you are using JDK 6 or a later version.

Note: Due to a bug in all versions of Java prior to JDK 6, construction of java.lang.Date and java.lang.Timestamp objects is slow, especially in multithreaded environments. This bug is fixed in JDK 6.

If you convert an oracle.sql data type to a Java standard data type, then the benefits of using the oracle.sql data type are lost.

11.2.2 About Converting SQL NULL Data

Java represents a SQL NULL datum by the Java value null . Java data types fall into two categories: primitive types, such as byte , int , and float , and object types, such as class instances. The primitive types cannot represent null . Instead, they store null as the value zero, as defined by the JDBC specification. This can lead to ambiguity when you try to interpret your results.

In contrast, Java object types can represent null . The Java language defines an object container type corresponding to every primitive type that can represent null . The object container types must be used as the targets for SQL data to detect SQL NULL without ambiguity.

11.2.3 About Testing for NULLs

You cannot use a relational operator to compare NULL values with each other or with other values. For example, the following SELECT statement does not return any row even if the COMMISSION_PCT column contains one or more NULL values.

PreparedStatement pstmt = conn.prepareStatement( "SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT = ?"); pstmt.setNull(1, java.sql.Types.VARCHAR);

The next example shows how to compare values for equality when some return values might be NULL . The following code returns all the FIRST_NAME from the EMPLOYEES table that are NULL , if there is no value of 205 for COMM .

PreparedStatement pstmt = conn.prepareStatement("SELECT FIRST_NAME FROM EMPLOYEES WHERE COMMISSION_PCT =? OR ((COMM IS NULL) AND (? IS NULL))"); pstmt.setBigDecimal(1, new BigDecimal(205)); pstmt.setNull(2, java.sql.Types.VARCHAR);

11.3 Result Set and Statement Extensions

The Statement object returns a java.sql.ResultSet . If you want to apply only standard JDBC methods to the object, then keep it as a ResultSet type. However, if you want to use the Oracle extensions on the object, then you must cast it to OracleResultSet . All of the Oracle Result Set extensions are in the oracle.jdbc.OracleResultSet interface and all the Statement extensions are in the oracle.jdbc.OracleStatement interface.

For example, assuming you have a standard Statement object stmt , do the following if you want to use only standard JDBC ResultSet methods:

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

If you need the extended functionality provided by the Oracle extensions to JDBC, you can select the results into a standard ResultSet variable and then cast that variable to OracleResultSet later.

Key extensions to the result set and statement classes include the getOracleObject and setOracleObject methods, used to access and manipulate data in oracle.sql.* formats.

11.4 Comparison of Oracle get and set Methods to Standard JDBC

This section describes get and set methods, particularly the JDBC standard getObject and setObject methods and the Oracle-specific getOracleObject and setOracleObject methods, and how to access data in oracle.sql.* format compared with Java format.

You can use the standard get XXX methods for all Oracle SQL types.

This section covers the following topics:

You cannot qualify a column name with a table name and pass it as a parameter to the get XXX method. For example:

ResultSet rset = stmt.executeQuery("SELECT employees.department_id, department.department_id FROM employees, department"); rset.getInt("employees.department_id");

The getInt method in the preceding code will throw an exception. To uniquely identify the columns in the get XXX method, you can either use column index or specify column aliases in the query and use these aliases in the get XXX method.

11.4.1 Standard getObject Method

The standard getObject method of a result set or callable statement has a return type of java.lang.Object . The class of the object returned is based on its SQL type, as follows:

For SQL data types that are not Oracle-specific, the getObject method returns the default Java type corresponding to the SQL type of the column, following the mapping in the JDBC specification.
For Oracle-specific data types, getObject returns an object of the appropriate oracle.sql.* class, such as oracle.sql.ROWID .
For Oracle database objects, getObject returns a Java object of the class specified in your type map. Type maps specify a mapping from database named types to Java classes. The getObject( parameter_index ) method uses the default type map of the connection. The getObject( parameter_index , map ) enables you to pass in a type map. If the type map does not provide a mapping for a particular Oracle object, then getObject returns an oracle.sql.OracleStruct object.

11.4.2 Oracle getOracleObject Method

If you want to retrieve data from a result set or callable statement as an oracle.sql.* object, then you must follow a special process. For an OracleResultSet object, you must cast the Result Set to oracle.jdbc.OracleResultSet and then call getOracleObject instead of getObject . The same applies to CallableStatement and oracle.jdbc.OracleCallableStatement .

The return type of getOracleObject is oracle.sql.Datum . The actual returned object is an instance of the appropriate oracle.sql.* class. The method signature is:

public oracle.sql.Datum getOracleObject(int parameter_index)

When you retrieve data into a Datum variable, you can use the standard Java instanceof operator to determine which oracle.sql.* type it really is.

Example: Using getOracleObject with a Result Set

The following example creates a table that contains a column of CHAR data and a column containing a BFILE locator. A SELECT statement retrieves the contents of the table as a result set. The getOracleObject then retrieves the CHAR data into the char_datum variable and the BFILE locator into the bfile_datum variable. Note that because getOracleObject returns a Datum object, the return values must be cast to CHAR and BFILE , respectively.

stmt.execute («CREATE TABLE bfile_table (x VARCHAR2 (30), b BFILE)»); stmt.execute («INSERT INTO bfile_table VALUES (‘one’, BFILENAME (‘TEST_DIR’, ‘file1’))»); ResultSet rset = stmt.executeQuery («SELECT * FROM bfile_table»); while (rset.next ())

Example: Using getOracleObject in a Callable Statement

The following example prepares a call to the procedure myGetDate , which associates a character string with a date. The program passes » HR» to the prepared call and registers the DATE type as an output parameter. After the call is run, getOracleObject retrieves the date associated with «HR» . Note that because getOracleObject returns a Datum object, the results are cast to DATE .

OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall ("begin myGetDate (?, ?); end;"); cstmt.setString (1, "HR"); cstmt.registerOutParameter (2, Types.DATE); cstmt.execute (); DATE date = (DATE) ((OracleCallableStatement)cstmt).getOracleObject (2); .

11.4.3 Summary of getObject and getOracleObject Return Types

The following table lists the underlying return types for the getObject and getOracleObject methods for each Oracle SQL type.

Keep in mind the following when you use these methods:

getObject always returns data into a java.lang.Object instance
getOracleObject always returns data into an oracle.sql.Datum instance

You must cast the returned object to use any special functionality.

Table 11-2 getObject and getOracleObject Return Types

Источник