2 Replies Latest reply on Oct 2, 2012 11:39 AM by 794081

    Legacy DB - Advanced @ManyToOne mapping


      I'm facing an advanced mapping problem which I'll describe in detail, this is my working environment:

      DB: legacy DB2 schema which cannot be altered as a decade-old application is using it.
      JPA: version 2, with persistence provider Apache OpenJPA (cannot be changed by me either).

      This are the tables that need to be mapped, in pseudo-code:
      /* Stripped down for clarity. */
        ID varchar, -- primary key
        FIRSTNAME varchar,
        LASTNAME varchar,
        TITLE smallint -- 'ID' of a referenced academic title, but this is not an actual foreign key! Pseudo-referenced column is 'TEXTS.ID'.
      /* Stripped down for clarity. This table not only holds academic titles but various objects that for some reason did not deverse a table of their own. */
        CATEGORY varchar, -- academic titles have category 'Title'.
        ID smallint, -- numerical ID, is unique only per CATEGORY.
        NAME varchar, -- holds the actual title, like 'PhD'
      This is what i want my JPA entity to look like:
      public class Person implements Serializable {
        private String id;
        private String firstName;
        private String lastName;
        /* Mapping magic will go here. */
        private String title;
      Now to my solution attempts:

      1.) Using @SecondaryTable to map the title to the person does not work as this annotation uses an inner join that will fail (produce wrong results). Reason: there is no CATEGORY in table PERSON, and there can be multiple rows that share the same ID in table TEXTS (but have different CATEGORY values).
      2.) Using @ManyToOne fails for the exact same reason: there is no way (well, that i know of) in standard JPA to somehow restrict a relationship on a given column value, save discriminator columns in inheritance situations.

      I know Hibernate has a @Where annotation that allows you to define a global where clause for an entity, which seems to at least go in the right direction. I'd need a @Where annotation that can be defined on join columns :-)

      The only possible but rather unsatisfactorily solution I could come up with was to define the title attribute as transient like this:
      private String title;
      Whenever I need a title (which I do a lot, people seem to love their shiny titles) it has to be queried manually. Are there any other solutions that I should know of, maybe involving some freak OpenJPA magic I'm unaware of?
        • 1. Re: Legacy DB - Advanced @ManyToOne mapping
          I wouldn't focus too much on wanting to solve this through JPA. What you have here is a 'problem' which you will run into in many forms - your business requirements do not map directly to the data layer. This simply means that you need some business logic to make the translation. For example if this were for a web layer I would implement a specialized bean which can take different entities and then provide an alternative view on the data, optionally by generating it.

          If 'calculated' data is closely tied to the database layer and less to the business layer then you could of course choose to fix it through the database itself - by creating a view and mapping an entity to that. That is especially useful if you need the same data in multiple aspects of the application framework and not only in the Java code (think of reporting and analysis for example), but it has other considerations like performance.
          • 2. Re: Legacy DB - Advanced @ManyToOne mapping

            I managed to totally overlook the view-based solution, thank you! Although in this specific case I'll go with your first suggestion and establish the mapping in my business layer (as I'm doing now with my transient title attribute). The reason is I don't have any reporting needs yet, so I'll stick with what I have.

            Thanks again!