Java Database Connectivity (JDBC)

Hello,

I want to map the following situation. I have a table called EDUCATION and a table called SCHOOLS. Between those tables I have an associative table called EDUCATION_SCHOOLS. The (usefull) fields:

EDUCATION:
id (long) - PK
name (varchar)
versionNr (long)

SCHOOLS:
id (long) - PK
name (varchar)
versionNr (long)

EDUCATION_SCHOOLS:
id (long) - PK
education_id (long) (FK to EDUCATION.id)
school_id (long) (FK to SCHOOLS.id)
name (varchar)
versionNr (long)

Their is a Unique Constraint between EDUCATION_SCHOOLS.education_id and EDUCATION_SCHOOLS.school_id.

What I want to be able to do:
EDUCATION: select, update, insert
SCHOOLS: select, update, insert
EDUCATION_SCHOOLS: select, update (only the non-FK fields), insert

I never want to delete anything in those tables. (and it's never ever going to be an option either)

Hibernate version:
Hibernate-Version: 3.0.5

Mapping documents:

Education:
<hibernate-mapping>
	<class name="##.Education" table="EDUCATION">
		<id name="id" column="ID" type="java.lang.Long">
			<generator class="sequence">
				<param name="sequence">EDUCATION_SEQ</param>
			</generator>
		</id>
		
		<version name="versionNr" column="VERSIONNR" type="long"/>
		
		<property name="name" column="NAME" type="string" />

        <set name="SCHOOLS" table="EDUCATION_SCHOOLS">
            <key column="EDUCATION_ID" />
            <many-to-many class="##.Schools" column="SCHOOL_ID" lazy="false" />
        </set>
</hibernate-mapping>

Schools:
<hibernate-mapping>
	<class name="##.Schools" table="SCHOOLS">
		<id name="id" column="ID" type="java.lang.Long">
			<generator class="sequence">
				<param name="sequence">SCHOOLS_SEQ</param>
			</generator>
		</id>
		
		<version name="versionNr" column="VERSIONNR" type="long"/>
		
		<property name="name" column="NAAM_NAME" type="string" />

        <set name="educations" table="EDUCATION_SCHOOLS" inverse="true" cascade="none">
            <key column="SCHOOL_ID" />
            <many-to-many class="##.Schools" column="SCHOOL_ID" lazy="proxy"/>
        </set>
</hibernate-mapping>

Education_schools:
<hibernate-mapping>
<class name="##.EducationSchools" table="EDUCATION_SCHOOLS">
		 <id name="id" column="ID" type="java.lang.Long" unsaved-value="0">
			<generator class="sequence">
				<param name="sequence">SEQ_EDUCATION_SCHOOLS</param>
			</generator>
		</id>
		
		<version name="versionNr" column="VERSIONNR" type="long" />
		
		<many-to-one name="education" class="##.Education" cascade="none" lazy="proxy"
        			column="EDUCATION_ID" not-null="true"/>
        <many-to-one name="schools" class="##.Schools" cascade="none" lazy="proxy"
                    column="SCHOOL_ID" not-null="true"/>   
</hibernate-mapping>    

Name and version of the database you are using:
Oracle XE 10g


I am able to:
EDUCATION: select, insert, update
SCHOOLS: select, insert, update
EDUCATION_SCHOOLS: select

Problems:
EDUCATION_SCHOOLS: when I try to insert, I sometimes get unique constraint violations. (when I should get them, thus I'm trying to insert something that already exists .. but how do I stop Hibernate from Inserting?)

EDUCATION_SCHOOLS: when I try to update, sometimes it works, but often I get:

23:03:55,484 [http-8081-1] ERROR be.vlaanderen.lne.vea.epb.ui.struts.EpbExceptionHandler - org.springframework.orm.hibernate3.HibernateOptimisticLockingFailureException: Object of class [##.EducationSchools] with identifier [null]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [##.EducationSchools#<null>]
ex.getMessage() Object of class [##.EducationSchools] with identifier [null]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [##.EducationSchools#<null>]
org.springframework.orm.hibernate3.HibernateOptimisticLockingFailureException: Object of class [##.EducationSchools] with identifier [null]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [##.EducationSchools#<null>]
Caused by: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect):

As you can see from the stacktrace I use Spring for the transactionManager: org.springframework.orm.hibernate3.HibernateTransactionManager in which I use e sessionFactory: org.springframework.orm.hibernate3.LocalSessionFactoryBean

In my DAO, I try to save with the regular this.getHibernateTemplate().saveOrUpdate that has always worked for me.

Another problem I have:
when i update "name" in EDUCATION, the records with that ID are delete from EDUCATION_SCHOOLS ...


As I am experiencing 3 different problems, I'm pretty sure something is wrong in the mapping files .. however I fail to find out what .. Any input would be greatly appreciated.

(I translated some class/table-names, that's what the ## cause)

Edited by: Bart_Blommaerts on Jul 29, 2008 11:53 PM