I came across a requirement to use
HStore capability of
postgres schema to store unstructured data along with the
EclipseLink as JPA implementation. I was
aware that I need to write an EclipseLink converter to support this functionality in my project.
I thought about using existing solution for this if one is available, I came across couple of solution at
Stack Overflow and Git Hub but all of them are incomplete.
After implementing these solution I started receiving an exception column
After lot of Google and digging into postgres library I came across a solution, I am writing the same hoping this can be helpful for others.
metadata is of
type hstore but expression is of type character varying from postgres. After lot of Google and digging into postgres library I came across a solution, I am writing the same hoping this can be helpful for others.
Exception Trace
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Caused by: org.postgresql.util.PSQLException: ERROR: column "metadata" is of type hstore but expression is of type character varying | |
| Hint: You will need to rewrite or cast the expression. | |
| Position: 38 | |
| at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) | |
| at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) | |
| at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) | |
| at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) | |
| at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) | |
| at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363) | |
| at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:890) | |
| ... 37 more |
Solution
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import java.sql.SQLException; | |
| import java.util.HashMap; | |
| import java.util.Map; | |
| import org.eclipse.persistence.mappings.DatabaseMapping; | |
| import org.eclipse.persistence.mappings.converters.Converter; | |
| import org.eclipse.persistence.sessions.Session; | |
| import org.postgresql.util.HStoreConverter; | |
| import org.postgresql.util.PGobject; | |
| @SuppressWarnings({ | |
| "serial", | |
| "unchecked" | |
| }) | |
| public class HStoreConvertor implements Converter { | |
| public Object convertObjectValueToDataValue(Object v, Session s) { | |
| if (v != null) { | |
| try { | |
| String value = HStoreConverter.toString((Map < String, Object > ) v); | |
| PGobject object = new PGobject(); | |
| object.setType("hstore"); | |
| object.setValue(value); | |
| return object; | |
| } catch (SQLException e) { | |
| e.printStackTrace(); | |
| } | |
| } | |
| return null; | |
| } | |
| public Object convertDataValueToObjectValue(Object v, Session s) { | |
| // EclipseLink Bug or Feature? HStore will be converted to HashMap, | |
| // not String..., so the target type should be Object | |
| if (v instanceof PGobject) { | |
| PGobject object = (PGobject) v; | |
| return HStoreConverter.fromString(object.getValue()); | |
| } else if (v instanceof String) { | |
| String str = (String) v; | |
| if (str.trim().length() == 0) { | |
| return new HashMap < String, Object > (); | |
| } | |
| return HStoreConverter.fromString((String) v); | |
| } else { | |
| return (Map < String, Object > ) v; | |
| } | |
| } | |
| public boolean isMutable() { | |
| return false; | |
| } | |
| public void initialize(DatabaseMapping mapping, Session session) { | |
| mapping.getField().setSqlType(java.sql.Types.OTHER); | |
| } | |
| } |
Post a Comment
Post a Comment