SQLEP 1.1.x POJO generation

###The tutorial is updated for SQLP 2 and SQLEP 1.3

##POJO model generation based on DB model

The next new feature of the SQLEP is the ability to generate the POJOs model based on DB layout. It can significantly help to improve coding efficiency.

The usage is rather simple. Let's have the following control directives in the definitions.qry to establish connection to the target database (this tutorial is based on the sample project https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc-pojo):

database-is-online;
database-jdbc-driver org.hsqldb.jdbcDriver;
database-has-url jdbc:hsqldb:mem:sqlproc;
database-login-username sa;
database-login-password "";
database-ddl-create hsqldb.ddl; // should be located in the same directory as definitions.qry

After the IDE restart (or writing database-is-offline, saving, database-is-online and again saving) the database is created on the fly in the memory (this is new feature of the SQLEP 1.2.0, for the SQLEP 1.1.x a connection to the real database should be established, as is described in SQLEP 1.1.x Basic Tutorial).

Next in pojo.qry lets model package

package org.sqlproc.sample.simple.model {

}

Put the cursor inside the curly brackets and press a Ctrl-Space. A content assist is activated and in the popup menu a couple of templates is offered. Select the new advanced template pojogen - Pojo generator. A block of model code is generated based on the DB layout. For every database table one POJO is created. For every database column one attribute is created. The type of the attribute is related to the SQL type of database column based on internal rules (built in the SQLEP). The database names are translated to Java names based on CamelCase.

For example let's have the following Oracle tables:

CREATE TABLE PERSON (
  ID NUMBER(19) NOT NULL,
  NAME VARCHAR2(100) NOT NULL
);

CREATE TABLE CONTACT (
  ID NUMBER(19) NOT NULL,
  PERSON_ID NUMBER(19) NOT NULL,
  ADDRESS VARCHAR2(100),
  PHONE_NUMBER VARCHAR2(100)
);

For these tables the next POJOs are created:

package org.sqlproc.sample.simple.model {
  pojo Person {
    id : java.lang.Long primaryKey
    name : java.lang.String required
  }

  pojo Contact {
    id : java.lang.Long primaryKey
    personId : java.lang.Long
    address : java.lang.String required
    homePhone : java.lang.String
  }
}

After saving this modified pojo.qry, also the related Java classes are generated, as is described in SQLEP 1.1.x POJO modelling.

Of course there's a gap between the relation model of DB and the object model of Java. The primary goal of the SLQEP is to make the translation process from DB to Java as simple as possible. Still there is a lot of directives (pojogen-*), which can have an influence on this translation process.

In the case the connection to the target database is established, in all these directives again the content assist can be utilized. Everywhere the name of the database table is to be used, a list of all database tables can be presented in the popup window. Also everywhere the name of the database column is to be used, a list of all database columns can be presented in the popup window.

The syntax of all control directives is "hinted" again using the content assist.

Tables selection

We can create models only for selected tables in the target database. The selection can be a positive one - only tables BOOK and PERSON are processed

pojogen-only-tables BOOK PERSON;

The selection can be a negative one - the tables LIBRARY and MOVIE are not processed

pojogen-ignore-tables LIBRARY MOVIE;

Columns selection

We can model only selected columns in the target database. The selection can be a negative one - the columns NAME and CONTACT in table SUBSCRIBER are not processed

pojogen-ignore-columns SUBSCRIBER NAME CONTACT;

