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!
