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:[email protected]] > Sent: Montag, 30. Juni 2014 15:49 > To: [email protected] > 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.
