missing rows with empty value in hibernate query

Question

I have two tables with foreign key references:

Comm TABLE:

+----+------------+
| ID |    NAME    |
+----+------------+
|  1 | comm name1 |
|  2 | comm name2 |
|  3 | comm name3 |
+----+------------+

LOCATION TABLE: - COMM_ID FK to  Comm --> id

+---------+------+-----+
| COMM_ID | FORM | TO  |
+---------+------+-----+
|       1 | 720  | 721 |
|       1 | 725  |     |
|       1 |      | 766 |
|       1 |      |     |
|       2 | 766  | 225 |
|       3 | 766  | 222 |
+---------+------+-----+

The problem is Hibernate returns my comm object with missing location in SET<location> All rows where there is no FROM and TO (like the last row with COMM_ID = 1 in table LOCATION) are missing.

Otherwise (if only one of FROM or TO) the row is returned... why?

Comm objects:

@ElementCollection
@CollectionTable(name="LOCATION",joinColumns=@JoinColumn(name="COMM_ID"))
 public Set<LOCATION> getLocations(){
    return locations;
 }
 public void setLocations(Set<LOCATION> locations){
    this.locations=locations;
 }

Location class:

@Embeddable
class Location implements java.io.Serializable {

    private BigDecimal fromLocationId;
    private BigDecimal toLocationId;

    public Location() {
    }

    public Location(BigDecimal fromLocationId, BigDecimal toLocationId) {
        this.fromLocationId = fromLocationId;
        this.toLocationId = toLocationId;
    }

    @Column(name="FROM", nullable=true, precision=22, scale=0)
    public BigDecimal getFromLocationId() {
        return this.fromLocationId;
    }

    public void setFromLocationId(BigDecimal fromLocationId) {
        this.fromLocationId = fromLocationId;
    }

    @Column(name="TO", nullable=true, precision=22, scale=0)
    public BigDecimal getToLocationId() {
        return this.toLocationId;
    }

    public void setToLocationId(BigDecimal toLocationId) {
        this.toLocationId = toLocationId;
    }

    @Override
    public int hashCode() {
        return com.google.common.base.Objects.hashCode(fromLocationId, toLocationId);
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null || getClass() != obj.getClass()) {
            return false;
        }
        final LOCATION  other = (LOCATION) obj;
        return com.google.common.base.Objects.equal(this.fromLocationId, other.fromLocationId) && com.google.common.base.Objects.equal(this.toLocationId, other.toLocationId);
    }
}

I'm using Hibernate - 4.3.6

LOG:

org.hibernate.SQL - 
    select
        locations0_.COMM_ID as COMM_ID1_2_0_,
        locations0_.FROM as FROM2_8_0_,
        locations0_.TO as TO4_8_0_
    from
        LOCATION  locations0_ 
    where
        locations0_.COMM_ID=1

I checked it in my DB and it's return the correct result.

Am I missing something? your data show from and to to be Strings, but you mapping says they should be numbers???? — Jens Schauder
– Jens Schauder, Commented Jun 23, 2015 at 5:21
Please post the whole LOCATION class. Since you're using Set it is mandatory that you override the equals and hashCode method correctly. — Tom
– Tom, Commented Jun 29, 2015 at 11:09
public boolean equals(com.google.common.base.Object obj) This doesn't look right. Please post the full class. And check if you post the real code. — Tom
– Tom, Commented Jun 29, 2015 at 11:24
Two things: first why is the "comm_id" variable missing? Don't you need it? It can be problematic if two rows of that table have different "comm_id"s, but both have no "from" or "to", so they are still "logical" equal to your program. Second: I assume that com.google.common.base.Objects.equal(this.fromLocationId, other.fromLocationId) is dangerous here. Since it should call BigDecimal#equals and this implementation is very restrictive about "equal" instances. To check if this is the problem, please test this implementation or use List instead of Set — Tom
– Tom, Commented Jun 29, 2015 at 11:58
I change my "equal" method and it's didnt help, anyway i add breakpoint to "equal" method and not hit was happend. (when location returns it's hit this breakpoint) — Idan
– Idan, Commented Jun 29, 2015 at 12:32

dom · Accepted Answer · 2015-07-06 10:05:30Z

3

An embeddable object's data is contained in several columns in its parent's table. Since there is no single field value, there is no way to know if a parent's reference to the embeddable is null. One could assume that if every field value of the embeddable is null, then the reference should be null, but then there is no way to represent an embeddable with all null values. JPA does not allow embeddables to be null, but some JPA providers may support this.

Hibernate : When loading an entity, embedded references are set to null if all of the columns in the embeddable are null.

for more info click here

so when u saving nullable embeddable values then hibernate allow to save but at the time of fetching hibernate discard those rows which are nullable.

edited Jul 6, 2015 at 10:05

answered Jul 6, 2015 at 9:54

dom

1,1061 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kucing_terbang · Accepted Answer · 2015-07-01 13:13:25Z

Actually, there is nothing wrong with the output. I think you just confused between embeddable and entity. because, as far as i know. You only use the embeddable class only for to make a development of an entity much easier if there are lot of entity which using the same fields.

And from what I see, the hashcode and equals methods do treat all those six records as a different record. So, I think what you want is to make the Location class into something like this.

@Entity
class Location implements java.io.Serializable {

private Long commId;
private BigDecimal fromLocationId;
private BigDecimal toLocationId;

// all the setter getter here

@Override
public int hashCode() {
    return com.google.common.base.Objects.hashCode(commId);
}

@Override
public boolean equals(Object obj) {
    if (this == obj) {
        return true;
    }
    if (obj == null || getClass() != obj.getClass()) {
        return false;
    }
    final LOCATION  other = (LOCATION) obj;
    return com.google.common.base.Objects.equal(this.commId, other.commId);
}
}

or, of course you can make the get method to return a List and then remove a row if the row is missing from or to value.

Collectives™ on Stack Overflow

missing rows with empty value in hibernate query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related