Clever Geek Handbook

How to use @Loader with positional parameters in hibernation?

I am trying to use @Loader annotation to populate a collection on demand using hibernate.

Suppose I have a menu class that contains a list of submenus. I want these submenus to be populated with @NamedNativeQuery.

The problem is that when I put positional parameters in a query, I don’t know how to set its value at runtime?

Here is my menu class. When I select a menu, all submenus are zero. where should i set the value of the positional parameter? Could you help me?

Here is my XML

    <!DOCTYPE hibernate-mapping PUBLIC  
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"  
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">  

    <hibernate-mapping>  
   <sql-query name="getMenus">
        <load-collection alias="menu" role="com.pmc.domain.Menu.submenus" />
        select 
            {menu.*}
        from
            Menu menu
        join menu_roles mr on (mr.menu_item_id=menu.menu_item_id)
        join roles r on (r.user_role_id=mr.user_role_id)
        join user_roles ur on (ur.user_role_id=r.user_role_id)
        join users u on (u.username = ur.username)
        where 
            menu.parent = ? and u.username=?

    </sql-query>

    </hibernate-mapping>

And here is my POZHO.

package com.pmc.domain;

import java.io.Serializable;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Transient;

import org.hibernate.annotations.Filter;
import org.hibernate.annotations.FilterDef;
import org.hibernate.annotations.Loader;
import org.hibernate.annotations.NamedNativeQuery;
import org.hibernate.annotations.ParamDef;

@Entity
@Table(name="menu", catalog="test")
public class Menu implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "menu_item_id")
    private Integer menuId;

    @ManyToMany(cascade={CascadeType.ALL})
    @JoinTable(name="MENU_ROLES",
    joinColumns={@JoinColumn(name="menu_item_id")},
    inverseJoinColumns={@JoinColumn(name="user_role_id")})
    private List<UserRole> userRoles;
    @Column(name = "label", length = 100)
    private String label;
    @Column(name = "link", length = 100)
    private String link;

    @ManyToOne(cascade={CascadeType.ALL})
    @JoinColumn(name = "parent")
    private Menu parent;

    @OneToMany(mappedBy="parent", fetch = FetchType.LAZY)
    @Loader(namedQuery = "getMenus")
    private List<Menu> submenus;


    public Integer getMenuId() {
        return menuId;
    }
    public void setMenuId(Integer menuId) {
        this.menuId = menuId;
    }

    public List<UserRole> getUserRoles() {
        return userRoles;
    }
    public void setUserRoles(List<UserRole> userRoles) {
        this.userRoles = userRoles;
    }
    public String getLabel() {
        return label;
    }
    public void setLabel(String label) {
        this.label = label;
    }
    public String getLink() {
        return link;
    }
    public void setLink(String link) {
        this.link = link;
    }
    public Menu getParent() {
        return parent;
    }
    public void setParent(Menu parent) {
        this.parent = parent;
    }
    public List<Menu> getSubmenus() {
        return submenus;
    }
    public void setSubmenus(List<Menu> submenus) {
        this.submenus = submenus;
    }



}

This is my DAO method

public List<Menu> getMenus() {
        Session session = sessionFactory.openSession();
        @SuppressWarnings("unchecked")
         Query query =   session.getNamedQuery("getMenus");
        query=query.setParameter(0, 1);
        query=query.setParameter(1, "jigar");
        List<Menu> menuList = session.createQuery("select menu from User user, IN (user.userRole) roles, IN(roles.menus) menu where user.username = 'jigar' and menu.parent=0").list();
        return menuList;
    }

When I call getMenus it returns the menu list. but when available in jsp submenu request results in below exception

Expected positional parameter count: 2, actual parameters: [1] [select 
        {menu.*}
    from
        Menu menu
    join menu_roles mr on (mr.menu_item_id=menu.menu_item_id)
    join roles r on (r.user_role_id=mr.user_role_id)
    join user_roles ur on (ur.user_role_id=r.user_role_id)
    join users u on (u.username = ur.username)
    where 
        menu.parent = ? and u.username=?]
