More Inheritance Tutorial - hudec/sql-processor GitHub Wiki

The tutorial is updated for SQLP 3.1, SQLMEP/SQLMOP 2.6

The next control directives have been changed: pojo to is-pojo, table to is-table, procedure to is-procedure, function to is-function.

Table of Contents

Introduction
Inheritance

Introduction

In the version 1.5 the SQL Processor engine can also handle the next inheritance strategy. This example is based on the Associations Tutorial with the model established in that sample.

Inheritance

The inheritance can be achieved in a couple of ways. In the previous version the SQL Processor engine supports only one possibility – table per subclass. It represents is a (=inheritance) relationship as has a (=foreign key) relationship. This is presented in the Inheritance Tutorial.
Now the SQL Processor supports a new possibility - table per class hierarchy. It enables polymorphism by denormalizing the SQL schema, and utilizing a discriminator column that holds type information.

To present this feature the previous simple model is extended – we have the new table BILLING_DETAILS:

CREATE TABLE BILLING_DETAILS (
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, SUBSCRIBER BIGINT NOT NULL
, TYPE VARCHAR(2) NOT NULL
, CC_NUMBER BIGINT
, BA_ACCOUNT VARCHAR(100)
);

ALTER TABLE BILLING_DETAILS ADD CONSTRAINT PK_BILLING_DETAILS
	PRIMARY KEY (ID)
;

The table BILLING_DETAILS is represented by the abstract class BillingDetails:

package org.sqlproc.sample.simple.model;

public abstract class BillingDetails {
    private Long id;
    // discriminator, CC=CreditCard, BA=BankAccount
    private String type;
    private Subscriber subscriber;

    public BillingDetails() {
    }

    public BillingDetails(String type, Subscriber subscriber) {
        this.type = type;
        this.subscriber = subscriber;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public Subscriber getSubscriber() {
        return subscriber;
    }

    public void setSubscriber(Subscriber subscriber) {
        this.subscriber = subscriber;
    }
}

The discriminator column in this table is TYPE. In the case of value CC the table is represented by the child class CreditCard:

package org.sqlproc.sample.simple.model;

public class CreditCard extends BillingDetails {
    private Long number;

    public CreditCard() {
    }

    public CreditCard(Long number, Subscriber subscriber) {
        super("CC", subscriber);
        this.number = number;
    }

    public Long getNumber() {
        return number;
    }

    public void setNumber(Long number) {
        this.number = number;
    }
}

In the case of value BA the table is represented by the child class BankAccount:

package org.sqlproc.sample.simple.model;

public class BankAccount extends BillingDetails {
    private String account;

    public BankAccount() {
    }

    public BankAccount(String account, Subscriber subscriber) {
        super("BA", subscriber);
        this.account = account;
    }

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

}

This example also presents a one-to-many and many-to-one relationship. The database is extended in the following way:

CREATE TABLE LIBRARY (
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, NAME VARCHAR(100) NOT NULL
);

ALTER TABLE LIBRARY ADD CONSTRAINT PK_LIBRARY
	PRIMARY KEY (ID)
;

CREATE TABLE SUBSCRIBER (
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, LIBRARY BIGINT NOT NULL
, CONTACT BIGINT
, NAME VARCHAR(100) NOT NULL
);

ALTER TABLE SUBSCRIBER ADD CONSTRAINT PK_SUBSCRIBER
	PRIMARY KEY (ID)
;

ALTER TABLE SUBSCRIBER ADD CONSTRAINT FK_SUBSCRIBER_LIBRARY
	FOREIGN KEY (LIBRARY) REFERENCES LIBRARY (ID) ON DELETE CASCADE
;

ALTER TABLE BILLING_DETAILS ADD CONSTRAINT FK_BILLING_DETAILS_SUBSCRIBER
	FOREIGN KEY (SUBSCRIBER) REFERENCES SUBSCRIBER (ID) ON DELETE CASCADE
;

The table LIBRARY is represented by the class Library:

package org.sqlproc.sample.simple.model;

public class Library {

    private Long id;
    private String name;

    public Library() {
    }

