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.3 the SQL Processor engine can also handle the 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. Right now the SQL Processor engine supports only one possibility – table per subclass. It represents is a (=inheritance) relationship as has a (=foreign key) relationship.

To present this feature the previous simple model is extended – we have the new tables MEDIA, MOVIE and BOOK. Between them there are the foreign key constraints to represent the inheritance FK_MOVIE_MEDIA and FK_BOOK_MEDIA:

CREATE TABLE MEDIA
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, TITLE VARCHAR(100) NOT NULL 
);
ALTER TABLE MEDIA ADD CONSTRAINT PK_MEDIA
	PRIMARY KEY (ID)
;

CREATE TABLE MOVIE
(
  MEDIA_ID BIGINT NOT NULL 
, URLIMDB VARCHAR(100) NOT NULL
, PLAYLENGTH INT NOT NULL
);
CREATE TABLE BOOK
(
  MEDIA_ID BIGINT NOT NULL 
, ISBN VARCHAR(100) NOT NULL
);

ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_MEDIA
    FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA (ID) ON DELETE CASCADE
;
ALTER TABLE BOOK ADD CONSTRAINT FK_BOOK_MEDIA
    FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA (ID) ON DELETE CASCADE
;

The table MEDIA is represented by the abstract class Media:

package org.sqlproc.sample.simple.model;

public abstract class Media {

    private Long id;
    private String title;

    public Media() {
    }

    public Media(String title) {
        this.title = title;
    }

    public Long getId() {
        return id;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @Override
    public String toString() {
        return "Media [id=" + id + ", title=" + title + "]";
    }
}

The table MOVIE is represented by the child class Movie:

package org.sqlproc.sample.simple.model;

public class Movie extends Media {

    private String urlIMDB;
    private Integer playLength;

    public Movie() {
    }

    public Movie(String title, String urlIMDB, Integer playLength) {
        super(title);
        this.urlIMDB = urlIMDB;
        this.playLength = playLength;
    }

    public String getUrlIMDB() {
        return urlIMDB;
    }

    public void setUrlIMDB(String urlIMDB) {
        this.urlIMDB = urlIMDB;
    }

    public Integer getPlayLength() {
        return playLength;
    }

    public void setPlayLength(Integer playLength) {
        this.playLength = playLength;
    }

    @Override
    public String toString() {
        return "Movie [urlIMDB=" + urlIMDB + ", playLength=" + playLength + ", getId()=" + getId() + ", getTitle()=" + getTitle() + "]";
    }
}

The table BOOK is represented by the child class Book:

package org.sqlproc.sample.simple.model;

public class Book extends Media {

    private String isbn;

    public Book() {
    }

    public Book(String title, String isbn) {
        super(title);
        this.isbn = isbn;
    }

    public String getIsbn() {
        return isbn;
    }

    public void setIsbn(String isbn) {
        this.isbn = isbn;
    }

    @Override
    public String toString() {
        return "Book [isbn=" + isbn + ", getId()=" + getId() + ", getTitle()=" + getTitle() + "]";
    }
}

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

CREATE TABLE PERSON_LIBRARY
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, PERSON_ID BIGINT NOT NULL 
, MEDIA_ID BIGINT NOT NULL 
);
ALTER TABLE PERSON_LIBRARY ADD CONSTRAINT PK_PERSON_LIBRARY
	PRIMARY KEY (ID)
;

ALTER TABLE PERSON_LIBRARY ADD CONSTRAINT FK_PERSON_LIBRARY_1
    FOREIGN KEY (PERSON_ID) REFERENCES PERSON (ID)
;
ALTER TABLE PERSON_LIBRARY ADD CONSTRAINT FK_PERSON_LIBRARY_2
    FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA (ID)
;

The table PERSON_LIBRARY provides the many-to-many relationship - every person can own a lot-of media, which are entities itself. It is presented by the attribute List<Media> library in the class Person.

Let's say we'd like to have a list of all people with their books and movies in their library. The result class is the Java class Person. The attribute for the library is a list List<Media> library:

public abstract class Person {
  ...
  private List<Media> library;
  
  public List<Media> getLibrary() {
    return library;
  }

  public void setLibrary(List<Media> library) {
    this.library = library;
  }
  ....
}

The usage of generics in this list can't help the SQL Processor to identify the correct result class for the media, as it's an abstract class. The META SQL query with the name ALL_PEOPLE_LIBRARY is defined in the meta statements file statements.meta:

ALL_PEOPLE_LIBRARY(QRY)=
  select p.ID @id(id), p.NAME @name, mo.MEDIA_ID @library(gtype=movie)id(id), bk.MEDIA_ID @library(gtype=book)id(id),
         m.TITLE @library.title, mo.URLIMDB @library.urlIMDB, mo.PLAYLENGTH @library.playLength, bk.ISBN @library.isbn
  from PERSON p left join PERSON_LIBRARY l on p.ID = l.PERSON_ID
  left join MEDIA m on m.ID = l.MEDIA_ID
  left join MOVIE mo on mo.MEDIA_ID = m.ID
  left join BOOK bk on bk.MEDIA_ID = m.ID
  {= where
   {& p.ID = :id}
   {& UPPER(p.NAME) like :+name}
  }
  {#NAME_TITLE order by p.NAME, m.TITLE}
;

We can see the next output values modifiers:

  • @id(id) is an embedded mapping rule. It tells the output value from the column p.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 Person has to be created.
  • @library(gtype=movie)id(id) is an embedded mapping rule. It tells the output value from the column mo.MEDIA_ID to be put into the attribute library.id. What's new is the output value modifier at the end of the mapping rule inside the parenthesis (id). This identifies the identifier of the subset of the result row and helps the SQL Processor to detect, when the new instance of the result class Media has to be created. Any subclass of Media which has to be created is identified with the modifier (gtype=movie). It shows the name of the generic type of the collection library as a movie. It has to be defined with the first occurrence of any attribute from this class in the META SQL query. The relation between this name and the result class is provided in the runtime.
  • @library(gtype=book)id(id) is an embedded mapping rule. It tells the output value from the column bk.MEDIA_ID to be put into the attribute library.id. What's new is the output value modifier at the end of the mapping rule inside the parenthesis (id). This identifies the identifier of the subset of the result row and helps the SQL Processor to detect, when the new instance of the result class Media has to be created. Any subclass of Media which has to be created is identified with the modifier (gtype=book). It shows the name of the generic type of the collection library as a book. It has to be defined with the first occurrence of any attribute from this class in the META SQL query. The relation between this name and the result class is provided in the runtime.

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_PEOPLE_LIBRARY");

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("movie", Movie.class);
moreResultClasses.put("book", Book.class);
SqlStandardControl sqlControl = new SqlStandardControl();
sqlControl.setMoreResultClasses(moreResultClasses);
List<Person> list = sqlEngine.query(session, Person.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 Media.

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** ⚠️