CODEWITHSUNDEEP
hibernatejpa
Alex
Alex

Reputation: 1081

JPA modeling a table without an entity ID

I'm building a simple indexing system for a list of products. I have a table of keywords and product IDs, and a score indicating the relevance of the keyword to the product:

CREATE TABLE item_index (
  keyword varchar(50) NOT NULL,
  item_id int NOT NULL,
  score int NOT NULL,
  INDEX keyword_index (keyword)
);

(item_id is actually a foreign key to the item table, not shown here.)

In SQL I would query the index as follows:

SELECT item_id,score FROM item_index WHERE keyword=? ORDER BY score DESC;

This gives me a list of item IDs relevant to the keyword, in order of relevance.

My question is: How can I do this using JPA?

My attempt is as follows: 

@Entity
@Table( name="item_index" )
@NamedQueries({
  @NamedQuery( name="ItemIndex.findByWord", query="SELECT i from ItemIndex i WHERE i.keyword=:keyword ORDER BY i.score DESC" ),
  @NamedQuery( name="ItemIndex.deleteAll", query="DELETE FROM ItemIndex" )
})
public class ItemIndex implements Serializable
{
  private static final long serialVersionUID = 1L;

  @Column( name = "keyword", length=50 )
  private String keyword;

  @ManyToOne( fetch = FetchType.LAZY )
  @JoinColumn( name = "item_id" )
  private Item item;

  @Column( name = "score" )
  private int score;

  /* Getters and setters not shown */
}

This approach doesn't work because JPA (Hibernate in my case) requires that the entity has an ID field. I don't need a unique ID, since the only way the table will ever be queried is as in the SELECT statement above.

Note that I don't want to store the keywords in the Item entity - the Item should have no knowledge of the indexing system.

I can envisage solutions involving a new table of {item_id,score} tuples, but I wonder if it's possible to do it using just the one table above?

Thanks for any help!

Upvotes: 1

Views: 332

Answers (1)

nakosspy
nakosspy

Reputation: 3942

The item_index table seems to have a composite id, that consists of item_id and keyword. If I understand correctly these pairs are unique in your table. This indicates that you could define this as primary key to your table and use it as composite id of your entity.

Take a look at JPA composite primary key for help regarding the definition of a composite id. This article could also be helpful.

Upvotes: 2

Related Questions