Select com condição em View (com JPA)

Programação Java
#1

Oi pessoal!!

Estou tendo um probleminha aqui com query em uma View e queria saber se alguém sabe alguma solução ou se realmente não há solução… rs…

Bom, é o seguinte: estou fazendo um select em uma view, com JPA, mas passao uma condição:

select stGeo 
from GeoPendingStandProperties stGeo
where stGeo.pk.standAdmDivCode = :stAdmDivCode
and stGeo.pk.standNucleusCode = :stNucleusCode
and stGeo.pk.standMngUnitCode = :stMngUnitCode
and stGeo.pk.standCode = :standCode
and stGeo.pk.standRevisionNum = :stRevisionNum

GeoPendingStandProperties é a View, e os campos da condição são todos String, com exceção do “standRevisionNum” que é int.

E ao tentar fazer esta pesquisa, é lançado o seguinte erro:

javax.ejb.TransactionRolledbackLocalException: java.lang.IllegalArgumentException: An exception occured while creating a query in EntityManager; nested exception is: java.lang.IllegalArgumentException: An exception occured while creating a query in EntityManager
java.lang.IllegalArgumentException: An exception occured while creating a query in EntityManager
	at oracle.toplink.essentials.internal.ejb.cmp3.EntityManagerImpl.createQuery(EntityManagerImpl.java:194)
	at com.evermind.server.ejb.persistence.AbstractEntityManagerProxy.createQuery(AbstractEntityManagerProxy.java:103)
	at com.evermind.server.ejb.persistence.EntityManagerProxy.createQuery(EntityManagerProxy.java:69)
	at com.mtcx.nap.landManagement.stands.dao.StandDAOBean.listPendingStandProperties(StandDAOBean.java:285)
	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 com.evermind.server.ejb.interceptor.joinpoint.EJBJoinPointImpl.invoke(EJBJoinPointImpl.java:35)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.SetContextActionInterceptor.invoke(SetContextActionInterceptor.java:44)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.TxRequiredInterceptor.invoke(TxRequiredInterceptor.java:50)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.InvocationContextPool.invoke(InvocationContextPool.java:55)
	at com.evermind.server.ejb.StatelessSessionEJBObject.OC4J_invokeMethod(StatelessSessionEJBObject.java:87)
	at StandDAOBean_LocalProxy_c7e6f1.listPendingStandProperties(Unknown Source)
	at com.mtcx.nap.landManagement.stands.StandManagerBean.listPendingStandProperties(StandManagerBean.java:395)
	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 com.evermind.server.ejb.interceptor.joinpoint.EJBJoinPointImpl.invoke(EJBJoinPointImpl.java:35)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.SetContextActionInterceptor.invoke(SetContextActionInterceptor.java:44)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.TxRequiredInterceptor.invoke(TxRequiredInterceptor.java:50)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.SecurityRoleInterceptor.invoke(SecurityRoleInterceptor.java:47)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
	at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
	at com.evermind.server.ejb.InvocationContextPool.invoke(InvocationContextPool.java:55)
	at com.evermind.server.ejb.StatelessSessionEJBObject.OC4J_invokeMethod(StatelessSessionEJBObject.java:87)
	at StandManagerBean_LocalProxy_c7e6f1.listPendingStandProperties(Unknown Source)
	at com.mtcx.nap.landManagement.web.stands.StandBackingBean.listPendingStandProperties(StandBackingBean.java:657)
	at com.mtcx.nap.landManagement.web.stands.StandBackingBean.getPendingStandProperties(StandBackingBean.java:178)
	at com.mtcx.nap.landManagement.web.stands.StandBackingBean.getTotalArea(StandBackingBean.java:376)
	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 javax.el.BeanELResolver.getValue(BeanELResolver.java:292)
	at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:143)
	at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:73)
	at com.sun.el.parser.AstValue.getValue(Unknown Source)
	at com.sun.el.parser.AstDiv.getValue(Unknown Source)
	at com.sun.el.ValueExpressionImpl.getValue(Unknown Source)
	at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
	at javax.faces.component.UIOutput.getValue(UIOutput.java:184)
	at sun.reflect.GeneratedMethodAccessor939.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at com.sun.facelets.util.DevTools.writeAttributes(DevTools.java:240)
	at com.sun.facelets.util.DevTools.writeStart(DevTools.java:284)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:189)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.writeComponent(DevTools.java:207)
	at com.sun.facelets.util.DevTools.debugHtml(DevTools.java:107)
	at com.sun.facelets.FaceletViewHandler.handleRenderException(FaceletViewHandler.java:677)
	at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:645)
	at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:108)
	at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:216)
	at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:110)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
	at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:266)
	at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65)
	at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:141)
	at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:281)
	at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
	at oracle.security.jazn.oc4j.JAZNFilter.doFilter(JAZNFilter.java:436)
	at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
	at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
	at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
	at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
	at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
	at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
	at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
	at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
	at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
	at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
	at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
	at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:298)
	at java.lang.Thread.run(Thread.java:595)
