Using the DAO Generator for PHP and MySQL

Using the DAO Generator for PHP and MySQL

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:

  • Install the DAO generator for PHP and MySQL
  • Generate a DAO artifact using a given database (bookstore)
  • Create an application using DAO generator tool and its factory classes
  • Create a transaction spanning multiple SQL operations
  • Add a custom DAO function to a generated class

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.

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.phpvarchar(50)varchar(50)varchar(50)varchar(50)int(11)int(11)varchar(50)varchar(50)int(11)int(11)generated/class/mysql/BooksMySqlDAO.class.phpvarchar(50)varchar(50)int(11)varchar(50)int(11)generated/class/dao/BooksDAO.class.phpgenerated/include_dao.phpgenerated/class/dao/DAOFactory.class.php
  5. 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:

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:

clean();$transaction->rollback();// Starting a new transaction$transaction = new Transaction();echo '****** Query All the books table ******'.'
'; $arr = DAOFactory::getBooksDAO()->queryAll();for($i=0;$iid.' '.$row->title.' '.$row->author.' '. $row->yearofpublication.' '.$row->publisher.' '. $row->price.'

';}echo '****** Deleting the third row ******'.'
';$rowDeleted = DAOFactory::getBooksDAO()->delete(3);echo 'rows deleted ='.$rowDeleted.'

';echo '****** Loading the fifth row ******'.'
';$art = DAOFactory::getBooksDAO()->load(5);echo 'Price for the fifth record book is = '.$art->price.'

';echo '****** Printing all rows order by title ******'.'
';$article = DAOFactory::getBooksDAO()->queryAllOrderBy('title');for($i=0;$iid.' '.$row->title.' '.$row->author.' '. $row->yearofpublication.' '.$row->publisher.' '. $row->price.'

';}// 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 2562 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 453 The Sonnets Edgar Allan Poe 1602 The Literature Page 3004 Winnetow Karl May 1956 The truth 1235 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 2565 JBoos Tools 3 Anghel Leonard 2009 Packt 5692 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 454 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);}

You can add to the example.php page to call the preceding function and print the records for which yearofpublication is between 1850 and 2009:

echo "****** Printing all rows where yearofpublication is between.    '1850' and '2009'******".'
';$arr = DAOFactory::getBooksDAO()->queryByYear();for($i=0;$iid.' '.$row->title.' '.$row->author.' '. $row->yearofpublication.' '.$row->publisher.' ' .$row->price.'

';}

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 454 Winnetow Karl May 1956 The truth 1235 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.

devx-admin

devx-admin

Share the Post:

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1

Copilot Revolution

Microsoft Copilot: A Suit of AI Features

Microsoft’s latest offering, Microsoft Copilot, aims to revolutionize the way we interact with technology. By integrating various AI capabilities, this all-in-one tool provides users with an improved experience that not

AI Girlfriend Craze

AI Girlfriend Craze Threatens Relationships

The surge in virtual AI girlfriends’ popularity is playing a role in the escalating issue of loneliness among young males, and this could have serious repercussions for America’s future. A

AIOps Innovations

Senser is Changing AIOps

Senser, an AIOps platform based in Tel Aviv, has introduced its groundbreaking AI-powered observability solution to support developers and operations teams in promptly pinpointing the root causes of service disruptions

Bebop Charging Stations

Check Out The New Bebob Battery Charging Stations

Bebob has introduced new 4- and 8-channel battery charging stations primarily aimed at rental companies, providing a convenient solution for clients with a large quantity of batteries. These wall-mountable and

Malyasian Networks

Malaysia’s Dual 5G Network Growth

On Wednesday, Malaysia’s Prime Minister Anwar Ibrahim announced the country’s plan to implement a dual 5G network strategy. This move is designed to achieve a more equitable incorporation of both

Advanced Drones Race

Pentagon’s Bold Race for Advanced Drones

The Pentagon has recently unveiled its ambitious strategy to acquire thousands of sophisticated drones within the next two years. This decision comes in response to Russia’s rapid utilization of airborne

Important Updates

You Need to See the New Microsoft Updates

Microsoft has recently announced a series of new features and updates across their applications, including Outlook, Microsoft Teams, and SharePoint. These new developments are centered around improving user experience, streamlining

Price Wars

Inside Hyundai and Kia’s Price Wars

South Korean automakers Hyundai and Kia are cutting the prices on a number of their electric vehicles (EVs) in response to growing price competition within the South Korean market. Many

Solar Frenzy Surprises

Solar Subsidy in Germany Causes Frenzy

In a shocking turn of events, the German national KfW bank was forced to discontinue its home solar power subsidy program for charging electric vehicles (EVs) after just one day,

Electric Spare

Electric Cars Ditch Spare Tires for Efficiency

Ira Newlander from West Los Angeles is thinking about trading in his old Ford Explorer for a contemporary hybrid or electric vehicle. However, he has observed that the majority of

Solar Geoengineering Impacts

Unraveling Solar Geoengineering’s Hidden Impacts

As we continue to face the repercussions of climate change, scientists and experts seek innovative ways to mitigate its impacts. Solar geoengineering (SG), a technique involving the distribution of aerosols

Razer Discount

Unbelievable Razer Blade 17 Discount

On September 24, 2023, it was reported that Razer, a popular brand in the premium gaming laptop industry, is offering an exceptional deal on their Razer Blade 17 model. Typically