README

KMapper 库是一个用于访问和操作 MySql 数据库的 PHP 工具包。它提供了一个名为 TableMapper 的查询构建器类。KMapper 是一个 PDO 包装库。数据以关联数组的形式返回。

说明

首先在你的 /app/config 或 /application/config 或 /config 目录中创建一个名为 kmapper.php 的文件，并包含以下内容。

• 对于非 MVC 框架，使用 define('KMAPPER_CONFIG_LOCATION', '/my/cistom/path') 定义 kmapper.php 配置路径。

return array(
    // default mandatory
    'default' => array(
        'host' => 'localhost',
        'dbname' => 'kdbtest',
        'user' => 'root',
        'password' => 'superpass',
        'prefix' => '',
        'pdoattributes' => array(
            array(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC),
            array(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION),
            array(\PDO::ATTR_EMULATE_PREPARES, false)
        )
    ),
    'db1' => array(
       'host' => 'localhost',
        'dbname' => 'otherdatabase',
        'user' => 'root',
        'password' => 'superpass',
        'prefix' => 'test',
        'pdoattributes' => array(
            array(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC),
            array(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION),
            array(\PDO::ATTR_EMULATE_PREPARES, false)
        )

    )
);

执行第一个查询

$DataObject = \KMapper\MySql::query("SELECT * FROM t1");
$DataObject = \KMapper\MySql::execute(
    "SELECT * FROM `#__t1` WHERE id = ? AND age = ?", 
    array(
        array(12,\PDO::PARAM_INT), 
        array(25, \PDO::PARAM_INT)
    )
);
$DataObject = \KMapper\MySql::execute(
    "SELECT * FROM `#__t1` WHERE id = :id AND age = :age", 
    array(
        array(':id' => 12,\PDO::PARAM_INT), 
        array(':age' => 25, \PDO::PARAM_INT)
    )
);

表前缀

#__ is a placeholder, it will be replaced with the value from 'prefix' in config.

多个数据库连接

$options['connection'] = new  KMapper\MySqlDbConnect('db1');

$DataObject = \KMapper\MySql::query("SELECT * FROM #__t1", $options);
$DataObject = \KMapper\MySql::execute("SELECT * FROM #__t1 WHERE id = ?", array(12), $options);

查询构建器

$UserMP = new \KMapper\TabelMapper("#__user", 'usr');

$UsersDataObject = $UserMP->setSelect(array("usr.first_name", "usr.last_name", "addr.zip"))
                    // table1, joinField1, onTable2, onField2, table1Alias
                    ->setInnerJoin("#__address", "id_user", "usr", "id", "addr")
                    ->setWhere("usr.status != ? AND usr.smart = ? AND (addr.zip = ? OR addr.zip = ?)", array('banned', false, '23000', '21000'))
                    ->setOrderBy("usr.name ASC")
                    ->fetchAll();

var_dump($UsersDataObject->toArray());
var_dump($UsersDataObject->toJson());

插入和更新

$UserMP = new \KMapper\TabelMapper("#__user");

$data = array(
    'first_name' => "Fu",
    'last_name'  => "Bar" 
);
// INSERT, no id provided
$UserMP->save($data);


$data = array(
    'id' => 22,
    'first_name' => "Fu",
    'last_name'  => "Bar" 
);
// UPDATE where id = 22
$UserMP->save($data);

如果 "id" 不是主键名称，则需要定义键

$UserMP = new \KMapper\TabelMapper("#__user");
$UserMP->setPrimaryKeyName('my_unstandard_id')->save($data);

多个更新和插入

$UserMP = new \KMapper\TabelMapper("#__user");

$data = array
    array('first_name' => 'Kriss', 'last_name' => 'Kristiansen'),
    array('first_name' => 'Johnny', 'last_name' => 'Johnosn')

);

$UserMP->batchSave($data);

WHERE id IN ()

$roles = array(1,2,5,8);
// set placeholders
$inPlaceholders =\KMapper\MySql::getPlaceholders($roles);

$i = KMapper\TableMapper::getInstance()
    ->setFrom('#__user')
    ->setWhere("`user_id` = ? AND `group_id` IN ( {$inPlaceholders} )", array_merge(array($user->getId()),$roles))
    ->countAll();

事务

try{
    \KMapper\MySql::transactionBegin());

    $last = \KMapper\MySql::query($sqlTask)->getLastID();

    if(!$last){
        throw new \Exception("Could not insert");
    }

    if(!\KMapper\MySql::query($sqlHierarchy)->isSuccess()){
        throw new \Exception("Query error");
    }

    \KMapper\MySql::transactionCommit());
    
}  catch (PDOException $E){
    \KMapper\MySql::transactionRollback();
}  catch (Exception $E){
    \KMapper\MySql::transactionRollback();
}

使用 Composer 安装 KMapper

"require": {
    "katropine/kmapper" : "dev-master"
}