Hi all, happy new Year!!!!!

I have the follow question:

I have a table with 17columns and there's a list where I just wanna show a
table fraction like 10 fields (like a summary table)
Then I created a entity manager native query selecting just 13 fields, but
when the query is executed JPA thows an exception that tell me that
the other 4 fields are not present in the result set to be passed to my
Business Object.

The only way is consulting all the 17 fields mapped ( BTW, I'm using
annotations ) in my native SQL Query.

Dou you know any way to tell JPA that just ignore the mapped fields that are no present in the resulting set??

Here goes the detail!

1. My Business Object is:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.Id;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.Table;
import javax.persistence.Transient;

@Entity
@Table(name = "AD_Language")
@SqlResultSetMapping(
name = "AdLanguageResult",
entities = {
@EntityResult(
entityClass = com.smemax.puntomlm.producto.dao.vo.AdLanguage .class//,
)
}
)
public class AdLanguage {

public AdLanguage( ) {

}

@Id
@Column(name = "AD_LANGUAGE_ID")
private java.lang.Long adLanguageId;

public java.lang.Long getAdLanguageId() {
return adLanguageId;
}

@Id
@Column(name = "AD_LANGUAGE")
private java.lang.String adLanguage;

public java.lang.String getAdLanguage() {
return adLanguage;
}

public void setAdLanguage(java.lang.String adLanguage) {
this.adLanguage = adLanguage;
}

@Column(name = "LANGUAGEISO")
private java.lang.String languageiso;

public java.lang.String getLanguageiso() {
return languageiso;
}

public void setLanguageiso(java.lang.String languageiso) {
this.languageiso = languageiso;
}

@Column(name = "AD_CLIENT_ID")
private java.lang.Long adClientId;

public java.lang.Long getAdClientId() {
return adClientId;
}

public void setAdClientId(java.lang.Long adClientId) {
this.adClientId = adClientId;
}

@Column(name = "AD_ORG_ID")
private java.lang.Long adOrgId;

public java.lang.Long getAdOrgId() {
return adOrgId;
}

public void setAdOrgId(java.lang.Long adOrgId) {
this.adOrgId = adOrgId;
}

@Column(name = "NAME")
private java.lang.String name;

public java.lang.String getName() {
return name;
}

public void setName(java.lang.String name) {
this.name = name;
}

@Column(name = "ISBASELANGUAGE")
private java.lang.String isbaselanguage;

public java.lang.String getIsbaselanguage() {
return isbaselanguage;
}

public void setIsbaselanguage(java.lang.String isbaselanguage) {
this.isbaselanguage = isbaselanguage;
}

@Column(name = "ISACTIVE")
private java.lang.String isactive;

public java.lang.String getIsactive() {
return isactive;
}

public void setIsactive(java.lang.String isactive) {
this.isactive = isactive;
}

@Column(name = "CREATED")
private java.sql.Timestamp created;

public java.sql.Timestamp getCreated() {
return created;
}

public void setCreated(java.sql.Timestamp created) {
this.created = created;
}

@Column(name = "CREATEDBY")
private java.lang.Long createdby;

public java.lang.Long getCreatedby() {
return createdby;
}

public void setCreatedby(java.lang.Long createdby) {
this.createdby = createdby;
}

@Column(name = "UPDATED")
private java.sql.Timestamp updated;

public java.sql.Timestamp getUpdated() {
return updated;
}

public void setUpdated(java.sql.Timestamp updated) {
this.updated = updated;
}

@Column(name = "UPDATEDBY")
private java.lang.Long updatedby;

public java.lang.Long getUpdatedby() {
return updatedby;
}

public void setUpdatedby(java.lang.Long updatedby) {
this.updatedby = updatedby;
}

@Column(name = "ISSYSTEMLANGUAGE")
private java.lang.String issystemlanguage;

public java.lang.String getIssystemlanguage() {
return issystemlanguage;
}

public void setIssystemlanguage(java.lang.String issystemlanguage) {
this.issystemlanguage = issystemlanguage;
}

@Column(name = "COUNTRYCODE")
private java.lang.String countrycode;

public java.lang.String getCountrycode() {
return countrycode;
}

public void setCountrycode(java.lang.String countrycode) {
this.countrycode = countrycode;
}

@Column(name = "PROCESSING")
private java.lang.String processing;

public java.lang.String getProcessing() {
return processing;
}

public void setProcessing(java.lang.String processing) {
this.processing = processing;
}

public void setAdLanguageId(java.lang.Long adLanguageId) {
this.adLanguageId = adLanguageId;
}

@Column(name = "PIXELSIZE")
private java.lang.Long pixelsize;

public java.lang.Long getPixelsize() {
return pixelsize;
}

public void setPixelsize(java.lang.Long pixelsize) {
this.pixelsize = pixelsize;
}

@Column(name = "TRANSLATEDBY")
private java.lang.String translatedby;

public java.lang.String getTranslatedby() {
return translatedby;
}

public void setTranslatedby(java.lang.String translatedby) {
this.translatedby = translatedby;
}

@Transient
private String adClientdescription;

public String getAdClientdescription() {
return adClientdescription;
}

public void setAdClientdescription(String adClientdescription) {
this.adClientdescription = adClientdescription;
}

@Transient
private String adOrgdescription;

public String getAdOrgdescription() {
return adOrgdescription;
}

public void setAdOrgdescription(String adOrgdescription) {
this.adOrgdescription = adOrgdescription;
}
}

