SQLMOP DAO generation

The tutorial is updated for SQLP 3.1 and 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.

DAO API generation based on DB model

The advanced feature of the SQLMOP is the ability to generate the DAO API 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.model to establish connection to the target database (this tutorial is based on the sample project https://github.com/hudec/sql-processor/blob/master/sql-samples/simple-jdbc-crud/src/main/resources/definitions.model):

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.meta
pojogen-generate-wrappers; // DB types are not converted to the Java native types 
compress-meta-directives;
pojogen-package org.sqlproc.sample.simple.model;
daogen-package org.sqlproc.sample.simple.dao;

The next steps are described in SQLMOP POJO 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.model 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 sample of the generated DAO API can be seen at the https://github.com/hudec/sql-processor/blob/master/sql-samples/simple-jdbc-crud/src/main/resources/dao.model.

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 SQLMOP DAO modelling. For example to force the SQLMOP to generate proper DAO utilizing interfaces org.sqlproc.sample.simple.dao.BaseDao and java.io.Serializable, use in definitions.model

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

Parent DAO

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

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

#Implementation(impl)
package org.sqlproc.sample.simple.dao {
  ...
  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.model

daogen-implementation-package impl;

Final DAOs

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

daogen-make-it-final;