at org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:429)
at org.hibernate.internal.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:195)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:134)
at org.hibernate.persister.collection.NamedQueryCollectionInitializer.initialize(NamedQueryCollectionInitializer.java:73)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:693)
at org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:92)
at org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1897)
at org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:558)
at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:260)
at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:554)
at org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:142)
at org.hibernate.collection.internal.PersistentBag.iterator(PersistentBag.java:294)
at org.apache.taglibs.standard.tag.common.core.ForEachSupport.toForEachIterator(ForEachSupport.java:348)
at org.apache.taglibs.standard.tag.common.core.ForEachSupport.supportedTypeForEachIterator(ForEachSupport.java:224)
at org.apache.taglibs.standard.tag.common.core.ForEachSupport.prepare(ForEachSupport.java:155)
at javax.servlet.jsp.jstl.core.LoopTagSupport.doStartTag(LoopTagSupport.java:256)
at org.apache.jsp.WEB_002dINF.views.menu_jsp._jspx_meth_c_005fforEach_005f0(menu_jsp.java:108)
at org.apache.jsp.WEB_002dINF.views.menu_jsp._jspService(menu_jsp.java:77)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:748)
at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:604)
at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:543)
at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:954)
at org.apache.jsp.WEB_002dINF.views.menu_jsp._jspx_meth_c_005fif_005f0(menu_jsp.java:186)
at org.apache.jsp.WEB_002dINF.views.menu_jsp._jspx_meth_c_005fforEach_005f0(menu_jsp.java:122)
at org.apache.jsp.WEB_002dINF.views.menu_jsp._jspService(menu_jsp.java:77)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:748)
at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:604)
at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:543)
at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:954)
at org.apache.jsp.WEB_002dINF.views.addressList_jsp._jspService(addressList_jsp.java:137)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:748)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:486)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:411)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:338)
at org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:172)
at org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:303)
at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1228)
at org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1011)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:955)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:315)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

Nov 17, 2014 10:25:39 AM org.apache.catalina.core.ApplicationDispatcher invoke
SEVERE: Servlet.service() for servlet jsp threw exception

If i use below code

Query query =   session.getNamedQuery("getMenus");
query=query.setParameter(0, 1);
query=query.setParameter(1, "jigar");
List<Menu> list = query.list();

it works fine and returns the correct result.

But when I pass this to @Loader for the collection, it cannot find the positional parameters. However, if I use one positional parameter, i.e.? then it works there too, since the submenu is mapped to the parent and it takes the parent value as a positional parameter. but for further positional parameters it doesn't work.

+3


source to share


2 answers


Change your code

@Entity
@NamedNativeQuery(
              name="submenus", 
              query="select * from menu where parent = ? ",
              resultClass=Menu.class)
@Table(name="menu", catalog="test")
public class Menu implements Serializable{

before

@NamedNativeQueries({@NamedNativeQuery(
                  name="submenus", 
                  query="select * from menu where parent = :parentName and menu_item_id = :menuItemId",
                  resultClass=Menu.class)
})
@Entity
@Table(name="menu", catalog="test")
public class Menu implements Serializable{

Remove @Loader annotation from

@OneToMany(mappedBy="parent",fetch = FetchType.LAZY)
@Loader(namedQuery = "submenus")
private List<Menu> submenus;

& place it on top of the menu class like



@NamedNativeQueries({@NamedNativeQuery(
                  name="submenus", 
                  query="select * from menu where parent = :parentName and menu_item_id = :menuItemId",
                  resultClass=Menu.class)
})
@Loader(namedQuery = "submenus")
@Entity
@Table(name="menu", catalog="test")
public class Menu implements Serializable{

Bcoz @Loader annotations are only applicable for classes and collections, not for associations like @OneToMany etc.

Call your native query like

Query query = session.getNamedQuery("submenus");
query.setParameter("parentName", "Whatever u want");
query.setParameter("menuItemId", "Whatever u want");

//or
//if ur query is like this
//select * from menu where parent = ? and menu_item_id = ?
query.setParameter(1, "Whatever u want");
query.setParameter(2, "Whatever u want");

Also take a look

Hope this helps you.

0


source


Named queries support multiple parameters, but @Loader

only support one and other query parameters that will not be overridden. Parameters cannot be set globally in a session (like in case @Filter

), but only for a named request instance.

When you use @NamedQuery

or @NamedNativeQuery

from @Loader

, you can only use one parameter (regardless of its type, positional / named), which will be replaced with the Serializable

id of the requested object.



    if ( query.hasNamedParameters() ) {
        query.setParameter( query.getNamedParameters()[0], id, persister.getIdentifierType() );
    }
    else {
        query.setParameter( 0, id, persister.getIdentifierType() );
    }

see org.hibernate.persister.entity.NamedQueryLoader # load (Serializable, Object, SessionImplementor)

0


source






More articles:


All Articles