You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Boblitz John <jo...@bertschi.com> on 2014/07/01 08:34:22 UTC
RE: Postgres HStore implementation
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.
Re: Postgres HStore implementation
Posted by Kevin Sutter <kw...@gmail.com>.
Thanks, John. That write-up would be helpful.
Thanks,
Kevin
On Tue, Jul 1, 2014 at 1:34 AM, Boblitz John <jo...@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: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.
>