README

一个用于基本CRUD操作的通用数据访问层。可以作为数据库与存储库/服务之间的层。

安装

通过composer以metarush/data-mapper安装

使用方法

初始化库

<?php

$builder = (new \MetaRush\DataMapper\Builder)
    ->setDsn('mysql:host=localhost;dbname=example') // PDO DSN
    ->setDbUser('foo')
    ->setDbPass('bar');

$dM = $builder->build();

创建新行

// insert 'foo' in column 'col1' and 'bar' in column 'col2'
$data = [
    'col1' => 'foo',
    'col2' => 'bar'
];
$lastInsertId = $dM->create('table', $data);

查找列

// find value of column 'col2' where 'col1' == 'foo'
$column = $dM->findColumn('table', ['col1' => 'foo'], 'col2');
\print_r($column); // bar

查找行

// find row where column 'col1' == 'foo'
$row = $dM->findOne('table', ['col1' => 'foo']);
\print_r($row);

查找多行

// find all rows
$rows = $dM->findAll('table');
\print_r($rows);

// find rows where column 'col1' = 'foo'
$rows = $dM->findAll('table', ['col1' => 'foo']);
\print_r($rows);

// find rows where column 'col1' = 'foo', order by col1 DESC
$rows = $dM->findAll('table', ['col1' => 'foo'], 'col1 DESC');
\print_r($rows);

// find rows where column 'col1' = 'foo', order by col2 DESC, limit 2, offset 3
$rows = $dM->findAll('table', ['col1' => 'foo'], 'col2 DESC', 2, 3);
\print_r($rows);

// find rows grouped by column 'col1'
$dM->groupBy('col1');
$rows = $dM->findAll('table');
\print_r($rows);

更新行

$data = ['col1' => 'bar'];
$where = ['col2' => 'foo'];
$dM->update('table', $data, $where);

删除行

$where = ['col1' => 'foo'];
$dM->delete('table', $where);

使用$where子句

根据Atlas.Query文档，如果列的值为数组，条件将为IN()。如果为null值，条件将为IS NULL。对于所有其他值，条件将为=。如果你传递一个没有值的键，该键将用作原始未转义的条件。

$where = [
    'foo' => ['a', 'b', 'c'],
    'bar' => null,
    'baz' => 'dib',
    'zim = NOW()'
];

上面的示例等同于WHERE foo IN (:__1__, :__2__, :__3__) AND bar IS NULL AND baz = :__4__ AND zim = NOW()

使用其他WHERE操作符的示例

$where = [
    'foo > 20',
    'bar <= 30',
    'baz BETWEEN 5 AND 10',
    "firstName LIKE 'test%'"
];

记住，如果你传递一个没有值的键（如这些其他的WHERE操作符），它们将不被转义。

事务方法

$dM->beginTransaction();
$dM->commit();
$dM->rollBack();

自定义SQL查询

你可以使用带占位符或命名参数的预处理语句

SELECT

$preparedStatement = 'SELECT * FROM table WHERE x = ? AND y = ?';
$bindParams = ['foo', 'bar'];
$fetchStyle = \PDO::FETCH_BOTH; // See https://php.ac.cn/manual/en/pdostatement.fetch.php for options. Default: \PDO::FETCH_BOTH
$rows = $dM->query($preparedStatement, $bindParams, $fetchStyle);
\print_r($rows);

单个INSERT

$preparedStatement = "INSERT INTO table (firstName, lastName, age) VALUES (?, ?, ?)";
$bindParams = ['Mark', 'Calaway', '18'];
$numberOfAffectedRows = $dM->exec($preparedStatement, $bindParams); // returns 1
$lastInsertID = $dM->getLastInsertId();

单条语句中的多个INSERT

$preparedStatement = "INSERT INTO table (firstName, lastName, age) VALUES (?, ?, ?), (?, ?, ?)";
$bindParams = ['Mark', 'Calaway', '18', 'Dwayne', 'Johnson', '17'];
$numberOfAffectedRows = $dM->exec($preparedStatement, $bindParams); // returns 2
$lastInsertID = $dM->getLastInsertId();

UPDATE

$preparedStatement = "UPDATE table SET age = ? WHERE lastName = 'Doe'";
$bindParams = ['18'];
$numberOfAffectedRows = $dM->exec($preparedStatement, $bindParams);

DELETE

$preparedStatement = "DELETE FROM table WHERE lastName = ?";
$bindParams = ['Doe'];
$numberOfAffectedRows = $dM->exec($preparedStatement, $bindParams);

可选的config/builder方法

->setStripMissingColumns(true);

如果设置为true，则create()和update()方法将在其$data参数中删除缺失的列。

->setTablesDefinition(array $tablesDefinition);

当使用setStripMissingColumns(true)时必须设置

$tablesDefinition的示例参数

$tablesDefinition = [
    'UsersTable' => [ // table name
        'id', 'firstName', 'lastName' // column names
    ],
    'PostsTable' => [ // table name
        'id', 'subject', 'message' // columns names
    ]
];

当前适配器

• PDO（通过Atlas.Query）