Hi guys,

I want to do a N:N relationship to map these classes: Document and Word.
Something like this:

Word {
int id;
String word; //must be unique
int freq;
}

Document {
int id;
List<Word> words;
}


By default, the mapping would be something like:

table_word:
id integer (pk)
word varchar (unique)
freq integer

table_document:
id integer (pk)

table_document_word:
id_document integer
id_word integer

At application level, when I am dealing with those objects, first I process Documents discovering new words.
At this time, I don't know yet the word id, only the word string. But, as I want to have unique strings at table_word,
How can I map these classes?

One naive map approach will violate the table_word unique constraint when already persisted words are found in Document.
By performance reasons, I don't want to query words objects by the string value to discover if the object is already persisted to get the id.
Also, I believe that maintain one Map<String, Integer> in memory to map each string to an existing database id is not the best approach.

I would like a solution in the same fashion way that Mysql does with "on duplicated key update":
INSERT INTO table_word (word,freq) VALUES ('word_string_value',1) ON DUPLICATE KEY UPDATE freq=freq+1;

How you guys would solve this problem?

Thanks in advance,

Rodrigo.