Skip to main content



      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 05:36 Go to next message
Eclipse UserFriend
Hello,

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:
@Entity
@Table(name = "array_test", catalog = "arraytest", schema = "arraytest")
@NamedQueries({
    /*Deleted automatically generated queries. */
})
public class ArrayTest implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
@Column(name = "id", nullable = false)
    private Integer id;
    @Column(name = "arr")
    @ElementCollection
    private List<String> arr = new ArrayList<String>();
/* Getter and Setter methods */
}

Unfortunately executing the standard .findAll query fails with the following exception:
Quote:
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(DatabaseException.java:333)



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!
Daniel
Re: Querying a table with an array column fails [message #645103 is a reply to message #644818] Wed, 15 December 2010 10:25 Go to previous message
Eclipse UserFriend
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.
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 Jul 07 00:41:33 EDT 2025

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

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

Back to the top