Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » @ManyToMany: how include columns from @Embeddable map key in primary keys to avoid duplicate errors
@ManyToMany: how include columns from @Embeddable map key in primary keys to avoid duplicate errors [message #660538] Sat, 19 March 2011 00:20 Go to next message
darren is currently offline darrenFriend
Messages: 19
Registered: March 2011
Location: Sydney, Australia
Junior Member
I have an interesting @ManyToMany map mapping case and have run into problems with duplicates on the primary keys, which are taken to be the same as the @JoinColumns; I want to figure out how to include columns from a discriminating @Embeddable to remove the duplicate error:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '184-174' for key 1
Error Code: 1062
Call: INSERT IGNORE INTO Activity_involves_BlockList (Block_ID, Activity_ID, LISTNAME, OWNERID) VALUES (?, ?, ?, ?)
bind => [4 parameters bound]
Query: DataModifyQuery(name="involvedBlockLists" sql="INSERT IGNORE INTO Activity_involves_BlockList (Block_ID, Activity_ID, LISTNAME, OWNERID) VALUES (?, ?, ?, ?)") 


Background of mapping:


I am developing a web application that employs a systems engineering strategy where human resource activities are related to physical "blocks" (the blocks are said to be "involved in" the activities, the activities "involve" the blocks). Often however there are lists of blocks, and it is too tedious to relate an activity to individual blocks, I need to be able to relate an activity to a list of blocks. I also need to maintain reverse links from blocks back to the activities they are involved in.

There are 2 basic candidates:

1. Have a separate ListWrapper entity and relate activities and lists of blocks via them; this is resource intensive and I wish to avoid it if possible.

2. Activities keep track of the property names (only) of child lists of block, together with the id of the owning block. Reflection is then used to obtain the required list whenever needed.

I've nearly got the 2nd approach working (right through to a JSF user interface), however I run into problems when I have 2 child lists owned by the same Block and involved in the same Activity.

Case: Block subclass OfficeBuilding has 2 separate lists:
- List<OfficeFloor> floors;
- List<OfficeTenant> tenants;

There is an Activity named (for test purposes) "this involves all floors and all tenancies of the office bulding", where the office building instance is unique within the project.

The attempted mapping and the duplicate problem:

In entity class Activity I have the following (I omit the support code that leverages reflection to exploit the mapping):
    private Map<ListKey,Block> involvedBlockLists;
    @ManyToMany
    @JoinTable(
        name="Activity_involves_BlockList",
        joinColumns={ @JoinColumn(name="Activity_ID", referencedColumnName="ID")},
        inverseJoinColumns={@JoinColumn(name = "Block_ID", referencedColumnName = "ID")}
        )
    public Map<ListKey, Block> getInvolvedBlockLists() {
        return involvedBlockLists;
    }
..

Where the embeddable ListKey (which happens to use Pojomatic for equals() and hashCode(), but this probably plays no role in the duplicate problem) is:
@Embeddable
public class ListKey implements Serializable {

        public ListKey() {}

        public ListKey(String listName, Long ownerId) {
            this.listName = listName;
            this.ownerId = ownerId;
        }

        private String listName;
        @Property
        public String getListName() {
            return listName;
        }
        public void setListName(String listName) {
            this.listName = listName;
        }

        private Long ownerId;
        @Property
        public Long getOwnerId() {
            return ownerId;
        }
        public void setOwnerId(Long ownerId) {
            this.ownerId = ownerId;
        }

    @Override
    public String toString() {
        return "ListKey: listName("+getListName()+"), ownerId("+getOwnerId()+")";
    }

    @Override
    public boolean equals(Object o) {
        return Pojomatic.equals(this,o);
    }

    @Override
    public int hashCode() {
        return Pojomatic.hashCode(this);
    }

}

And on the other side of the @ManyToMany the entity class Block has:
    private List<Activity> activitiesInvolvingLists;
    @ManyToMany(mappedBy="involvedBlockLists")
    public List<Activity> getActivitiesInvolvingLists() {
        return activitiesInvolvingLists;
    }


It works like a charm until I attempt to "involve" 2 different lists from the same owning Block in the one Activity (and I note here it worked completely before I had a bi-directional mapping).

The problem is that the @JoinTable Activity_involves_BlockList has Activity_ID and Block_ID as primary keys, as revealed by direct inspection of the created table in MySQL. What I need is to be able to (using JPA and/or EclipseLink annotations) additionally include the LISTNAME column from the embeddable map key ListKey (the other ListKey column OWNERID is in fact redundant and always equals Block_ID in the join table, but is handy to have when I retrieve the ListKey object).

Example:
- the OfficeBuilding instance (a kind of Block) has ID 174.
- the Activity instance has ID 184.
- the OfficeBuilding has a list named 'floors', which is involved successfully in the activity to give a row:

[Activity_ID = 184, Block_ID = 174, LISTNAME = floors, OWNERID = 174]

- the OfficeBuilding has another list named 'tenancies', which causes MySQL to spit the duplicates dummy when addition of the following row with the same Activity_ID and Block_ID (primary keys) is attempted:

[Activity_ID = 184, Block_ID = 174, LISTNAME = tenancies, OWNERID = 174]

Q: How can I (using EclipseLink and JPA, without hacking the SQL after the tables are generated) include the LISTNAME column - from the embeddable ListKey used as map key - in the primary keys of the join table (as a discriminator w.r.t. otherwise identical activity and block) ?

Most grateful for any feedback and tips,

Webel IT Australia
Re: @ManyToMany: how include columns from @Embeddable map key in primary keys to avoid duplicate err [message #660544 is a reply to message #660538] Sat, 19 March 2011 02:26 Go to previous message
darren is currently offline darrenFriend
Messages: 19
Registered: March 2011
Location: Sydney, Australia
Junior Member
Replying to own post.

I am pleased to report that (encouraged by this wikibooks article
Mapping a Join Table with Additional Columns) I have tried using an explicit intermediary entity corresponding to the join table to manage the mapping between named list properties of Block and Activity objects, and it works very well:
@Entity
public class BlockListMapper extends All_ implements Serializable {
    private static final long serialVersionUID = 1L;
    private Long id;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    
    private String listName;
    /** The property name of the mapped list.
     */
    public String getListName() {
        return listName;
    }
    public void setListName(String listName) {
        this.listName = listName;
    }
    
    private Block block;
    @ManyToOne
    /** The owner of the mapped list
     */
    public Block getBlock() {
        return block;
    }
    public void setBlock(Block block) {
        this.block = block;
    }

    private Element owner;
    @ManyToOne
    /** The owner of this list mapper (not the owner of the list) */
    public Element getOwner() {
        return owner;
    }
    public void setOwner(Element owner) {
        this.owner = owner;
    }
    
    public BlockListMapper() {}

    public BlockListMapper(Element owner, Block block, String listName) {        
        this.owner = owner;
        this.block = block;
        this.listName = listName;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof BlockListMapper)) {
            return false;
        }
        BlockListMapper other = (BlockListMapper) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Transient
    public Method getGetterForList() {
        try {
            String $i = "getGetterForList";
            String $getter = "get" + StringHelper.upperFirst(listName);
            return block.getClass().getMethod($getter);
        } catch (NoSuchMethodException ex) {
            log_error(ex);
        } catch (SecurityException ex) {
            log_error(ex);
        }
        return null;
    }

    @Transient
    public List<Element> getListElements() {
        List l = new ArrayList<Element>();
        for (Object o: getList()) {
            if (o instanceof Element) l.add((Element)o);
        }
        return l;
    }

    @Transient
    public List getList() {
            try {
                return (List) getGetterForList().invoke(block);
            } catch (IllegalAccessException ex) {
                log_error(ex);
            } catch (IllegalArgumentException ex) {
                log_error(ex);
            } catch (InvocationTargetException ex) {
                log_error(ex);
            }
            return null;
        }
}


lthough it requires a bit more coding in some parts to manage the bi-directional relationship via the intermediary, it brings with it a number of compelling advantages:

- I can reuse the mapper for various relationships and in many client classes.

- My list property reflection code (see above) is well isolated in the BlockListMapper, instead if polluting clients of the mapping, and the JSF user interface has quick and direct access to the list.

- Iteration of the mappers in JSF is easy.

- I can associate other information with the involvement relationship (and this invites having different categories of involvement of a block in an activity, without needing a separate relationship, i.e. I can parametrise the relationship).

This avoids any need to deal with maps (or nuances of the JPA implementation regarding maps), and few minor utillity classes I otherwise needed fell away, replaced by the single list mapper entity.

So I have a complete working solution, right through to the user interface, with easy backlinks to an involving Activity from both the owner Block of a list and the elements of that list, too.

I remain interested in the original question on how to force an embeddable map key's columns to be used as primary keys.
Previous Topic:MOXy 2.2.0 and CDATA
Next Topic:How to choose sessions.xml
Goto Forum:
  


Current Time: Thu Dec 18 12:39:44 GMT 2014

Powered by FUDForum. Page generated in 0.08662 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software