Thanks Chris!
The part I was missing was the Annotation - I had almost completed a custom implementation,
but this works well now.
FWIW: With the latest jdbc4 driver from Postgres, a lot of the legwork is no longer needed.
I'll do a write up of my solution and post it to the group - maybe it will help someone else.
Cheers,
John
> -----Original Message-----
> From: Krzysztof [mailto:yazuna@gmail.com]
> Sent: Montag, 30. Juni 2014 15:49
> To: users@openjpa.apache.org
> Subject: Re: Postgres HStore implementation
>
> You'd need jdbc mapper for hstore (might be included in newer jdbc drivers),
> I use this one:
>
>
> /*-----------------------------------------------------------------------------
> *
> * Gaia CU7 variability
> *
> * Copyright (C) 2005-2020 Gaia Data Processing and Analysis
> Consortium
> *
> *
> * CU7 variability software is free software; you can redistribute it and/or
> * modify it under the terms of the GNU Lesser General Public
> * License as published by the Free Software Foundation; either
> * version 2.1 of the License, or (at your option) any later version.
> *
> * CU7 variability software is distributed in the hope that it will be useful,
> * but WITHOUT ANY WARRANTY; without even the implied warranty of
> * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
> * Lesser General Public License for more details.
> *
> * You should have received a copy of the GNU Lesser General Public
> * License along with this CU7 variability software; if not, write to the
> * Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
> * MA 02110-1301 USA
> *
>
> *-----------------------------------------------------------------------------
> */
> package gaia.cu7.om.dal.dictionary.PGType;
>
> /*
>
> * or more contributor license agreements. See the NOTICE file
> * distributed with this work for additional information
> * regarding copyright ownership. The ASF licenses this file
> * to you under the Apache License, Version 2.0 (the
> * "License"); you may not use this file except in compliance
> * with the License. You may obtain a copy of the License at
> *
> * http://www.apache.org/licenses/LICENSE-2.0
> *
> * Unless required by applicable law or agreed to in writing,
> * software distributed under the License is distributed on an
> * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
> * KIND, either express or implied. See the License for the
> * specific language governing permissions and limitations
> * under the License.
> */
>
> import java.io.Serializable;
> import java.util.Collection;
> import java.util.Iterator;
> import java.util.LinkedHashMap;
> import java.util.Map;
> import java.util.Set;
>
> import org.postgresql.util.PGobject;
> import org.postgresql.util.PSQLException;
> import org.postgresql.util.PSQLState;
>
> /**
> * Class that handles hstore contrib datatype.
> * In PG JDBC uses non-standard PGObject interface instead of SQLData
> *
> * @author knienart inspired by Petr Jelinek
> * @version $Id: PGhstore.java 319537 2013-10-08 20:49:15Z knienart $
> * @since 10.0
> */
> @SuppressWarnings("rawtypes")
> public class PGhstore extends PGobject implements Serializable, Cloneable,
> Map {
>
> private final static long serialVersionUID = 1;
> private Map hashList;
>
> public PGhstore() {
> setType("hstore");
> }
>
> public PGhstore(String value) throws java.sql.SQLException {
> this();
> setValue(value);
> }
>
> @SuppressWarnings("unchecked")
> public PGhstore(Map value) {
> this();
> hashList = new LinkedHashMap(value);
> }
>
> @SuppressWarnings("unchecked")
> @Override
> public void setValue(String s) throws java.sql.SQLException {
> hashList = new LinkedHashMap();
>
> if (s != null) {
> char[] chars = s.toCharArray();
> String key = null;
> StringBuffer buffer = new StringBuffer();
> boolean insideKey = true;
> boolean insideVal = false;
> boolean insideString = false;
>
> for (int i = 0; i < chars.length; i++) {
> // escape character that we need to skip
> if (chars[i] == '\\') {
> i++;
> }
>
> // white space
> else if (!insideString &&
> Character.isWhitespace(chars[i])) {
> continue;
> }
>
> // the => between key and value
> else if (!insideString && chars[i] == '=') {
> i++;
> if (i == chars.length)
> throw new
> PSQLException("Unexpected end of string", PSQLState.DATA_ERROR);
>
> if (!insideKey || chars[i] != '>')
> throw new
> PSQLException("Syntax error at position " + i, PSQLState.DATA_ERROR);
>
> insideKey = false;
> insideVal = true;
>
> key = buffer.toString();
> buffer.setLength(0);
>
> continue;
> }
>
> // quote, item separator or end of string
> else if (chars[i] == '"' || (!insideString &&
> chars[i] == ',') || i == chars.length - 1) {
> if (chars[i] == '"') {
> insideString = !insideString;
> if (i != chars.length - 1)
> continue;
> } else if (chars[i] != ',' && buffer !=
> null) {
> buffer.append(chars[i]);
> }
>
> String b = (buffer == null) ? null :
> buffer.toString();
>
> // end of element, add it to list
> if (b != null && (b.length() > 0 ||
> insideVal)) {
> hashList.put(key,
> b.equalsIgnoreCase("NULL") ? null : b);
> }
>
> insideKey = true;
> insideVal = false;
> buffer = new StringBuffer();
>
> continue;
> }
>
> if (buffer != null)
> buffer.append(chars[i]);
> }
> }
> }
>
> @Override
> public String getValue() {
> if (hashList == null)
> return null;
>
> Iterator iter = hashList.entrySet().iterator();
> if (!iter.hasNext())
> return null;
>
> Entry e = (Entry) iter.next();
> StringBuffer buffer = new StringBuffer();
> appendEntry(buffer, e);
>
> while (iter.hasNext()) {
> e = (Entry) iter.next();
> buffer.append(',');
> appendEntry(buffer, e);
> }
>
> return buffer.toString();
> }
>
> private void appendEntry(StringBuffer buf, Entry e) {
> appendValue(buf, e.getKey(), true);
> buf.append("=>");
> appendValue(buf, e.getValue(), false);
> }
>
> private void appendValue(StringBuffer buf, Object v, boolean isKey)
> {
> if (v == null) {
> if (isKey)
> buf.append("\"NULL\"");
> else
> buf.append("NULL");
> return;
> }
>
> String s = v.toString();
>
> buf.append('"');
> for (int i = 0; i < s.length(); i++) {
> char c = s.charAt(i);
> if (c == '"' || c == '\\')
> buf.append('\\');
> buf.append(c);
> }
> buf.append('"');
> }
>
> public Collection values() {
> return hashList.values();
> }
>
> public int size() {
> return hashList.size();
> }
>
> public Object remove(Object key) {
> return hashList.remove(key);
> }
>
> @SuppressWarnings("unchecked")
> public void putAll(Map m) {
> hashList.putAll(m);
> }
>
> @SuppressWarnings("unchecked")
> public Object put(Object key, Object value) {
> return hashList.put(key, value);
> }
>
> public Set keySet() {
> return hashList.keySet();
> }
>
> public boolean isEmpty() {
> return hashList.isEmpty();
> }
>
> public Set entrySet() {
> return hashList.entrySet();
> }
>
> public boolean containsKey(Object key) {
> return hashList.containsKey(key);
> }
>
> public Object get(Object key) {
> return hashList.get(key);
> }
>
> public boolean containsValue(Object value) {
> return hashList.containsValue(value);
> }
>
> public void clear() {
> hashList.clear();
> }
>
> }
>
>
> //end of pghstore
>
> add it to jdbc properties:
> jdbc://yourjdbcURL?datatype.hstore=gaia.cu7.om.dal.dictionary.PGType.PG
> hstore
>
>
> define the strategy:
> import gaia.cu7.om.dal.dictionary.PGType.PGhstore;
>
> import java.sql.Types;
> import java.util.HashMap;
> import java.util.Map;
>
> import org.apache.openjpa.jdbc.identifier.DBIdentifier;
> import org.apache.openjpa.jdbc.kernel.JDBCStore;
> import org.apache.openjpa.jdbc.meta.JavaSQLTypes;
> import org.apache.openjpa.jdbc.meta.ValueMapping;
> import org.apache.openjpa.jdbc.meta.strats.AbstractValueHandler;
> import org.apache.openjpa.jdbc.schema.Column;
> import org.apache.openjpa.jdbc.schema.ColumnIO;
> import org.apache.openjpa.jdbc.sql.DBDictionary;
> import org.apache.openjpa.util.InternalException;
>
> /**
> * Base class for PG hstore value handlers.
> *
> * @author knienart
> * @version $Id: HStoreValueHandler.java 359484 2014-04-08 13:57:13Z
> knienart $
> * @since 10.0
> */
> @SuppressWarnings("serial")
> public class HStoreValueHandler
> extends AbstractValueHandler {
> private static final String dbTypeName = "hstore";
> private static final int javaSQLType = JavaSQLTypes.PC;
> private static final int jdbcType = Types.OTHER;
> private static final int storeSize = -1;
>
> public Column[] map(ValueMapping vm, String name, ColumnIO io,
> boolean adapt) {
> DBDictionary dict = vm.getMappingRepository().getDBDictionary();
> DBIdentifier colName = DBIdentifier.newColumn(name, dict != null ?
> dict.delimitAll() : false);
> return map(vm, colName, io, adapt);
> }
>
> public Column[] map(ValueMapping vm, DBIdentifier name, ColumnIO io,
> boolean adapt) {
> Column col = new Column();
> col.setIdentifier(name);
> col.setJavaType(javaSQLType);
> col.setSize(storeSize);
> col.setTypeIdentifier(DBIdentifier.newColumnDefinition(dbTypeName));
> col.setType(jdbcType);
>
> return new Column[]{ col };
> }
>
> public Object toDataStoreValue(ValueMapping vm, Object val,
> JDBCStore store) {
> // check for null value.
> if (val == null)
> return null;
> if(!(val instanceof Map))
> throw new InternalException("Wrong type - not a map but "
> + val.getClass().getCanonicalName());
> if(((Map)val).isEmpty())return null;
> PGhstore result = new PGhstore((Map)val);
> return result;
>
> }
>
> public Object toObjectValue(ValueMapping vm, Object val) {
> // check for null value.
> if (val == null)
> return null;
> try {
> /**get the object/string and marshall it into the map<key,value>
> * We could use PGObject mapping here but initialization in JPA is
> problematic
> */
> if(!(val instanceof HashMap))
> throw new InternalException("Wrong type - not a HashMap
> but " + val.getClass().getCanonicalName());
>
> return (Map) val;
> }
> catch (Exception e) {
> e.printStackTrace();
> throw new InternalException(e);
> }
> }
> }
>
> and use it like this i.e.:
>
> @PersistentMap(fetch=FetchType.EAGER )
> @Strategy ("gaia.cu7.om.dal.dictionary.HStoreValueHandler")
> protected Map<String, String> otherStringParameters = new
> HashMap<String,
> String>();
>
> cheers
> Chris
>
>
>
> Boblitz John wrote
> > Hello,
> >
> > I'm trying to get the Postgres HStore type to work and play nice in
> > openJPA 2.3 and I've hit a wall ...
> >
> > Are there any examples around / has anyone done this before and would
> > be willing to share?`
> >
> > Thanks & Best Regards,
> >
> > John
>
>
>
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Postgres-HStore-implementation-
> tp7586887p7586890.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
|