2. The mapped attributes are:
LANGUAGEISO,
AD_LANGUAGE,
AD_CLIENT_ID,
AD_ORG_ID,
NAME,
ISBASELANGUAGE,
ISACTIVE,
ISSYSTEMLANGUAGE,
COUNTRYCODE,
PROCESSING,
AD_LANGUAGE_ID,
PIXELSIZE,
TRANSLATEDBY,
CREATED,
CREATEDBY,
UPDATED,
UPDATEDBY,

3. I have the follow query:
SELECT LANGUAGEISO,
AD_LANGUAGE,
AD_CLIENT_ID,
AD_ORG_ID,
NAME,
ISBASELANGUAGE,
ISACTIVE,
ISSYSTEMLANGUAGE,
COUNTRYCODE,
PROCESSING,
AD_LANGUAGE_ID,
PIXELSIZE,
TRANSLATEDBY FROM AD_LANGUAGE

4. The fields below are no present in the sql query:

CREATED,
CREATEDBY,
UPDATED,
UPDATEDBY,


5. I use the next code to query the database:
Query query = entityManager.createNativeQuery( sqlQuery , "AdLanguageResult" );
query.getResultList( )


6. The @SqlResultSetMapping is:
@SqlResultSetMapping(
name = "AdLanguageResult",
entities = {
@EntityResult(
entityClass = com.smemax.puntomlm.producto.dao.vo.AdLanguage .class
)
}
)


7. When I execute the query.getResultList( ) the next error is thrown:

Hibernate: SELECT LANGUAGEISO, AD_LANGUAGE, AD_CLIENT_ID, AD_ORG_ID, NAME, ISBASELANGUAGE, ISACTIVE, ISSYSTEMLANGUAGE, COUNTRYCODE, PROCESSING, AD_LANGUAGE_ID, PIXELSIZE, TRANSLATEDBY FROM AD_Language
4109 [main] INFO org.hibernate.type.TimestampType - could not read column value from result set: CREATED; [ibm][db2][jcc][10150][10300] Invalid parameter: Unknown column name CREATED.
4141 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -99999, SQLState: null
4141 [main] ERROR org.hibernate.util.JDBCExceptionReporter - [ibm][db2][jcc][10150][10300] Invalid parameter: Unknown column name CREATED.
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:637)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:74)
at com.smemax.puntomlm.producto.dao.impl.AdLanguageDAOImpl.innerFindFilteder(AdLanguageDAOImpl.java:145)
at com.smemax.puntomlm.producto.dao.impl.AdLanguageDAOImpl.findFiltered(AdLanguageDAOImpl.java:112)
at com.smemax.puntomlm.producto.dao.impl.AdLanguageDAOImpl.findAll(AdLanguageDAOImpl.java:100)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy15.findAll(Unknown Source)
at com.smemax.puntomlm.producto.facade.impl.AdLanguageFacadeImpl.findAll(AdLanguageFacadeImpl.java:64)
at com.smemax.puntomlm.producto.test.AdLanguageTest.main(AdLanguageTest.java:21)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:65)
... 16 more
Caused by: com.ibm.db2.jcc.c.SqlException: [ibm][db2][jcc][10150][10300] Invalid parameter: Unknown column name CREATED.
at com.ibm.db2.jcc.c.n.a(n.java:1323)
at com.ibm.db2.jcc.c.wf.a(wf.java:1252)
at com.ibm.db2.jcc.c.wf.getTimestamp(wf.java:1104)
at org.hibernate.type.TimestampType.get(TimestampType.java:53)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2124)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1404)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1332)
at org.hibernate.loader.Loader.getRow(Loader.java:1230)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:603)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 23 more
Exception in thread "main" java.lang.NullPointerException
at com.smemax.puntomlm.producto.test.AdLanguageTest.main(AdLanguageTest.java:22)

Hello all!!

There’s anybody that also think that make a query to all the rows in a tables is not the way to get the best performance? I’m using objects, memory, cpu, etc, when I just don’t need it.

