jpa - Elegant localized Java ORM with default locale -

we have dto ui purposes; java pojo this: id (primary key keep track), code (non-localized attribute), and, notably, name , description. these 2 attributes need localized.

we need entity , database model allow localization of "tuple" (name, description). use jpa oracle database, try avoid vendor-specific features. in java, prefer criteria api queries. if non-standard ways have strong benefits, consider them, though.

i. – no restrictions regarding database design

1. what straight-forward, or (if differing) best performing, database model , mapped java entity this?

2. what sql select one row (id, code, name, description) given id , locale - returning values name , description taken default localization if requested locale not default , translation not yet exist object (id)? , criteria api java code this, i.e., directly populate our dto in given locale context? use case german user listing data sets not localized data available in german, in rows need display english text.

we prefer have fall-back decisions being made on database side. if code becomes cleaner or more maintainable, or query clearer or faster, accept default-localized data always transferred java (even if not strictly needed in every case), plus data one requested localization (if needed , / or available). try avoid fetch data all locales, since query results go "directly" ui without caching, presented logged-in user favors view data in personal locale.

ii. – special database design

now let's assume additional requirement, regarding database design. simple table required base data, along default-localized text data - i.e., merely above dto mapped one simple database table columns id, code, name , description. if data requested default locale - english - sufficient simple select table.

all localized data (name, description) non-default locales - e.g., german - should kept somewhere else (one or more additional tables). data non-localized attributes, such above code property, should stored according dry, i.e., not duplicated.

i tend call "one one-or-more" mapping - common name such design? can fact adding localized data (columns) must done in more 1 table somehow enforced?

and again, database , java models , queries (sql, , jpa / criteria api) in section i.?