DB2 Data Type Java Mapping

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® for Linux, UNIX, and Windows, 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.setXXXor 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 database 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	BIGINT12
java.math.BigInteger	BIGINT11
java.math.BigInteger	CHAR(n)11,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	XML10
byte[]	CHAR(n) FOR BIT DATA5
byte[]	VARCHAR(n) FOR BIT DATA7
byte[]	BINARY(n)5, 13
byte[]	VARBINARY(n)7, 13
byte[]	BLOB9
byte[]	ROWID
byte[]	XML10
java.sql.Blob	BLOB
java.sql.Blob	XML10
java.sql.Clob	CLOB
java.sql.Clob	DBCLOB9
java.sql.Clob	XML10
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP
java.io.ByteArrayInputStream	BLOB
java.io.StringReader	CLOB
java.io.ByteArrayInputStream	CLOB
java.io.InputStream	XML10
com.ibm.db2.jcc.DB2RowID (deprecated)	ROWID
java.sql.RowId	ROWID
com.ibm.db2.jcc.DB2Xml (deprecated)	XML10
java.sql.SQLXML	XML10
java.util.Date	CHAR(n)11,5
java.util.Date	VARCHAR(n)11,5
java.util.Date	DATE11
java.util.Date	TIME11
java.util.Date	TIMESTAMP11
java.util.Calendar	CHAR(n)11,5
java.util.Calendar	VARCHAR(n)11,5
java.util.Calendar	DATE11
java.util.Calendar	TIME11
java.util.Calendar	TIMESTAMP11
Notes: For column updates, the data server has no exact equivalent for the Java boolean or byte data types, but the best fit is SMALLINT. 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. n=16 or n=34. DECFLOAT is valid for connections to DB2 Version 9.1 for z/OS, DB2 V9.5 for Linux, UNIX, and Windows, or DB2 for i V6R1, or later database servers. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later. n<=255. m<=127. n<=32704. m<=16352. This mapping is valid only if the database server can determine the data type of the column. XML is valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2V9.1 for Linux, UNIX, and Windows or later database servers. This mapping is valid only for IBM Data Server Driver for JDBC and SQLJ version 4.13 or later. BIGINT is valid for connections to DB2 Version 9.1 for z/OS or later database servers, DB2V9.1 for Linux, UNIX, and Windows or later database servers, and all supported DB2 for i database servers. BINARY and VARBINARY are valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 for i5/OS™ V5R3 and later database servers.

Data types for retrieval from table columns

The following table summarizes the mappings of DB2 or IBM Informix data types to Java data types forResultSet.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 database server data types to Java data types for retrieving data from database 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
BIGINT5	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)6	byte[]	None
VARBINARY(n)6	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. Use java.sql.Clob.
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	java.sql.Timestamp	java.sql.String, java.sql.Date, java.sql.Time, java.sql.Timestamp
Notes: n=16 or n=34. DECFLOAT is valid for connections to DB2 Version 9.1 for z/OS, DB2 V9.5 for Linux, UNIX, and Windows, or DB2 for i V6R1, or later database servers. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later. This mapping is valid only if the database server can determine the data type of the column. XML is valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2V9.1 for Linux, UNIX, and Windows or later database servers. BIGINT is valid for connections to DB2 Version 9.1 for z/OS or later database servers, DB2V9.1 for Linux, UNIX, and Windows or later database servers, and all supported DB2 for i database servers. BINARY and VARBINARY are valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 for i5/OS V5R3 or later database servers.

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 database 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 type1
boolean2, java.lang.Boolean	BIT	SMALLINT
byte2, java.lang.Byte	TINYINT	SMALLINT
short, java.lang.Short	SMALLINT	SMALLINT
int, java.lang.Integer	INTEGER	INTEGER
long, java.lang.Long	BIGINT	BIGINT6
float, java.lang.Float	REAL	REAL
float, java.lang.Float	FLOAT	REAL
double, java.lang.Double	DOUBLE	DOUBLE
java.math.BigDecimal	DECIMAL	DECIMAL
java.math.BigDecimal	java.types.OTHER	DECFLOATn3
java.math.BigDecimal	com.ibm.db2.jcc.DB2Types.DECFLOAT	DECFLOATn3
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	BINARY5
byte[]	VARBINARY	VARBINARY5
byte[]	LONGVARBINARY	VARCHAR FOR BIT DATA
byte[]	VARBINARY	BLOB4
byte[]	LONGVARBINARY	BLOB4
java.sql.Date	DATE	DATE
java.sql.Time	TIME	TIME
java.sql.Timestamp	TIMESTAMP	TIMESTAMP
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
Notes: A DB2 for z/OS stored procedure or user-defined function parameter cannot have the XML data type. 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. DECFLOAT parameters in Java routines are valid only for connections to DB2 Version 9.1 for z/OS or later database servers. DECFLOAT parameters in Java routines are not supported for connections to for Linux, UNIX, and Windows or DB2 for i. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later. This mapping is valid only if the database server can determine the data type of the column. BINARY and VARBINARY are valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 for i5/OS V5R3 and later database servers. BIGINT is valid for connections to DB2 Version 9.1 for z/OS or later database servers, DB2V9.1 for Linux, UNIX, and Windows or later database servers, and all supported DB2 for i database servers.

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 for Linux, UNIX, and Windows, if more than one Java data type is listed for an SQL data type, only thefirst 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 FUNCTION1	Data type in Java stored procedure or user-defined function method2
SMALLINT	short, java.lang.Integer
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	java.sql.Timestamp
BLOB	java.sql.Blob
CLOB	java.sql.Clob
DBCLOB	java.sql.Clob
ROWID	java.sql.Types.ROWID
Notes: A DB2 for z/OS stored procedure or user-defined function parameter cannot have the XML data type. For a stored procedure or user-defined function on a DB2 for Linux, UNIX, and Windowsserver, only the first data type is valid. BIGINT is valid for connections to DB2 Version 9.1 for z/OS or later database servers orDB2 V9.1 for Linux, UNIX, and Windows or later database servers. DECFLOAT parameters in Java routines are valid only for connections to DB2 Version 9.1 for z/OS or later database servers. DECFLOAT parameters in Java routines are not supported for connections to for Linux, UNIX, and Windows or DB2 for i. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later. BINARY and VARBINARY are valid for connections to DB2 Version 9.1 for z/OS or later database servers.