SQLEP 1.2.x DAO generation

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

###Samples All new features are presented in the samples Advances Samples.

###Important All generated artifacts (POJO, META SQL, DAO) are created in a compatible fashion. For any generator to produce correct code, first the file definitions.qry should be opened in IDE.

##DAO layer generation based on DB model The next new feature of the SQLEP is the ability to generate the DAO layer based on DB layout. It can significantly help to improve coding efficiency. The generated code can be taken as the first step in a new project, mainly for CRUD statements. Later the generated classes can be manually overwritten and improved.

The usage is rather simple. Let's have the following control directives in the definitions.qry, as is described in SQLEP 1.2.x Changes Tutorial, to establish connection to the target database:

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

Next the template tables should be used to generate the following snippet in definitions.qry (it's described in SQLEP 1.1.x Basic Tutorial):

table person PERSON;
table personLibrary PERSON_LIBRARY;
...

As a further step the POJOs should be generated. The steps are described in SQLEP 1.1.x POJO generation.

As a next step the META SQL statements should be generated. The steps are described in SQLEP 1.2.x META SQL generation.

As a final step the DAO layer can be generated. There are several control directives daogen-..., which are devoted to this process. The initial content of the dao.qry can be for example

package org.sqlproc.sample.simple.dao {

}

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 daogen - DAO generator. A block of model code is generated based on the DB layout. For every database table (and so for every POJO) one DAO class is created. For every basic META SQL statement (INSERT, GET, UPDATE, DELETE, SELECT) a couple of methods are created.

The samples of generated META SQL statements can be seen in the https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc-crud/src-gen/org/sqlproc/sample/simple/dao.

The process of the DAO layer generation is controlled by the next control directives

• pojogen-*
• daogen-*

Tables/POJOs selection

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

daogen-only-tables BOOK PERSON;

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

daogen-ignore-tables LIBRARY MOVIE;

Interfaces

All the generated DAO classes can be forced to implement required interfaces as is described in SQLEP 1.2.x DAO modelling. For example to force the SQLEP to generate proper DAO utilizing interfaces org.sqlproc.sample.simple.dao.BaseDao and java.io.Serializable, use in definitions.qry

daogen-implements-interfaces org.sqlproc.sample.simple.dao.BaseDao java.io.Serializable java.io.Reader;

Parent DAO

All the generated Java classes can be forced to extend one Java class as is described in SQLEP 1.2.x DAO modelling. For example to force the SQLEP to generate proper DAO utilizing Java class org.sqlproc.sample.simple.dao.impl.BaseDaoImpl, use in definitions.qry

daogen-extends-class org.sqlproc.sample.simple.dao.impl.BaseDaoImpl;

###Implementation separated from interface The correct approach to DAO layer is to have the implementation separated from the interface. For example for the following snippet

package org.sqlproc.sample.simple.dao {

  implementation-package impl

  ...
  final dao LibraryDao :: Library {
    scaffold
  }
  ...
}

the DAO interface LibraryDao.java is automatically generated in the directory src-gen/org/sqlproc/sample/simple/dao and the DAO implementation LibraryDaoImpl.java is automatically generated in the directory src-gen/org/sqlproc/sample/simple/dao/impl. The implementation subdirectory is controlled by the control directive implementation-package impl. To force the generator to create this control directive, use in definitions.qry

daogen-implementation-package impl;

###Final DAOs The DAO can be assigned using the final in the DAO declaration (described in SQLEP 1.2.x DAO modelling). This is an indicator for the DAO generator to not overwrite this class in the next daogen template usage. To force SQLEP to mark all generated DAOs as final, we can use

daogen-make-it-final;