Hibernate - select from local table foreign key

Question

i have 2 tables,combo_comment and combo(it would be something along the lines of a forum post or a question in stack overflow) where the combo_id is a foreign key in combo_comment, i want to select a comment based on a combo_id without using any type of join, i want to basically do this

SELECT * FROM COMBO_COMMENT where COMBO_ID= 3;

where i'm referring to COMBO_COMMENT.COMBO_ID, not COMBO.COMBO_ID, however when i try to do this with hibernate it returns the combo object inside the comment, i know i can use @JsonIgnore to ignore the combo object when returning the answer but then i wont have the combo_id field. i have tried using criteria and HQL

here's my combo_comment entity class without the getter and setter(they don't have any type of annotation)

@Entity
@Table(name="COMBO_COMMENT")
public class ComboComment {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="COMMENT_ID",nullable = false)
private int commentId;
@ManyToOne(fetch = FetchType.EAGER,targetEntity = Combo.class,cascade = CascadeType.ALL)
@JoinColumn(name = "COMBO_ID",nullable = false,referencedColumnName="COMBO_ID")
private Combo combo;
@Column(name="COMBO_COMMENT",nullable = false)
private String ComboComment;
@Column(name="POST_DATE",nullable = false)
private long postDate;
public ComboComment(){

}

}

and here is the two ways i'm trying to do it

with criteria:

Criteria criteria = createEntityCriteria();
    criteria.add(Restrictions.eq("combo.comboId", comboId));
    criteria.addOrder(Order.desc("postDate"));
    criteria.setMaxResults(10);
    if (page != 0) {
        criteria.setFirstResult(page * 10);
    }

with HQL:

String hql = "from ComboComment where combo.comboId =:comboId";
    Query query = getSession().createQuery(hql).setInteger("comboId", comboId);

those are the only two ways i have manage to get a successful response without any type of exception

Update:

Exception when setting FetchType to LAZY

HTTP Status 500 - Could not write content: could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->com.mycompany.ufggservice.model.ComboComment["combo"]->com.mycompany.ufggservice.model.Combo_$$_jvst3d5_2["comboId"]); nested exception is com.fasterxml.jackson.databind.JsonMappingException: could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->com.mycompany.ufggservice.model.ComboComment["combo"]->com.mycompany.ufggservice.model.Combo_$$_jvst3d5_2["comboId"])

So your question is not about how to get the data, from the persistence layer, with Hibernate. It's about how to transfer the combo ID instead of the combo object, from the presentation layer, to the browser.That should answer the question: if the objects of the presentation layer doesn't match with what you want to transfer from the presentation layer, use different objects, or use Jackson annotations allowing to transform a combo object into its ID. Or use a dedicated query returning only the fields you want to return from the presentation layer. — JB Nizet, Oct 02 '16 at 21:04
my question how to get the exact data i want FROM the database, i would prefer not to use any join table to get that data because that information is in the combo_comment table. — Alexander Jimenez, Oct 02 '16 at 22:47

score 0 · Answer 1 · answered Oct 02 '16 at 22:35

0

"however when i try to do this with hibernate it returns the combo object inside the comment"

As far as I understand, you want to use a method like get of hibernate to fetch a certain object, however you are also hitting the DB for the associated object name combo.

You are getting this behavior because you are using the FetchType.EAGER , which says to hibernate to fetch all related associated objects within the requested object.

So if you are trying to fetch comment, it will also fetch for you the combo associated to it.

The solution is just to use:

fetch = FetchType.LAZY

answered Oct 02 '16 at 22:35

Moshe Arad

3,587
4
18
33

if i set the fetchType to LAZY then i get an exception telling me that it can't acces the combo table, i agree that my solution would be to be able to get the data with fetchType.LAZY but i don't know how to do it with the foreign key – Alexander Jimenez Oct 02 '16 at 22:52
Can you post the exception stack trace and details? – Moshe Arad Oct 02 '16 at 23:12

score 0 · Accepted Answer · edited May 23 '17 at 12:15

after more research i manage to get to run the fetchType.Lazy query, all i needed was to configure MappingJackson2HttpMessageConverter and add the hibernateModule(in my case hibernate5) taken from Avoid Jackson serialization on non fetched lazy objects, the query doesn't join the combo table to get the info i want.

i still don't have the response i want in the json mapper but eventually i'll figure it out

Hibernate - select from local table foreign key

2 Answers2