Eclipse Community Forums: EclipseLink » Join table with constant value column

Home » Eclipse Projects » EclipseLink » Join table with constant value column

Join table with constant value column [message #552113]

Tue, 10 August 2010 17:58

Eclipse User

I'm attempting to map on top of a set of existing tables defined by XRMS (an open source CRM) and I'm having trouble figuring out the best way to set up a JPA mapping that I was hoping I could get some advice on.

There are 2 tables that I am trying to map via a join table. The source table is companies and has a column named company_id. The destination table is categories which has a category_id. There is a join table named entity_category_map which has a category_id, on_what_id, on_what_table. The on_what_id would be the company_id in this case and the on_what_table would be set to 'company'.

What I can't figure out is how to define a constant value for the on_what_table join column. I found online that OpenJPA supports this by specifying the constant value in the referencedColumnName like below:

@ManyToMany
@JoinTable(
name="entity_category_map", schema="crm",
joinColumns={@JoinColumn(name="on_what_id", referencedColumnName="company_id"),
@JoinColumn(name="on_what_table", referencedColumnName="'company'")},
inverseJoinColumns=@JoinColumn(name="category_id", referencedColumnName="category_id")
)
public List<Category> getCategories() {
return categories;
}

public void setCategories(List<Category> categories) {
this.categories = categories;
}

Re: Join table with constant value column [message #552557 is a reply to message #552113]

Thu, 12 August 2010 11:51

Eclipse User

You can use a DescriptorCustomizer to set a selectionCritieria on your ManyToManyMapping in EclipseLink to add the check for the on_what_table.

See,
http://wiki.eclipse.org/EclipseLink/Examples/JPA/MappingSele ctionCriteria

You will also need to set the insertSQLString for the mapping to insert this constant value as well.

Another solution is to create an Entity to represent the join table, define a 1-m to the join entity and have it define a 1-1 or VariableOneToOne to the target.

The best solution is probably to fix the data model to use a proper join table per relationship. Then you would also be able to define a foreign key constraint on the database.

Re: Join table with constant value column [message #552776 is a reply to message #552557]

Fri, 13 August 2010 09:22

Eclipse User

Thanks for the feedback. I hadn't looked at the DescriptorCustomizer before, that definitely looks like it would work.

For now I just ended up creating a view of the join table since it's only being used as read-only right now.

Previous Topic:	JoinFetch does not work with ORDER BY in query
Next Topic:	NamedQuery generates invalid SQL

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 02 07:00:20 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter