hi list,
i'm using hibernate 3.0.2 with postgres 8.0.x.

I have the following model:

Place(id,name) -- many-to-many-- Tags(id, tag_string, language)


The most special thing on this is that i want a
UNIQUE (tag_string, language) constraint on Tag.


My problem from the application side is how to handle the UNIQUE constraint on the Tag object on insert of a new Place with serveral Tags.

I now of course that i can catch the "ConstraintViolationException" if i would insert each Tag step by step.
But what i really want is a kind of correct Hibernate Model, or a Trigger in the database so that i can just call:

session.saveOrUpdate(place);

Note that i don't need bimodal relationship (so dont need Tag to Place - i attached my mapping). So there's maybe an easy way to handle the unique constraint?



----------------------------------
My model configuration is like this


=== SQL =============================

CREATE TABLE place (
id serial PRIMARY KEY,
name VARCHAR
);
CREATE TABLE tag (
id serial PRIMARY KEY,
tag_string VARCHAR,
language VARCHAR(2),
UNIQUE(tag_string,language)
);
CREATE TABLE place_tag (
place_id integer REFERENCES place ON UPDATE CASCADE ON DELETE SET NULL,
tag_id integer REFERENCES tag ON UPDATE CASCADE ON DELETE SET NULL,
PRIMARY KEY (place_id, tag_id)
);



== place.hbm.xml ===========================
<class name="Place" table="place" schema="public" lazy="true" select-before-update="true">
<id name="id" type="integer">
<column name="id" />
<generator class="sequence">
<param name="sequence">location_id_seq</param>
</generator>
</id>
<property name="name" type="string">
<column name="name"/>
</property>
<set name="tags" table="place_tag" cascade="all">
<key>
<column name="place_id" not-null="true" />
</key>
<many-to-many class="Tag">
<column name="tag_id" not-null="true" />
</many-to-many>
</set>
</class>

== tag.hbm.xml =================================
<class name="Tag" table="tag" schema="public" lazy="true" select-before-update="true">
<id name="id" type="integer">
<column name="id" />
<generator class="sequence">
<param name="sequence">tag_id_seq</param>
</generator>
</id>
<properties name="uniquetag" unique="true">
<property name="tagString" type="string">
<column name="tag_string" not-null="true" />
</property>
<property name="language" type="locale">
<column name="language" length="2" />
</property>
</properties>
</class>


Exception if i save a location 2 times ( so that tags are the same the unique constraint is violated )

session.saveOrUpdate(place);
session.flush(place);
session.saveOrUpdate(place);


i get

Hibernate: select nextval ('place_id_seq')
Hibernate: select nextval ('tag_id_seq')
Hibernate: select nextval ('tag_id_seq')
Hibernate: insert into public.location (name, id) values (?, ?)
Hibernate: insert into public.tag (tag_string, lang, id) values (?, ?, ?)
Hibernate: insert into public.tag (tag_string, lang, id) values (?, ?, ?)
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:63)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:179)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:72)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:67)
at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:148)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1829)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2190)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:46)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:726)
at de.emld.Main.directInsert(Main.java:137)
at de.emld.Main.builder(Main.java:67)
at de.emld.Main.main(Main.java:47)
Caused by: java.sql.BatchUpdateException: Batch-Eintrag 0 insert into public.tag (tag_string, lang, hits, id) values (day2, de, 0, 90) wurde abgebrochen. Rufen Sie getNextException auf, um die Ursache zu erfahren.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2392)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1257)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:334)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2451)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:172)
... 15 more
Exception in thread "main"







Pointing me to a doc which describes "handling of database constrainst in hibernate" would really help me.

Thx,
chris

Sorry,
forgot the important part.


The way i do it now is:

--------------------------------
// ...
Place place = new Place(..,..)
//...
Session session = HibernateUtil.currentSession();
Transaction tx = session.beginTransaction();
tagDAO.checkUniqueConstraint(place.getTags());
session.saveOrUpdate(place);
session.flush();
tx.commit();


where tagDAO#checkUniqueConstraint is:
-------------------------
public void checkUniqueConstraint(Set tags) {
Set tmpTags = new HashSet();
for(Iterator i=tags.iterator(); i.hasNext();) {
Tag tag = (Tag) i.next();
Tag tmpTag = getTag(tag.getTagString(), tag.getLang());
if(tmpTag != null) {
i.remove();
tmpTags.add(tmpTag);
}
}
tags.addAll(tmpTags);
}


public Tag getTag(String tagString, Locale lang) {
List res = session.createSQLQuery(
"SELECT {t.*} " +
"FROM tag as t " +
"WHERE tag_string='" + tagString + "' and lang='" + lang.getLanguage() + "'"
)
.addEntity("t", Tag.class)
.list();
if(res.size()>0) {
Tag tag = (Tag) res.iterator().next();
return tag;
}
return null;
}



Thats the stuff i want to make better.
Not checking each tag speratly before inserting a new place.



Chris