Search This Blog

Saturday, September 29, 2012

Generating a DAO Class in The Black Toolkit.

DAO (Data Access Object) code pattern has advantages over using exposed DB commands:
  • Database independency: What DBs are you using? MySQL, Oracle, LDAP, PostgreSQL,...? Can be SQL or not. DAO isolates the DB data from the rest of the code, making easy to support many DBs.
  • Performance for SQL: SELECT with many JOINs are slower than a SELECT per table joined by the programming language. DAO avoids using much of these  JOINs.
  • Maintain code: Separating the DB code from other parts of the source makes it much more easy to  maintain.

Since 1.0.7 the IDE has a Visual RAD Form to generate and maintain the DAO class and Transfer Object  for Java and PHP.
Since 1.0.8 the IDE can import it from a SQL table, and support many drivers. Just go Database->Import DAO/CRUD, and connect to the database

Just do a right-click on the files panel  and go New->Visual Form->DAO Class.

  1. Set the Form properties.
  2. Create a Field Instance for each Field in the table, and set its attributes.
  3. If using it in many DBMS, you will need to set abstractDAO as true and make DAO class abstract.
  4. If the class is not abstract, set the getConnection event to get the PDO/Connection object.
  5. Create your own (abstracts or not) methods in the declarationsDAO event.
  6. The IDE gives the Transfer Object class and the Data Access Object class with some SQL generic methods:
  •  select() - selects a row.
  • query() - selects many rows. You can add a WHERE / ORDER BY clause  for parameter. This method will NOT load fileds which loadonquery property is false You may use this method just in the subclasses or not, depending for what you want to do.
  • map() - maps an array to a key-value array, by the primary key. It's for joining with another table's query.
  • insert() - inserts a row. You probably will need also to put the primary key 's value, using a generator or sequencer.
  • update() - updates a row.
  • delete() - deletes a row.

If you use many DBs you 'll need to create a concrete subclass of this DAO class for each DB, like this:

public class CategoryDAOPgSQL extends CategoryDAO { //Using PgSQL Syntax
   public long genID() {

   public Category[] findByDescription(String description) {
        return this.query(
             "WHERE to_tsvector('portuguese', name||' '||description) @@ to_tsquery('portuguese', '"+description.replace("'", "''")+"')");

You 'll also need to create the Factory Class which encapsulates the DAO Class:

public abstract class DAOFactory {
    public abstract CategoryDAO getCategoryDAO();
    public abstract EmployeeDAO getEmployeeDAO();

   public static DAOFActory getInstance() {
   return new DAOFactory() {
    CategoryDAO getCategoryDAO() { return new CategoryDAOPgSQL(); }
    EmployeeDAO getEmployeeDAO() { return new EmployeeDAOPgSQL(); }
return new DAOFactory() {
    CategoryDAO getCategoryDAO() { return new CategoryDAOOracle(); }
    EmployeeDAO getEmployeeDAO() { return new EmployeeDAOOracle(); }