-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: Hibernate query returns a list with null elements
PostPosted: Mon Oct 10, 2016 7:23 pm 
Newbie

Joined: Mon Oct 10, 2016 6:56 pm
Posts: 4
Hi all,

I have seen the issue that I described in the subject, but I think in my case is due to another different cause.

Let's say that I've already created my Database manually (so tables were not created by hibernate). SQL Script (using an Oracle DB) to create one of my tables is like this:

Code:
CREATE TABLE "SOMETHING_TABLE"
  (
    "COLUMN1"       CHAR(10 BYTE) CONSTRAINT "SOMETHING_TABLE_COLUMN1_PK" NOT NULL ENABLE,
    "COLUMN2"       CHAR(10 BYTE),
    "COLUMN3"       CHAR(2 BYTE),
  )
CREATE UNIQUE INDEX "PK_SOMETHING_TABLE" ON "SOMETHING_TABLE"
  (
    "COLUMN1", "COLUMN2", "COLUMN3"
  )


My hibernate entity is like the following:

Code:
@Entity
@Table(name = "SOMETHING_TABLE")
public class Something_tableImpl {

    private static final long serialVersionUID = 1L;
    @Id
    @Column(name = "COLUMN1")
    private String column1 = null;

    @Id
    @Column(name = "COLUMN2")
    private String column2 = null;

    @Id
    @Column(name = "COLUMN3")
    private String column3 = null;

    @Column(name = "COLUMN4")
    private String column4 = null;

    public String getColumn1() {
        return column1;
    }

    public void setColumn1(String column1) {
        this.column1 = column1;
    }

    public String getColumn2() {
        return column2;
    }

    public void setColumn2(String column2) {
        this.column2 = column2;
    }

    public String getColumn3() {
        return column3;
    }

    public void setColumn3(String column3) {
        this.column3 = column3;
    }

    public String getColumn4() {
        return column4;
    }

    public void setColumn4(String column4) {
        this.column4 = column4;
    }
}


And the hibernate query is like the following:

Code:
public Something_table GetSomethingFrom(String column1, String column2, String column3) {
            Criteria queryCriteria = myDAO.getCriteria(Something_tableImpl.class);
            queryCriteria.add(Restrictions.eq("column1", column1));
            if (StringUtils.isNotBlank(column2)) {
                queryCriteria.add(Restrictions.eq("column2", column2));
            }
            if (StringUtils.isNotBlank(column3)) {
                queryCriteria.add(Restrictions.eq("column3", column3));
            }
            List < Something_table > somethingTableList = queryCriteria.list();
            if (!somethingTableList.isEmpty()) {
                return somethingTableList.get(0);
            }
        return null;
    }


Let's say that I've already inserted one row in the DB where COLUMN2 and COLUMN3 are NULL (which are part of the index):

Code:
INSERT INTO SOMETHING_TABLE (COLUMN1,COLUMN2,COLUMN3) VALUES ('00011Z66',NULL,NULL);


Whenever I try to get that row that I just inserted, using the above hibernate query, I get a list with one element which is NULL.
If I try to get the same row but directly in Oracle, using the query that underlying was generated by hibernate, then I'm able to get that row that was inserted without problems.


So my question is: Why hibernate is not able to get that row that was inserted?


Top
 Profile  
 
 Post subject: Re: Hibernate query returns a list with null elements
PostPosted: Tue Oct 11, 2016 6:14 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Try using an embeddable id instead. Maybe there's an issue with the way composite ids are handled by this query.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate query returns a list with null elements
PostPosted: Tue Oct 11, 2016 9:25 am 
Newbie

Joined: Mon Oct 10, 2016 6:56 pm
Posts: 4
vlad wrote:
Try using an embeddable id instead. Maybe there's an issue with the way composite ids are handled by this query.


I don't think I can use embeddable id. This would be like to use @idClass, right?. Then, none of fields that belong to the index can be null, and what I need is to allow that column2 or column3 can be null.


Top
 Profile  
 
 Post subject: Re: Hibernate query returns a list with null elements
PostPosted: Tue Oct 11, 2016 9:57 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
It's not an IdClass. Check the Hibernate 5.2 documentation link that I gave you. Also, a Primary Key is not allowed to use NULL because NULL means UNKNOWN in database terms.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate query returns a list with null elements
PostPosted: Tue Oct 11, 2016 1:38 pm 
Newbie

Joined: Mon Oct 10, 2016 6:56 pm
Posts: 4
vlad wrote:
It's not an IdClass. Check the Hibernate 5.2 documentation link that I gave you. Also, a Primary Key is not allowed to use NULL because NULL means UNKNOWN in database terms.


Yes, no problem with the primary key concept :) .

Let me explain you why I'm asking these weird things: someone created in the DB that unique index that I posted in the first comment. Why that person did that? basically to ensure that the combination of column1+column2+column3 was always different, allowing nulls values in that combination for column2 and column3.

Then this person, added @Idclass in the hibernate entity, which obviusly produced errors (since the pk can't include null values).

We don't want to change the DB structure, nor the values that historically have been there.

So I was trying to apply some ""fix"" from the hibernate side. But looks like there's no other way than modify the DB structure OR modify those nulls values in the DB to have some default value.

So that is the reason that I think the embeddable ids strategy won't work. Whatever I do, if there are any null value. Then, Hibernate will explote.

I was thinking in use something like this: http://stackoverflow.com/a/1776125/6243160

But like a one guy commented:


Quote:
I wanted to mention, because I just had this problem, that if any of those columns you are using for your composite key are NULL, than Hibernate will return null objects. I was pulling everything from a View and one of the columns was all null so hibernate was returning a list with the right number of items in it, but all the items were null. In my case I couldn't modify the field, so I just removed it from the Key


which is the same issue that I'm having.


Top
 Profile  
 
 Post subject: Re: Hibernate query returns a list with null elements
PostPosted: Wed Oct 12, 2016 1:30 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
But what's the PK of that table? You can use that PK as an identifier and leave that UNIQUE INDEX which doesn't really enforce strict unicity since it allows null values.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate query returns a list with null elements
PostPosted: Wed Oct 12, 2016 8:07 am 
Newbie

Joined: Mon Oct 10, 2016 6:56 pm
Posts: 4
vlad wrote:
But what's the PK of that table? You can use that PK as an identifier and leave that UNIQUE INDEX which doesn't really enforce strict unicity since it allows null values.


That's the point. That table doesn't have any PK, like the script is showing. So actually we have been trying to ""fix"" an issue from the wrong side ( hibernate) just because we wanted modify the DB ( therefore, take care about all the issues that might trigger in production)

Finally we decided to add a new column in that table ( a sequence which will be the PK).Which was what I wanted from the very start.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.