Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

How to use @Loader with positional parameters in hibernate?

Tags:

java

database

spring

orm

hibernate

I am trying to use @Loader annotation to populate the collection by query using hibernate.

Suppose I have a Menu class which contains List of submenus. I want these submenus to be populated using @NamedNativeQuery.

The problem is that when i put the positional parameters in query, i don't know how to set it's value at run time?

Here is my Menu class. When I fetch the menus, all the submenus are null. where should I set the positional parameter value? Can you please help?

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 POJO.

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 list of menus. but when they are accessed at jsp, submenu query results in an exception below

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 proper result.

But when I pass this in @Loader for collection, it can't find positional parameters. However, if I use single positional parameter i.e. ? then it works there too, as submenus are mapped by parent and it takes parent value as positional parameter. but for further positional parameters, it doesn't work.

like image 355
Jigar Patel Avatar asked Jan 17 '26 10:01

Jigar Patel

1 Answers

Change your code from 

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

to 

@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 Menu class as

@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 annotation is only applicable for Class & Collections, not for Association like @OneToMany, etc.

Call your named native query as

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 have a look at

  1. @Loader on @OneTOMany doesn't work.
  2. Hibernate Named Query (XML Mapping)

I hope it helps you.

like image 127
OO7 Avatar answered Jan 20 '26 00:01

OO7
Sign in to Comment

Related questions

Confused with different ssl certificates
How to create a bigram/trigrams index in Lucene 3.4.0?
How to use Java EE and Jakarta EE dependencies together?
Utilizing Logging MDC with Virtual Threads
How to get assertion result from JsonUnit?
Get response from CompletableFuture
Google Firebase authentication: What is the server Client's ID that's passed requestIdToken
Making sure a string repr of decimal number is always n characters long
Is it possible to check time being taken by a method call in parallel
jOOQ does not translate SQL properly for H2 database in PostgreSQL mode
Java Spring Patch RFC-6902 Instant type conversion exception

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!