parameter not set!

[Alana Sparx]

Hibernate is driving me crazy.

Model class (informationDisplay.java):

 
 
    public static final String PROP_GROUP = "groups";
    public static final String PROP_TYPE = "type";
    public static final String PROP_EXPIRED = "expired";
 
    private String type;
    private Date expired;
    private Set<Group> groups = new LinkedHashSet<Group>(0);
 
    //standard getters/setters for type/expired
   
    /**
     * @hibernate.set
     * table="GROUP_INFORMATION_DISPLAY"
     *      lazy="true"
     *      cascade="all"
     * @hibernate.key
     *      column="INFORMATION_DISPLAY_ID"
     * @hibernate.many-to-many
     * column="GROUP_ID"
     *      class="a.b.c.Group"
     */
    public Set<Group> getGroups(){return groups;}
    public void setGroups(Set<Group> groups){this.groups = groups;}

daoHibernate class

    public List<InformationDisplay> getInformationDisplays(final Set<Group> groups, final String type) {

HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException {
            
            Criteria infoCriteria = CriteriaUtil.createCriteria(session, InformationDisplay.class);
                infoCriteria.add(in(PROP_GROUP, groups));
                infoCriteria.add(eq(PROP_TYPE, type));
                infoCriteria.add(isNull(PROP_EXPIRED));
                
                return infoCriteria.list();
            }
        };
 
        List<InformationDisplay> list = (List) getHibernateTemplate().execute(callback);
        return list;
    }
    

if I comment out the line infoCriteria.add(in(PROP_GROUP, groups));, everything works as expected. Leaving it in throws it all over the place:
1) sql error stating parameter #2 has not been set - eh?
2) the id of the Group object contained in the group set looks like it is being used to search the id of Information_Display - it should be set to the GROUP_ID of GROUP_INFORMATION_DISPLAY. I thought Hibernate took care of this.

Can anyone help. I've been battering my head against this wall all afternoon, in which time I could have JDBC'd half the bloody universe!!!

[Alana Sparx]

Sorted it myself.

I'd tell you, but owing to your reticence, you don't deserve to know.

[Mark Spritzler]

I am missing something here in

infoCriteria.add(in(PROP_GROUP, groups));

where is the "in" method? Is that a method you have implemented, or something that is coming from a static import? And if you implemented it what does it look like.

Thanks

Mark

[Mark Spritzler]

Originally posted by Alana Sparx:
Sorted it myself.

I'd tell you, but owing to your reticence, you don't deserve to know.

Alana, Sorry, I had my response on my machine for a couple hours, but got called into a meeting. Also the more detailed the question the more responses you get, I think what happened here was that people weren't quite sure what you were asking. There was some missing information.

Regardless of getting answers or not, I will take your "you don't deserve to know." as a joke rather than breaking one of the only two rules we have at JavaRanch, be nice.

Mark

[Alana Sparx]

I am missing something here in

infoCriteria.add(in(PROP_GROUP, groups));

where is the "in" method? Is that a method you have implemented, or something that is coming from a static import? And if you implemented it what does it look like.


Yes, it was a static import from the Restrictions class.

I could have implemented it myself, but it would have been awful....

[Mark Spritzler]

Originally posted by Alana Sparx:

I am missing something here in

infoCriteria.add(in(PROP_GROUP, groups));

where is the "in" method? Is that a method you have implemented, or something that is coming from a static import? And if you implemented it what does it look like.


Yes, it was a static import from the Restrictions class.

I could have implemented it myself, but it would have been awful....

OK, I assume that either the collection wasn't the right type, or something along those lines.

Mark

[Steven Swor]

Alana Sparx wrote:Sorted it myself.

I'd tell you, but owing to your reticence, you don't deserve to know.

I'm having the same problem with the parameter not being set. I've tried everything I can think of to make it work, and I'm all out of ideas. Can you please post how you fixed this?

[Paul Sturrock]

Steven Swor wrote:

Alana Sparx wrote:Sorted it myself.

I'd tell you, but owing to your reticence, you don't deserve to know.

I'm having the same problem with the parameter not being set. I've tried everything I can think of to make it work, and I'm all out of ideas. Can you please post how you fixed this?

I'm not sure Alana will be around to answer you more than a year an a half after this question was posted!

When you say I have the same problem are you doing the same thing? Or are you doing something different and getting a simmilar message? Can you post your code and any error message/stack trace you have?

[Steven Swor]

Thanks for the quick response, Paul

My project uses Hibernate 3 and MySQL 5 with the InnoDB engine. (For what it's worth, I'm also using Spring, XFire, and Tomcat, but I doubt my problem is related to any of these)

I have the following classes in my domain model:
UserGroup.java

package users;

import java.io.Serializable;
import javax.persistence.*;

@Entity
public class UserGroup implements Serializable {

    @Id
    @GeneratedValue
    private Long ident;

    public UserGroup(){
    }

    public void setIdent(Long ident){
        this.ident=ident;
    }

    public Long getIdent(){
        return ident;
    }
}

ProgramUser.java

package users;

import java.io.Serializable;
import java.util.Set;
import javax.persistence.*;

@Entity
public class ProgramUser implements Serializable {
    
    @Id
    @GeneratedValue
    private Long ident;

    @ManyToMany(fetch=FetchType.EAGER)
    private Set<UserGroup> groups;

    public ProgramUser(){
    }

    public Long getIdent(){
        return ident;
    }

    public void setIdent(Long ident){
        this.ident=ident;
    }

    public Set<UserGroup> getGroups() {
        return groups;
    }

    public void setGroups(Set<UserGroup> groups) {
        this.groups = groups;
    }    
}

