Querying a table with an array column fails [message #644818] |
Tue, 14 December 2010 10:36 |
Daniel Messages: 2 Registered: December 2010 |
Junior Member |
|
|
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 15:25 |
|
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
|
|
|
Powered by
FUDForum. Page generated in 0.02678 seconds