Skip to main content

Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Querying a table with an array column fails
Querying a table with an array column fails [message #644818] Tue, 14 December 2010 10:36 Go to next message
Daniel  is currently offline Daniel Friend
Messages: 2
Registered: December 2010
Junior Member

I'm having trouble with querying a table which consists of a serial and a varchar-array column.

The (test) database layout is:
create database arraytest;
create schema arraytest;
create table array_test (id serial primary key, arr varchar(64)[]);
insert into arraytest.array_test (arr) values ('{"some", "value"}');

The entity code is:
@Table(name = "array_test", catalog = "arraytest", schema = "arraytest")
    /*Deleted automatically generated queries. */
public class ArrayTest implements Serializable {
    private static final long serialVersionUID = 1L;
@Column(name = "id", nullable = false)
    private Integer id;
    @Column(name = "arr")
    private List<String> arr = new ArrayList<String>();
/* Getter and Setter methods */

Unfortunately executing the standard .findAll query fails with the following exception:
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: cross-database references are not implemented: "arraytest.arraytest.array_test"
Error Code: 0
Call: SELECT id FROM arraytest.arraytest.array_test
Query: ReadAllQuery(name="ArrayTest.findAll" referenceClass=ArrayTest sql="SELECT id FROM arraytest.arraytest.array_test")
at org.eclipse.persistence.exceptions.DatabaseException.sqlExce ption(

Doing the same query in psql works fine.
System: Netbeans 6.9.1, Glassfish 3.0.1, eclipselink 2.0.2., postgresql 9.0

Any ideas?

Thanks in advance!
Re: Querying a table with an array column fails [message #645103 is a reply to message #644818] Wed, 15 December 2010 15:25 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

JPA does not support object-relational data-types such as PostgreSQL's array type.

In JPA, and @ElementCollection mapping is used to store a basic collection in another table. You need to have this other table, not an array type.

If you need to use the PostgreSQL array type depending on how PostgreSQL supports this through JDBC, you should be able to use the ArrayMapping in EclipseLink (through a DescriptorCustomizer). We have only tested this with Oralce's object-relational data-type support, but it should work with any database that supports object-relational data-types, however you will probably need to customize your PostgreSQLPlatform.

James : Wiki : Book : Blog : Twitter
Previous Topic:JPA 2, problem with ORDER BY AVG
Next Topic:EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance issues
Goto Forum:

Current Time: Mon Mar 30 10:31:58 GMT 2020

Powered by FUDForum. Page generated in 0.04198 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top