加建网网站,学历提升中心,WordPress指定用户组可见,网络技术挑战赛之前文章#xff1a;
illuminate/database 使用 一-CSDN博客
illuminate/database 使用 二-CSDN博客
illuminate/database 使用 三-CSDN博客
illuminate/database 使用 四-CSDN博客
一、原生查询
1.1 原理 根据之前内容调用执行的静态类为Illuminate\Database\Capsule\M…之前文章
illuminate/database 使用 一-CSDN博客
illuminate/database 使用 二-CSDN博客
illuminate/database 使用 三-CSDN博客
illuminate/database 使用 四-CSDN博客
一、原生查询
1.1 原理 根据之前内容调用执行的静态类为Illuminate\Database\Capsule\Manager类。 根据其源码调用其未定义的静态类返回的是Illuminate\Database\MySqlConnection类对应的静态类。 调用顺序Manager-DatabaseManager-ConnectionFactory-MySqlConnection。 Manager通过对象的定义和调用实现调用DatabaseManager类。 ConnectionFactory通过工厂模式调用MySqlConnection。 MySqlConnection继承Connection。 Connection中可用方法 selectOne($query, $bindings [], $useReadPdo true) 查询并返回一行 selectFromWriteConnection($query, $bindings []) 从读库中查询并返回 select($query, $bindings [], $useReadPdo true) 查询 cursor($query, $bindings [], $useReadPdo true) insert($query, $bindings []) 插入数据 返回布尔执行结果 update($query, $bindings []) 修改数据 返回影响行数 delete($query, $bindings []) 删除数据 statement($query, $bindings []) 返回布尔执行结果 affectingStatement($query, $bindings []) 返回结果影响行数 unprepared($query) 对PDO连接运行一个未准备的原始查询
1.2 实现代码
function test2()
{$sql select * from userinfo where id:id;$info Capsule::select($sql, [id 1]);var_dump($info);$sql select * from userinfo where id in (?) order by id desc;$info Capsule::selectOne($sql, [2, 3]);var_dump($info);$sql insert into userinfo(name,age) values(:name,:age);$row Capsule::insert($sql, [name name, age 10]);var_dump($row);$sql update userinfo set name:name where id:id;$row Capsule::update($sql, [name name2, id 4]);var_dump($row);$sql select * from userinfo where id:id;$info Capsule::statement($sql, [id 1]);var_dump($info);$sql select * from userinfo where id:id;$info Capsule::affectingStatement($sql, [id 1]);var_dump($info);$sql insert into userinfo(name,age) values(name1,10);$info Capsule::unprepared($sql);var_dump($info);$sql delete from userinfo where namename1;$row Capsule::delete($sql);var_dump($row);
}
test2(); 执行结果
array(1) {[0] class stdClass#15 (3) {public $id int(1)public $name string(3) 123public $age int(22)}
}
class stdClass#17 (3) {public $id int(2)public $name string(5) name2public $age int(13)
}
bool(true)
int(0)
bool(true)
int(1)
bool(true)
int(1)
1.3 源代码
namespace Illuminate\Database\Capsule;
use Illuminate\Container\Container;
use Illuminate\Contracts\Events\Dispatcher;
use Illuminate\Database\Connectors\ConnectionFactory;
use Illuminate\Database\DatabaseManager;
use Illuminate\Database\Eloquent\Model as Eloquent;
use Illuminate\Support\Traits\CapsuleManagerTrait;
use PDO;class Manager
{/*** Dynamically pass methods to the default connection.** param string $method* param array $parameters* return mixed*/public static function __callStatic($method, $parameters){return static::connection()-$method(...$parameters);}/*** Get a connection instance from the global manager.** param string|null $connection* return \Illuminate\Database\Connection*/public static function connection($connection null){return static::$instance-getConnection($connection);}/*** Get a registered connection instance.** param string|null $name* return \Illuminate\Database\Connection*/public function getConnection($name null){return $this-manager-connection($name);}/*** Create a new database capsule manager.** param \Illuminate\Container\Container|null $container* return void*/public function __construct(Container $container null){$this-setupContainer($container ?: new Container);// Once we have the container setup, we will setup the default configuration// options in the container config binding. This will make the database// manager work correctly out of the box without extreme configuration.$this-setupDefaultConfiguration();$this-setupManager();}/*** Build the database manager instance.** return void*/protected function setupManager(){$factory new ConnectionFactory($this-container);$this-manager new DatabaseManager($this-container, $factory);}
}
namespace Illuminate\Database;use Doctrine\DBAL\Types\Type;
use Illuminate\Database\Connectors\ConnectionFactory;
use Illuminate\Support\Arr;
use Illuminate\Support\ConfigurationUrlParser;
use Illuminate\Support\Str;
use InvalidArgumentException;
use PDO;
use RuntimeException;class DatabaseManager implements ConnectionResolverInterface
{/*** Create a new database manager instance.** param \Illuminate\Contracts\Foundation\Application $app* param \Illuminate\Database\Connectors\ConnectionFactory $factory* return void*/public function __construct($app, ConnectionFactory $factory){$this-app $app;$this-factory $factory;$this-reconnector function ($connection) {$this-reconnect($connection-getNameWithReadWriteType());};}/*** Reconnect to the given database.** param string|null $name* return \Illuminate\Database\Connection*/public function reconnect($name null){$this-disconnect($name $name ?: $this-getDefaultConnection());if (!isset($this-connections[$name])) {return $this-connection($name);}return $this-refreshPdoConnections($name);}/*** Get a database connection instance.** param string|null $name* return \Illuminate\Database\Connection*/public function connection($name null){[$database, $type] $this-parseConnectionName($name);$name $name ?: $database;// If we havent created this connection, well create it based on the config// provided in the application. Once weve created the connections we will// set the fetch mode for PDO which determines the query return types.if (!isset($this-connections[$name])) {$this-connections[$name] $this-configure($this-makeConnection($database), $type);}return $this-connections[$name];}/*** Make the database connection instance.** param string $name* return \Illuminate\Database\Connection*/protected function makeConnection($name){$config $this-configuration($name);// First we will check by the connection name to see if an extension has been// registered specifically for that connection. If it has we will call the// Closure and pass it the config allowing it to resolve the connection.if (isset($this-extensions[$name])) {return call_user_func($this-extensions[$name], $config, $name);}// Next we will check to see if an extension has been registered for a driver// and will call the Closure if so, which allows us to have a more generic// resolver for the drivers themselves which applies to all connections.if (isset($this-extensions[$driver $config[driver]])) {return call_user_func($this-extensions[$driver], $config, $name);}return $this-factory-make($config, $name);}
}
namespace Illuminate\Database\Connectors;use Illuminate\Contracts\Container\Container;
use Illuminate\Database\Connection;
use Illuminate\Database\MySqlConnection;
use Illuminate\Database\PostgresConnection;
use Illuminate\Database\SQLiteConnection;
use Illuminate\Database\SqlServerConnection;
use Illuminate\Support\Arr;
use InvalidArgumentException;
use PDOException;class ConnectionFactory
{/*** Establish a PDO connection based on the configuration.** param array $config* param string|null $name* return \Illuminate\Database\Connection*/public function make(array $config, $name null){$config $this-parseConfig($config, $name);if (isset($config[read])) {return $this-createReadWriteConnection($config);}return $this-createSingleConnection($config);}/*** Create a single database connection instance.** param array $config* return \Illuminate\Database\Connection*/protected function createSingleConnection(array $config){$pdo $this-createPdoResolver($config);return $this-createConnection($config[driver], $pdo, $config[database], $config[prefix], $config);}/*** Create a new connection instance.** param string $driver* param \PDO|\Closure $connection* param string $database* param string $prefix* param array $config* return \Illuminate\Database\Connection** throws \InvalidArgumentException*/protected function createConnection($driver, $connection, $database, $prefix , array $config []){if ($resolver Connection::getResolver($driver)) {return $resolver($connection, $database, $prefix, $config);}switch ($driver) {case mysql:return new MySqlConnection($connection, $database, $prefix, $config);case pgsql:return new PostgresConnection($connection, $database, $prefix, $config);case sqlite:return new SQLiteConnection($connection, $database, $prefix, $config);case sqlsrv:return new SqlServerConnection($connection, $database, $prefix, $config);}throw new InvalidArgumentException(Unsupported driver [{$driver}].);}
}
namespace Illuminate\Database;use Doctrine\DBAL\Driver\PDOMySql\Driver as DoctrineDriver;
use Doctrine\DBAL\Version;
use Illuminate\Database\PDO\MySqlDriver;
use Illuminate\Database\Query\Grammars\MySqlGrammar as QueryGrammar;
use Illuminate\Database\Query\Processors\MySqlProcessor;
use Illuminate\Database\Schema\Grammars\MySqlGrammar as SchemaGrammar;
use Illuminate\Database\Schema\MySqlBuilder;
use Illuminate\Database\Schema\MySqlSchemaState;
use Illuminate\Filesystem\Filesystem;
use PDO;class MySqlConnection extends Connection
{} namespace Illuminate\Database;use Closure;
use DateTimeInterface;
use Doctrine\DBAL\Connection as DoctrineConnection;
use Doctrine\DBAL\Types\Type;
use Exception;
use Illuminate\Contracts\Events\Dispatcher;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Database\Events\StatementPrepared;
use Illuminate\Database\Events\TransactionBeginning;
use Illuminate\Database\Events\TransactionCommitted;
use Illuminate\Database\Events\TransactionRolledBack;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Query\Grammars\Grammar as QueryGrammar;
use Illuminate\Database\Query\Processors\Processor;
use Illuminate\Database\Schema\Builder as SchemaBuilder;
use Illuminate\Support\Arr;
use LogicException;
use PDO;
use PDOStatement;
use RuntimeException;class Connection implements ConnectionInterface
{/*** Get a new query builder instance.** return \Illuminate\Database\Query\Builder*/public function query(){return new QueryBuilder($this, $this-getQueryGrammar(), $this-getPostProcessor());}/*** Run a select statement against the database.** param string $query* param array $bindings* param bool $useReadPdo* return array*/public function select($query, $bindings [], $useReadPdo true){return $this-run($query, $bindings, function ($query, $bindings) use ($useReadPdo) {if ($this-pretending()) {return [];}// For select statements, well simply execute the query and return an array// of the database result set. Each element in the array will be a single// row from the database table, and will either be an array or objects.$statement $this-prepared($this-getPdoForSelect($useReadPdo)-prepare($query));$this-bindValues($statement, $this-prepareBindings($bindings));$statement-execute();return $statement-fetchAll();});}/*** Run an insert statement against the database.** param string $query* param array $bindings* return bool*/public function insert($query, $bindings []){return $this-statement($query, $bindings);}/*** Run an update statement against the database.** param string $query* param array $bindings* return int*/public function update($query, $bindings []){return $this-affectingStatement($query, $bindings);}/*** Run a delete statement against the database.** param string $query* param array $bindings* return int*/public function delete($query, $bindings []){return $this-affectingStatement($query, $bindings);}/*** Run a raw, unprepared query against the PDO connection.** param string $query* return bool*/public function unprepared($query){return $this-run($query, [], function ($query) {if ($this-pretending()) {return true;}$this-recordsHaveBeenModified($change $this-getPdo()-exec($query) ! false);return $change;});}/*** Execute the given callback in dry run mode.** param \Closure $callback* return array*/public function pretend(Closure $callback){return $this-withFreshQueryLog(function () use ($callback) {$this-pretending true;// Basically to make the database connection pretend, we will just return// the default values for all the query methods, then we will return an// array of queries that were executed within the Closure callback.$callback($this);$this-pretending false;return $this-queryLog;});}/*** Execute an SQL statement and return the boolean result.** param string $query* param array $bindings* return bool*/public function statement($query, $bindings []){return $this-run($query, $bindings, function ($query, $bindings) {if ($this-pretending()) {return true;}$statement $this-getPdo()-prepare($query);$this-bindValues($statement, $this-prepareBindings($bindings));$this-recordsHaveBeenModified();return $statement-execute();});}
}
二、 sql输出
2.1 原理 Illuminate\Database\MySqlConnection类包含变量queryLog获取对应执行语句实际上就是获取queryLog。根据源码每次执行sql语句都使用该类run()函数执行run()中包括logQuery()函数。而logQuery函数就是对queryLog变量进行设置。 返回的queryLog变量值顺序和执行顺序相同所以取左后一条就是最新执行的sql。 框架中默认记录不记录queryLog。
2.2 实现代码
function test3()
{Capsule::enableQueryLog();$sql select * from userinfo where id:id;$info Capsule::select($sql, [id 1]);$sql select * from userinfo where id in (?) order by id desc;$info Capsule::selectOne($sql, [2, 3]);$logs Capsule::getQueryLog();var_dump($logs);var_dump(end($logs));
}
test3(); 执行结果
array(2) {[0] array(3) {query string(35) select * from userinfo where id:idbindings array(1) {id int(1)}time double(7.35)}[1] array(3) {query string(55) select * from userinfo where id in (?) order by id descbindings array(2) {[0] int(2)[1] int(3)}time double(0.33)}
}
array(3) {query string(55) select * from userinfo where id in (?) order by id descbindings array(2) {[0] int(2)[1] int(3)}time double(0.33)
}
说实话输出结果和想的不太一样。我还是希望传回拼接后的语句当然传$sql的时候就可以传拼接后的sql语句。
因为需要打印sql大概有两种情况
调试sql对于使用复杂sql时很实用。调试数据导致的bug。实际运行bug情况很多有些是数据导致查看对应业务的sql执行有时候能快速发现问题。
2.3 源码
class Connection implements ConnectionInterface
{/*** All of the queries run against the connection.** var array*/protected $queryLog [];/*** Indicates whether queries are being logged.** var bool*/protected $loggingQueries false;/*** Enable the query log on the connection.** return void*/public function enableQueryLog(){$this-loggingQueries true;}/*** Disable the query log on the connection.** return void*/public function disableQueryLog(){$this-loggingQueries false;}/*** Log a query in the connections query log.** param string $query* param array $bindings* param float|null $time* return void*/public function logQuery($query, $bindings, $time null){$this-event(new QueryExecuted($query, $bindings, $time, $this));if ($this-loggingQueries) {$this-queryLog[] compact(query, bindings, time);}}/*** Run a SQL statement and log its execution context.** param string $query* param array $bindings* param \Closure $callback* return mixed** throws \Illuminate\Database\QueryException*/protected function run($query, $bindings, Closure $callback){foreach ($this-beforeExecutingCallbacks as $beforeExecutingCallback) {$beforeExecutingCallback($query, $bindings, $this);}$this-reconnectIfMissingConnection();$start microtime(true);// Here we will run this query. If an exception occurs well determine if it was// caused by a connection that has been lost. If that is the cause, well try// to re-establish connection and re-run the query with a fresh connection.try {$result $this-runQueryCallback($query, $bindings, $callback);} catch (QueryException $e) {$result $this-handleQueryException($e, $query, $bindings, $callback);}// Once we have run the query we will calculate the time that it took to run and// then log the query, bindings, and execution time so we will report them on// the event that the developer needs them. Well log time in milliseconds.$this-logQuery($query, $bindings, $this-getElapsedTime($start));return $result;}/*** Run a raw, unprepared query against the PDO connection.** param string $query* return bool*/public function unprepared($query){return $this-run($query, [], function ($query) {if ($this-pretending()) {return true;}$this-recordsHaveBeenModified($change $this-getPdo()-exec($query) ! false);return $change;});}
}