Caused by: Exception [TOPLINK-8030] (Oracle TopLink Essentials - 2.0 (Build b41-beta2 (03/30/2007))): oracle.toplink.essentials.exceptions.EJBQLException
Exception Description: Error compiling the query [
                        select stGeo 
                        from GeoPendingStandProperties stGeo
                        where stGeo.pk.standAdmDivCode = :stAdmDivCode
                        and stGeo.pk.standNucleusCode = :stNucleusCode
                        and stGeo.pk.standMngUnitCode = :stMngUnitCode
                        and stGeo.pk.standCode = :standCode
                        and stGeo.pk.standRevisionNum = :stRevisionNum
                    ], line 1, column 140: unknown state or association field [standAdmDivCode] of class [com.mtcx.nap.landManagement.stands.GeoPendingStandPropertiesPk].
	at oracle.toplink.essentials.exceptions.EJBQLException.unknownAttribute(EJBQLException.java:459)
	at oracle.toplink.essentials.internal.parsing.DotNode.validate(DotNode.java:86)
	at oracle.toplink.essentials.internal.parsing.Node.validate(Node.java:100)
	at oracle.toplink.essentials.internal.parsing.BinaryOperatorNode.validate(BinaryOperatorNode.java:43)
	at oracle.toplink.essentials.internal.parsing.EqualsNode.validate(EqualsNode.java:50)
	at oracle.toplink.essentials.internal.parsing.Node.validate(Node.java:100)
	at oracle.toplink.essentials.internal.parsing.LogicalOperatorNode.validate(LogicalOperatorNode.java:48)
	at oracle.toplink.essentials.internal.parsing.Node.validate(Node.java:100)
	at oracle.toplink.essentials.internal.parsing.LogicalOperatorNode.validate(LogicalOperatorNode.java:48)
	at oracle.toplink.essentials.internal.parsing.Node.validate(Node.java:100)
	at oracle.toplink.essentials.internal.parsing.LogicalOperatorNode.validate(LogicalOperatorNode.java:48)
	at oracle.toplink.essentials.internal.parsing.Node.validate(Node.java:100)
	at oracle.toplink.essentials.internal.parsing.LogicalOperatorNode.validate(LogicalOperatorNode.java:48)
	at oracle.toplink.essentials.internal.parsing.WhereNode.validate(WhereNode.java:43)
	at oracle.toplink.essentials.internal.parsing.ParseTree.validate(ParseTree.java:226)
	at oracle.toplink.essentials.internal.parsing.ParseTree.validate(ParseTree.java:202)
	at oracle.toplink.essentials.internal.parsing.ParseTree.validate(ParseTree.java:192)
	at oracle.toplink.essentials.internal.parsing.EJBQLParseTree.populateReadQueryInternal(EJBQLParseTree.java:119)
	at oracle.toplink.essentials.internal.parsing.EJBQLParseTree.populateQuery(EJBQLParseTree.java:93)
	at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:204)
	at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:174)
	at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:138)
	at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.<init>(EJBQueryImpl.java:99)
	at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.<init>(EJBQueryImpl.java:84)
	at oracle.toplink.essentials.internal.ejb.cmp3.EJBQueryImpl.<init>(EJBQueryImpl.java:71)
	at oracle.toplink.essentials.internal.ejb.cmp3.EntityManagerImpl.createQuery(EntityManagerImpl.java:189)
	... 98 more

