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.

Answer 1

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.

Answer 2

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.