Associations Tutorial - hudec/sql-processor GitHub Wiki

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

Introduction
Associations
Collections

In the version 1.3 the SQL Processor engine can also handle associations one-to-one, one-to-many and many-to-many. For the basic Simple Tutorial, please see Simple Tutorial. For the basic Simple CRUD Tutorial, please see CRUD Tutorial. Next only the associations are described.

The association deals with a has-one or a has-many type relationship. It can be provided by the SQL Processor engine right now only in one way – using left joins with the repeating subsets of joined results. To distinguish repeating subsets in the output values, the knowledge of identifiers is required. To enable identifications of identifiers, the output values modifiers can be utilized.

To present this feature, the simple model introduced in the Simple Tutorial is extended – we have the modified table PERSON, the new table CONTACT and the foreign key FK_CONTACT_PERSON:

CREATE TABLE PERSON
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, NAME VARCHAR(100) NOT NULL 
);
ALTER TABLE PERSON ADD CONSTRAINT PK_PERSON
  PRIMARY KEY (ID)
;
CREATE TABLE CONTACT
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, PERSON_ID BIGINT NOT NULL 
, ADDRESS VARCHAR(100) NOT NULL 
);
ALTER TABLE CONTACT ADD CONSTRAINT PK_CONTACT
  PRIMARY KEY (ID)
;
ALTER TABLE CONTACT ADD CONSTRAINT FK_CONTACT_PERSON
  FOREIGN KEY (PERSON_ID) REFERENCES PERSON (ID) ON DELETE CASCADE
;

This model enables a one-to-many association – one person can have multiple contacts. The related model consists of the Java classes Person and Contact:

package org.sqlproc.sample.simple.model;

import java.util.List;

public class Person {

    private Long id;
    private String name;
    private List<Contact> contacts;

    public Person() {
    }

    public Person(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;
    }

    public List<Contact> getContacts() {
        return contacts;
    }

    public void setContacts(List<Contact> contacts) {
        this.contacts = contacts;
    }

    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + ", contacts=" + contacts + "]";
    }
}

package org.sqlproc.sample.simple.model;

public class Contact {

    private Long id;
    private Long personId;
    private String address;

    public Contact() {
    }

    public Contact(String address) {
        this.address = address;
    }

    public Long getId() {
        return id;
    }

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

    public Long getPersonId() {
        return personId;
    }

    public void setPersonId(Long personId) {
        this.personId = personId;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Contact [id=" + id + ", personId=" + personId + ", address=" + address + "]";
    }

}

We'd like to have a list of all people with their contacts cumulated in a list. The result class is the Java class Person. The attribute for the contacts is a list List<Contact> contacts - the usage of generics in this list can help the SQL Processor to identify the correct result class for the contacts. The META SQL query with the name ALL_PEOPLE_AND_CONTACTS is defined in the meta statements file statements.meta:

ALL_PEOPLE_AND_CONTACTS(QRY)=
  select p.ID @id(id), p.NAME @name, c.ID @contacts.id(id), c.PERSON_ID @contacts.personId, c.ADDRESS @contacts.address
  from PERSON p left join CONTACT c on p.ID = c.PERSON_ID
  {= where
   {& id=:id}
   {& UPPER(name) like :+name}
  }
  {#NAME_ADDRESS order by p.NAME, c.ADDRESS}
;

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.
• @contacts.id(id) is an embedded mapping rule. It tells the output value from the column c.ID to be put into the attribute contacts.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 Contact has to be created.

The runtime usage is standard, like 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
SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("ALL_PEOPLE_AND_CONTACTS");

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

List<Person> list = sqlEngine.query(session, Person.class);

An association one-to-many is related to the attribute, which is in fact a collection. In the previous example it was the attribute contacts in the class Person. The type of this attribute is an abstract interface java.util.List. The SQL Processor is trying to instantiate an adequate class, which implements this interface. The standard behaviour is the next one

• for the interface java.util.List the class java.util.ArrayList is instantiated
• for the interface java.util.Set the class java.util.HashSet is instantiated

We can overwrite, which class has to be instantiated for collection of any type, like in the next example. We'd like to have the same list of all people with their contact cumulated in a list. The META SQL query with the name ALL_PEOPLE_AND_CONTACTS2 is defined in the meta statements file statements.meta:

ALL_PEOPLE_AND_CONTACTS2(QRY)=
  select p.ID @id(id), p.NAME @name, c.ID @contacts(dtype=linked)id(id), c.PERSON_ID @contacts.personId, c.ADDRESS @contacts.address
  from PERSON p left join CONTACT c on p.ID = c.PERSON_ID
  {= where
   {& id=:id}
   {& UPPER(name) like :+name}
  }
  {#NAME_ADDRESS order by p.NAME, c.ADDRESS}
;

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.
• @contacts(dtype=linked)id(id) is an embedded mapping rule. It tells the output value from the column c.ID to be put into the attribute contacts.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 Contact has to be created. The other new feature is the partial modifier (dtype=linked). It shows that the name of the collection is dynamic type linked. It has to be defined with the first occurrence of any attribute from this collection in the META SQL query. The relation between this name and the collection class is provided in the runtime.

The runtime usage is standard, like 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
SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("ALL_PEOPLE_AND_CONTACTS2");

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("linked", LinkedList.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 collections type names in the META SQL query and the real implementation of that collections. In this case instead of java.util.ArrayList the java.util.LinkedList is instantiated.

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