Documentation Home
MySQL Connector/J 5.1 Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 477.0Kb
PDF (A4) - 477.6Kb
EPUB - 145.9Kb
HTML Download (TGZ) - 119.5Kb
HTML Download (Zip) - 146.0Kb

5.3 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.


All TEXT types return Types.LONGVARCHAR with different getPrecision() values (65535, 255, 16777215, and 2147483647 respectively) with getColumnType() returning -1. This behavior is intentional even though TINYTEXT does not fall, regarding to its size, within the LONGVARCHAR category. This is to avoid different handling inside the same base type. And getColumnType() returns -1 because the internal server handling is of type TEXT, which is similar to BLOB.

Also note that getColumnTypeName() will return VARCHAR even though getColumnType() returns Types.LONGVARCHAR, because VARCHAR is the designated column database-specific name for this type.

Starting with Connector/J 3.1.0, the JDBC driver 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 5.1 Connection Properties - Miscellaneous

These MySQL Data Types Can always be converted to these Java types
CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET java.lang.String,,, 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


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 value returned by ResultSetMetaData.GetColumnClassName() is also shown below. For more information on the JDBC types, see the reference on the java.sql.Types class.

Table 5.2 MySQL Types to Java Types for ResultSet.getObject()

MySQL Type Name Return value of GetColumnClassName Returned as Java Class
BIT(1) (new in MySQL-5.0) BIT java.lang.Boolean
BIT( > 1) (new in MySQL-5.0) BIT byte[]
TINYINT TINYINT java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not.
BOOL, BOOLEAN TINYINT See TINYINT, above as these are aliases for TINYINT(1), currently.
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)] [UNSIGNED] INTEGER [UNSIGNED] java.lang.Integer, if UNSIGNED java.lang.Long
BIGINT[(M)] [UNSIGNED] BIGINT [UNSIGNED] java.lang.Long, if UNSIGNED java.math.BigInteger
FLOAT[(M,D)] FLOAT java.lang.Float
DOUBLE[(M,B)] DOUBLE java.lang.Double
DECIMAL[(M[,D])] DECIMAL java.math.BigDecimal
DATE DATE java.sql.Date
DATETIME DATETIME java.sql.Timestamp
TIMESTAMP[(M)] TIMESTAMP java.sql.Timestamp
TIME TIME java.sql.Time
YEAR[(2|4)] YEAR If 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) CHAR java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
VARCHAR(M) [BINARY] VARCHAR java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
TINYTEXT VARCHAR java.lang.String
BLOB BLOB byte[]
TEXT VARCHAR java.lang.String
LONGTEXT VARCHAR java.lang.String
ENUM('value1','value2',...) CHAR java.lang.String
SET('value1','value2',...) CHAR java.lang.String

User Comments
  Posted by Erik Brandsberg on September 26, 2016
When using Unsigned types, such as an Unsigned Integer, the type returned by Connector/J is upgraded to the next larger type, BUT the actual SQL type returned by getColumnType is not upgraded, i.e. the unsigned value will continue to be reported as type "Integer". This can cause compatibility issues with Java, as code often uses the SQL type to determine how to return the responses. One example of this is the CachedRowSetImpl. This issue has caused problems with JOOQ ( and other mapping libraries as well.
Sign Up Login You must be logged in to post a comment.