    public Library(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

The table SUBSCRIBER is represented by the class Subscriber:

package org.sqlproc.sample.simple.model;

import java.util.List;

public class Subscriber {

    private Long id;
    private String name;
    private Contact contact;
    private Library library;
    private List<BillingDetails> billingDetails;

    public Subscriber() {
    }

    public Subscriber(String name, Library library) {
        this.name = name;
        this.library = library;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Contact getContact() {
        return contact;
    }

    public void setContact(Contact contact) {
        this.contact = contact;
    }

    public Library getLibrary() {
        return library;
    }

    public void setLibrary(Library library) {
        this.library = library;
    }

    public List<BillingDetails> getBillingDetails() {
        return billingDetails;
    }

    public void setBillingDetails(List<BillingDetails> billingDetails) {
        this.billingDetails = billingDetails;
    }

}

Any subscriber of any library can have a lots of billing details. These billing details can be of type credit card or of type bank account.

Let's say we'd like to have a list of all subscribers with their billing details. The result class is the Java class Subscriber. The attribute for the billing details is a list List<BillingDetails> billingDetail - the usage of generics in this list can't help the SQL Processor to identify the correct result class for the billing detail, as it's an abstract class. The META SQL query with the name ALL_SUBSCRIBERS_BILLING_DETAILS is defined in the meta statements file statements.meta:

ALL_SUBSCRIBERS_BILLING_DETAILS(QRY)=
  select s.ID @id(id), s.NAME @name, s.LIBRARY @library.id(id),
         bd.TYPE @billingDetails(discr)type, bd.ID @billingDetails.id(id),
         bd.CC_NUMBER @billingDetails.number, bd.BA_ACCOUNT @billingDetails.account
  from SUBSCRIBER s left join BILLING_DETAILS bd on s.ID = bd.SUBSCRIBER
  {= where
   {& UPPER(s.name)=:+name}
  }
  {#ID order by s.ID, bd.ID}
;

We can see the next output values modifiers:

  • @id(id) is an embedded mapping rule. It tells the output value from the column s.ID to be put into the attribute id. What's new is the output value modifier at the end of the mapping rule inside the parenthesis (id). This identifies the main identifier of the result row and helps the SQL Processor to detect, when the new instance of the result class Subscriber has to be created.
  • @library.id(id) is an embedded mapping rule. It tells the output value from the column s.LIBRARY to be put into the attribute id. What's new is the output value modifier at the end of the mapping rule inside the parenthesis (id). This identifies the sub-ordered identifier of the result row and helps the SQL Processor to detect, when the new instance of the result class Library has to be created.
  • @billingDetails(discr)type is an embedded mapping rule. It tells the output value from the column bd.TYPE to be put into the attribute billingDetails.type. What's new is the partial output value modifier (discr). It shows the name of the generic type of the collection library is going to be identified in the runtime based on the value of the column bd.TYPE. It has to be defined with the first occurrence of any attribute from this class in the META SQL query.
  • @billingDetails.id(id) is an embedded mapping rule. It tells the output value from the column bd.ID to be put into the attribute id. What's new is the output value modifier at the end of the mapping rule inside the parenthesis (id). This identifies the sub-ordered identifier of the result row and helps the SQL Processor to detect, when the new instance of the result class BillingDetails has to be created.

The runtime usage is the next one (for the case the SQL Processor runs on top of the JDBC stack):

JdbcEngineFactory sqlFactory = new JdbcEngineFactory();
sqlFactory.setMetaFilesNames("statements.meta");
sqlFactory.setFilter(SqlFeature.HSQLDB); // the optional database type to improve the SQLP behaviour
SqlCrudEngine sqlEngine = sqlFactory.getCrudEngine("ALL_SUBSCRIBERS_BILLING_DETAILS");

Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:sqlproc", "sa", "");
SqlSessionFactory sessionFactory = new JdbcSessionFactory(connection);
SqlSession session = sessionFactory.getSqlSession();

Map<String, Class<?>> moreResultClasses = new HashMap<String, Class<?>>();
moreResultClasses.put("BA", BankAccount.class);
moreResultClasses.put("CC", CreditCard.class);
SqlStandardControl sqlControl = new SqlStandardControl();
sqlControl.setMoreResultClasses(moreResultClasses);
List<Person> list = sqlEngine.query(session, Subscriber.class, null, sqlControl);

The map moreResultClasses provides the relationship between the generic type names in the META SQL query and the real result sub-classes of the class BillingDetails:

  • in the case of value BA for the discriminator column, the subclass BankAccount is going to be used
  • in the case of value CC for the discriminator column, the subclass CreditCard is going to be used

The sample Simple is available in GIT https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc.

Add a custom footer
⚠️ **GitHub.com Fallback** ⚠️