Using the DAO Generator for PHP and MySQL

Posted by huongnt2 at November 20, 2009
1 comments | 1,683 views

Data access object (DAO) provides an abstract interface to a database—giving developers access to common database operations without them having to know the database schema details—essentially, data access object separate the business logic and data tiers of an application. This separation is important, because these two important application tiers should know almost nothing about each other, and you can expect both to evolve frequently and independently. With DAO in place, changing business logic can use the same DAO interface, while changes to persistence logic does not affect DAO clients—as long as the interface remains correctly implemented.

The DAO generator for PHP and MySQL automates the process of creating DAO classes for MySQL databases. In this article, you’ll see how to:

DAO generator for PHP and MySQL

Figure 1. DAO Generator Architecture: The diagram shows the architecture and data flow for the DAO generator for PHP and MySQL.

A DAO generator creates classes in a specific language, usually for a specific target database. You use the DAO generator for PHP and MySQL to generate PHP classes that query and update MySQL databases using the DAO design pattern. The tool is relatively simple to install and use, but to use it effectively, you need to know a little about the DAO mechanism.

You can download the DAO generator for PHP and MySQL here. To install it, follow these steps:

  1. Download the phpdao-1.7.zip archive.
  2. Unzip the archive in the htdocs directory.
  3. Setup a connection to an existing database by specifying the database properties in the templates/class/dao/sql/ConnectionProperty.class.php file.
  4. Run the generate.php script.
  5. The tool places generated classes in the newly created folder named generated.

Figure 1 shows a diagram of the DAO generator for PHP and MySQL tool architecture.

Introduction to the DAO Classes

Here’s a simple description of each of the important DAO classes:

  • ConnectionFactory class: This is the class that works most closely with the database. This “factory” class is responsible for opening connections to a database. It does that with a getConnection method, which takes three arguments representing the database name and username/password credentials. These arguments are mapped in POJO style in the ConnectionProperty class.
  • Connection class: This class represents a single connection to the database. The connection is pulled from ConnectionFactory class through the Connection constructor.

      public function Connection() {

         $this->connection = ConnectionFactory::getConnection();
}

      This class is also responsible for closing a connection and executing SQL queries.
      Transaction class      : This class encapsulates a set of functions that enable transactional operations against the database. It offers an array of transactions through the helper class ArrayList, which is a class that simulates a collection over a PHP array. The transactions use connections provided by the Connection class. In addition, the Transaction class provides access to the current transaction through the getCurrentTransaction function.

    • QueryExecutor class: The QueryExecutor class provides functions for executing SQL statements. (Actually, it provides functions that implement the CRUD syntax.) The execute function executes any SELECT SQL statement, while the executeUpdate function executes UPDATE, DELETE, and INSERT

    • statements:

      public static function execute($sqlQuery){
   $transaction = Transaction::getCurrentTransaction();
   if(!$transaction){
      $connection = new Connection();
   }else{
      $connection = $transaction->getConnection();
   }
   $query = $sqlQuery->getQuery();
   $result = $connection->executeQuery($query);
   if(!$result){
      throw new Exception(mysql_error());
   }
   $i=0;
   $tab = array();
   while ($row = mysql_fetch_array($result)){
      $tab[$i++] = $row;
   }
   mysql_free_result($result);
   if(!$transaction){
      $connection->close();
   }
   return $tab;
}

