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.
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.
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, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE14,15 |
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 | TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE11,14,15 |
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 | TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE11,14,15 |
Notes:
|
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.
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, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE7,8 | java.sql.Timestamp | java.sql.String, java.sql.Date, java.sql.Time, java.sql.Timestamp |
Notes:
|
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.
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:
|
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 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.
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:
|