createNativeQuery and SqlResultSetMapping related question

tariqahsan · **Joined:** Thu Dec 18, 2008 1:40 pm **Posts:** 8

Hello All,

Learning to use Hibernate, JPA stuff. Trying to use createNativeQuery to which is getting common column values from 2 database tables by using Oracle's UNION function. Eventually need to get the field values from the query output assigned to corresponding variables of the code. I could have created a view from similar query used here and create the entity class using Hibernate Code Generator. That would have been lot easier for me to do. But just wanted to find out how I can do the same using createNativeQuery.

Any help or hints would be greatly appreciated.

Here's snippets of the codes I have along with the error message being generated -

Code:

public class table1 extends java.io.Serializable {

import javax.persistence.Query;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;

...

@Entity
@Table(name = "TABLE1", uniqueConstraints = {})
@SqlResultSetMapping(name="testing", entities = 
        @EntityResult(entityClass=table1.class, fields= {
            @FieldResult(name="col1", column="col1"),
            @FieldResult(name="col2", column="col2"),
            @FieldResult(name="col3", column="col3"),
        })
)

...

public class TestAction  implements TestInterface {

public String col1;
...

@DataModel
private List<table1> queryresults;
...

// Here's sql statement for a Oracle 9i database
String sqlStmt = " select col1, col2, col3, col4  from (select col1, col2, col3, col4" +   
             "from table1" +    
             "union " +   
             "select col1, col2, col3, col4" +   
             "from table2) " +   
             "where col1 = :col1";  
queryresults = em.createNativeQuery(sqlStmt, "testing").getResultList();   

// Not sure this would be the riqht way to get individual field value from
// the query result output list.
col1 = queryresults.get(0).getCol1();

...

But getting org.hibernate.QueryException: Not all named parameters have been set: ... error

Madan_Prabhu · **Posted:** Tue Jan 20, 2009 8:02 am

u r missing to set the value for col1 in [where col1 = :col1]
so use setParameter() to set the input parameter in where clause.

for example.,
setParameter("col1", column1, Hibernate.INTEGER);

tariqahsan · **Joined:** Thu Dec 18, 2008 1:40 pm **Posts:** 8

Thanks Madan,

Exactly that what I was missing - setParameter("col1", col1) in the createNativeQuery statement -

So, it worked with the following -

queryresults = em.createNativeQuery(sqlStmt, "testing").setParameter("col1", col1).getResultList();

Now the problem I am having is to extract the individual column values and set it to the entity variables of table1.java.

queryList is an List object of type 'table1'. The columns of table in the query are type - VARCHAR2, DATE and NUMBER.
The query is going to return only one single row. So the List object of type 'table1' would only have only one element in it.

Getting to the error - java.lang.ClassCastException: [Ljava.lang.Object; ...

now while trying to do something like this -

col1 = queryresults.get(0).getCol1();
...

Any idea how can iterate through the list object to get the column values and set to it's corresponding variables of it's respective data type?

Thanks