- 19.3.5 Connector/J (JDBC) Reference
- 19.3.5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J
- 19.3.5.2 JDBC API Implementation Notes
- 19.3.5.3 Java, JDBC and MySQL Types
- 19.3.5.4 Using Character Sets and Unicode
- 19.3.5.5 Connecting Securely Using SSL
- 19.3.5.6 Using Master/Slave Replication with ReplicationConnection
- 19.3.5.7 Mapping MySQL Error Numbers to SQLStates
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:
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.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 |
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
java.sql.Types classes see
Java
2 Platform Types.
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 if UNSIGNED or not) |
| MEDIUMINT[(M)] [UNSIGNED] | MEDIUMINT [UNSIGNED] |
java.lang.Integer, if UNSIGNED
java.lang.Long (C/J 3.1 and
earlier), or
java.lang.Integer for C/J 5.0
and later |
| 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. |
| BINARY(M) | BINARY | byte[] |
| VARBINARY(M) | VARBINARY | byte[] |
| TINYBLOB | TINYBLOB | byte[] |
| TINYTEXT | VARCHAR | java.lang.String |
| BLOB | BLOB | byte[] |
| TEXT | VARCHAR | java.lang.String |
| MEDIUMBLOB | MEDIUMBLOB | byte[] |
| MEDIUMTEXT | VARCHAR | java.lang.String |
| LONGBLOB | LONGBLOB | byte[] |
| LONGTEXT | VARCHAR | java.lang.String |
| ENUM('value1','value2',...) | CHAR | java.lang.String |
| SET('value1','value2',...) | CHAR | java.lang.String |
User Comments
Add your own comment.