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.
- Set the Form properties.
- Create a Field Instance for each Field in the table, and set its attributes.
- If using it in many DBMS, you will need to set abstractDAO as true and make DAO class abstract.
- If the class is not abstract, set the getConnection event to get the PDO/Connection object.
- Create your own (abstracts or not) methods in the declarationsDAO event.
- 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
@Override
public long genID() {
...
}
@Override
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(); }
};
*/
}
}