As you can see, each user maintains a collection of the groups to which it belongs. I also want to be able to get a listing of all users who belong to a particular group (while avoiding circular references), so I have the following method in my HibernateAuthDAO.java:

    public List<ProgramUser> getUsersForGroup(final Long groupId){
        return (List<ProgramUser>) transactionTemplate.execute(new TransactionCallback(){

            public Object doInTransaction(TransactionStatus arg0) {
                Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
                //get the group with the specified ID
                UserGroup group = (UserGroup) session.createCriteria(UserGroup.class)
                        .add(Restrictions.eq("ident",groupId))
                        .uniqueResult();
                //make an array containing the group (needed by Restrictions.in())
                UserGroup[] groupAsArray = {group};
                //Get a list of all users whose 'groups' collection contains the specified group
                return session.createCriteria(ProgramUser.class)
                        .add(Restrictions.in("groups", groupAsArray))
                        .list();
            }
        });
    }

The above method looks like it should give me what I want (I'll admit that I'm still fairly new to both Hibernate and JPA), but when I try to run it, it throws this exception:

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2216)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.summit.datalinc.auth.common.HibernateAuthDAO$30.doInTransaction(HibernateAuthDAO.java:663)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
at com.summit.datalinc.auth.common.HibernateAuthDAO.getUsersForGroup(HibernateAuthDAO.java:655)
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:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy12.getUsersForGroup(Unknown Source)
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:597)
at org.codehaus.xfire.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:59)
at org.codehaus.xfire.service.binding.ServiceInvocationHandler.sendMessage(ServiceInvocationHandler.java:320)
at org.codehaus.xfire.service.binding.ServiceInvocationHandler$1.run(ServiceInvocationHandler.java:86)
at org.codehaus.xfire.service.binding.ServiceInvocationHandler.execute(ServiceInvocationHandler.java:134)
at org.codehaus.xfire.service.binding.ServiceInvocationHandler.invoke(ServiceInvocationHandler.java:109)
at org.codehaus.xfire.handler.HandlerPipeline.invoke(HandlerPipeline.java:131)
at org.codehaus.xfire.transport.DefaultEndpoint.onReceive(DefaultEndpoint.java:64)
at org.codehaus.xfire.transport.AbstractChannel.receive(AbstractChannel.java:38)
at org.codehaus.xfire.transport.http.XFireServletController.invoke(XFireServletController.java:304)
at org.codehaus.xfire.transport.http.XFireServletController.doService(XFireServletController.java:129)
at org.codehaus.xfire.spring.remoting.XFireServletControllerAdapter.handleRequest(XFireServletControllerAdapter.java:67)
at org.codehaus.xfire.spring.remoting.XFireExporter.handleRequest(XFireExporter.java:48)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:637)
Caused by: java.sql.SQLException: No value specified for parameter 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2176)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2100)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1826)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2213)
... 50 more

With show_sql and format_sql turned on, I see that the exception was thrown when Hibernate attempted to run the following query:

select
        this_.ident as ident6_1_,
        groups2_.ProgramUser_ident as Program1_3_,
        usergroup3_.ident as groups2_3_,
        usergroup3_.ident as ident2_0_
    from
        ProgramUser this_ 
    left outer join
        ProgramUser_UserGroup groups2_ 
            on this_.ident=groups2_.ProgramUser_ident 
    left outer join
        UserGroup usergroup3_ 
            on groups2_.groups_ident=usergroup3_.ident 
    where
        this_.ident in (
            ?
        )

For some reason, the parameter isn't being set in the prepared statement, but I'm not sure why. I can run this query in MySQL Query Browser and get results. I figure I'm doing something wrong with my "Restrictions.in()" part, but I'm baffled as to what it could be.

[Steven Swor]

Nevermind. I found the problem and fixed it. I misunderstood how Restrictions.in() worked.

Restrictions.in() is kind of like Restrictions.eq(), except that it works with a list of values instead of a single one. For example, if I wanted to get a list of ProgramUsers from a known set of IDs, I could use the following method:

    public List<ProgramUser> getUsersDemo(){
        return (List<ProgramUser>) transactionTemplate.execute(new TransactionCallback(){

            public Object doInTransaction(TransactionStatus arg0) {
                Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
                Long[] idArray={1,2,3,4,5};
                //Get a list containing the 5 specified users  
                return session.createCriteria(ProgramUser.class)
                        .add(Restrictions.in("ident",idArray))
                        .list();
            }
        });
    }

This method will return a list containing 5 users (assuming all 5 idents appear in the ProgramUser table). This has its uses, but it won't do what I need it to do.

What I really needed was an additional Criterion on each user's "groups" collection, to make sure that the group appeared in that collection. Here's the corrected method:

    public List<ProgramUser> getUsersForGroup(final Long groupId){
        return (List<ProgramUser>) transactionTemplate.execute(new TransactionCallback(){

            public Object doInTransaction(TransactionStatus arg0) {
                Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
                //Get a list of all users whose 'groups' collection contains the specified group  
                return session.createCriteria(ProgramUser.class)
                        .createCriteria("groups")
                        .add(Restrictions.eq("ident",groupId))
                        .list();
            }
        });
    }

And here's the query generated by Hibernate. Aside from the table/column aliases, it's pretty much what I would have done if I'd written it in pure SQL.

    select
        this_.ident as ident6_1_,
        groups3_.ProgramUser_ident as Program1_,
        usergroup1_.ident as groups2_,
        usergroup1_.ident as ident2_0_
    from
        ProgramUser this_ 
    inner join
        ProgramUser_UserGroup groups3_ 
            on this_.ident=groups3_.ProgramUser_ident 
    inner join
        UserGroup usergroup1_ 
            on groups3_.groups_ident=usergroup1_.ident 
    where
        usergroup1_.ident=?