public static function executeUpdate($sqlQuery){
   $transaction = Transaction::getCurrentTransaction();
   if(!$transaction){
      $connection = new Connection();
   }else{
      $connection = $transaction->getConnection();
   }
   $query = $sqlQuery->getQuery();
   $result = $connection->executeQuery($query);
   if(!$result){
      throw new Exception(mysql_error());
   }
   return mysql_affected_rows();
}
    • {databasename}MySqlDAO class: The DAO generator creates this class, which provides a set of DAO functions specific to a particular database. The generated class resides in the generated/class/mysql folder, and its name is a combination of the database name and the suffix MySqlDAO. Later in this article you'll see how to generate and use this class.
      • static public function getConnection() {
$conn = mysql_connect(ConnectionProperty::getHost(),
ConnectionProperty::getUser(),
ConnectionProperty::getPassword());
mysql_select_db(ConnectionProperty::getDatabase());
if(!$conn){
throw new Exception('could not connect to database');
}
return $conn;
}
    With that background in place, it’s time to jump into an example.

    Generating DAO Artifacts

    This section shows the process of generating a DAO artifact using a database named bookstore. The database contains a single table named books that has the structure shown in Figure 2.

    Figure 2. The Books Table Structure: This screen shows shows the structure and some sample data from the books table in the bookstore database.
    To create the table in Figure 2, execute this SQL statement:
    create table books
   (id int not null auto_increment primary key,
    title varchar(50),
    author varchar(50),
    yearofpublication int,
    publisher varchar(50),
    price int);

    Before generating the DAO artifacts, populate the books table with five records by executing the following INSERT statements:

    insert into books values(1,"Annabel Lee",
   "Edgar Allan Poe",1849,"The Literature Page",256);
insert into books values(2,"The Ballad of Reading Gaol",
   "Oscar Wilde",1898,"The Literature Page",475);
insert into books values(3,"The Sonnets",
   "Edgar Allan Poe",1602,"The Literature Page",300);
insert into books values(4,"Winnetow",
   "Karl May",1956,"The truth",123);
insert into books values(5,"JBoss Tools 3",
   "Anghel Leonard",2009,"Packt",569);

    With the database and books table created and populated, you can execute the DAO generator to create the DAO artifacts. To run the generator, follow these steps:

    1. In the ConnectionProperty class set the connection properties host, user, password, and database.
    2. Run the generated.php script.
    3. The output will look like this:
      4. generated/class/dto/Books.class.php
varchar(50)
varchar(50)
varchar(50)
varchar(50)
int(11)
int(11)
varchar(50)
varchar(50)
int(11)
int(11)
generated/class/mysql/BooksMySqlDAO.class.php
varchar(50)
varchar(50)
int(11)
varchar(50)
int(11)
generated/class/dao/BooksDAO.class.php
generated/include_dao.php
generated/class/dao/DAOFactory.class.php
    4. The last three lines of the output show the names of generated classes, placed in a new generated folder that appears as a subfolder in the phpdao-1.7 directory.

    The three classes generated for application work with the books table. The first class, in the file generated/class/dto/Books.php, defines an object that represents the books table:

    

    < ?php
   /**
    * Object represents table 'books'
    *
         * @author: http://phpdao.com
         * @date: 2009-08-10 22:50
    */
   class Books{

      var $id;
      var $title;
      var $author;
      var $yearofpublication;
      var $publisher;
      var $price;
   }
?>

    The second class, in the generated/class/dao/BooksDAO.php file (see Listing 1), is an interface that defines various operations on the books table.

    The third and final class, in the file generated/class/mysql/BooksMySqlDAO.php, implements the preceding interface class. You can see this class in the corresponding folder, (it’s not included here because of its length).

    Working with a Transaction

    This section shows how to use the DAO generator for PHP and MySQL to create a transaction. To use DAO generator for PHP and MySQL create the example.php script and place it into the generated folder, which should contain all DAO files. In this example the transaction spans two SELECT and one DELETE SQL statements:

    

    < ?php
