Re: ResultSet.getClob() causing problems when used with JPA's @Lob

Thomas Kellerer schrieb am 20.07.2016 um 13:51:
>> I have a varchar-column in a table which maps to a field (of type String) in a
>> JPA-entity. When marking that String-property with the JPA @Lob-annotation,
>> using Hibernate as my JPA-provider, it stops working as expected when using
>> PostgreSQL (works as expected on Oracle and SQL Server). The problem is that
>> Hibernate, correctly, calls ResultSet.getClob() but PG internally calls
>> getLong(), which obviously won't work with varchar-types, resulting in:
>>
>>
>> After googling around I see this issue has come up before:
>> http://archives.postgresql.org/pgsql-jdbc/2010-02/msg00004.php
>>
>> One is encurraged to use ResultSet.getString() instead. Hm, well - being at
>> Hibernate's mercy here, I don't really have that luxury. So, is PG's JDBC-
>> driver going to fix this flaw (IMNSHO it is a flaw) or is there consensus in the
>> PG-community that clobs are special and are not to be treated as
>> Strings/varchars?
>
>
> Is there anything new here?
> This still doesn't work with 9.4.1208
>
> We are using Hibernate with an application that has to support Oracle and Postgres.
>
> For Oracle we have to use CLOB to store Strings longer then 4000 characters - which means we *have* to use CLOB.
> The Hibernate Entity is therefore annotated with @Lob and thus the whole thing fails with Postgres
>
> So we are caught between a rock and a hard place: change to String and lose the ability to work with Oracle (which we can't)
>or stick with @Lob and lose the possibility to also support Postgres.
>

Please find attached a patch that does the following:

* An implementation of the java.sql.Clob interface based on a String
* An implementation of the java.sql.Blob interface based on a byte[]
*A modified PgResultSet that checks the column type in getClob() and getBlob() and
returns the approriate Lob implementation if the column is a varchar/text or bytea
column.

The methods to set a stream are not implemented yet though.

I did not extend AbstractBlobClob as that makes too many assumption that the
underlying Lob is a "large object" in the database and I would wind up overwriting
nearly all methods in there.

I did however create an AbstractBasicLob to re-use the check for the correct position. The assertPosition()
methods in there are a copy from AbstractBlobClob. If this is something that gets accepted, it might
make sense to derive AbstractBlobClob from AbstractBasicLob and keep the assertions in a single place.

I don't know if we also need support for creating empty Clobs based on Strings that can be filled later

I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check
the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs()
would provide that information, but I don't know how to use that. If someone points me in the right
direction I can add that as well.

Regards
Thomas