Documentation Home
Connectors and APIs Manual
Download this Manual
PDF (US Ltr) - 5.3Mb
PDF (A4) - 5.3Mb


Connectors and APIs Manual  /  ...  /  Java, JDBC, and MySQL Types

3.5.5 Java, JDBC, and MySQL Types

MySQL Connector/J is flexible in the way it handles conversions between MySQL data types and Java data types.

In general, any MySQL data type can be converted to a java.lang.String, and any numeric type can be converted to any of the Java numeric types, although round-off, overflow, or loss of precision may occur.

Connector/J issues warnings or throws DataTruncation exceptions as is required by the JDBC specification, unless the connection was configured not to do so by using the property jdbcCompliantTruncation and setting it to false.

The conversions that are always guaranteed to work are listed in the following table. The first column lists one or more MySQL data types, and the second column lists one or more Java types to which the MySQL types can be converted.

Table 3.22 Possible Conversions Between MySQL and Java Data Types

These MySQL Data Types Can always be converted to these Java types
CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET java.lang.String, java.io.InputStream, java.io.Reader, java.sql.Blob, java.sql.Clob
FLOAT, REAL, DOUBLE PRECISION, NUMERIC, DECIMAL, TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINT java.lang.String, java.lang.Short, java.lang.Integer, java.lang.Long, java.lang.Double, java.math.BigDecimal
DATE, TIME, DATETIME, TIMESTAMP java.lang.String, java.sql.Date, java.sql.Timestamp

Note

Round-off, overflow or loss of precision may occur if you choose a Java numeric data type that has less precision or capacity than the MySQL data type you are converting to/from.

The ResultSet.getObject() method uses the type conversions between MySQL and Java types, following the JDBC specification where appropriate. The values returned by ResultSetMetaData.GetColumnTypeName()and ResultSetMetaData.GetColumnClassName() are shown in the table below. For more information on the JDBC types, see the reference on the java.sql.Types class.

Table 3.23 MySQL Types and Return Values for ResultSetMetaData.GetColumnTypeName()and ResultSetMetaData.GetColumnClassName()

MySQL Type NameReturn value of GetColumnTypeNameReturn value of GetColumnClassName
BIT(1)BITjava.lang.Boolean
BIT( > 1)BITbyte[]
TINYINT(1) SIGNED, BOOLEAN

If tinyInt1isBit=true and transformedBitIsBoolean=false: BIT

If tinyInt1isBit=true and transformedBitIsBoolean=true: BOOLEAN

If tinyInt1isBit=false: TINYINT

If tinyInt1isBit=true and transformedBitIsBoolean=false: java.lang.Boolean

If tinyInt1isBit=true and transformedBitIsBoolean=true: java.lang.Boolean

If tinyInt1isBit=false: java.lang.Integer

TINYINT( > 1) SIGNEDTINYINTjava.lang.Integer
TINYINT( any ) UNSIGNEDTINYINT UNSIGNEDjava.lang.Integer
SMALLINT[(M)] [UNSIGNED]SMALLINT [UNSIGNED]java.lang.Integer (regardless of whether it is UNSIGNED or not)
MEDIUMINT[(M)] [UNSIGNED]MEDIUMINT [UNSIGNED]java.lang.Integer (regardless of whether it is UNSIGNED or not)
INT,INTEGER[(M)]INTEGERjava.lang.Integer
INT,INTEGER[(M)] UNSIGNEDINTEGER UNSIGNEDjava.lang.Long
BIGINT[(M)]BIGINT java.lang.Long
BIGINT[(M)] UNSIGNEDBIGINT UNSIGNEDjava.math.BigInteger
FLOAT[(M,D)]FLOATjava.lang.Float
DOUBLE[(M,B)] [UNSIGNED]DOUBLEjava.lang.Double (regardless of whether it is UNSIGNED or not)
DECIMAL[(M[,D])] [UNSIGNED]DECIMALjava.math.BigDecimal (regardless of whether it is UNSIGNED or not)
DATEDATEjava.sql.Date
DATETIMEDATETIMEjava.time.LocalDateTime
TIMESTAMP[(M)]TIMESTAMPjava.sql.Timestamp
TIMETIMEjava.sql.Time
YEAR[(2|4)]YEARIf yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short. If set to true (the default), then the returned object is of type java.sql.Date with the date set to January 1st, at midnight.
CHAR(M)CHARjava.lang.String
VARCHAR(M)VARCHARjava.lang.String
BINARY(M), CHAR(M) BINARYBINARYbyte[]
VARBINARY(M), VARCHAR(M) BINARYVARBINARYbyte[]
BLOBBLOBbyte[]
TINYBLOBTINYBLOBbyte[]
MEDIUMBLOBMEDIUMBLOBbyte[]
LONGBLOBLONGBLOBbyte[]
TEXTTEXTjava.lang.String
TINYTEXTTINYTEXTjava.lang.String
MEDIUMTEXTMEDIUMTEXTjava.lang.String
LONGTEXTLONGTEXTjava.lang.String
JSONJSONjava.lang.String
GEOMETRYGEOMETRYbyte[]
ENUM('value1','value2',...)CHARjava.lang.String
SET('value1','value2',...)CHARjava.lang.String