Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Best way for Entities
Best way for Entities [message #509079] Thu, 21 January 2010 05:59 Go to next message
sebastian.schneider
Messages: 9
Registered: January 2010
Junior Member
Hello,

I've got these three tables, which I'd like to use with Eclipselink.

Language (approx. 20 records)
=================
LANGUAGE_ID (PK)

Category: (approx. 10.000 records)
=================
CATEGORY_ID (PK)
CATEGORY_NUMBER


Category_Lang (approx. 20 x 10.000 = 200.000 records)
=================
CATEGORY_ID (FK)
LANGUAGE_ID (FK)
CATEGORY_NAME
CATEGORY_SHORTNAME

The classes:


@Entity
@Table(name = "LANGUAGE")
public class Language implements Serializable {
	@Id
	@Column(name = "LANGUAGE_ID")
        private String id;
}

@Entity
@Table(name = "CATEGORY")
@SequenceGenerator(name = "CategorySequence", sequenceName = "SEQ_CATEGORY$CATEGORY_ID", allocationSize = 1)
public class Category implements Serializable {
	@Id
	@GeneratedValue(generator = "CategorySequence")
	@Column(name = "CATEGORY_ID")
        private int id;
	@Column(name = "CATEGORY_NUMBER")
	private boolean number;

         @OneToMany(mappedBy = "category")
	 private List<CategoryLang> langs;
	
	 public List<CategoryLang> getLangs() {
		 return langs;
	 }
}


@Entity
@Table(name = "CATEGORY_LANG")
public class CategoryLang implements Serializable {
	@Id
	@ManyToOne(targetEntity = Category.class)
	@JoinColumn(name = "CATEGORY_ID")
	private Category category;
	@Id
	@Column(name = "LANGUAGE_ID")
	private String languageId;

	@Column(name = "CATEGORY_NAME")
	private String name;
	@Column(name = "CATEGORY_SHORTNAME")
	private String shortName;
}


What is the best way migrate the following SQL query to EclipseLink

select c.categoryId, c.categoryNumber, cl.categoryName
from category c, category_lang cl
where c.categoryId = cl.categoryId
and cl.languageId = 'en'
order by cl.category_name

Thank you.

[Updated on: Thu, 21 January 2010 06:02]

Report message to a moderator

Re: Best way for Entities [message #509111 is a reply to message #509079] Thu, 21 January 2010 07:22 Go to previous messageGo to next message
Doug Clarke is currently offline Doug Clarke
Messages: 155
Registered: July 2009
Senior Member
The first thing I notice in your schema is that you appear to be duplicating the CATEGORY_NAME and CATEGORY_SHORTNAME in every row of Category_lang with the same CATEGORY_ID. If these values are duplicated I would recommend moving the unique values into the Category table. This would allow you to use Category_lang as the join-table in a M:M between Category and Lang.

If we assume you cannot modify the schema we can still address this.

Your SQL query converted to JPQL might look like:

SELECT cl FROM CategoryLang cl FETCH JOIN cl.category WHERE cl.languageId = 'en' ORDER BY cl.name

This will return a List<CategoryLang> with the Category relationship populated.

Doug
Re: Best way for Entities [message #509124 is a reply to message #509111] Thu, 21 January 2010 08:07 Go to previous message
sebastian.schneider
Messages: 9
Registered: January 2010
Junior Member
Hello,

thanks for your help.

The values aren't duplicated. Category_lang is the table which translates the name of the category in different languages.

I'm trying to explain the problem:
When I have let's say a class A with a Category category.

When I get a Object of A I want to have the category with the translated names of categoryNames for a given languageId.

Thanks

Basti

[Updated on: Thu, 21 January 2010 08:07]

Report message to a moderator

Previous Topic:Problems with auto-generated sequence
Next Topic:problem with persistence unit
Goto Forum:
  


Current Time: Wed Jul 23 16:19:42 EDT 2014

Powered by FUDForum. Page generated in 0.01516 seconds