O detalhe maior é que na classe GeoPendingStandPropertiesPk existe o campo que ele diz não encontrar:

@Embeddable
public class GeoPendingStandPropertiesPk implements Serializable {
    
    //pendding stand attibutes:
    private String _standAdmDivCode;
    private String _standNucleusCode;
    private String _standMngUnitCode;
    private String _standCode;
    private Integer _standRevisionNum;
    
    //active property attibutes:
    private String _propAdmDivCode;
    private String _propNucleusCode;
    private String _propCode;
    
    
    /** Creates a new instance of GeoPendingStandProperties */
    public GeoPendingStandPropertiesPk() {
    }

    @Column(name="S_AD_CODE")
    public String getStandAdmDivCode() {
        return _standAdmDivCode;
    }

    public void setStandAdmDivCode(String standAdmDivCode) {
        this._standAdmDivCode = standAdmDivCode;
    }

    @Column(name="S_N_CODE")
    public String getStandNucleusCode() {
        return _standNucleusCode;
    }

    public void setStandNucleusCode(String standNucleusCode) {
        this._standNucleusCode = standNucleusCode;
    }

    @Column(name="S_MU_CODE")
    public String getStandMngUnitCode() {
        return _standMngUnitCode;
    }

    public void setStandMngUnitCode(String standMngUnitCode) {
        this._standMngUnitCode = standMngUnitCode;
    }

    @Column(name="S_CODE")
    public String getStandCode() {
        return _standCode;
    }

    public void setStandCode(String standCode) {
        this._standCode = standCode;
    }

    @Column(name="S_REVISION_NUM")
    public Integer getStandRevisionNum() {
        return _standRevisionNum;
    }

    public void setStandRevisionNum(Integer standRevisionNum) {
        this._standRevisionNum = standRevisionNum;
    }

    @Column(name="P_AD_CODE")
    public String getPropAdmDivCode() {
        return _propAdmDivCode;
    }

    public void setPropAdmDivCode(String propAdmDivCode) {
        this._propAdmDivCode = propAdmDivCode;
    }

    @Column(name="P_N_CODE")
    public String getPropNucleusCode() {
        return _propNucleusCode;
    }

    public void setPropNucleusCode(String propNucleusCode) {
        this._propNucleusCode = propNucleusCode;
    }

    @Column(name="P_CODE")
    public String getPropCode() {
        return _propCode;
    }

    public void setPropCode(String propCode) {
        this._propCode = propCode;
    } 
    
    @Override
    public boolean  equals(Object obj) {
        
        if ( obj instanceof GeoPendingStandPropertiesPk ) {
            GeoPendingStandPropertiesPk pk = (GeoPendingStandPropertiesPk)obj;
            
            return ( this.getPropAdmDivCode().equals(pk.getPropAdmDivCode()) && 
                    this.getPropNucleusCode().equals(pk.getPropNucleusCode()) && 
                    this.getPropCode().equals(pk.getPropCode()) && 
                    this.getStandAdmDivCode().equals(pk.getStandAdmDivCode()) && 
                    this.getStandNucleusCode().equals(pk.getStandNucleusCode()) && 
                    this.getStandMngUnitCode().equals(pk.getStandMngUnitCode()) && 
                    this.getStandCode().equals(pk.getStandCode()) &&
                    this.getStandRevisionNum().equals(pk.getStandRevisionNum())); 
                    
        } else return false;
    }
}

@Entity
@Table(name="LM_GEO_PENDING_STAND_PROP")
public class GeoPendingStandProperties implements Serializable {
    
    private GeoPendingStandPropertiesPk _pk;
    
    private Double _intersectionArea;
    private Double _totalArea;
    
    /** Creates a new instance of GeoPendingStandProperties */
    public GeoPendingStandProperties() {
    }

    @EmbeddedId
    public GeoPendingStandPropertiesPk getPk() {
        if ( _pk == null )
            _pk = new GeoPendingStandPropertiesPk();
        return _pk;
    }

    public void setPk(GeoPendingStandPropertiesPk pk) {
        this._pk = pk;
    }

    @Column(name="INTERSECTION_AREA")
    public Double getIntersectionArea() {
        return _intersectionArea;
    }

    public void setIntersectionArea(Double intersectionArea) {
        this._intersectionArea = intersectionArea;
    }

    @Column(name="TOTAL_AREA")
    public Double getTotalArea() {
        return _totalArea;
    }

    public void setTotalArea(Double totalArea) {
        this._totalArea = totalArea;
    }
    
    @Override
    public boolean  equals(Object obj) {
        
        if ( obj instanceof GeoPendingStandProperties ) {
            GeoPendingStandProperties stand = (GeoPendingStandProperties)obj;
            
            return ( this._pk.getPropAdmDivCode().equals(stand.getPk().getPropAdmDivCode()) && 
                    this._pk.getPropNucleusCode().equals(stand.getPk().getPropNucleusCode()) && 
                    this._pk.getPropCode().equals(stand.getPk().getPropCode()) && 
                    this._pk.getStandAdmDivCode().equals(stand.getPk().getStandAdmDivCode()) && 
                    this._pk.getStandNucleusCode().equals(stand.getPk().getStandNucleusCode()) && 
                    this._pk.getStandMngUnitCode().equals(stand.getPk().getStandMngUnitCode()) && 
                    this._pk.getStandCode().equals(stand.getPk().getStandCode()) && 
                    this._pk.getStandRevisionNum().equals(stand.getPk().getStandRevisionNum()) );
            
        } else return false;
    }
}

Alguém já teve um problema assim? :?
Thanks.

#2

Bom pessoal… encontrei uma solução… aparentemente funciona… que é fazer uma query nativa ao invés de ser por jpa. Ficou assim:

Método no DaoBean:

[code]public List<GeoPendingStandProperties> listPendingStandProperties(Stand stand) {
_log.entering(stand);

    List&lt;GeoPendingStandProperties&gt; stands = new ArrayList&lt;GeoPendingStandProperties&gt;();
    
    Query qry = _em.createNativeQuery(getSql("listGeoPendingStandProperties"), GeoPendingStandProperties.class);
    
    qry.setParameter("stAdmDivCode", stand.getMngUnit().getNucleus().getAdmDivision().getCode());
    qry.setParameter("stNucleusCode", stand.getMngUnit().getNucleus().getCode());
    qry.setParameter("stMngUnitCode", stand.getMngUnit().getCode());
    qry.setParameter("standCode", stand.getCode());
    qry.setParameter("stRevisionNum", stand.getRevision());
    
    stands = qry.getResultList();
    _log.exiting(stands);
    return stands;
}[/code]

Query no statementFile.xml :

&lt;!-- listGeoPendingStandProperties --&gt; &lt;sql-stmt&gt; &lt;sql-stmt-name&gt;listGeoPendingStandProperties&lt;/sql-stmt-name&gt; &lt;sql-stmt-value&gt;&lt;![CDATA[ select * from LM_GEO_PENDING_STAND_PROP geo where geo.S_AD_CODE = #stAdmDivCode and geo.S_N_CODE = #stNucleusCode and geo.S_MU_CODE = #stMngUnitCode and geo.S_CODE = #standCode and geo.S_REVISION_NUM = #stRevisionNum ]]&gt;&lt;/sql-stmt-value&gt; &lt;/sql-stmt&gt;

#3