Retrieving SQL enum causes java.lang.ClassCastException: java.lang.Character cannot be cast to java.lang.String

Question

I have a query like this

Query importQuery = advertiserDao.getEntityManager().createNativeQuery(
    "select matching_type from group_key where key_id = " + Key.getkeyId());
String match = (String) importQuery.getSingleResult();

The matching_type is a enum('good', 'bad', 'repared') type. This gives an exception as following:

java.lang.ClassCastException: java.lang.Character cannot be cast to java.lang.String

What does it mean, the actual value is a string. How can I solve this?

Answer 1

Try to user a java.lang.Character variable as suggested by the exception:

Character match = (Character) importQuery.getSingleResult();

Answer 2

This is very strange. According to the official documentation, a MySQL ENUM type should definitely be returned as a String (actually, any MySQL data type can be converted to a String):

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

Starting with Connector/J 3.1.0, the JDBC driver will issue warnings or throw 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, | java.lang.String, java.io.InputStream,
and SET                          | java.io.Reader, java.sql.Blob, java.sql.Clob
---------------------------------+---------------------------------------------

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    | Returned as Java Class
                            | GetColumnClassName | 
----------------------------+--------------------+-----------------------
...                         | ...                | ...
----------------------------+--------------------+-----------------------
ENUM('value1','value2',...) | CHAR               | java.lang.String

I suspect a JDBC driver bug. What version are you using? Can you try with a more recent version? Does in work with raw JDBC?