Data types that map to database data types in Java applications

To write efficient JDBC and SQLJ programs, you need to use the best mappings between Java™ data types and table column data types.

The following tables summarize the mappings of Java data types to JDBC and database data types for a Db2® on Linux®, UNIX, and Windows systems, Db2 for z/OS®, or IBM® Informix® system.

Data types for updating table columns

The following table summarizes the mappings of Java data types to database data types for PreparedStatement.setXXX or ResultSet.updateXXX methods in JDBC programs, and for input host expressions in SQLJ programs. When more than one Java data type is listed, the first data type is the recommended data type.

Table 1. Mappings of Java data types to data server data types for updating database tables
Java data type Database data type
short, java.lang.Short SMALLINT
boolean1, byte1, java.lang.Boolean, java.lang.Byte SMALLINT
int, java.lang.Integer INTEGER
long, java.lang.Long BIGINT13
java.math.BigInteger BIGINT12
java.math.BigInteger CHAR(n)12,5
float, java.lang.Float REAL
double, java.lang.Double DOUBLE
java.math.BigDecimal DECIMAL(p,s)2
java.math.BigDecimal DECFLOAT(n)3,4
java.lang.String CHAR(n)5
java.lang.String GRAPHIC(m)6
java.lang.String VARCHAR(n)7
java.lang.String VARGRAPHIC(m)8
java.lang.String CLOB9
java.lang.String XML11
byte[] CHAR(n) FOR BIT DATA5
byte[] VARCHAR(n) FOR BIT DATA7
byte[] BINARY(n)5, 14
byte[] VARBINARY(n)7, 14
byte[] BLOB9
byte[] ROWID
byte[] XML11
java.sql.Blob BLOB
java.sql.Blob XML11
java.sql.Clob CLOB
java.sql.Clob DBCLOB10
java.sql.NClob DBCLOB10
java.sql.Clob XML11
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE15,16
java.io.ByteArrayInputStream BLOB
java.io.StringReader CLOB
java.io.ByteArrayInputStream CLOB
java.io.InputStream XML11
com.ibm.db2.jcc.DB2RowID (deprecated) ROWID
java.sql.RowId ROWID
com.ibm.db2.jcc.DB2Xml (deprecated) XML11
java.sql.SQLXML XML11
java.util.Date CHAR(n)12,5
java.util.Date VARCHAR(n)12,5
java.util.Date DATE12
java.util.Date TIME12
java.util.Date TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE12,15,16
java.util.Calendar CHAR(n)12,5
java.util.Calendar VARCHAR(n)12,5
java.util.Calendar DATE12
java.util.Calendar TIME12
java.util.Calendar TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE12,15,16
Notes:
  1. For column updates, the data server has no exact equivalent for the Java boolean or byte data types, but the best fit is SMALLINT.
  2. p is the decimal precision and s is the scale of the table column.

    You should design financial applications so that java.math.BigDecimal columns map to DECIMAL columns. If you know the precision and scale of a DECIMAL column, updating data in the DECIMAL column with data in a java.math.BigDecimal variable results in better performance than using other combinations of data types.

  3. n=16 or n=34.
  4. DECFLOAT is valid for connections to Db2 for z/OS, Db2 on Linux, UNIX, and Windows systems, or Db2 for IBM i data servers. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later.
  5. n<=254.
  6. m<=127.
  7. n<=32672.
  8. m<=16336.
  9. This mapping is valid only if the data server can determine the data type of the column.
  10. Use java.sql.Clob if property useJDBC4DataType is set to DB2BaseDataSource.NO. Use java.sql.NClob if property useJDBC4DataType is not set or is set to DB2BaseDataSource.YES.
  11. XML is valid for connections to Db2 for z/OS or Db2 on Linux, UNIX, and Windows systems data servers.
  12. This mapping is valid only for IBM Data Server Driver for JDBC and SQLJ version 4.13 or later.
  13. BIGINT is valid for connections to Db2 for z/OS data servers, Db2 on Linux, UNIX, and Windows systems data servers, and Db2 for IBM i data servers.
  14. BINARY and VARBINARY are valid for connections to Db2 for z/OS data servers, Db2 for IBM i 5/OS V5R3 or later data servers, or Db2 on Linux, UNIX, and Windows systems data servers.
  15. p indicates the timestamp precision, which is the number of digits in the fractional part of the timestamp. 0<=p<=12. The default is 6. TIMESTAMP(p) is supported for connections to Db2 on Linux, UNIX, and Windows systems and Db2 for z/OS.
  16. The WITH TIME ZONE clause is supported for connections to Db2 for z/OS only.

Data types for retrieval from table columns

The following table summarizes the mappings of Db2 or IBM Informix data types to Java data types for ResultSet.getXXX methods in JDBC programs, and for iterators in SQLJ programs. This table does not list Java numeric wrapper object types, which are retrieved using ResultSet.getObject.

Table 2. Mappings of data server data types to Java data types for retrieving data from data server tables
SQL data type Recommended Java data type or Java object type Other supported Java data types
SMALLINT short byte, int, long, float, double, java.math.BigDecimal, boolean, java.lang.String
INTEGER int short, byte, long, float, double, java.math.BigDecimal, boolean, java.lang.String
BIGINT6 long int, short, byte, float, double, java.math.BigDecimal, boolean, java.lang.String
DECIMAL(p,s) or NUMERIC(p,s) java.math.BigDecimal long, int, short, byte, float, double, boolean, java.lang.String
DECFLOAT(n)1,2 java.math.BigDecimal long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.lang.String
REAL float long, int, short, byte, double, java.math.BigDecimal, boolean, java.lang.String
DOUBLE double long, int, short, byte, float, java.math.BigDecimal, boolean, java.lang.String
CHAR(n) java.lang.String long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.io.InputStream, java.io.Reader
VARCHAR(n) java.lang.String long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.io.InputStream, java.io.Reader
CHAR(n) FOR BIT DATA byte[] java.lang.String, java.io.InputStream, java.io.Reader
VARCHAR(n) FOR BIT DATA byte[] java.lang.String, java.io.InputStream, java.io.Reader
BINARY(n)7 byte[] None
VARBINARY(n)7 byte[] None
GRAPHIC(m) java.lang.String long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.io.InputStream, java.io.Reader
VARGRAPHIC(m) java.lang.String long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.io.InputStream, java.io.Reader
CLOB(n) java.sql.Clob java.lang.String
BLOB(n) java.sql.Blob byte[]3
DBCLOB(m) No exact equivalent.5  
ROWID java.sql.RowId byte[], com.ibm.db2.jcc.DB2RowID (deprecated)
XML4 java.sql.SQLXML byte[], java.lang.String, java.io.InputStream, java.io.Reader
DATE java.sql.Date java.sql.String, java.sql.Timestamp
TIME java.sql.Time java.sql.String, java.sql.Timestamp
TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE8,9 java.sql.Timestamp java.sql.String, java.sql.Date, java.sql.Time, java.sql.Timestamp
Notes:
  1. n=16 or n=34.
  2. DECFLOAT is valid for connections to Db2 for z/OS, Db2 on Linux, UNIX, and Windows systems, or Db2 for IBM i data servers. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later.
  3. This mapping is valid only if the data server can determine the data type of the column.
  4. XML is valid for connections to Db2 for z/OS or Db2 on Linux, UNIX, and Windows systems data servers.
  5. Use java.sql.Clob if property useJDBC4DataType is set to DB2BaseDataSource.NO. Use java.sql.NClob if property useJDBC4DataType is not set or is set to DB2BaseDataSource.YES.
  6. BIGINT is valid for connections to Db2 for z/OS data servers, Db2 on Linux, UNIX, and Windows systems data servers, and all supported Db2 for IBM i data servers.
  7. BINARY and VARBINARY are valid for connections to Db2 for z/OS data servers, Db2 for IBM i 5/OS V5R3 and later data servers, or Db2 on Linux, UNIX, and Windows systems data servers.
  8. p indicates the timestamp precision, which is the number of digits in the fractional part of the timestamp. 0<=p<=12. The default is 6. TIMESTAMP(p) is supported for connections to Db2 on Linux, UNIX, and Windows systems V9.7 and later and Db2 for z/OS V10 and later only.
  9. The WITH TIME ZONE clause is supported for connections to Db2 for z/OS only.

Data types for calling stored procedures and user-defined functions

The following table summarizes mappings of Java data types to JDBC data types and Db2 or IBM Informix data types for calling user-defined function and stored procedure parameters. The mappings of Java data types to JDBC data types are for CallableStatement.registerOutParameter methods in JDBC programs. The mappings of Java data types to data server data types are for parameters in stored procedure or user-defined function invocations.

If more than one Java data type is listed in the following table, the first data type is the recommended data type.

Table 3. Mappings of Java, JDBC, and SQL data types for calling stored procedures and user-defined functions
Java data type JDBC data type SQL data type
boolean3, java.lang.Boolean BOOLEAN BOOLEAN1,2
boolean3, java.lang.Boolean BIT SMALLINT
byte3, java.lang.Byte TINYINT SMALLINT
short, java.lang.Short SMALLINT SMALLINT
int, java.lang.Integer INTEGER INTEGER
long, java.lang.Long BIGINT BIGINT7
float, java.lang.Float REAL REAL
float, java.lang.Float FLOAT REAL
double, java.lang.Double DOUBLE DOUBLE
java.math.BigDecimal NUMERIC DECIMAL
java.math.BigDecimal DECIMAL DECIMAL
java.math.BigDecimal java.types.OTHER DECFLOATn4
java.math.BigDecimal com.ibm.db2.jcc.DB2Types.DECFLOAT DECFLOATn4
java.lang.String CHAR CHAR
java.lang.String CHAR GRAPHIC
java.lang.String VARCHAR VARCHAR
java.lang.String VARCHAR VARGRAPHIC
java.lang.String LONGVARCHAR VARCHAR
java.lang.String VARCHAR CLOB
java.lang.String LONGVARCHAR CLOB
java.lang.String CLOB CLOB
byte[] BINARY CHAR FOR BIT DATA
byte[] VARBINARY VARCHAR FOR BIT DATA
byte[] BINARY BINARY6
byte[] VARBINARY VARBINARY6
byte[] LONGVARBINARY VARCHAR FOR BIT DATA
byte[] VARBINARY BLOB5
byte[] LONGVARBINARY BLOB5
java.sql.Date DATE DATE
java.sql.Time TIME TIME
java.sql.Timestamp TIMESTAMP TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE8,9
java.sql.Blob BLOB BLOB
java.sql.Clob CLOB CLOB
java.sql.Clob CLOB DBCLOB
java.io.ByteArrayInputStream None BLOB
java.io.StringReader None CLOB
java.io.ByteArrayInputStream None CLOB
com.ibm.db2.jcc.DB2RowID (deprecated) com.ibm.db2.jcc.DB2Types.ROWID ROWID
java.sql.RowId java.sql.Types.ROWID ROWID
com.ibm.db2.jcc.DB2Xml (deprecated) com.ibm.db2.jcc.DB2Types.XML XML AS CLOB
java.sql.SQLXML java.sql.Types.SQLXML XML
java.sql.SQLXML java.sql.Types.SQLXML XML AS CLOB
java.sql.Array java.sql.Types.ARRAY ARRAY2
java.sql.Struct java.sql.Types.STRUCT ROW1,2
java.sql.ResultSet com.ibm.db2.jcc.DB2Types.CURSOR CURSOR type2
Notes:
  1. This parameter data type is supported for IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to Db2 on Linux, UNIX, and Windows systems only.
  2. This parameter data type is supported for stored procedure calls only.
  3. A stored procedure or user-defined function that is defined with a SMALLINT parameter can be invoked with a boolean or byte parameter. However, this is not recommended.
  4. DECFLOAT parameters in Java routines are valid only for connections to Db2 for z/OS data servers. DECFLOAT parameters in Java routines are not supported for connections to for Linux, UNIX, and Windows or Db2 for IBM i. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later.
  5. This mapping is valid only if the data server can determine the data type of the column.
  6. BINARY and VARBINARY are valid for connections to Db2 for z/OS data servers, Db2 for IBM i 5/OS V5R3 and later data servers, or Db2 on Linux, UNIX, and Windows systems data servers.
  7. BIGINT is valid for connections to Db2 for z/OS data servers and Db2 on Linux, UNIX, and Windows systems data servers .
  8. p indicates the timestamp precision, which is the number of digits in the fractional part of the timestamp. 0<=p<=12. The default is 6. TIMESTAMP(p) is supported for connections to Db2 on Linux, UNIX, and Windows systems and Db2 for z/OS.
  9. The WITH TIME ZONE clause is supported for connections to Db2 for z/OS.

