CODEWITHSUNDEEP
sqlhibernatemany-to-many
Ali
Ali

Reputation: 9994

Query for Many-To-Many relation using hibernate

I have a many To many relation in my resource classes using hibernate. In the Category class: 

@Entity
public class Category {

    Id
    @GeneratedValue
    private long id;

    @ManyToMany
    @JoinTable(name = "category_activity", 
    joinColumns = { @JoinColumn(name = "Category_id") }, 
    inverseJoinColumns = { @JoinColumn(name = "activities_id") })
    private Collection<Activity> activities;
}

And in the Activity class: 

@Entity
public class Activity {

    @Id
    @GeneratedValue
    private long id;

    @ManyToMany
    @JoinTable(name="category_activity", 
    joinColumns={@JoinColumn(name="activities_id")},
    inverseJoinColumns={@JoinColumn(name="Category_id")})
    private Collection<Category> category;
}

When I had oneToMany relation between them I was using a Query that worked fine for me: 

Session session = HibernateUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();
Collection<Activity> activities = (Collection<Activity>) session.createQuery("from Activity as a where a.category.id=" + categoryId + " order by a.key").list();
session.getTransaction().commit();

I appreciate any help that I can fix this query for the ManyToMany relation?

I also used this query :

activities = (List<Activity>) session.createQuery("from Activity a join a.category cs where cs.id= :categoryId order by a.key").setLong("categoryId", categoryId).list();

and I got this error in stack trace:

<html><head><title>Apache Tomcat/7.0.23 - Error report</title><style><!--H1 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:22px;} H2 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:16px;} H3 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:14px;} BODY {font-family:Tahoma,Arial,sans-serif;color:black;background-color:white;} B {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;} P {font-family:Tahoma,Arial,sans-serif;background:white;color:black;font-size:12px;}A {color : black;}A.name {color : black;}HR {color : #525D76;}--></style> </head><body><h1>HTTP Status 500 - </h1><HR size="1" noshade="noshade"><p><b>type</b> Exception report</p><p><b>message</b> <u></u></p><p><b>description</b> <u>The server encountered an internal error () that prevented it from fulfilling this request.</u></p><p><b>exception</b> <pre>java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to java.lang.Comparable
    java.util.ComparableTimSort.countRunAndMakeAscending(Unknown Source)
    java.util.ComparableTimSort.sort(Unknown Source)
    java.util.ComparableTimSort.sort(Unknown Source)
    java.util.Arrays.sort(Unknown Source)
    java.util.Collections.sort(Unknown Source)
    se.softwerk.timelog.controller.ActivityManager.activityList2(ActivityManager.java:64)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    java.lang.reflect.Method.invoke(Unknown Source)
    com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
    com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
    com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
    com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:288)
    com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
    com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
    com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1469)
    com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1400)
    com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1349)
    com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1339)
    com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
    com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:537)
    com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:708)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
</pre></p><p><b>note</b> <u>The full stack trace of the root cause is available in the Apache Tomcat/7.0.23 logs.</u></p><HR size="1" noshade="noshade"><h3>Apache Tomcat/7.0.23</h3></body></html>

Upvotes: 0

Views: 2232

Answers (1)

Luxspes
Luxspes

Reputation: 6760

First of all you shouldn't be concatenating strings, that opens the door for SQLInjection attacks, you should be using named parameters. More on named parameters here

Second, if you have ManyToMany relationshio, both sides should be pluralized:

This

private Collection<Category> category;

Should be this:

private Collection<Category> categories;

Now on your particular question, you have this

from Activity as a where a.category.id= :categoryId order by a.key

You need this:

from Activity a join a.category cs where cs.id= :categoryId  order by a.key

Upvotes: 1

Related Questions