What about if I have a table with 50 or 100 fields and some of this are referencing to other compose objects (an association)?? Do I need to query all the 50 or 100 fields to show just 10 in my lists??

Now, if I have n hundred rows with the same behavior (a table with 50 or 100 fields and some of this are referencing to other compose object (an association))???

I mean, everybody knows that in the application lists we don’t show all the table fields, so, why am I forced to query all if I just need a fraction?

read hibernate reference documentation about HQL query language that allows you to select just the parts you want.

Hi Max!!!
Thanks for your reply

I did what u suggest me and I read the HQL reference, and for the HQL based queries it works, the only thing that I realize is that when I execute the HQL query with fields in the select statement jpa does not returns the original mapped object with the selected fields filled, instead it returns an array with objects and every object in the array represents an attribute in the mapped class, this means that if I want to create the original mapped object based on the query result I have to create some kind of mapper.

Now, my issue is oriented to using native SQL query, we already have defined a bunch of native sql query and we want to keep using it, the problems is that when I try to use some native SQL queries that does not have all the mapped fields in the select statement the error that I mentioned above appears!!

May be do u have any idea about how to make this works?!!!

Any Idea Thanks!! Thanks!! Thanks!! Thanks!! in advance!!!

[/b]

Have you read this
http://www.hibernate.org/hib_docs/v3/reference/en-US/html/querysql.html
and try to use addEntity()?

Christophe

setResultTransformer can do the mapping for you, but just realize these are not entities just data objects that can't be used for anything but reporting.

Hi Croudet!!

Thanks for your reply!!

this was my test:

1) I'm using JPA as persistence layer, so to get the Hibernate Session I should use the method EntityManager.getDelegate(), then I use the Session reference to invoke the session.createSQLQuery() with my native SQL query like this:

SQLQuery queryH = session.createSQLQuery( "SELECT LANGUAGEISO, AD_LANGUAGE, AD_CLIENT_ID, AD_ORG_ID, NAME, ISBASELANGUAGE, ISACTIVE, ISSYSTEMLANGUAGE, COUNTRYCODE, PROCESSING, AD_LANGUAGE_ID, PIXELSIZE, TRANSLATEDBY FROM AD_LANGUAGE " );

Once again I exclude this fields fields from the query:
CREATED,
CREATEDBY,
UPDATED,
UPDATEDBY,


2) Add the mapped entity to the SQLQuery

queryH.addEntity( AdLanguage .class );

3) Make the query:

List result = queryH.list( );

When I execute the SQLQuery.list() method the same exception is thrown:

Caused by: com.ibm.db2.jcc.c.SqlException: [ibm][db2][jcc][10150][10300] Invalid parameter: Unknown column name CREATED.

When I add the field to the SQL Query everthing is fine :(

SQLQuery queryH = session.createSQLQuery( "SELECT LANGUAGEISO, AD_LANGUAGE, AD_CLIENT_ID, AD_ORG_ID, NAME, ISBASELANGUAGE, ISACTIVE, ISSYSTEMLANGUAGE, COUNTRYCODE, PROCESSING, AD_LANGUAGE_ID, PIXELSIZE, TRANSLATEDBY, CREATED FROM AD_LANGUAGE " );


Now, there's someting to notice, and is that, if a field is not mapped in the class you can use it in the query and it executes fine :O

I mean, may be in the table exist AD_LANGUAGE a field like FIELD1, and in the class does not exist a @Column(name = "FIELD1") then you can use FIELD1 in the SQLQuery!!

Hi Max!!
Thanks for your reply!!!

The ResultTransformer works pretty good, this's the code in my test:

The question now is: there's any way to do the same with JPA and the entity manager?? or I have to use the underlying hibernate implementation for this???


public class SampleTransformer implements ResultTransformer {

public List transformList(List arg0) {

return arg0;
}

public Object transformTuple(Object[] arg0, String[] arg1) {
AdTable adTable = new AdTable( );
for( int index = 0; index < arg1.length; index++ ){
String methodName = NamesUtils.getFormatedMethodName( arg1[ index ] );
System.out.println( "AdTable.set" + methodName );
try{
Method method = adTable.getClass().getMethod( "set" + methodName, new Class[]{ arg0[ index ].getClass( ) } );
method.invoke( adTable, new Object[]{ arg0[ index ] } );
}catch (Exception e) {
e.printStackTrace( );
}
}
return adTable;
}

}

Look in Hibernate entitymanager how to use .getDelegate() to get access to hibernate specific api.

about your transformer you dont have to implement that one, just use one of the prebaked ones on Transformers.

Thanks, Thanks, Thanks a lot croudet and max for your replies!!