SQLEP 1.2.x DAO modelling

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

###Introduction The new features of the SQL Processor Eclipse plugin (SQLEP) version 1.2.0 are

• DAO modelling and source code generation
• DAO layer generation based on DB model

The new features of the SQL Processor Eclipse plugin (SQLEP) version 1.2.1 is

• SqlSession as an optional parameter for all generated DAO

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

###Control directives location The best approach is to put all new control directives into a separate file dao.qry. It has to be located alongside the statements.qry (contains the runtime SQLP artifacts), definitions.qry (contains the control artifacts described in the tutorial SQLEP 1.1.x Basic Tutorial) and pojo.qry (contains the POJO model described in the tutorial SQLEP 1.1.x POJO modelling):

##DAO modelling and source code generation The SQLEP enables DAO definition using the Xtext grammar, like in the following snippet from the dao.qry:

package org.sqlproc.sample.simple.dao {

  final dao LibraryDao :: Library {
    scaffold
  }
}

After saving the dao.qry - the DAO LibraryDao.java is automatically generated in the directory src-gen/org/sqlproc/sample/simple/dao:

package org.sqlproc.sample.simple.dao;

import java.util.HashMap;
// other imports

public class LibraryDao {
  protected final Logger logger = LoggerFactory.getLogger(getClass());

  private SqlEngineFactory sqlEngineFactory;
  private SqlSessionFactory sqlSessionFactory;
	
  public LibraryDao(SqlEngineFactory sqlEngineFactory, SqlSessionFactory sqlSessionFactory) {
    this.sqlEngineFactory = sqlEngineFactory;
    this.sqlSessionFactory = sqlSessionFactory;
  }


  public Library insert(Library library, SqlControl sqlControl) {
    if (logger.isTraceEnabled()) {
      logger.trace("insert library: " + library + " " + sqlControl);
    }
    SqlCrudEngine sqlInsertLibrary = sqlEngineFactory.getCheckedCrudEngine("INSERT_LIBRARY");
    int count = sqlInsertLibrary.insert(sqlSessionFactory.getSqlSession(), library);
    if (logger.isTraceEnabled()) {
      logger.trace("insert library result: " + count + " " + library);
    }
    return (count > 0) ? library : null;
  }

  // other methods
}

To force this feature in the IDE, the project has to be imported with the following snippet in the pom.xml:

<build>
    <plugins>
        <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>build-helper-maven-plugin</artifactId>
            <version>1.7</version>
            <executions>
                <execution>
                    <id>add-source</id>
                    <phase>generate-sources</phase>
                    <goals>
                        <goal>add-source</goal>
                    </goals>
                    <configuration>
                        <sources>
                            <source>src-gen</source>
                        </sources>
                    </configuration>
                </execution>
            </executions>
        </plugin>
        ....
</build>

###Packages The top level element in the DAO modelling is the package:

package org.sqlproc.sample.simple.dao {
  ...
}

There can be a couple of packages and any package can have a suffix

package org.sqlproc.sample.simple.model {
  ...
}

package org.sqlproc.sample.simple.model.impl suffix Impl {
  ...
}

In the previous example the package org.sqlproc.sample.simple.model.impl has a suffix Impl. The result effect is that every generated Java class has a name suffix of the same value.

###DAOs Inside the package there are DAOs' definitions

  dao LibraryDao :: Library {
    ...
  }

  dao MovieDao :: Movie {
    ...
  }

After the keyword dao there's a DAO name and the related POJO name. DAO can be accompanied with the modifiers

• prefix abstract - the result Java class is abstract
• prefix final - the DAO definition won't be overwritten in the case the DAOs are generated from DB model
• postfix extends AnotherDao - the result Java class extends AnotherDao class
• postfix serializable someNumber - the result Java class is serializable with the specific value of serialVersionUID

All the generated Java classes can be forced to implement required interfaces. It can be done as in the following example

package org.sqlproc.sample.simple.dao {

  implements org.sqlproc.sample.simple.dao.BaseDao

  dao LibraryDao :: Library {
    ...
}

All the generated Java classes can be forced to extend one Java class. It can be done as in the following example

package org.sqlproc.sample.simple.dao {

  extends org.sqlproc.sample.simple.dao.impl.BaseDaoImpl

  dao LibraryDao :: Library {
    ...
}

###Methods Inside the DAO there's an indicator scaffold (in this version of the SQLEP not required), that the basic methods have to be created

final dao LibraryDao :: Library {
  scaffold
}

In generated code the following scaffold methods are created

• public Pojo insert(Pojo pojo, SqlControl sqlControl)
• public Pojo insert(Pojo pojo)
• public Pojo get(Pojo pojo, SqlControl sqlControl)
• public Pojo get(Pojo pojo)
• public int update(Pojo pojo, SqlControl sqlControl)
• public int update(Pojo pojo)
• public int delete(Pojo pojo, SqlControl sqlControl)
• public int delete(Pojo pojo)
• public List<Pojo> list(Pojo pojo, SqlControl sqlControl)
• public List<Pojo> list(Pojo pojo)

It means, that for every CRUD and SELECT statements there are a couple of methods. In the case the POJOs, META SQL statements and DAOs are generated based on DB model, all generated artifacts are created in a compatible fashion.

###Inheritance In the case of any kind of supported POJO inheritance, there should be an indicator, which class have to be created as the result of the SQL statement execution. For the following snippet

dao PaymentDao :: Payment {
  scaffold
  billingDetails ::: BA ::BankAccount CC ::CreditCard
}

the next code is created

public List<Payment> list(Payment payment, SqlControl sqlControl) {
  ...
  sqlControl = getMoreResultClasses(payment, sqlControl);
  List<Payment> paymentList = sqlEnginePayment.query(sqlSessionFactory.getSqlSession(), Payment.class, payment, sqlControl);
  ...
}

SqlControl getMoreResultClasses(Payment payment, SqlControl sqlControl) {
  ...
  if (payment != null && payment.toInit(Payment.Association.billingDetails.name())) {
    if (moreResultClasses == null)
      moreResultClasses = new HashMap<String, Class<?>>();
    moreResultClasses.put("BA", BankAccount.class);
    moreResultClasses.put("CC", CreditCard.class);
  }
  ...
}

For more information regarding inheritance, please see the Tutorials Inheritance Tutorial and More Inheritance Tutorial.

###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.

###SqlSession as an optional parameter All methods in created DAO classes have an alternative with SqlSession parameter. For example in PersonDao there is

public List<Payment> list(Payment payment, SqlControl sqlControl)

and also

public List<Payment> list(SqlSession sqlSession, Payment payment, SqlControl sqlControl)

It enables to share one session for all DAO invocations in one transaction.

##DAO generation based on DB model Please see SQLEP 1.2.x DAO generation