illuminate/database 使用 五
2023-12-16 17:48:33
之前文章：
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 name='name1'";
$row = Capsule::delete($sql);
var_dump($row);
}
test2();?执行结果：
array(1) {
[0] =>
class stdClass#15 (3) {
public $id =>
int(1)
public $name =>
string(3) "123"
public $age =>
int(22)
}
}
class stdClass#17 (3) {
public $id =>
int(2)
public $name =>
string(5) "name2"
public $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 haven't created this connection, we'll create it based on the config
// provided in the application. Once we've 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, we'll 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=:id"
'bindings' =>
array(1) {
'id' =>
int(1)
}
'time' =>
double(7.35)
}
[1] =>
array(3) {
'query' =>
string(55) "select * from userinfo where id in (?) order by id desc"
'bindings' =>
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 desc"
'bindings' =>
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 connection's 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 we'll determine if it was
// caused by a connection that has been lost. If that is the cause, we'll 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. We'll 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;
});
}
}
文章来源:https://blog.csdn.net/lsswear/article/details/135033087
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！