Типы данных java oracle
В Java есть 8 примитивных типов, которые делят на 4 группы, вот они:
- Целые числа - byte, short, int, long
- Числа с плавающей точкой (иначе вещественные) - float, double
- Логический - boolean
- Символьный - char
Целочисленные типы различаются между собой только диапазонами возможных значений, например, для хранения номера элемента в таблице Менделеева пока хватит переменной типа byte.
Тип | Размер (бит) | Диапазон |
---|---|---|
byte | 8 бит | от -128 до 127 |
short | 16 бит | от -32768 до 32767 |
char | 16 бит | беззнаковое целое число, представляющее собой символ UTF-16 (буквы и цифры) |
int | 32 бит | от -2147483648 до 2147483647 |
long | 64 бит | от -9223372036854775808L до 9223372036854775807L |
Пример использования целочисленных типов:
Символы тоже относят к целочисленным типам из-за особенностей представления в памяти и традиций.
Тип | Размер (бит) | Диапазон |
---|---|---|
float | 32 | от 1.4e-45f до 3.4e+38f |
double | 64 | от 4.9e-324 до 1.7e+308 |
Тип | Размер (бит) | Значение |
---|---|---|
boolean | 8 (в массивах), 32 (не в массивах используется int) | true (истина) или false (ложь) |
В стандартной реализации Sun JVM и Oracle HotSpot JVM тип boolean занимает 4 байта (32 бита), как и тип int. Однако, в определенных версиях JVM имеются реализации, где в массиве boolean каждое значение занимает по 1-му биту.
Ссылочные типы - это все остальные типы: классы, перечисления и интерфейсы, например, объявленные в стандартной библиотеке Java, а также массивы.
Строки это объекты класса String, они очень распространены, поэтому в некоторых случаях обрабатываются отлично от всех остальных объектов. Строковые литералы записываются в двойных кавычках.
Эта программа выведет:
Hello World
foo == bar ? true
foo равен bar ? true
foo == baz ? false
foo равен baz ? true
Если требуется создать ссылку на один из примитивных типов данных, необходимо использовать соответствующий класс-обертку. Также в таких классах есть некоторые полезные методы и константы, например минимальное значение типа int можно узнать использовав константу Integer.MIN_VALUE. Оборачивание примитива в объект называется упаковкой (boxing), а обратный процесс распаковкой (unboxing).
Тип | Класс-обертка |
---|---|
byte | Byte |
short | Short |
int | Integer |
long | Long |
char | Character |
float | Float |
double | Double |
boolean | Boolean |
Рекомендуется использовать valueOf, он может быть быстрее и использовать меньше памяти потому что применяет кэширование, а конструктор всегда создает новый объект.
The Java programming language is statically-typed, which means that all variables must first be declared before they can be used. This involves stating the variable's type and name, as you've already seen:
short: The short data type is a 16-bit signed two's complement integer. It has a minimum value of -32,768 and a maximum value of 32,767 (inclusive). As with byte , the same guidelines apply: you can use a short to save memory in large arrays, in situations where the memory savings actually matters.
int: By default, the int data type is a 32-bit signed two's complement integer, which has a minimum value of -2 31 and a maximum value of 2 31 -1. In Java SE 8 and later, you can use the int data type to represent an unsigned 32-bit integer, which has a minimum value of 0 and a maximum value of 2 32 -1. Use the Integer class to use int data type as an unsigned integer. See the section The Number Classes for more information. Static methods like compareUnsigned , divideUnsigned etc have been added to the Integer class to support the arithmetic operations for unsigned integers.
long: The long data type is a 64-bit two's complement integer. The signed long has a minimum value of -2 63 and a maximum value of 2 63 -1. In Java SE 8 and later, you can use the long data type to represent an unsigned 64-bit long, which has a minimum value of 0 and a maximum value of 2 64 -1. Use this data type when you need a range of values wider than those provided by int . The Long class also contains methods like compareUnsigned , divideUnsigned etc to support arithmetic operations for unsigned long.
float: The float data type is a single-precision 32-bit IEEE 754 floating point. Its range of values is beyond the scope of this discussion, but is specified in the Floating-Point Types, Formats, and Values section of the Java Language Specification. As with the recommendations for byte and short , use a float (instead of double ) if you need to save memory in large arrays of floating point numbers. This data type should never be used for precise values, such as currency. For that, you will need to use the java.math.BigDecimal class instead. Numbers and Strings covers BigDecimal and other useful classes provided by the Java platform.
double: The double data type is a double-precision 64-bit IEEE 754 floating point. Its range of values is beyond the scope of this discussion, but is specified in the Floating-Point Types, Formats, and Values section of the Java Language Specification. For decimal values, this data type is generally the default choice. As mentioned above, this data type should never be used for precise values, such as currency.
boolean: The boolean data type has only two possible values: true and false . Use this data type for simple flags that track true/false conditions. This data type represents one bit of information, but its "size" isn't something that's precisely defined.
char: The char data type is a single 16-bit Unicode character. It has a minimum value of '\u0000' (or 0) and a maximum value of '\uffff' (or 65,535 inclusive).
In addition to the eight primitive data types listed above, the Java programming language also provides special support for character strings via the java.lang.String class. Enclosing your character string within double quotes will automatically create a new String object; for example, String s = "this is a string"; . String objects are immutable, which means that once created, their values cannot be changed. The String class is not technically a primitive data type, but considering the special support given to it by the language, you'll probably tend to think of it as such. You'll learn more about the String class in Simple Data Objects
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.
Although there are specific get XXX methods for all the Oracle SQL types, you can use the general get methods for convenience or simplicity, or if you are not certain in advance what type of data you will receive.
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:
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.
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, getObject 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.STRUCT object.
JDBC Escape Syntax to Oracle SQL Syntax Example
You can write a simple program to translate JDBC escape syntax to Oracle SQL syntax. The following program prints the comparable Oracle SQL syntax for statements using JDBC escape syntax for function calls, date literals, time literals, and timestamp literals. In the program, the oracle.jdbc. OracleSql class parse() method performs the conversions.
The following code is the output that prints the comparable SQL syntax.
Result Set and Statement Extensions
The JDBC Statement object returns an OracleResultSet object, typed as 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 ResultSet 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:
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.
Similarly, when you use executeQuery to run a stored procedure using a callable statement, the returned object is an OracleCallableStatement . The type of the return value of executeQuery( ) is java.sql.CallableStatement . If your application needs only the standard JDBC methods, you need not cast the variable. However, to take advantage of the Oracle extensions, you must cast the variable to OracleCallableStatement . Similar rules apply to prepareStatement , prepareCall , and so on.
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.
Summary of getObject and getOracleObject Return Types
Table 13-2 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 13-2 getObject and getOracleObject Return Types
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
class specified in type map
or oracle.sql.STRUCT (if no type map entry)
Oracle object reference
collection (varray or nested table)
Footnote 1 ResultSet.getObject returns java.sql.Timestamp only if the oracle.jdbc.J2EE13Compliant connection property is set to TURE , else the method returns oracle.sql.TIMESTAMP .
The ResultSet.getObject method returns java.sql.Timestamp for the TIMESTAMP SQL type, only when the connection property oracle.jdbc.J2EE13Compliant is set to TRUE . This property has to be set when the connection is obtained. If this connection property is not set or if it is set after the connection is obtained, then the ResultSet.getObject method returns oracle.sql.TIMESTAMP for the TIMESTAMP SQL type.
The oracle.jdbc.J2EE13Compliant connection property can also be set without changing the code in the following ways:
Including the classes12dms.jar and ojdbc14dms.jar files in CLASSPATH . These files set oracle.jdbc.J2EE13Compliant to TRUE by default. These files are located in $ORACLE_HOME/jdbc/lib .
Setting the system property by calling the java command with the flag -Doracle.jdbc.J2EE13Compliant=true . For example,
When the J2EE13Compliant is set to TRUE the behaviour is as in Table B-3 of the JDBC specification.
Table A-1, "Valid SQL Data Type-Java Class Mappings", for information on type compatibility between all SQL and Java types.
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:
Scalar Functions
Oracle JDBC drivers do not support all scalar functions. To find out which functions the drivers support, use the following methods supported by the Oracle-specific oracle.jdbc. OracleDatabaseMetaData class and the standard Java java.sql.DatabaseMetadata interface:
Returns a comma-delimited list of math functions supported by the driver. For example, ABS , COS , SQRT .
Returns a comma-delimited list of string functions supported by the driver. For example, ASCII , LOCATE .
Returns a comma-delimited list of system functions supported by the driver. For example, DATABASE , USER .
Returns a comma-delimited list of time and date functions supported by the driver. For example, CURDATE , DAYOFYEAR , HOUR .
Oracle JDBC drivers support fn , the function keyword.
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:
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.
T his chapter describes data access in oracle.sql.* formats, as opposed to standard Java formats. The oracle.sql.* formats are a key factor of the Oracle Java Database Connectivity (JDBC) extensions, offering significant advantages in efficiency and precision in manipulating SQL data.
Using oracle.sql.* formats 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:
Embedded JDBC Escape Syntax
Oracle JDBC drivers support some embedded JDBC escape syntax, which is the syntax that you specify between curly braces. The current support is basic.
JDBC escape syntax was previously known as SQL92 Syntax or SQL92 escape syntax.
This section describes the support offered by the drivers for the following constructs:
Where driver support is limited, these sections also describe possible workarounds.
Disabling Escape Processing
The processing for JDBC escape syntax is enabled by default, which results in the JDBC driver performing escape substitution before sending the SQL code to the database. If you want the driver to use regular Oracle SQL syntax, which is more efficient than JDBC escape syntax processing, then use this statement:
Table of Mappings
Table 13-1 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 10 g . 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 a oracle.sql.* Java type. Manipulating SQL data as oracle.sql.* data types minimizes conversions, improving performance and eliminating conversion losses.
Table 13-1 Default Mappings Between SQL Types and Java Types
STANDARD JDBC 1.0 TYPES :
STANDARD JDBC 2.0 TYPES :
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
For database versions, such as 8.1.7, which do not support the TIMESTAMP data type, TIMESTAMP is mapped to DATE .
Default Values
It's not always necessary to assign a value when a field is declared. Fields that are declared but not initialized will be set to a reasonable default by the compiler. Generally speaking, this default will be zero or null , depending on the data type. Relying on such default values, however, is generally considered bad programming style.
The following chart summarizes the default values for the above data types.
Data Type | Default Value (for fields) |
---|---|
byte | 0 |
short | 0 |
int | 0 |
long | 0L |
float | 0.0f |
double | 0.0d |
char | '\u0000' |
String (or any object) | null |
boolean | false |
Local variables are slightly different; the compiler never assigns a default value to an uninitialized local variable. If you cannot initialize your local variable where it is declared, make sure to assign it a value before you attempt to use it. Accessing an uninitialized local variable will result in a compile-time error.
Function Call Syntax
Oracle JDBC drivers support the following procedure and function call syntax:
Notes Regarding Mappings
This section provides further detail 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:
The standard java.sql.SQLData
The Oracle-specific oracle.sql.ORAData
The setObject and setOracleObject Methods
Just as there is a standard getObject and Oracle-specific getOracleObject in result sets and callable statements, there are also standard setObject and Oracle-specific setOracleObject methods in OraclePreparedStatement and OracleCallableStatement . The setOracleObject methods take oracle.sql.* input parameters.
To bind standard Java types to a prepared statement or callable statement, use the setObject method, which takes a java.lang.Object as input. The setObject method does support a few of the oracle.sql.* types. However, the method has been implemented so that you can enter instances of the oracle.sql.* classes that correspond to the following JDBC standard types: Blob , Clob , Struct , Ref , and Array .
To bind oracle.sql.* types to a prepared statement or callable statement, use the setOracleObject method, which takes a subclass of oracle.sql.Datum as input. To use setOracleObject , you must cast your prepared statement or callable statement to OraclePreparedStatement or OracleCallableStatement .
Example of Using setObject and setOracleObject
Valid SQL-JDBC Data Type Mappings
Table 11-1 describes the default mappings between Java classes and SQL data types supported by Oracle JDBC drivers. Compare the contents of the JDBC Type Codes, Standard Java Types, and SQL Data Types columns in Table 11-1 with the contents of Table A-1.
For classes where oracle.sql.ORAData appears in italic, these can be generated by JPublisher.
Table A-1 Valid SQL Data Type-Java Class Mappings
CHAR , VARCHAR2 , LONG
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
user defined named types, ADTs
opaque named types
nested tables and VARRAY named types
references to named types
The type UROWID is not supported.
The oracle.sql.Datum class is abstract. The value passed to a parameter of type oracle.sql.Datum must be of the Java type corresponding to the underlying SQL type. Likewise, the value returned by a method with return type oracle.sql.Datum must be of the Java type corresponding to the underlying SQL type.
Time and Date Literals
Databases differ in the syntax they use for date, time, and timestamp literals. JDBC supports dates and times written only in a specific format. This section describes the formats you must use for date, time, and timestamp literals in SQL statements.
Date Literals
The JDBC drivers support date literals in SQL statements written in the format:
Where yyyy-mm-dd represents the year, month, and day. For example:
The JDBC drivers will replace this escape clause with the equivalent Oracle representation: "22 OCT 1995".
The following code snippet contains an example of using a date literal in a SQL statement.
Time Literals
The JDBC drivers support time literals in SQL statements written in the format:
where, hh:mm:ss represents the hours, minutes, and seconds. For example:
The JDBC drivers will replace this escape clause with the equivalent Oracle representation: "05:10:45".
If the time is specified as:
Then the equivalent Oracle representation would be "14:20:50", assuming the server is using a 24-hour clock.
This code snippet contains an example of using a time literal in a SQL statement.
Timestamp Literals
The JDBC drivers support timestamp literals in SQL statements written in the format:
where yyyy-mm-dd hh:mm:ss.f. represents the year, month, day, hours, minutes, and seconds. The fractional seconds portion ( .f. ) is optional and can be omitted. For example: represents, in Oracle format, NOV 01 1997 13:22:45.
This code snippet contains an example of using a timestamp literal in a SQL statement.
Mapping SQL DATE Data type to Java
Oracle Database 8 i and earlier versions did not support TIMESTAMP data, but Oracle DATE data used to have a time component as an extension to the SQL standard. So, Oracle Database 8 i and earlier versions of JDBC drivers mapped oracle.sql.DATE to java.sql.Timestamp to preserve the time component. Starting with Oracle Database 9.0.1, TIMESTAMP support was included and 9 i JDBC drivers started mapping oracle.sql.DATE to java.sql.Date . This mapping was incorrect as it truncated the time component of Oracle DATE data. To overcome this problem, Oracle Database 11.1 introduced a new flag mapDateToTimestamp . The default value of this flag is true , which means that by default the drivers will correctly map oracle.sql.DATE to java.sql.Timestamp , retaining the time information. If you still want the incorrect but 10 g compatible oracle.sql.DATE to java.sql.Date mapping, then you can get it by setting the value of mapDateToTimestamp flag to false .
In Oracle Database 11 g , if you have an index on a DATE column to be used by a SQL query, then to obtain faster and accurate results, you must use the setObject method in the following way:
This is because if you use the setDate method, then the time component of the Oracle DATE data will be lost and if you use the setTimestamp method, then the index on the DATE column will not be used.
To overcome the problem of oracle.sql.DATE to java.sql.Date mapping, Oracle Database 9.2 had introduced a flag, V8Compatible . The default value of this flag was false , which allowed the mapping of Oracle DATE data to java.sql.Date data. But, users could retain the time component of the Oracle DATE data by setting the value of this flag to true . This flag is desupported in 11 g because it controlled Oracle Database 8 i compatibility, which is no longer supported.
Data Types For Returned Objects from getObject and getXXX
The return type of getObject is java.lang.Object . The returned value is an instance of a subclass of java.lang.Object . Similarly, the return type of getOracleObject is oracle.sql.Datum , and the class of the returned value is a subclass of oracle.sql.Datum . You normally cast the returned object to the appropriate class to use particular methods and functionality of that class.
In addition, you have the option of using a specific get XXX method instead of the generic getObject or getOracleObject methods. The get XXX methods enable you to avoid casting, because the return type of get XXX corresponds to the type of object returned. For example, the return type of getCLOB is oracle.sql.CLOB , as opposed to java.lang.Object .
Example of Casting Return Values
This example assumes that you have fetched data of the NUMBER type as the first column of a result set. Because you want to manipulate the NUMBER data without losing precision, cast your result set to OracleResultSet and use getOracleObject to return the NUMBER data in oracle.sql.* format. If you do not cast your result set, then you have to use getObject , which returns your numeric data into a Java Float and loses some of the precision of your SQL data.
The getOracleObject method returns an oracle.sql.NUMBER object into an oracle.sql.Datum return variable unless you cast the output. Cast the getOracleObject output to oracle.sql.NUMBER if you want to use a NUMBER return variable and any of the special functionality of that class.
Alternatively, you can return the object into a generic oracle.sql.Datum return variable and cast it later when you use NUMBER -specific methods.
This uses the FIXME method of oracle.sql.NUMBER . The FIXME method is not defined on oracle.sql.Datum and would not be reachable without the cast.
Standard Types Versus Oracle Types
Use the oracle.sql.OraData rather than the java.sql.SqlData if the OraData 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.DATE or oracle.sql.TIMESTAMP rather than java.sql.Date or java.sql.Timestamp if you are using JDK 5.0 or earlier versions or require maximum performance. You may also use the oracle.sql data type if you want to read many date values or compute or display only a small percentage. Due to a bug in all versions of Java prior to JDK 5.1, construction of java.lang.Date and java.lang.Timestamp objects is slow, especially in multithreaded environments. This bug is fixed in JDK 5.1.
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 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
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.
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.
STRUCT , ARRAY , BLOB , CLOB , REF , and ROWID are all the implementation classes of the corresponding JDBC standard interface types. So, there is no benefit of using the Oracle extension types as they are identical to the JDBC standard types.
BFILE , TIMESTAMPTZ , and TIMESTAMPLTZ have no representation in the JDBC standard. You must use these Oracle extensions.
In all other cases, you should use the standard JDBC type rather than the Oracle extensions.
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.
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 .
Oracle JDBC Notes and Limitations
The following limitations exist in the Oracle JDBC implementation, but all of them are either insignificant or have easy workarounds. This section covers the following topics:
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 TIME ZONE
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:
The standard java.sql.SQLData
The Oracle-specific oracle.jdbc.OracleData
Related Topics
Outer Joins
Oracle JDBC drivers do not support the outer join syntax. The workaround is to use Oracle outer join syntax:
Use Oracle SQL syntax:
Using Underscore Characters in Numeric Literals
In Java SE 7 and later, any number of underscore characters ( _ ) can appear anywhere between digits in a numerical literal. This feature enables you, for example. to separate groups of digits in numeric literals, which can improve the readability of your code.
For instance, if your code contains numbers with many digits, you can use an underscore character to separate digits in groups of three, similar to how you would use a punctuation mark like a comma, or a space, as a separator.
The following example shows other ways you can use the underscore in numeric literals:
You can place underscores only between digits; you cannot place underscores in the following places:
- At the beginning or end of a number
- Adjacent to a decimal point in a floating point literal
- Prior to an F or L suffix
- In positions where a string of digits is expected
The following examples demonstrate valid and invalid underscore placements (which are highlighted) in numeric literals:
This appendix contains detailed Java Database Connectivity (JDBC) reference information, including the following topics:
Other setXXX Methods
As with the get XXX methods, there are several specific set XXX methods. Standard set XXX methods are provided for binding standard Java types, and Oracle-specific set XXX methods are provided for binding Oracle-specific types.
Similarly, there are two forms of the setNull method:
void setNull(int parameterIndex, int sqlType )
This is specified in the standard java.sql.PreparedStatement interface. This signature takes a parameter index and a SQL type code defined by the java.sql.Types or oracle.jdbc.OracleTypes class. Use this signature to set an object other than a REF , ARRAY , or STRUCT to NULL .
void setNull(int parameterIndex , int sqlType , String sql_type_name )
With JDBC 2.0, this signature is also specified in the standard java.sql.PreparedStatement interface. This method takes a SQL type name in addition to a parameter index and a SQL type code. Use this method when the SQL type code is java.sql.Types.REF , ARRAY , or STRUCT . If the type code is other than REF , ARRAY , or STRUCT , then the given SQL type name is ignored.
Similarly, the registerOutParameter method has a signature for use with REF , ARRAY , or STRUCT data:
Binding Oracle-specific types using the appropriate setXXX methods, instead of the methods used for binding standard Java types, may offer some performance advantage.
This section covers the following topics:
Input Parameter Types of setXXX Methods
Table 13-4 summarizes the input types for all the setXXX methods and specifies which are Oracle extensions under JDK 1.2.x. To use methods that are Oracle extensions, you must cast your statement to OraclePreparedStatement or OracleCallableStatement .
LIKE Escape Characters
The characters % and _ have special meaning in SQL LIKE clauses. You use % to match zero or more characters and _ to match exactly one character. If you want to interpret these characters literally in strings, then you precede them with a special escape character. For example, if you want to use ampersand ( & ) as the escape character, then you identify it in the SQL statement as:
If you want to use the backslash character (\) as an escape character, then you must enter it twice, that is, \\. For example:
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 a ResultSet , you must cast the result set itself 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:
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 ResultSet
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.
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 " SCOTT" to the prepared call and registers the DATE type as an output parameter. After the call is run, getOracleObject retrieves the date associated with "SCOTT" . Note that because getOracleObject returns a Datum object, the results are cast to DATE .
Supported SQL and PL/SQL Data Types
The tables in this section list SQL and PL/SQL data types, and whether Oracle JDBC drivers support them. Table A-2 describes Oracle JDBC driver support for SQL data types.
Table A-2 Support for SQL Data Types
Footnote 1 The NCHAR type is supported indirectly. There is no corresponding java.sql.Types type, but if your application calls the formOfUse(NCHAR) method, then this type can be accessed.
Footnote 2 In JSE 6, the NVARCHAR2 type is supported directly. In J2SE 5.0, the NVARCHAR2 type is supported indirectly. There is no corresponding java.sql.Types type, but if your application calls the formOfUse(NCHAR) method, then this type can be accessed.
Table A-3 describes Oracle JDBC support for the ANSI-supported SQL data types.
Table A-3 Support for ANSI-92 SQL Data Types
NATIONAL CHARACTER VARYING
NATIONAL CHAR VARYING
Table A-4 describes Oracle JDBC driver support for SQL User-Defined types.
Table A-4 Support for SQL User-Defined Types
Object types ( JAVA_OBJECT )
Nested table types and VARRAY types
Table A-5 describes Oracle JDBC driver support for PL/SQL data types. Note that PL/SQL data types include these categories:
Scalar character types, which includes BOOLEAN and DATE data types
Large object (LOB) types
Table A-5 Support for PL/SQL Data Types
Scalar Character Types:
REF CURSOR types
object reference types
The types NATURAL , NATURAL n , POSITIVE , POSITIVE n , and SIGNTYPE are subtypes of BINARY INTEGER .
The types DEC , DECIMAL , DOUBLE PRECISION , FLOAT , INT , INTEGER , NUMERIC , REAL , and SMALLINT are subtypes of NUMBER .
The types NCHAR and NVARCHAR2 are supported indirectly. There is no corresponding java.sql.Types type, but if your application calls formOfUse(NCHAR) , then these types can be accessed. Refer to "NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property in JDK 1.5" for details.
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 COMM column contains one or more NULL values.
The next example shows how to compare values for equality when some return values might be NULL . The following code returns all the ENAMES from the EMP table that are NULL , if there is no value of 100 for COMM .
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 wrapper type corresponding to every primitive type that can represent null . The object wrapper types must be used as the targets for SQL data to detect SQL NULL without ambiguity.
Literals
You may have noticed that the new keyword isn't used when initializing a variable of a primitive type. Primitive types are special data types built into the language; they are not objects created from a class. A literal is the source code representation of a fixed value; literals are represented directly in your code without requiring computation. As shown below, it's possible to assign a literal to a variable of a primitive type:
Integer Literals
An integer literal is of type long if it ends with the letter L or l ; otherwise it is of type int . It is recommended that you use the upper case letter L because the lower case letter l is hard to distinguish from the digit 1 .
Values of the integral types byte , short , int , and long can be created from int literals. Values of type long that exceed the range of int can be created from long literals. Integer literals can be expressed by these number systems:
- Decimal: Base 10, whose digits consists of the numbers 0 through 9; this is the number system you use every day
- Hexadecimal: Base 16, whose digits consist of the numbers 0 through 9 and the letters A through F
- Binary: Base 2, whose digits consists of the numbers 0 and 1 (you can create binary literals in Java SE 7 and later)
For general-purpose programming, the decimal system is likely to be the only number system you'll ever use. However, if you need to use another number system, the following example shows the correct syntax. The prefix 0x indicates hexadecimal and 0b indicates binary:
Floating-Point Literals
A floating-point literal is of type float if it ends with the letter F or f ; otherwise its type is double and it can optionally end with the letter D or d .
The floating point types ( float and double ) can also be expressed using E or e (for scientific notation), F or f (32-bit float literal) and D or d (64-bit double literal; this is the default and by convention is omitted).
Character and String Literals
Literals of types char and String may contain any Unicode (UTF-16) characters. If your editor and file system allow it, you can use such characters directly in your code. If not, you can use a "Unicode escape" such as '\u0108' (capital C with circumflex), or "S\u00ED Se\u00F1or" (Sí Señor in Spanish). Always use 'single quotes' for char literals and "double quotes" for String literals. Unicode escape sequences may be used elsewhere in a program (such as in field names, for example), not just in char or String literals.
The Java programming language also supports a few special escape sequences for char and String literals: \b (backspace), \t (tab), \n (line feed), \f (form feed), \r (carriage return), \" (double quote), \' (single quote), and \\ (backslash).
There's also a special null literal that can be used as a value for any reference type. null may be assigned to any variable, except variables of primitive types. There's little you can do with a null value beyond testing for its presence. Therefore, null is often used in programs as a marker to indicate that some object is unavailable.
Finally, there's also a special kind of literal called a class literal, formed by taking a type name and appending " .class" ; for example, String.class . This refers to the object (of type Class ) that represents the type itself.
Other getXXX Methods
Standard JDBC provides a get XXX for each standard Java type, such as getByte , getInt , getFloat , and so on. Each of these returns exactly what the method name implies.
In addition, the OracleResultSet and OracleCallableStatement classes provide a full complement of get XXX methods corresponding to all the oracle.sql.* types. Each get XXX method returns an oracle.sql. XXX object. For example, getROWID returns an oracle.sql.ROWID object.
There is no performance advantage in using the specific get XXX methods. However, they do save you the trouble of casting, because the return type is specific to the object being returned.
This section covers the following topics:
Return Types of getXXX Methods
Table 13-3 summarizes the return types for each get XXX method and specifies which are Oracle extensions under Java Development Kit (JDK) 1.2.x. You must cast the returned object to OracleResultSet or OracleCallableStatement to use methods that are Oracle extensions.
Table 13-3 Summary of getXXX Return Types
getBigDecimal (see Notes)
getBoolean (see Notes)
subclasses of oracle.sql.Datum
Special Notes about getXXX Methods
This section provides additional details about some get XXX methods.
The simplified input signature is:
The scale parameter, used to specify the number of digits to the right of the decimal, is no longer necessary. The Oracle JDBC drivers retrieve numeric values with full precision.
Because there is no BOOLEAN database type, when you use getBoolean a data type conversion always occurs. The getBoolean method is supported only for numeric columns. When applied to these columns, getBoolean interprets any zero value as false and any other value as true . When applied to any other sort of column, getBoolean raises the exception java.lang.NumberFormatException .
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:
Читайте также: