Hi
I am new to Hibernate and I would like to persist a list of tags/words that i get from an XML file. I have a POJO (with id,tag) for a Tag and use the generator to automatically assign the id

<id
name="id"
column="TAG_ID">
<generator class="increment"/>
</id>

<property
name="tag"
column="TAG"
node="tag"
not-null="true"
unique="true"/>

<property
name="count"
column="COUNT"
not-null="true"
unique="false"/>

The problem is that I would like to have the word to be unique. ie do not insert if already present. If I try to

currentSession.save(tag1);

And a tag with the same name is already present. I get a "Duplicate key or integrity constraint violation" ConstraintViolationException (stacktrace below).

In order to avoid this I tried to use:

Tag tag1 = (Tag) currentSession.createQuery("from Tag as Tag where Tag.tag like :tagString")
.setParameter("tagString", tagString)
.uniqueResult();

if (tag1 == null){
// tag is new
currentSession.saveOrUpdate(tag);
} else {
// update count
}
Which would have been alright, but I need to do this for over 10 million tags, which is gonna be inefficient.

I am wondering if someone can help me with what is the correct way to map this.

There is another thing I read about "business keys" and have appropriately implemented the equals and hashcode in the TAG POJO, but I am not sure how to check it the tag is already presisted.

Alternatively is it that I need to use composite-id? In which case it seems like I would not be able to use the <generator class="increment"/>.

It seems to me like there might be an easy way to do this and I am just missing some detail here.
I would appreciate any help or pointers to examples.
Thanks
Akshay





Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
] Exception in thread "main" org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:69)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
[java] at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
[java] at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:230)
[java] at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
[java] at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
[java] at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
[java] at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:980)
[java] at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:353)
[java] at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
[java] at edu.umbc.weblog.feed.WeblogManager.testTag(Unknown Source)
[java] at edu.umbc.weblog.feed.WeblogManager.main(Unknown Source)
[java] Caused by: java.sql.BatchUpdateException: Duplicate key or integrity constraint violation message from server: "Duplicate entry 'Akshay' for key 2"
[java] at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1540)
[java] at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
[java] at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
[java] ... 9 more

Name and version of the database you are using:
MySQL
The generated SQL (show_sql=true):
create table TAG (
[java] TAG_ID bigint not null,
[java] TAG varchar(255) not null unique,
[java] COUNT bigint not null,
[java] primary key (TAG_ID)
[java] )
Debug level Hibernate log excerpt:

Here are a few approaches, but it depends on your situation (how many words, how often, how many repeats, etc.):

1) check if it exists before you do it (as you suggest in your question)

2) handle the exception from the DB when there is a clash

3) use the word as the primary key -- since it must be unique, this will cause Hibernate to update the existing record (if necessary) when you encounter the same word again.

4) manage some cache of words yourself, to reduce explicit DB checks. The trick there is to not try to cache all 10 million words.

More details about your situation may help us answer.

hi damonf
Thanks for the reply!
Basically what I am trying to do is read a bunch of xml files and dump them into the database (I have about 10 million XML nodes that need to be updated into the DB)

Basically it seems like there are following alternatives:

I'm not sure which query style is faster. I assume native SQL is fastest, so if you need to performance tune you can try that.

There are a few ways to query for number of matching rows here: http://www.hibernate.org/118.html#A2