Thanks, John. That write-up would be helpful. Thanks, Kevin
On Tue, Jul 1, 2014 at 1:34 AM, Boblitz John <john.bobl...@bertschi.com> wrote: > 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:yaz...@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. >