Data types in Java stored procedures and user-defined functions

The following table summarizes mappings of the SQL parameter data types in a CREATE PROCEDURE or CREATE FUNCTION statement to the data types in the corresponding Java stored procedure or user-defined function method.

For Db2 on Linux, UNIX, and Windows systems, if more than one Java data type is listed for an SQL data type, only the first Java data type is valid.

For Db2 for z/OS, if more than one Java data type is listed, and you use a data type other than the first data type as a method parameter, you need to include a method signature in the EXTERNAL clause of your CREATE PROCEDURE or CREATE FUNCTION statement that specifies the Java data types of the method parameters.

Table 4. Mappings of SQL data types in a CREATE PROCEDURE or CREATE FUNCTION statement to data types in the corresponding Java stored procedure or user-defined function program
SQL data type in CREATE PROCEDURE or CREATE FUNCTION Data type in Java stored procedure or user-defined function method2
SMALLINT short, java.lang.Integer
BOOLEAN1 boolean
INTEGER int, java.lang.Integer
BIGINT3 long, java.lang.Long
REAL float, java.lang.Float
DOUBLE double, java.lang.Double
DECIMAL java.math.BigDecimal
DECFLOAT4 java.math.BigDecimal
CHAR java.lang.String
VARCHAR java.lang.String
CHAR FOR BIT DATA byte[]
VARCHAR FOR BIT DATA byte[]
BINARY5 byte[]
VARBINARY5 byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE6,7 java.sql.Timestamp
BLOB java.sql.Blob
CLOB java.sql.Clob
DBCLOB java.sql.Clob
ROWID java.sql.Types.ROWID
ARRAY1 java.sql.Array
ROW1 java.sql.Struct
XML AS CLOB java.sql.Types.SQLXML
Notes:
  1. This parameter data type is supported for stored procedures only.
  2. For a stored procedure or user-defined function on a Db2 on Linux, UNIX, and Windows systems server, only the first data type is valid.
  3. BIGINT is valid for connections to Db2 for z/OS data servers or Db2 on Linux, UNIX, and Windows systems data servers.
  4. DECFLOAT parameters in Java routines are valid only for connections to Db2 for z/OS data servers. DECFLOAT parameters in Java routines are not supported for connections to for Linux, UNIX, and Windows or Db2 for IBM i. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later.
  5. BINARY and VARBINARY are valid for connections to Db2 for z/OS data servers, Db2 for IBM i 5/OS V5R3 or later data servers, or Db2 on Linux, UNIX, and Windows systems data servers.
  6. p indicates the timestamp precision, which is the number of digits in the fractional part of the timestamp. 0<=p<=12. The default is 6. TIMESTAMP(p) is supported for connections to Db2 on Linux, UNIX, and Windows systems data servers and Db2 for z/OS data servers.
  7. The WITH TIME ZONE clause is supported for connections to Db2 for z/OS data servers.