Hibernate alias to a simple boolean field

Question

I am struggling with the Hibernate Criteria API.

In class Conversation I have:

@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
@JoinTable(name = "Conversation_ConversationParticipants",
           joinColumns = @JoinColumn(name = "ConversationID"), 
           inverseJoinColumns = @JoinColumn(name = "ConversationParticipantID"))
private List<ConversationParticipant> participants;

And in class ConversationParticipant I have:

@OneToOne
@JoinColumn(name = "ParticipantID")
private User participant;

@Type(type = "true_false")
@Column(name = "Viewed")
private boolean viewed;

In my criteria I have

    Criteria criteria = super.createCriteria();
    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    criteria.createAlias("participants", "participants");  
    criteria.createAlias("participants.participant", "participant");
    criteria.add(Restrictions.eq("participant.id", user.getId()));

    return (List<Conversation>) criteria.list();

This all works ok and I can see all of the conversations for the given user. However, what I want is to see all of the conversations that the user has not viewed yet. To do so, I try to add:

    criteria.add(Restrictions.eq("participants.viewed", false));

However, this returns 0 conversations (if I put 'true' I get the same result and I have checked db and made sure that there are values with both true and false). How can I achieve this? What am I doing wrong?

The query that is run is:

from
    Conversations this_ 
inner join
    Conversation_ConversationParticipants participan4_ 
        on this_.id=participan4_.ConversationID 
inner join
    ConversationParticipants participan1_ 
        on participan4_.ConversationParticipantID=participan1_.id 
inner join
    Users participan2_ 
        on participan1_.ParticipantID=participan2_.id 
where
    participan1_.Viewed=? 
    and participan2_.id=?

DB Table:

 CREATE TABLE ConversationParticipants(ID BIGINT NOT NULL IDENTITY, Viewed CHAR(1), Type VARCHAR (255), ParticipantID BIGINT, PRIMARY KEY (ID));

Based on the answer below, I was able to make it work by using:

 criteria.add(Restrictions.eq("participants.viewed", Boolean.FALSE));

Side note: This looks hella complicated; `AS`,`WHERE` and `JOIN` clauses in SQL and Linq are *much* simpler. I can see why you're straguling with it. — Robert Harvey, Dec 26 '12 at 21:11
This should work. What is the generated SQL query, and the values bound to the prepared statement? I wonder if the true_false type is not what causes the trouble. Also, why aren't you using HQL for such a static query. Criteria is useful for dynamic queries, but it makes things more complex and less readable than they should be for static queries. — JB Nizet, Dec 26 '12 at 21:29
I have added the query that is being generated. As for not using HQL, well I am not sure how to write one, plus everything else is dynamic so I guess that it is not a real issue to use it criteria here as well — Boris Horvat, Dec 26 '12 at 21:35

Raul Rene · Accepted Answer · 2012-12-27T13:38:15.347

6

What you are trying to do is theoretically correct and should work. Nevertheless, I am thinking of some possible things that may prove to be wrong:

Make sure your getter and setter are properly used. Your code should look like:

private boolean viewed;

@Type(type = "true_false")
@Column(name = "Viewed")
public boolean isViewed() {
    return viewed;
}

public void setViewed(boolean viewed) {
    this.viewed = viewed;
}

Make sure your DB field is a BIT (or the corresponding data type for boolean values on the DB you are using).
If neither 1 nor 2 work, I suggest removing the @Type annotation as it is not necessary, although it should do no harm.

Edit:

You are trying to map a boolean to a char. I do not really understand why you would use a char(1) instead of a BIT. Nevertheless, if you want to do so, work with a String in the model class.

Then, if your DB column holds 0 or 1, use:

criteria.add(Restrictions.eq("participants.viewed", "0"));

Or if your DB column holds true/false, use:

criteria.add(Restrictions.eq("participants.viewed", "false"));

edited Dec 27 '12 at 13:38

answered Dec 27 '12 at 10:01

Raul Rene

10,014
9
53
75

I have given above how I construct my table. The Viewed char (1) goes hand in hand with @Type annotation. If I put BIT for the column and remove the type it seems to work. However I would still like to keep char as the type (it is sortof a requirement), and I am a bit confused why it wouldnt work in this manner anyway (tnx for help) – Boris Horvat Dec 27 '12 at 12:45
2

Hi, tnx for the help. I have actually tried that before, your example got me thinking and eventually I tried to use Boolean.FALSE and it worked. – Boris Horvat Dec 27 '12 at 20:19
I edited your answer because the `"false"` will fail. use the datatype itself e.g. `false` or `0` instead. – DanielK Sep 24 '15 at 15:14

Hibernate alias to a simple boolean field

1 Answers1