Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » NamedNativeQuery(can not cast to class)
NamedNativeQuery [message #531708] Wed, 05 May 2010 16:54 Go to next message
Larry  is currently offline 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
Re: NamedNativeQuery [message #531916 is a reply to message #531708] Thu, 06 May 2010 10:09 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
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.
Re: NamedNativeQuery [message #532261 is a reply to message #531916] Fri, 07 May 2010 13:45 Go to previous messageGo to next message
Larry  is currently offline 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!!!
Re: NamedNativeQuery [message #532272 is a reply to message #531708] Fri, 07 May 2010 14:21 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
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
Previous Topic:Filtering a OneToMany relationship
Next Topic:Delay betwen persist and find of a relationship
Goto Forum:
  


Current Time: Thu Apr 24 01:36:24 EDT 2014

Powered by FUDForum. Page generated in 0.01620 seconds