Ask a Question
Advertise on boostr.in
cannot store euro sign into lob string property with hibernate postgresql
cannot store euro sign into lob string property with hibernate postgresql Â using -'hibernate,postgresql,character-encoding,lob'
I am having trouble writing and reading back special characters like the Euro-sign (â‚¬) into LOB String properties in PostgreSQL 8.4 with Hibernate 3.6.10.
What I know is that PostgreSQL provides two distinct ways to store large character objects in a column of a table. They can be stored either directly into that table column or indirectly in a separate table (it's actually called pg_largeobject). In the latter case, the column holds a reference (OID) to the row in pg_largeobject.
The default behaviour in Hibernate 3.6.10 is the indirect OID approach. However, it is possible to add an extra annotation @org.hibernate.annotations.Type(type="org.hibernate.type.TextType") to the Lob property to get the direct storage behaviour.
Both approaches work fine, except for the moment that I want to work with special characters like the Euro sign (â‚¬). In that case the direct storage mechanism keeps working, but the indirect storage mechanism breaks.
I'd like to demonstrate that with an example. I created a test entity with 2 @Lob properties. One follows the direct storage principle, the other the indirect storage:
@Column(name = "CLOB_VALUE_INDIRECT_STORAGE", length = 2147483647)
public String getClobValueIndirectStorage()
@Column(name = "CLOB_VALUE_DIRECT_STORAGE", length = 2147483647)
public String getClobValueDirectStorage()
If I create an entity, populate both properties with the Euro sign and then persist it towards the database I see the following when I do a SELECT I see
Â id | clob_value_direct_storage | clob_value_indirect_storage
Â Â 6 | â‚¬ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | 910579 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
If I then query the table pg_largeobject I see:
Â Â loid Â | pageno | data
Â 910579 | Â Â Â Â Â 0 | \254
The 'data' column of pg_largeobject is of type bytea, which means that the information is stored as raw bytes. The expression '\254' represents one single byte and in UTF-8 represents the character 'Â¬'. This is exactly the value that I get back when I load the entity back from the database.
The Euro sign in UTF-8 consists of 3 bytes, so I would have expected the 'data' column to have 3 bytes and not 1.
This does not only occur for the Euro sign, but for many special characters. Is this a problem in Hibernate? Or the JDBC driver? Is there a way I can tweak this behaviour?
Thanks in advance,
Franck de Bruijn
Â Â Â Â
Oct 13, 2015
to add a comment.
Your name to display (optional):
Email me at this address if my answer is selected or commented on:
Email me if my answer is selected or commented on
Privacy: Your email address will only be used for sending these notifications.
To avoid this verification in future, please