We can inject new columns to the tables (and so POJOs). In the following example we added new attributes homePhone and businessPhone to the POJO Subscriber related to table SUBSCRIBER (don't forget to name translation based on CamelCase)

pojogen-create-columns SUBSCRIBER HOME_PHONE->:java.lang.String BUSINESS_PHONE->:java.lang.String;

Names modification

Wen can change the name of the database tables (and so POJOs). In the following example we change the SUBSCRIBER to LIBRARY_SUBSCRIBER (and so the POJO LibrarySubscriber)

pojogen-rename-tables SUBSCRIBER->LIBRARY_SUBSCRIBER;

Wen can change the name of the database columns (and so attributes). In the following example we change the NAME to SUBSCRIBER_NAME (and so the attribute subscriberName) and CONTACT to SUBSCRIBER_CONTACT (and so the attribute subscriberContact)

pojogen-rename-columns SUBSCRIBER NAME->SUBSCRIBER_NAME CONTACT->SUBSCRIBER_CONTACT;

Types modification

Wen can change the attribute type. It can be done globally in the prescription, how to translate SQL type to Java type. In the following example every column with the type VARCHAR(2) is going to be an attribute of type java.lang.Character

pojogen-types-sqltypes VARCHAR2(1)->:java.lang.Character;

This modification can be restricted to one table, like in the following example for the table CONTACT (and so POJO Contact)

pojogen-types-in-table CONTACT VARCHAR2(1)->:java.lang.Character;

The name modification can be prescribed to the selected columns in any table. Next the column ID (and so attribute id) in table CONTACT is going be of type java.lang.Short and the column PERSON_ID (and so attribute personId) is going to be a reference to the POJO Person

pojogen-types-for-columns CONTACT ID->:java.lang.Short PERSON_ID->::Person;

The effects can be combined, like the name and type modification

pojogen-rename-columns CONTACT PERSON_ID->PERSON;
pojogen-types-for-columns CONTACT PERSON_ID->::Person;

We can ask for the SQL type like in the following code

pojogen-show-type-for-column CONTACT PHONE_NUMBER->VARCHAR2(100);

Simply set the cursor after the control sequence -> and press a Ctrl-Space.

Requirements modification

The attribute can have a modifier required in the case the related database column is NOT NULL. It can be suppressed

pojogen-not-required-columns CONTACT PERSON_ID PHONE_NUMBER;

or it can be forced

pojogen-required-columns CONTACT PERSON_ID PHONE_NUMBER;

Handling many-to-one

Based on database foreign keys there can be a relationship between tables. Let's have two tables as is described in Associations Tutorial. Automatically the next POJOs are generated

pojo Person {
  id : java.lang.Long primaryKey
  name : java.lang.String required
  contacts : java.util.List <:Contact>
}
pojo Contact {
  id : java.lang.Long primaryKey
  person :: Person
  address : java.lang.String required
}

The attribute person represents this many-to-one association. It's type is a reference to POJO Person. You can see, that the name translation is also done PERSON_ID -> person. The suffix _ID is cut.

This association can be suppressed, like in the following example

pojogen-ignore-many-to-one CONTACT PERSON_ID->PERSON;

The result effect is that the next POJO is generated

pojo Contact {
  id : java.lang.Long primaryKey
  personId : java.lang.Long
  address : java.lang.String required
}

On the other side this association can be injected

pojogen-create-many-to-one CONTACT ANOTHER_PERSON->PERSON;

and the next POJO is generated

pojo Contact {
  id : java.lang.Long primaryKey
  person :: Person
  anotherPerson :: Person
  address : java.lang.String required
}

Handling one-to-many

Based on database foreign keys there can be a relationship between tables. Let's have two tables as is described in Associations Tutorial. Automatically the next POJOs are generated

pojo Person {
  id : java.lang.Long primaryKey
  name : java.lang.String required
  contacts : java.util.List <:Contact>
}
pojo Contact {
  id : java.lang.Long primaryKey
  person :: Person
  address : java.lang.String required
}

The attribute contacts represents this one-to-many association. It's type is a list of references to POJO Contact. You can see, that the name is automatically provided based on table name CONTACT -> contacts.

This association can be suppressed, like in the following example

pojogen-ignore-one-to-many PERSON ID->CONTACT; 

The ID in this control directive is referenced primary key in the table PERSON. The result effect is that this attribute is ommited

pojo Person {
  id : java.lang.Long primaryKey
  name : java.lang.String required
}

On the other side this association can be injected

pojogen-create-one-to-many PERSON ID->MEDIA;

and the new attribute medias is generated (based on the related table name 'MEDIA`)

pojo Person {
  id : java.lang.Long primaryKey
  name : java.lang.String required
  contacts : java.util.List <:Contact>
  medias : java.util.List <:Media>
}

We can set up also the name of this new attribute

pojogen-create-one-to-many PERSON ID->MEDIA->LIBRARY;

ant the new attribute has the name library

pojo Person {
  id : java.lang.Long primaryKey
  name : java.lang.String required
  library : java.util.List <:Media>
  contacts : java.util.List <:Contact>
}

Handling many-to-many

Based on database foreign keys there can be a relationship between tables. Let's have the next Oracle tables

CREATE TABLE PERSON (
  ID NUMBER(19) NOT NULL,
  NAME VARCHAR2(100) NOT NULL
);
CREATE TABLE PERSON_LIBRARY (
  ID NUMBER(19) NOT NULL,
  PERSON_ID NUMBER(19) NOT NULL,
  MEDIA_ID NUMBER(19) NOT NULL
);
CREATE TABLE MEDIA (
  ID NUMBER(19) NOT NULL,
  TITLE VARCHAR2(100) NOT NULL
);

The table PERSON_LIBRARY is typically many-to-many table and represents in fact the association between the tables PERSON and MEDIA. The implicit generated POJOs only reflect one-to-many and many-to-one associations

pojo Person {
  id : java.lang.Long primaryKey
  name : java.lang.String required
  personLibraries : java.util.List <:PersonLibrary>
}
pojo PersonLibrary {
  id : java.lang.Long primaryKey
  person :: Person required
  media :: Media required
}
pojo Media {
  id : java.lang.Long primaryKey
  title : java.lang.String required
  personLibraries : java.util.List <:PersonLibrary>
}

We must tell the SQLEP, that the table PERSON_LIBRARY is many-to-many table

pojogen-table-many-to-many PERSON_LIBRARY ID->MEDIA->LIBRARY;

The ID in this control directive is referenced primary key in the table PERSON. The MEDIA is the opposite table going to be referenced. And the LIBRARY is going to be the name of the new attribute in PERSON. The result effect is the next one

pojo Person {
  id : java.lang.Long primaryKey
  name : java.lang.String required
  library : java.util.List <:Media>
}
pojo PersonLibrary {
  id : java.lang.Long primaryKey
  personId : java.lang.Long required
  mediaId : java.lang.Long required
}
pojo Media {
  id : java.lang.Long primaryKey
  title : java.lang.String required
}

The POJO PersonLibrary in this fashion is required in the SQLP runtime. The association many-to-many can be of course also bidirectional

pojogen-table-many-to-many PERSON_LIBRARY ID->MEDIA->LIBRARY;
pojogen-table-many-to-many PERSON_LIBRARY ID->PERSON->OWNERS;

In this case the POJO Media has a new attribute

owners : java.util.List <:Person>

Handling inheritance

There are two ways, how to model the inheritance. The first one is to have a table per subclass and is described in Inheritance Tutorial. Let's use the same tables. Automatically the POJOs reflecting the many-to-one associations are generated. To force the SQLEP to model the inheritance relationship instead of many-to-one, use

pojogen-inherit-many-to-one BOOK MEDIA_ID->MEDIA->ID;
pojogen-inherit-many-to-one MOVIE MEDIA_ID->MEDIA->ID;

In the first control directive the table BOOK (and so the related POJO Book) is going to be a child of the table MEDIA (and so the related POJO Media) using the foreign key MEDIA_ID in BOOK and the primary key ID in MEDIA. In the second control directive the table MOVIE (and so the related POJO Movie) is going to be a child of the table MEDIA (and so the related POJO Media) using the foreign key MEDIA_ID in BOOK and the primary key ID in MEDIA. The next POJOs are generated

pojo Book extends Media {
  isbn : java.lang.String required
}
pojo Movie extends Media {
  urlimdb : java.lang.String required
  playlength : java.lang.Long required
}
abstract pojo Media {
  id : java.lang.Long primaryKey
  title : java.lang.String required
}

You can see, that the POJO Media is now abstract, and the POJOs Book and Movie extends the POJO Media.

The second way is to have a table per class hierarchy utilizing the discriminator column and is described in More inheritance tutorial. Let's use the same tables. Automatically only one POJO BillingDetails is generated. To force the SQLEP to model new POJOs for every discriminator value, use

pojogen-inherit-discriminator BILLING_DETAILS TYPE BA->BANK_ACCOUNT->BA_ACCOUNT CC->CREDIT_CARD->CC_NUMBER;

In this control directive the new table BANK_ACCOUNT (and so the related POJO BankAccount) is created for the discriminator value BA (discriminator column is TYPE). It is going to have the column BA_ACCOUNT. At the same time the new table CREDIT_CARD (and so the related POJO CreditCard) is created for the discriminator value CC (discriminator column is TYPE). It is going to have the column CC_NUMBER. The next POJOs are generated

abstract pojo BillingDetails {
  id : java.lang.Long primaryKey
  subscriber :: Subscriber required
  type : java.lang.String discriminator required
}
pojo BankAccount extends BillingDetails discriminator BA {
  baAccount : java.lang.String required
}
pojo CreditCard extends BillingDetails discriminator CC {
  ccNumber : java.lang.Long
}

You can see, that the POJO BillingDetails is now abstract, and the POJOs BankAccount and CreditCard extends the POJO BillingDetails.

Handling SELECT JOIN

Sometimes it's necessary to make a joined selection. The result class is going to have the columns (=attributes) from all tables used in the related SELECT JOIN. Let's say we use three tables in the select: PERSON, PERSON_LIBRARY and LIBRARY. We can force the SQLEP to generated a proper model

pojogen-join-tables LIBRARY->PERSON_LIBRARY->PERSON;

Except the POJO Person two new POJOs are generated

pojo PersonLibraryPerson extends Person {
  personId : java.lang.Long required
  mediaId : java.lang.Long required
}
pojo LibraryPersonLibraryPerson extends PersonLibraryPerson {
  subscribers : java.util.List <:Subscriber>
}

The result POJO has all the attributes from all the tables based on Java inheritance. This feature has one restriction - columns in all tables have to have unique names. In the case it's not true, we can rename the "non unique" columns using the control directive pojogen-rename-columns.

Methods

We can model three methods in POJOs as is described in SQLEP 1.1.x POJO modelling. To force to SQLEP to automatically generate all these methods (or any of them), use

pojogen-generate-methods toString equals hashCode;

Interfaces

All the generated Java classes can be forced to implement required interfaces as is described in SQLEP 1.1.x POJO modelling. For example to force the SQLEP to generate proper model utilizing interfaces java.io.Reader and java.io.Serializable, use

pojogen-implements-interfaces java.io.Serializable java.io.Reader;

The interface java.io.Serializable` is handled in special, as it can be see in the sample https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc-pojo.

Parent POJO

All the generated Java classes can be forced to extend one Java class as is described in SQLEP 1.1.x POJO modelling. For example to force the SQLEP to generate proper model utilizing Java class org.sqlproc.sample.simple.model.BasePojo, use

pojogen-extends-class org.sqlproc.sample.simple.model.BasePojo;