我读了很多关于如何使用PDO生成DAO类的页面,但我还没有找到一种好的、更快的方法来获得它。
假设我们有一个名为Animals的数据库表,其结构为
CREATE TABLE animals
(
idAnimal int PRIMARY KEY NOT NULL AUTO_INCREMENT,
name varchar(20),
dateOfBirth DATETIME
);
一个好的开发方法是创建两个类:像这个一样的Animals
和AnimalsDAO
class Animals{
/**
* @var integer
*/
public $idAnimal;
/**
* @var string
*/
public $name;
/**
* @var DateTime
*/
public $dateOfBirth;
/**
* Animals constructor.
* @param int $idAnimal
* @param string $name
* @param DateTime $dateOfBirth
*/
public function __construct($idAnimal, $name, DateTime $dateOfBirth) {
$this->idAnimal = $idAnimal;
$this->name = $name;
$this->dateOfBirth = $dateOfBirth;
}
}
在AnimalsDAO
中,通常有从数据库中插入和检索对象的方法。在这个类中,已经存在ORM问题(ORM对象关系映射),因为PDO的fetch方法不能正确地处理对象转换多年来,我一直以这种方式开发DAO
class ClienteDAO {
/**
* @param $idAnimal integer
* @return Cliente
* @throws Exception
*/
public static function getClienteById($idAnimal ){
$q="SELECT * FROM animals WHERE idAnimal =:idanim";
$sth=PDOConnection::instance()->prepare($q);
$sth->bindParam(':idanim', $idCliente,PDO::PARAM_INT);
if($sth->execute()==0)
throw new PDOException("ERROR EXECUTE");
if($sth->rowCount()!=1)
throw new PDOException("ERROR ROW NUMBERS");
$row=$sth->fetch(PDO::FETCH_NUM);
$row[2]=new DateTime($row[2]);
return new Animals(...$row);
}
}
因此,如果我更改添加或删除数据库字段,我只需要编辑Animal
类中的变量,重新生成构造函数(我使用PhpStorm),并最终编辑获取后的行
是否存在生成DAO类的最佳且更快的方法?(另一种方法是检索类属性名称并使用fetch-Names方法,但dateTime列再次存在强制转换问题)
当有一个到数据库的继承通过关联解决,并且继承在php类中时,问题会更加复杂
ER图
数据库结构被翻译成数据库结构
显然,在php端有一个父超类和两个子类(扩展了fater类)
为孩子生成DAO方法的更快方法是什么?
根据OP req,我的Codebase 中的一个例子
设备
namespace DAO;
use commonContext;
use enumsDeviceOStypeEnum;
use NAOInboundIBDeviceSpec;
use NAOOutboundOBDevice;
use protocolsIClassInit;
use protocolsITokenizer;
use traitsClassInitTrait;
class Device extends AbstractDataObject implements IClassInit , ITokenizer
{
const APPLE_PERMANENT_DEVICE_GUID = "f8d55ac7-6e6a-4a0c-a5ec-20df1f384d62";
const GOOGLE_PERMANENT_DEVICE_GUID = "788996ff-5da3-47f2-9601-3f9ae79b51aa";
use ClassInitTrait;
/** @var int $id */
protected $id;
/** @var string $deviceGuid */
var $deviceGuid;
/** @var DeviceOStypeEnum $osType */
var $osType;
/** @var Version $osVersion */
var $osVersion;
/** @var string $manufacturer */
var $manufacturer;
/** @var string $modelCode */
var $modelCode;
/** @var DateTime $createdOn */
var $createdOn;
/**@var DateTime $lastSeen */
var $lastSeen;
/** @var bool $active */
var $active;
/** @var $isPhone */
var $isPhone;
/** @var App $app */
var $app;
public static function postInit($c , $isTraceEnabled , $isPDOuser)
{
}
/**
* Device constructor.
*
* @param int $id
* @param string $deviceGuid
* @param DeviceOStypeEnum $osType
* @param Version $osVersion
* @param string $manufacturer
* @param string $modelCode
* @param DateTime $createdOn
* @param DateTime $lastSeen
* @param App $app
* @param bool $isPhone
* @param bool $active
*/
public function __construct($id , $deviceGuid ,
$osType , $osVersion , $manufacturer , $modelCode ,
DateTime $createdOn , DateTime $lastSeen ,
$active , $isPhone , $app)
{
$this->id = $id;
$this->deviceGuid = $deviceGuid;
$this->osType = $osType;
$this->osVersion = $osVersion;
$this->manufacturer = $manufacturer;
$this->modelCode = $modelCode;
$this->createdOn = $createdOn;
$this->lastSeen = $lastSeen;
$this->active = $active;
$this->app = $app;
$this->isPhone = $isPhone;
}
/**
* @param array $row
*
* @return Device
*/
public static function fromAssociativeArray($row)
{
$OStype = new DeviceOStypeEnum($row['os_type']);
$osVersion = Version::fromString($row['os_version']);
$createdOn = dateTimeFromSQLquery($row['created_on']);
$lastSeen = dateTimeFromSQLquery($row['last_seen']);
$active = (bool) $row['active'];
$deviceGuid = binaryGuidAsStringGuid($row['device_guid_bin']);
$isPhone = (bool) $row['is_phone'];
$app = AppDAO::applicationWithId($row['app_id']);
return new Device(
$row['id'] ,
$deviceGuid ,
$OStype ,
$osVersion ,
$row['manufacturer'] ,
$row['model_code'] ,
$createdOn ,
$lastSeen ,
$active ,
$isPhone ,
$app
);
}
// plus a whole bunch of business logic after
设备DAO(部分)
namespace DAO;
use enumsDeviceOStypeEnum;
use NAOInboundIBDeviceSpec;
use protocolsIClassInit;
use traitsClassInitTrait;
class DeviceDAO implements IClassInit
{
use ClassInitTrait;
/**
* @param string $guid
* @param DeviceOStypeEnum $osType
* @param Version $osVersion
* @param string $manufacturer
* @param string $modelCode
* @param boolean $isPhone
* @param App $app
*
* @return Device|null
*/
public static function insert($guid ,
DeviceOStypeEnum $osType , Version $osVersion ,
$manufacturer , $modelCode ,
$isPhone , App $app)
{
$pdo = self::getClassPDO();
$q = $e = null;
$createdOn = now();
$lastSeen = now();
$sql = <<<SQL
INSERT INTO Device SET device_guid_bin = :guid,
os_type = :ost,
os_version = :version ,
manufacturer=:manufacturer,model_code=:model,
created_on=:co, last_seen = :lastseen , active=1, `is_phone`=:phone, `app_id` = :appid
SQL;
$device = null;
try {
$q = $pdo->prepare($sql);
$q->bindValue('guid' , stringGuidAsBinaryGuid($guid) , PDO::PARAM_STR);
$q->bindValue('ost' , $osType->stringValue , PDO::PARAM_STR);
$q->bindValue('version' , $osVersion->__toString() , PDO::PARAM_STR);
$q->bindValue('manufacturer' , $manufacturer , PDO::PARAM_STR);
$q->bindValue('model' , $modelCode , PDO::PARAM_STR);
$q->bindValue('co' , dateTimeAsSQLstring($createdOn) , PDO::PARAM_STR);
$q->bindValue('lastseen' , dateTimeAsSQLstring($lastSeen) , PDO::PARAM_STR);
$q->bindValue('phone' , $isPhone , PDO::PARAM_BOOL);
$q->bindValue('appid' , $app->getId() , PDO::PARAM_INT);
if ($q->execute()) {
$id = $pdo->lastInsertId();
$device = new Device(
$id , $guid ,
$osType , $osVersion ,
$manufacturer , $modelCode ,
$createdOn , $lastSeen , true , $isPhone ,
$app
);
} else {
self::logQueryFail("Unknown error while inserting a device" , $q , $e);
}
} catch (Exception $e) {
self::logQueryFail("Error while inserting a Device" , $q , $e);
}
return $device;
}
/**
* @param IBDeviceSpec $spec
*
* @return Device|null
*/
public static function insertWithDeviceSpec(IBDeviceSpec $spec)
{
$app = AppDAO::applicationWithGuid($spec->appGuid);
return self::insert(
$spec->deviceGuid , $spec->osType , $spec->osVersion , $spec->manufacturer , $spec->modelCode ,
$spec->isPhone , $app
);
}
/**
* @param Device $device
*
* @return bool
*/
public static function update(Device $device)
{
if (!$device) {
self::getClassLogger()->error("Attemptempt to update null Device");
return false;
}
$pdo = self::getClassPDO();
$q = $e = null;
$sql = <<<SQL
UPDATE Device
SET device_guid_bin = :guid,
os_type = :ost,
os_version = :version ,
manufacturer=:manufacturer,
model_code=:model,
created_on=:co,
last_seen = :lastseen,
active=:ac,
`is_phone`=:phone,
`app_id`=:appid
WHERE
id=:id
SQL;
try {
$q = $pdo->prepare($sql);
$q->bindValue('id' , $device->getId() , PDO::PARAM_STR);
$q->bindValue('guid' , stringGuidAsBinaryGuid($device->deviceGuid) , PDO::PARAM_STR);
$q->bindValue('ost' , $device->osType->stringValue , PDO::PARAM_STR);
$q->bindValue('version' , $device->osVersion->__toString() , PDO::PARAM_STR);
$q->bindValue('manufacturer' , $device->manufacturer , PDO::PARAM_STR);
$q->bindValue('model' , $device->modelCode , PDO::PARAM_STR);
$q->bindValue('co' , dateTimeAsSQLstring($device->createdOn) , PDO::PARAM_STR);
$q->bindValue('lastseen' , dateTimeAsSQLstring($device->lastSeen) , PDO::PARAM_STR);
$q->bindValue('ac' , $device->active , PDO::PARAM_BOOL);
$q->bindValue('phone' , $device->isPhone , PDO::PARAM_BOOL);
$q->bindValue('appid' , $device->app->getId() , PDO::PARAM_INT);
if ($q->execute()) {
return true;
} else {
self::logQueryFail("Unknown error while updating a device" , $q , $e);
}
} catch (Exception $e) {
self::logQueryFail("Error while inserting a Device" , $q , $e);
}
return false;
}
/**
* @param string $guid
*
* @return Device|null
*/
public static function deviceWithDeviceGuid($guid)
{
if (!$guid) return null;
$pdo = self::getClassPDO();
$q = $e = null;
$device = null;
$sql = <<<SQL
SELECT * FROM Device WHERE device_guid_bin=:gu
SQL;
try {
$q = $pdo->prepare($sql);
$q->bindValue(':gu' , stringGuidAsBinaryGuid($guid) , PDO::PARAM_STR);
if ($q->execute()) {
$rows = $q->fetchAll();
if (count($rows) == 0) {
self::getClassLogger()->trace(__FUNCTION__ . " Query for device [$guid] returned no device");
} else if (count($rows) > 1) {
self::logQueryFail(__FUNCTION__ . " : Query for device returned multiple rows ! [$guid]" , $q , $e);
} else {
$row = $rows[0];
$device = Device::fromAssociativeArray($row);
}
} else {
self::logQueryFail(__FUNCTION__ . " : Error while fetching device with guid[$guid]" , $q , $e);
}
} catch (Exception $e) {
self::logQueryFail(__FUNCTION__ . " : Error while fetching device with guid[$guid]" , $q , $e);
}
return $device;
}
}
// etc ...
SQL
--
-- Table structure for table `Device`
--
DROP TABLE IF EXISTS `Device`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Device`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`app_id` bigint(20) NOT NULL,
`os_type` enum ('android','iPhone OS','iOS','nix') NOT NULL,
`os_version` varchar(11) DEFAULT NULL,
`manufacturer` varchar(50) DEFAULT NULL,
`model_code` varchar(50) DEFAULT NULL,
`created_on` datetime NOT NULL,
`last_seen` datetime NOT NULL,
`active` tinyint(4) NOT NULL DEFAULT '1',
`is_phone` tinyint(4) NOT NULL DEFAULT '1',
`device_guid_bin` varbinary(16) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_device_guid` (`device_guid_bin`),
KEY `idx_app` (`app_id`),
KEY `idx_active` (`active`),
CONSTRAINT `fk_device_app` FOREIGN KEY (`app_id`) REFERENCES `App` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB
AUTO_INCREMENT = 68
DEFAULT CHARSET = utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
钞票
- 没有显示
dateTimeFromSQLquery
等功能。。。所有这些都在我的全局功能中。我用它来规范DB。休息时(DB)和飞行中(API)的时间始终为UTC - 对于关系,我系统地更喜欢惰性加载方法(不在显示的代码中)
- SomeObjectDAO完全封装了缓存(或不封装)