current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

we have question about inheritance in PostgreSQL and mapping it as entities in JPA. Our database, and tables we want to map are:

CREATE TABLE Answer (
    idAnswer SERIAL,
    answerContent VARCHAR,
    idQuestion INTEGER,
    version INTEGER,

    CONSTRAINT Answer_idAnswer_PK PRIMARY KEY (idAnswer),
    CONSTRAINT Answer_idQuestion_FK FOREIGN KEY (idQuestion) REFERENCES Question(idQuestion)
);


CREATE TABLE MatchAnswer (
    matchingAnswer VARCHAR NOT NULL,
    version INTEGER,

    CONSTRAINT MatchAnswer_idAnswer_PK PRIMARY KEY (idAnswer)       
) INHERITS(Answer);


CREATE TABLE TrueFalseAnswer (
    isTrue BOOLEAN NOT NULL,
    version INTEGER,

    CONSTRAINT TrueFalseAnswer_idAnswer_PK PRIMARY KEY (idAnswer)   
) INHERITS(Answer);

And we mapped them for entities using automatic tool in Netbeans 7.1.2. At first I thought it would be enough just to add 

@Entity
@Table(name = "truefalseanswer", catalog = "jobfairdb", schema = "public")
@XmlRootElement
public class Truefalseanswer extends Answer implements Serializable {
    private static final

so just extends, but it didn't work properly. What is the best approach to this? Thanks in advance.

share|improve this question
    
Which PostgreSQL version are you working with? It helps to say, as different versions have different capabilities. –  Craig Ringer Aug 7 '12 at 8:33
    
PostgreSQL 9.1.4, but we decided to simplify the idea. –  Atais Aug 7 '12 at 9:27

2 Answers 2

up vote 4 down vote accepted

JPA's concept of inheritance is based on ordinary tables. It doesn't really "get" the idea of PostgreSQL's table inheritance. That's one of the costs of working with a spec designed to expose the lowest common denominator of features and do so portably.

See this guide for a decent summary of JPA inheritance strategies. Note that in the newer Java 6 JavaDoc for @Inheritance there is a note saying that:

If the Inheritance annotation is not specified or if no inheritance type is specified for an entity class hierarchy, the SINGLE_TABLE mapping strategy is used.

... and if you look at how SINGLE_TABLE works, it's not surprising it doesn't work for you; it's expecting all subclasses to be in one big table with a magic discriminator value.

InheritanceType.TABLE_PER_CLASS is closer to how Pg behaves, but I suspect that the JPA impl will get a bit confused when the base type tables have entries for each entity of a leaf type. It tries to do things like UNION queries across the subclass tables when querying on the superclass, and that could produce odd results - at least duplication if UNION is used and performance issues if it uses UNION ALL. Depending on exactly how the provider implements the strategy it may work at least partially. You'd have to test, and the results would possibly be fairly provider specific.

A really good implementation of PG inheritance support for JPA would probably require JPA provider extensions for a new inheritance strategy that understood the PostgreSQL extensions for inheritance and for ONLY queries.

If you can convince your JPA implementation to use SELECT ... FROM ONLY subclass_table when in InheritanceType.TABLE_PER_CLASS mode then it should inter-operate OK with PostgreSQL inheritance. It would see only the non-inherited rows in each table and work with them as if they were ordinary tables. Your other non-JPA code could then keep using the inheritance features. I guess it's possible you could modify the PostgreSQL dialect code for Hibernate to do this, but personally I wouldn't go there unless I absolutely had to make JPA support existing PostgreSQL schema that relied heavily on inheritance.

share|improve this answer
    
Well, saying that actually gave us a view of what we've tried to implement, so we decide to simplify the idea by restructuring the tables... Thanks anyway! –  Atais Aug 7 '12 at 9:29
up vote 1 down vote

Inheritance in Hibernate has nothing to do with PostgreSQL inheritance, even though both try to accomplish the same and might look the same.

The reason for this is that Hibernate takes the SQL standards as base, and adjusts the small specific features from each RDBMS. For instance, while MySQL have an "auto-increment" for sequential IDs, Oracle uses sequences.

Historically, data inheritance (or specialization) has been done by using separate tables for the specific fields, with primary-foreign-keys linking the tables together. In your example, MatchAnswer would have the ID as PK and FK to Answer.idAnswer. Same with TrueFalseAnswer (the ID is PK/FK to Answer.idAnswer).

The "inherits" definition that you posted is not (AFAIK) defined in any SQL standard, so, I'd be surprised if Hibernate supported this, specially because it seems to be something extremely specific to PostgreSQL and it seems it's a somewhat experimental feature: look at "Caveats" on the "Inheritance" chapter of the PostgreSQL documentation.

That said, I'd suggest to keep your data sane, mapping them according to the best relational-model practices. Then, in your Hibernate mappings, you can express data inheritance where it makes sense.

share|improve this answer
    
It's not extremely experimental as such, but it's certainly a limited feature. I wouldn't use PostgreSQL inheritance from JPA - or for anything except the odd few cases it does come in handy. It's mostly used for making table partitioning transparent to the application these days, and even there some odd quirks exist. –  Craig Ringer Aug 7 '12 at 8:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.