ORM: OneToOne mapping on Non Primary-Key Join column - Book and Inventory mapped by ISBN

Question

I have a Book model and Inventory model mapped by ISBN number, but ISBN is not the primary key in either. Books belong to Bookstores and Inventory is for a group of Bookstores(BookstoreChain). Inventory is shared by all Bookstores belonging to a BookstoreChain.

I'm using Hibernate @OneToOne mapping on the book side to fetch inventory info by joining the ISBN column. Somehow, Hibernate generates the left outer join query correctly, but inventory is null on the Book object. Its not lazy loaded either. Ignoring the Bookstore and Chain, how do i do a OneToOne or ManyToOne join and fetch inventory when Books are fetched?

class Book{
@Id
Long id

@Column
String isbn;

@Column
String title;

@OneToOne(optional = true)
@JoinColumn(name = "isbn", referencedColumnName = "isbn",insertable = false, updatable = false)
Inventory inventory;
}

class Inventory{
@Id
Long id

@Column
String chainId

@Column
String isbn

@Column
Long availableQty
}

Answer 1

just a guess: does name = 'ISBN' need to be the same case as the field in Inventory?

Answer 2

I doubt that this has anything to do with the issue, but I thought I would bring it up anyways just to ensure that it isn't a gotcha that was overlooked:

Note that when using referencedColumnName to a non primary key column, the associated class has to be Serializable.

Reference: [http://docs.jboss.org/ejb3/app-server/HibernateAnnotations/reference/en/html_single/index.html][1]

I know you said that the LEFT OUTER JOIN is being generated, but I thought that the default fetch was LAZY if it was not specified. Perhaps, explicilty specifying the fetch mode may yield different results.

Lastly, if you are using an HQL query, perhaps posting that with any other entity classes will help the community help resolve the issue.

Answer 3

Your database schema doesn't make sense, based on what you've shown here. The relationship between book and inventory should be one to many - presumably, the same book is in several inventories, which means you can't associate books and inventories just with the isbn. Since the isbn is not unique across inventories, you'll have multiple rows in inventory with the same isbn but different chainIds - which row is the right row for a given book/isbn? Book should have a foreign key to inventory.id, not inventory.isbn.

Answer 4

You have to name your join reference something else. isbn is already a column. Try this:

@OneToOne(optional = true)
@JoinColumn(name = "inventory", referencedColumnName = "isbn",insertable = false, updatable = false)
Inventory inventory;