// Include all DAO files
require_once('include_dao.php');
$transaction = new Transaction();
//Clean table
//DAOFactory::getBooksDAO()->clean();
$transaction->rollback();
// Starting a new transaction
$transaction = new Transaction();
echo '****** Query All the books table ******'.'<br />';
$arr = DAOFactory::getBooksDAO()->queryAll();
for($i=0;$i<count ($arr);$i++){
   $row = $arr[$i];
   echo $row->id.' '.$row->title.' '.$row->author.' '.
      $row->yearofpublication.' '.$row->publisher.' '.
      $row->price.'<br /><br />';
}
echo '****** Deleting the third row ******'.'<br />';
$rowDeleted = DAOFactory::getBooksDAO()->delete(3);
echo 'rows deleted ='.$rowDeleted.'<br /><br />';
echo '****** Loading the fifth row ******'.'<br />';
$art = DAOFactory::getBooksDAO()->load(5);
echo 'Price for the fifth record book is = '.$art->price.'<br /><br />';
echo '****** Printing all rows order by title ******'.'<br />';
$article = DAOFactory::getBooksDAO()->queryAllOrderBy('title');
for($i=0;$i</count><count ($article);$i++){
   $row = $article[$i];
   echo $row->id.' '.$row->title.' '.$row->author.' '.
      $row->yearofpublication.' '.$row->publisher.' '.
      $row->price.'<br /><br />';
}
// Commit transaction
$transaction->commit();
?>

    When you run the example, it outputs:

    ****** Query All the books table ******
1 Annabel Lee Edgar Allan Poe 1849 The Literature Page 256
2 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 45
3 The Sonnets Edgar Allan Poe 1602 The Literature Page 300
4 Winnetow Karl May 1956 The truth 123
5 JBoos Tools 3 Anghel Leonard 2009 Packt 569
****** Deleting the third row ******
rows deleted =1
****** Loading the fifth row ******
Price for the fifth record book is = 569
****** Printing all rows order by title ******
1 Annabel Lee Edgar Allan Poe 1849 The Literature Page 256
5 JBoos Tools 3 Anghel Leonard 2009 Packt 569
2 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 45
4 Winnetow Karl May 1956 The truth 123

    Adding a New DAO Function

    This section shows how to create a new DAO function that will print all the records with publication dates between 1850 and 2009. Add this function to the BooksMySQLDAO.class.php after all the existing query functions:

    

    public function queryByYear(){
   $sql = "SELECT * FROM books WHERE yearofpublication".
      " BETWEEN '1850' AND '2009'";
   $sqlQuery = new SqlQuery($sql);
   return $this->getList($sqlQuery);
}
</code></pre>
You can add to the <tt>example.php</tt> page to call the preceding function and print the records for which <tt>yearofpublication</tt> is between 1850 and 2009:
<pre><code>echo "****** Printing all rows where yearofpublication is between.
   '1850' and '2009'******".'<br />';
$arr = DAOFactory::getBooksDAO()->queryByYear();
for($i=0;$i<count ($arr);$i++){
   $row = $arr[$i];
   echo $row->id.' '.$row->title.' '.$row->author.' '.
      $row->yearofpublication.' '.$row->publisher.' '
      .$row->price.'<br /><br />';
}
</count></code>
</pre><pre><code>

    The output is:

    ****** Printing all rows where yearofpublication is between '1850' AND '2009'******
2 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 45
4 Winnetow Karl May 1956 The truth 123
5 JBoss Tools 3 Anghel Leonard 2009 Packt 569

    You’ve seen how the DAO mechanism works, including the process of using a factory class schema to generate a DAO artifact using a specified database (bookstore), create an application using the DAO generator tools and its factory classes, create a transaction over the bookstore database, and how to add new DAO functions to the generated {databasename}MySqlDAO class. While you can code all this functionality manually, it’s better to automate such repetitive work, because automation reduces errors and saves time.

    Source: http://www.devx.com/webdev/Article/42625/0/page/1

    Related posts:

    1. Write SQL Code for MySQL Using HeidiSQL 4
    2. Using Php and Mysql to Develop a Cms
    3. Create Remove and Repair a MySQL or PostgreSQL Database in CPanel
    4. 10 Useful Code Snippets For PHP Developers
    5. Sample Application use Smarty: Guestbook – Part 2
    StumbleIt!
    Articles & Tutorials, Outsourcing & Offshore Development, PHP, Project Management
    , , , , ,
    Related posts
    Comments
    Portable generator
    May 25, 2010

    • good article,can’t hardly wait for more article.. thanks

    --ReplyReply to this comment


    Leave a comment

    (required)

    (required)


    Spam protection by WP Captcha-Free