Java EE (Java Enterprise Edition) General Discussion

Hi All,

I am developing a land surveying application in Java EE 5 (JDK1.6.0_17, GF 2.1 on XP) with the following characteristics & outcomes:
( i ) The data supplied is made up of detail between counties and surrounding schools. There are a dozen of schools on average in any county. However, there will be some over-lapping schools that are a long the border of adjacent counties. For instance, county 1 consists of A, B, C, D, E, F, G, H, I, J, K, L schools while county 2 (next to each other) is made up of M, N, B, O, P, Q, R, E, S, T, U, H schools. The common ones are B, E and H and are stored as duplicate records in the SCHOOL table regardless of whether OneToMany or ManyToMany entity relationship is used. On the other hand, ManyToOne model only store the last school by overwriting the earlier ones. The COUNTY was the owning table and both uni and bi-directional for all 3 models have been tested.

( ii ) An updateOrSave() which uses manager.merge() has exasperated the issue by multiplying the number of duplicate by 3-4 folds.

( iii ) Setting uniqueConstraints on SCHOOL such as the one below prevented both county 1 and its schools (A, B, C, D, E, F, G, H, I, J, K, L) from being persisted:

@Entity
    @Table(name="SCHOOL", uniqueConstraints={@UniqueConstraint(columnNames={"SCHOOL_NAME"})})

server.log showed the following error:
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'SCHOOL C' for key 'UNQ_SCHOOL_0'
Error Code: 1062
even though school C was unique.

( iv ) I am wondering whether this duplication was due to the CascadeType set to ALL such as:

@OneToMany(cascade={CascadeType.ALL}, fetch=FetchType.EAGER)
    @JoinTable(name="COUNTY_SCHOOL", 
               joinColumns={@JoinColumn(name="COUNTY_NAME", referencedColumnName="COUNTY_NAME"),
                                        @JoinColumn(name="STATE", referencedColumnName="STATE")},
               inverseJoinColumns={@JoinColumn(name="SCHOOL_ID")})
    private Set<School> schools = new HashSet<School>();

In short, I am looking for advice on adopting the simplest entity relationship where hopefully that manager.merge() could eliminates the need to carry out explicit checks for each schools. Perhaps by separating manager.persist() for county and school so that new county record will not add, but update (associate) existing school to it instead, if possible. The type of answers I am looking for are:
( a ) Which entity relationship is the best fit and why?
( b ) Which table should be the owner and why?
( c ) Which CascadeType to use & why? How to manually add county and schools separately if ALL is not correct?
( d ) Is it possible to use manager.merge() (depending on JPA) to efficiently add unique school record once and link/associate to more than one counties? In other word, no need to query database for every new school record inserted.

I have spent a few months on this issue trying out various entity relationships but still struggle to find a resolution to this issue. Is it possible that this issue was due to a short-coming in GF 2.1? I have gone through numerous examples including Java EE 5 tutorials and do not have problem coming up with either of these models.

As always, your assistance would be much appreciated.

Thanks,

Jack