PHP code example of jhq0113 / roach-orm
1. Go to this page and download the library: Download jhq0113/roach-orm library . Choose the download type require .
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
jhq0113 / roach-orm example snippets
\roach\Container::set('db', [
'class' => 'roach\orm\Connection',
//
'masters' => [
[
'dsn' => 'mysql:host=192.168.1.14;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
],
[
'dsn' => 'mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
//可以通过options指定配置属性
'options' => [
\PDO::ATTR_TIMEOUT => 3,
]
],
],
//如果没有slave节点，可以不配置，会自动复用master节点
'slaves' => [
[
'dsn' => 'mysql:host=192.168.1.15;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
'options' => [
\PDO::ATTR_TIMEOUT => 2,
]
],
[
'dsn' => 'mysql:host=192.168.1.16;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
'options' => [
\PDO::ATTR_TIMEOUT => 2,
]
],
]
]);
/**
* Created by PhpStorm.
* User: Jiang Haiqiang
* Date: 2020/7/5
* Time: 1:33 PM
*/
/**
* Class UserModel
* @datetime 2020/7/5 1:33 PM
* @author roach
* @email [email protected]
*/
class UserModel extends \roach\orm\Model
{
/**表名称
* @var string
* @datetime 2020/7/5 1:33 PM
* @author roach
* @email [email protected]
*/
public static $tableName = 'user';
}
/**
* 此处返回受影响行数
*/
$rows = UserModel::insert([
'user_name' => 'zhou boss',
'true_name' => '周**',
'password' => hash_hmac('md5', 'Mr.zhou', 'sdfs#$#@3fd'),
'update_time' => time()
]);
if($rows < 1) {
exit('插入失败'.PHP_EOL);
}
//如果想获取刚刚插入数据的`id`,通过如下方式
$newUserId = UserModel::getDb()->lastInsertId();
exit('插入成功，用户id为'.$newUserId.PHP_EOL);
/**
* 此处返回受影响行数
*/
$rows = UserModel::multiInsert([
[
'user_name' => 'zhao boss',
'true_name' => '赵**',
'password' => hash_hmac('md5', 'Mr.zhao', 'sdfs#$#@3fd'),
'update_time' => time()
],
[
'user_name' => 'li boss',
'true_name' => '李**',
'password' => hash_hmac('md5', 'Mr.li', 'sdfs#$#@3fd'),
'update_time' => time()
],
]);
var_dump($rows);
//SELECT * FROM `user` WHERE `id`=1 LIMIT 1
$user = UserModel::find()
->where([
'id' => 1,
])
->one();
//SELECT * FROM `user` WHERE `id` IN(1,2,3) LIMIT 1000
$userList = UserModel::find()
->where([
'id' => [1, 2, 3]
])
->all();
//SELECT * FROM `user` WHERE `id` BETWEEN 1 AND 3 LIMIT 1000
$userList = UserModel::find()
->where([
'id BETWEEN' => [1, 3]
])
->all();
//SELECT * FROM `user` WHERE `id`<3 LIMIT 1000
$userList = UserModel::find()
->where([
'id <' => 3
])
->all();
//SELECT * FROM `user` WHERE `true_name` LIKE '周%' LIMIT 1000
$userList = UserModel::find()
->where([
'true_name LIKE' => '周%'
])
->all();
//SELECT * FROM `user` WHERE `id`=1 AND `is_on`=1
$user = UserModel::find()
->where([
'id' => 1,
'is_on' => 1
])
->one();
//SELECT COUNT(`is_on`) AS `count`,`is_on` FROM `user` GROUP BY `is_on` LIMIT 1000
$list = UserModel::find()
->select('COUNT(`is_on`) AS `count`,`is_on`')
->group([
'is_on', //可以接多个
])
->all();
//SELECT 'id', 'true_name' FROM `user` ORDER BY `id` DESC LIMIT 1000
$userList = UserModel::find()
->select([
'id', 'true_name'
])
->order([
'id' => SORT_DESC,
'is_on' => SORT_ASC,
])
->all();
//SELECT * FROM `user` LIMIT 0,10
$userList = UserModel::find()
->offset(0)
->limit(10)
->all();
//这里返回的是受影响行数
//UPDATE `user` SET `true_name`='sun boss' WHERE `id`=1;
$rows = UserModel::updateAll(['true_name' => 'sun boss'], ['id' => 1]);
//这里返回的是受影响行数
//DELETE FROM `user` WHERE `id`=4;
$rows = UserModel::deleteAll(['id' => 4]);
$db = UserModel::getDb();
try {
$db->begin();
$user = UserModel::find()
->where([
'id' => 1,
'is_on' => 1
])
//事务要都用主库查询
->one(true);
if(!isset($user['id'])) {
//返回false会自动回滚事务
return false;
}
//.....其他操作
$rows = $db->execute('UPDATE `user` SET `true_name`=? WHERE id=1 AND version=?', [
'zheng boss', $user['version']
]);
//如果受影响函数是1，返回true，返回true会自动提交事务
if($rows === 1) {
$db->commit();
}
$db->rollback();
}catch (\Exception $exception) {
$db->rollback();
}
$success = UserModel::getDb()->transaction(function (\roach\orm\Connection $connection){
$user = UserModel::find()
->where([
'id' => 1,
'is_on' => 1
])
//事务要都用主库查询
->one(true);
if(!isset($user['id'])) {
//返回false会自动回滚事务
return false;
}
//.....其他操作
$rows = $connection->execute('UPDATE `user` SET `true_name`=? WHERE id=1 AND version=?', [
'zheng boss', $user['version']
]);
//如果受影响函数是1，返回true，返回true会自动提交事务
return $rows === 1;
});
if(!$success) {
exit('事务提交失败'.PHP_EOL);
}
exit('事务提交成功'.PHP_EOL);
//SELECT * FROM `user` WHERE `id`=1 AND `is_on`=1
$user = UserModel::find()
->where([
'id' => 1,
'is_on' => 1
])
->one(true);
$users = UserModel::getDb()->queryAll('SELECT * FROM `user` WHERE id=? UNION SELECT * FROM `user` WHERE id=?', [
1, 2
]);
//这里返回受影响行数
$rows = UserModel::getDb()->execute('UPDATE `user` SET `true_name`=? WHERE id=1 AND version=1', [
'wu boss'
]);
//将一组新的数据库集群注册到`Container`中，key自己定义即可
\roach\Container::set('tradeDb', [
'class' => 'roach\orm\Connection',
//
'masters' => [
[
'dsn' => 'mysql:host=192.168.1.14;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
],
[
'dsn' => 'mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
//可以通过options指定配置属性
'options' => [
\PDO::ATTR_TIMEOUT => 3,
]
],
],
//如果没有slave节点，可以不配置，会自动复用master节点
'slaves' => [
[
'dsn' => 'mysql:host=192.168.1.15;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
'options' => [
\PDO::ATTR_TIMEOUT => 2,
]
],
[
'dsn' => 'mysql:host=192.168.1.16;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
'options' => [
\PDO::ATTR_TIMEOUT => 2,
]
],
]
]);
/**
* Created by PhpStorm.
* User: Jiang Haiqiang
* Date: 2020/7/5
* Time: 1:33 PM
*/
/**
* Class TradeModel
* @datetime 2020/7/5 1:33 PM
* @author roach
* @email [email protected]
*/
class TradeModel extends \roach\orm\Model
{
/**表名称
* @var string
* @datetime 2020/7/5 1:33 PM
* @author roach
* @email [email protected]
*/
public static $tableName = 'trade';
/**
* @return mixed|\roach\orm\Connection
* @throws ReflectionException
* @datetime 2020/7/5 2:22 PM
* @author roach
* @email [email protected]
*/
public static function getDb()
{
return \roach\Container::get('tradeDb');
}
}
\roach\Container::set('db', [
'class' => 'roach\orm\Connection',
//
'masters' => [
[
'dsn' => 'mysql:host=192.168.1.14;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
],
[
'dsn' => 'mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
//可以通过options指定配置属性
'options' => [
\PDO::ATTR_TIMEOUT => 3,
]
],
],
//如果没有slave节点，可以不配置，会自动复用master节点
'slaves' => [
[
'dsn' => 'mysql:host=192.168.1.15;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
'options' => [
\PDO::ATTR_TIMEOUT => 2,
]
],
[
'dsn' => 'mysql:host=192.168.1.16;port=3306;dbname=roach;charset=utf8',
'username' => 'roach',
'password' => 'roach',
'options' => [
\PDO::ATTR_TIMEOUT => 2,
]
],
],
'calls' => [
[
'method' => 'on',
'params' => [
\roach\orm\Connection::EVENT_EXCEPTION_CONNECT,
function(\roach\events\EventObject $event){
//。。。打日志报警等各种处理，该事件触发了，并不一定所有的节点都不能用了
//exception中是异常信息，config是节点配置
var_dump($event->data['exception'], $event->data['config']);
}
]
],
[
'method' => 'on',
'params' => [
\roach\orm\Connection::EVENT_EXCEPTION_CONNECT_LOST, function (\roach\events\EventObject $event){
//...各种操作
//sql是指当执行某条sql时，mysql连接断了，但是会自动重连一次，如果重连失败，不会再触发该事件，会抛出异常
var_dump($event->data['sql'], $event->data['exception']);
}
]
],
[
'method' => 'on',
'params' => [
\roach\orm\Connection::EVENT_BEFORE_QUERY, function (\roach\events\EventObject $event){
//params为参数绑定查询的参数
var_dump($event->data['stmt'], $event->data['sql'], $event->data['params']);
}
]
],
[
'method' => 'on',
'params' => [
\roach\orm\Connection::EVENT_AFTER_QUERY, function (\roach\events\EventObject $event){
//params为参数绑定查询的参数
var_dump($event->data['stmt'], $event->data['sql'], $event->data['params']);
}
]
],
]
]);
/**此处不会去连接数据库，只是创建\roach\orm\Connection类而已，当真正执行sql的时候才会真正的去连接数据库
* @var \roach\orm\Connection $db
*/
$db = \roach\Container::get('db');
$db->on(\roach\orm\Connection::EVENT_EXCEPTION_CONNECT, function(\roach\events\EventObject $event){
//。。。打日志报警等各种处理，该事件触发了，并不一定所有的节点都不能用了
//exception中是异常信息，config是节点配置
var_dump($event->data['exception'], $event->data['config']);
});
$db->on(\roach\orm\Connection::EVENT_EXCEPTION_CONNECT_LOST, function (\roach\events\EventObject $event){
//...各种操作
//sql是指当执行某条sql时，mysql连接断了，但是会自动重连一次，如果重连失败，不会再触发该事件，会抛出异常
var_dump($event->data['sql'], $event->data['exception']);
});
$db->on(\roach\orm\Connection::EVENT_BEFORE_QUERY, function (\roach\events\EventObject $event){
//params为参数绑定查询的参数
var_dump($event->data['stmt'], $event->data['sql'], $event->data['params']);
});
$db->on(\roach\orm\Connection::EVENT_AFTER_QUERY, function (\roach\events\EventObject $event){
//params为参数绑定查询的参数
var_dump($event->data['stmt'], $event->data['sql'], $event->data['params']);
});