Magento 2 中怎么运行直接SQL查询

Posted by

开发人员使用模型来访问和修改 Magento 2 数据库中的数据。但是,有时,项目要求需要使用自定义 SQL 查询与数据库进行通信。

执行/运行直接SQL查询的简单方法,如下所示:

<?php
/**
 * Created by PhpStorm.
 * User: yshuq
 * Date: 2022/7/19
 * Time 2:09 PM
 */

namespace Cg\MetaTitleCron\Controller\Demo;

use Magento\Framework\App\Action\Context;
use Magento\Framework\App\Action\HttpGetActionInterface;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\App\ObjectManager;

class Index  extends \Magento\Framework\App\Action\Action implements HttpGetActionInterface
{

    protected $resourceConnection;
    public function __construct(
        ResourceConnection $resourceConnection,
        Context $context
    )
    {
        $this->resourceConnection = $resourceConnection;
        parent::__construct($context);
    }

    public function  execute()
    {
        $connection = $this->resourceConnection->getConnection();
        // $table is table name
        $table = $connection->getTableName('my_custom_table');
        //For Select query
        $query = "Select * FROM " . $table;
        $result = $connection->fetchAll($query);
        $this->_logger->log(print_r($result, true));
        $id = 2;
        $query = "SELECT * FROM `" . $table . "` WHERE id = $id ";
        $result1 = $connection->fetchAll($query);
        $this->_logger->log(print_r($result1, true));
        //For Insert query
        $tableColumn = ['id', 'name', 'age'];
        $tableData[] = [5, 'xyz', '20'];
        $connection->insertArray($table, $tableColumn, $tableData);
        $query = "INSERT INTO `" . $table . "`(`id`, `name`, `age`) VALUES (7,'mtm',33)";
        $connection->query($query);
        // For Update query
        $id = 1;
        $query = "UPDATE `" . $table . "` SET `name`= 'test' WHERE id = $id ";
        $connection->query($query);
        $query1 = "UPDATE `" . $table . "` SET `name`= 'test', `age` = 14 WHERE id = $id ";
        $connection->query($query1);
        // For Delete query
        $id = 1;
        $query = "DELETE FROM `" . $table . "` WHERE id = $id ";
        $connection->query($query);
    }
}