Merge two query when using hibernate

Question

I am using hibernate to map the data from mysql database.

This is my queries :

long id = (Long) session.createQuery("Select category.id from Project as p where p.id =:projectId").setLong("projectId", projectId).uniqueResult();

activities = session.createQuery("Select name from Activity as a where a.parent.id =:activityId").setLong("activityId", id).list();

It works fine. How can I merge these two queries to one query?

addenda

Activity class:

@Entity
public class Activity implements BaseEntry, Comparable<Activity> {

    private static final long serialVersionUID = 4937176332500551910L;

    @Id
    @GeneratedValue
    private long id;

    @Column(name = "short", unique = true, nullable = false)
    private String key;

    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    private Activity parent;

    @OneToMany(targetEntity = Activity.class, mappedBy = "parent", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JsonIgnore
    private Collection<Activity> children;

    @OneToMany(targetEntity = Project.class, mappedBy = "category", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JsonIgnore
    private Collection<Project> projects;

Project class:

@Entity
public class Project implements BaseEntry, Comparable<Project> {

    private static final long serialVersionUID = 1911160043392595450L;

    @Id
    @GeneratedValue
    private long id;

    @Column(name = "short", unique = true, nullable = false)
    private String key;

    @Column(nullable = false)
    private String name;

    @ManyToOne(optional = false, fetch = FetchType.EAGER)
    private Activity category;

I used:

activities = session.createQuery("Select a.name from Activity as a where a.parent.id = (Select p.category.id from Project as p where p.id =:projectId)")
                    .setLong("projectId", projectId).list();

And got an Error:

Apache Tomcat/7.0.23 - Error report

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.codehaus.jackson.map.JsonMappingException: could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->se.softwerk.timelog.model.Activity["parent"]->se.softwerk.timelog.model.Activity_$$_javassist_3["id"])
    org.codehaus.jackson.map.JsonMappingException.wrapWithPath(JsonMappingException.java:218)
    org.codehaus.jackson.map.JsonMappingException.wrapWithPath(JsonMappingException.java:183)
    org.codehaus.jackson.map.ser.std.SerializerBase.wrapAndThrow(SerializerBase.java:140)
    org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:158)
    org.codehaus.jackson.map.ser.BeanSerializer.serialize(BeanSerializer.java:112)
    org.codehaus.jackson.map.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:446)
    org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:150)
    org.codehaus.jackson.map.ser.BeanSerializer.serialize(BeanSerializer.java:112)
    org.codehaus.jackson.map.ser.std.StdContainerSerializers$IndexedListSerializer.serializeContents(StdContainerSerializers.java:122)
    org.codehaus.jackson.map.ser.std.StdContainerSerializers$IndexedListSerializer.serializeContents(StdContainerSerializers.java:71)
    org.codehaus.jackson.map.ser.std.AsArraySerializerBase.serialize(AsArraySerializerBase.java:86)
    org.codehaus.jackson.map.ser.StdSerializerProvider._serializeValue(StdSerializerProvider.java:610)
    org.codehaus.jackson.map.ser.StdSerializerProvider.serializeValue(StdSerializerProvider.java:256)
    org.codehaus.jackson.map.ObjectMapper.writeValue(ObjectMapper.java:1604)
    org.codehaus.jackson.jaxrs.JacksonJsonProvider.writeTo(JacksonJsonProvider.java:558)
    com.sun.jersey.json.impl.provider.entity.JacksonProviderProxy.writeTo(JacksonProviderProxy.java:160)
    com.sun.jersey.spi.container.ContainerResponse.write(ContainerResponse.java:306)
    com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1437)
    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)

root cause

org.hibernate.LazyInitializationException: could not initialize proxy - no Session
    org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:149)
    org.hibernate.proxy.AbstractLazyInitializer.getImplementation(AbstractLazyInitializer.java:195)
    org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer.invoke(JavassistLazyInitializer.java:185)
    se.softwerk.timelog.model.Activity$$_javassist_3.getId(Activity_$$_javassist_3.java)
    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)
    org.codehaus.jackson.map.ser.BeanPropertyWriter.get(BeanPropertyWriter.java:483)
    org.codehaus.jackson.map.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:418)
    org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:150)
    org.codehaus.jackson.map.ser.BeanSerializer.serialize(BeanSerializer.java:112)
    org.codehaus.jackson.map.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:446)
    org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:150)
    org.codehaus.jackson.map.ser.BeanSerializer.serialize(BeanSerializer.java:112)
    org.codehaus.jackson.map.ser.std.StdContainerSerializers$IndexedListSerializer.serializeContents(StdContainerSerializers.java:122)
    org.codehaus.jackson.map.ser.std.StdContainerSerializers$IndexedListSerializer.serializeContents(StdContainerSerializers.java:71)
    org.codehaus.jackson.map.ser.std.AsArraySerializerBase.serialize(AsArraySerializerBase.java:86)
    org.codehaus.jackson.map.ser.StdSerializerProvider._serializeValue(StdSerializerProvider.java:610)
    org.codehaus.jackson.map.ser.StdSerializerProvider.serializeValue(StdSerializerProvider.java:256)
    org.codehaus.jackson.map.ObjectMapper.writeValue(ObjectMapper.java:1604)
    org.codehaus.jackson.jaxrs.JacksonJsonProvider.writeTo(JacksonJsonProvider.java:558)
    com.sun.jersey.json.impl.provider.entity.JacksonProviderProxy.writeTo(JacksonProviderProxy.java:160)
    com.sun.jersey.spi.container.ContainerResponse.write(ContainerResponse.java:306)
    com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1437)
    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)

note The full stack trace of the root cause is available

Yes I add them to the end of the question. – Ali Sep 08 '12 at 12:30 — Ali, Sep 08 '12 at 12:30

score 0 · Accepted Answer · answered Sep 08 '12 at 12:51

0

The simpliest way to merge these queries is to use a subquery:

Select a.name from Activity as a where a.parent.id = 
    (Select p.category.id from Project as p where p.id =:projectId)

answered Sep 08 '12 at 12:51

axtavt

239,438
41
511
482

I used it but I got an error which I added to the end of the question. – Ali Sep 08 '12 at 13:01
@Ali: "PermGen space" is not related to the queries and usually is solved [by JVM configuration](http://stackoverflow.com/questions/88235/dealing-with-java-lang-outofmemoryerror-permgen-space-error). However, check that you don't create a new `SessionFactory` for each request. – axtavt Sep 08 '12 at 15:42
I found the problem here : `@ManyToOne(fetch = FetchType.LAZY) private Activity parent;` in Activity class. I need to use `FetchType.EAGER`. Thanks for your help. – Ali Sep 08 '12 at 16:24

Merge two query when using hibernate

HTTP Status 500 -

1 Answers1