views:

65

answers:

2

When using Blob-fields in n-to-m-relations Hibernate and MSSQL are failing for some reason.

SQL Error: 421, SQLState: S0001
The image data type cannot be selected as DISTINCT because it is not comparable.
...
could not initialize a collection: [Dataset.documents#someID]

My classes look as follows:

@Entity
class Dataset {
    @OneToMany(fetch = FetchType.LAZY)
    public List<Document> documents = new ArrayList<Document>();
}

@Entity
class Document {
    @Id
    public long id;

    @Lob
    public byte[] data;
}

Any ideas on this? I already tried using a Set or Document[] to avoid the errors. It seems that Hibernate always tries a distinct SELECT on my tables. How can I workaround this?

[1] MSSQL error codes

A: 

I managed to solve the problem by adding a reversed reference to my Document class. I am sure there is a more direct way, because I do not need this reference at all, so it basically only helps to workaround the problem.

@Entity
class Dataset {
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "dataset")
    public List<Document> documents = new ArrayList<Document>();
}

@Entity
    class Document {
    @Id
    public long id;

    @Lob
    public byte[] data;

    @ManyToOne
    @JoinColumn
    public Dataset dataset;
}
Jan
+1  A: 

It would have been interesting to post the executed queries and the tables. But one difference I can think of between the two mappings (unidirectional one-to-many vs a bidirectional) is the way they're represented at the database level.

By default, a unidirectional one-to-many will use a join table:

DATASET       DATASET_DOCUMENT       DOCUMENT
-------       ----------------       --------
ID            DATASET_ID             ID
              DOCUMENT_ID

While a bidirectional will use the following representation:

DATASET       DOCUMENT  
-------       ----------
ID            ID        
              DATASET_ID

In JPA 2.0, it is now possible to use a unidirectional association without a join table (in a standard way) by specifying a @JoinColumn on the @OneToMany side:

@Entity
class Dataset {
    @Id
    @Column(name="ID")
    public Long id;
    ...
    @OneToMany
    @JoinColumn(name="DATASET_ID", referencedColumnName="ID")
    public List<Document> documents = new ArrayList<Document>();
}

I would give the above a try.

References

Pascal Thivent
You are right, this is exactly how it is done. Thank you for the explanation.
Jan
@Jan you're welcome
Pascal Thivent