Eclipse Community Forums: EclipseLink

Help

Home

Home » Eclipse Projects » EclipseLink » NamedNativeQuery(can not cast to class)

Show: Today's Messages :: Show Polls :: Message Navigator

NamedNativeQuery [message #531708]

Wed, 05 May 2010 20:54

Larry

Messages: 30
Registered: December 2009

Member

I have the following query:

@NamedNativeQuery(
name="getCar",
query="select * from car where id = 10 and type= 1")

I did setup a Car Entity that runs fine using the following code:
Car car= em.find(Car .class, new Car Pk(101000,1));

When I execute the following:
Car c = (Car ) em.createNamedQuery("getCar").getSingleResult();

I get the following error stack:

Exception in thread "main" java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.jimbo.jpa.Car
at Driver.doTest4(Driver.java:66)
at Driver.run(Driver.java:34)
at Driver.main(Driver.java:83)

Now, if I change my query buy adding a resultClass:
@NamedNativeQuery(
name="getCar",
query="select * from car where id = 10 and type= 1",
resultClass=Car.class)

I then get the following stack error:
Exception Description: The primary key read from the row [DatabaseRecord(
=> 10
=> 1
car.size => 10000
=> null)] during the execution of the query was detected to be null. Primary keys must not contain null.

Can someone tell me what I'm doing wrong? It seems in the last attempt using resultClass, that all fields are being treated as part of the primary key. I have the CarPk setup with the id and type as the ids.

Thanks

Report message to a moderator

Re: NamedNativeQuery [message #531916 is a reply to message #531708]

Thu, 06 May 2010 14:09

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Hello,

For the first attempt, you are specifying a native query but not what it will return, so you get back raw data instead of car objects. This results in the classcastException when your app tries to cast it to a Car.

The second attempt is failing for what looks like case issues. What database are you using, and how are the columns in Car defined? Some database such as Oracle return uppercase field names, where as others such as postgres return lowercase (unless they are delimited). If the database does not return them the same as they are defined in the car object, you are hitting the issue described in bug 299926 which is fixed in the nightly builds. You can workaround it by using a resultset mapping so that the field names are defined as the database will return them, or define the field names in Car to match what will be returned eactly.

Report message to a moderator

Re: NamedNativeQuery [message #532261 is a reply to message #531916]

Fri, 07 May 2010 17:45

Larry

Messages: 30
Registered: December 2009

Member

We are using Informix 11.5

Not sure what's going on. I did have one Enitity/table work fine with a native query. But most do not. I think it might have something to do with the columns, but not sure what. I will give all files to show what I'm doing and maybe someone can see a mistake.

Adr Entity:

@Entity
@Table(name="adr")
@NamedNativeQuery(
name="getAdr",
query="select doc,acct,addr_num," +
"NVL(street,'aa')," +
"NVL(city, 'aa')," +
"NVL(state,'aa')," +
"NVL(zip,'aa')," +
"NVL(zip4,' ') " +
" from adr",resultClass=Adr.class)
public class Adr implements Serializable {
private static final long serialVersionUID = 1L;

//@EmbeddedId
//private AdrPK id;

@Id private int doc;
@Id private int acct;

@Column(name="addr_num", length=1)
private String addrNum;

@Column(name="addr_or_school", length=1)
private String addrOrSchool;

@Column(length=20)
private String city;

@Column(length=2)
private String state;

@Column(length=40)
private String street;

@Column(length=5)
private String zip;

@Column(length=4)
private String zip4;

public Adr() {
}

... getters and setters

Executing:
List actList = em.createNamedQuery("getAdr").getResultList();

thrown error:
Exception Description: The primary key read from the row [DatabaseRecord(
=> 101000
=> 3
adr.addr_num => 1
=> aa
=> aa
=> aa
=> 00000
=> aa)] during the execution of the query was detected to be null. Primary keys must not contain null.
Query: ReadAllQuery(name="getAdr" referenceClass=Adr sql="select doc,acct,addr_num,NVL(street,'aa'),NVL(city, 'aa'),NVL(state,'aa'),NVL(zip,'aa'),NVL(zip4,'aa') from adr")

I also tried using '*' instead of selecting each individual field.

I also downloaded v2.0.3 from nightly build to get the column bug fix.

It works fine if just get raw data - only have problems with the resultClass!!!

Any help would be great!!!

Report message to a moderator

Re: NamedNativeQuery [message #532272 is a reply to message #531708]

Fri, 07 May 2010 18:21

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Hello,

I believe Informix returns column names in lower case, so you are likely hitting the issue described in bug 299926. The nightly you used is not the nightly with the fix - it is targetd for 2.1, and so would be in a nightly download from the 2.1 builds at:
http://www.eclipse.org/eclipselink/downloads/nightly.php

The problem again will be that the column names for attributes doc, acct etc that are not explicitely defined will default in JPA to upper case "DOC", "ACCT" etc. Since Informix returns them in lower case, it gets null when it searches the results for "DOC" even though there maybe a value for the "doc" column.

You can get around this by using aliases in your native SQL to force the database to return the field name+case expected. Ie
"select doc as /"DOC/", acct as /"ACCT/" ....
Or, you can tell EclipseLink what to look for in the SQL using a resultsetmapping - telling it that the field for attribute doc will be returned as "doc" instead of the default "DOC".
Or, you can define the fields in the annotation definitions to match what will be returned. ie :
@Id @Column(name="doc") private int doc;

Just a warning on using native sql to return entities. The entities returned will be managed and cached. This means that any data that gets populated via functions will be assumed to exist in the field. Any subsequent fetches or finds that return that entity will have that same data in it

Best Regards,
Chris

Report message to a moderator

Previous Topic:	Filtering a OneToMany relationship
Next Topic:	Delay betwen persist and find of a relationship

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 13:28:05 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter