What determines the type of list item in a scalar inline JPA query?

Question

JPA provides the ability to run your own SQL query:

final Query q = entityManager.createNativeQuery("SELECT hork FROM foobar"); assert q != null; final List results = q.getResultList(); 

In the request above, which object will be present in my List ?

It seems simple, but:

In our case, there is an Informix TEXT column. Informix TEXT type looks like a CLOB .

Informix JDBC reports - and whether it is true or not, we cannot change it so that the value of the java.sql.Types field to which this column is displayed is java.sql.Types#LONGVARCHAR . OK

If you run a regular old JDBC query, for example:

 final ResultSet rs = someStatement.executeQuery("SELECT hork FROM foobar"); assert rs != null; while (rs.next()) { final Object result = rs.getObject(1); // see if you can guess what result.getClass() is } 

... you are working with byte[] , not String .

Now, if you run this basic query using two-way JPA providers (Hibernate, EclipseLink), you will get different objects in the result list.

Hibernate returns a String , which is simply the contents of a column in the form of a String , presumably through either ResultSet#getString(int) (instead of ResultSet#getObject(int) ), or through some platform encoding of the resulting byte[] .

EclipseLink returns the hexadecimal representation of byte[] , which, when decoded and interpreted in an ASCII character set, is actually the contents of the column.

I have two related questions:

• How to instruct JPA what type of scalar source SQL query should be?

• If EclipseLink does not return me a String representing the contents of the column, how did this happen, does it not just return me byte[] ?

Answer 1

It is very strange that your Informix JDBC driver returns the TEXT value as a byte [], this seems very wrong, since this is a character field, not a binary field, it should be a string. You can see if your JDBC driver has a fix for this.

It seems that the JPA gets the value as a String because the JDBC metadata says it is a string.

For EclipseLink, what really happens is that LONGVARCHAR is interpreted as a type of Clob, and since Clob can just be a locator, the whole value must be selected, so it converts the expected Clob to String, but since it is actually a byte [], it Gets a HEX converter. You can fix this in your InformixPlatofrm by overriding isClob and returning false for LONGVARCHAR, but the real problem is that the JDBC driver returns byte [] for the character field.