Symfony4 实现从数据库获取数据的方法_(new ())->gettable()-优快云博客

本文链接：https://blog.youkuaiyun.com/hudeyong926/article/details/99540738

从数据库获取数据的方法

$conn  = $this->getDoctrine()->getConnection();
$conn  = $this->getEntityManager()->getConnection();
$sql   = "SELECT name,color FROM test";
$field = $conn->fetchColumn($sql);
$row   = $conn->fetchAssoc($sql);
$list  = $conn->fetchAll($sql);
$conn->exec("update test set name=111");

$repository->find($id);
$repository->findAll();
$repository->findOneByName('Foo');
$repository->findAllOrderedByName();

$repository->findOneBy(array('name' => 'foo', 'price' => 19.99));
$repository->findBy(array('name' => 'foo'),array('price' => 'ASC'));

使用 EchoSQLLogger

你可以通过设置 EchoSQLLogger 来打印所有的 SQL 查询到控制台。这是最简单的方法之一，适用于快速调试。

use Doctrine\DBAL\Logging\EchoSQLLogger;

// 创建 Doctrine 连接
$conn = $this->container->get('doctrine.dbal.default_connection');
// 启用查询日志
$conn->getConfiguration()->setSQLLogger(new EchoSQLLogger());
$this->container->get('doctrine')->getRepository(User::class)->findAll();

这段代码会将所有执行的 SQL 语句直接输出到控制台。首先确保你的 config/packages/dev/doctrine.yaml 文件中启用了日志记录

doctrine:
    dbal:
        logging: true

然后在 config/packages/dev/monolog.yaml 中添加一个处理器来捕获这些日志信息：

monolog:
    handlers:
        doctrine:
            type: stream
            path: '%kernel.logs_dir%/%kernel.environment%.doctrine.log'
            level: debug
            channels: ['doctrine']

这样所有的 Doctrine 查询都会被记录到单独的日志文件中（如：var/log/dev.doctrine.log)

BaseRepository封装

创建一个BaseRepository 类来封装常用的数据库操作可以提高代码的复用性和可维护性。

<?php

namespace App\Repository;

use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\DBAL\Exception\ConnectionException;
use Doctrine\DBAL\RetryableTransactionCallback;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\EntityRepository;
use Doctrine\Persistence\ManagerRegistry;

abstract class BaseRepository extends ServiceEntityRepository
{
    protected EntityManagerInterface $entityManager;

    public function __construct(ManagerRegistry $registry, string $entityClass)
    {
        parent::__construct($registry, $entityClass);
        $this->entityManager = $registry->getManager();
    }

    public function findAll(): array
    {
        return $this->createQueryBuilder('e')
            ->getQuery()
            ->getResult();
    }

    public function findOneById(int $id)
    {
        return $this->find($id);
    }

    public function save(object $entity): void
    {
        $this->entityManager->persist($entity);
        $this->flush();
    }

    public function delete(object $entity): void
    {
        $this->entityManager->remove($entity);
        $this->flush();
    }

    public function flush(): void
    {
        try {
            $this->entityManager->flush();
        } catch (ConnectionException $e) {
            $this->reconnect();
            $this->entityManager->flush();
        }
    }

    public function transactional(callable $callback): mixed
    {
        return $this->entityManager->transactional($callback);
    }

    public function retryable(callable $callback): mixed
    {
        return $this->entityManager->getConnection()->retryableTransaction(
            new RetryableTransactionCallback($callback)
        );
    }

    protected function reconnect(): void
    {
        $this->entityManager->getConnection()->close();
        $this->entityManager->getConnection()->connect();
    }

    // 多表操作方法
    public function joinQuery(string $dql, array $parameters = []): array
    {
        $query = $this->entityManager->createQuery($dql);
        foreach ($parameters as $key => $value) {
            $query->setParameter($key, $value);
        }
        return $query->getResult();
    }

    public function nativeQuery(string $sql, array $parameters = [], array $resultMapping = []): array
    {
        $conn = $this->entityManager->getConnection();
        $stmt = $conn->prepare($sql);
        foreach ($parameters as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        return $stmt->fetchAllAssociative();
    }

    public function insertInto(string $table, array $data): void
    {
        $conn = $this->entityManager->getConnection();
        $columns = implode(', ', array_keys($data));
        $values = ':' . implode(', :', array_keys($data));
        $sql = "INSERT INTO $table ($columns) VALUES ($values)";
        $stmt = $conn->prepare($sql);
        foreach ($data as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
    }

    public function updateTable(string $table, array $data, string $where, array $whereParams = []): void
    {
        $conn = $this->entityManager->getConnection();
        $set = [];
        foreach ($data as $key => $value) {
            $set[] = "$key = :$key";
        }
        $setStr = implode(', ', $set);
        $sql = "UPDATE $table SET $setStr WHERE $where";
        $stmt = $conn->prepare($sql);
        foreach ($data as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        foreach ($whereParams as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
    }

    public function deleteFrom(string $table, string $where, array $whereParams = []): void
    {
        $conn = $this->entityManager->getConnection();
        $sql = "DELETE FROM $table WHERE $where";
        $stmt = $conn->prepare($sql);
        foreach ($whereParams as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
    }
}

查询实例

use Doctrine\Common\Collections\Criteria;

class GenusRepository extends EntityRepository
{
    static public function createExpertCriteria()
    {
        return Criteria::create()
            ->andWhere(Criteria::expr()->gt('yearsStudied', 20))
            ->orderBy(['yearsStudied', 'DESC']);
    }

    public function findAllExperts()
    {
        return $this->createQueryBuilder('genus')
            ->addCriteria(self::createExpertCriteria())
            ->getQuery()->getScalarResult();
    }

    public function getWhatYouWant()
    {
        $qb = $this->createQueryBuilder('u');
        $count = $qb->select( 'count(1)' )
            #清除limit offset,size
            ->setFirstResult(null)->setMaxResults(null)
            ->getQuery()->getSingleScalarResult();

        $qb->where('u.id != :identifier')
            ->groupBy('p.server_number')
            ->setParameter('identifier', 1);
        #打印查询sql
        return $qb->getQuery()->getSQL();
    }
}

同时连接多个数据库配置\config\packages\doctrine.yaml

doctrine:
  dbal:
      default_connection: default
      connections:
         default:
            # configure these for your database server
            driver: 'pdo_mysql'
            server_version: '5.7'
            charset: utf8mb4
            url: '%env(resolve:DATABASE_URL)%'
         test:
            # configure these for your database server
            driver: 'pdo_mysql'
            server_version: '5.7'
            charset: utf8mb4
            url: '%env(resolve:DATABASE_TEST_URL)%'
  orm:
        default_entity_manager: default
        entity_managers:
            default:
              connection: default
              mappings:
                  Main:
                      is_bundle: false
                      type: annotation
                      dir: '%kernel.project_dir%/src/Entity/Main'
                      prefix: 'App\Entity\Main'
                      alias: Main
            customer:
              connection: test
              mappings:
                  Customer:
                      is_bundle: false
                      type: annotation
                      dir: '%kernel.project_dir%/src/Entity/Customer'
                      prefix: 'App\Entity\Customer'
                      alias: Customer

Controller使用test数据库查询

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
class TestController extends Controller
{
    public function index()
    {
        $conn  = $this->getDoctrine()->getConnection('test');
        $conn  = $this->get('doctrine.dbal.test_connection');
        //$conn->getParams(),$conn->getHost(); 
        $users = $conn->fetchAll('SELECT * FROM users');
    }
}

use Doctrine\ORM\EntityManagerInterface;
class UserController extends AbstractController
{
    public function index(EntityManagerInterface $entityManager)
    {
        $entityManager = $this->getDoctrine()->getManager();
        $customerEntityManager = $this->getDoctrine()->getManager('customer');
        $customerEntityManager = $this->get('doctrine.orm.customer_entity_manager');
    }
}

Command中使用

class ImportDataCommand extends DoctrineCommand {

$conn = $this->getContainer()->get('doctrine.dbal.test_connection');

在Symfony框架中，集成laravel的ORM

Composer.json

"require": {  
    ...  
    "wouterj/eloquent-bundle": "^1.0",  
    ...  
},

调用设置新的数据库连接，让Illuminate\model支持container

<?php
namespace App\Model;

use Illuminate\Database\Eloquent\Model;
use Symfony\Component\DependencyInjection\ContainerInterface;

class BaseModel extends Model
{
    static $container;

    public function setContainer(ContainerInterface $container)
    {
        static::$container = $container;

        return new static();
    }

    public function getContainer()
    {
        return static::$container;
    }

    /**
     * 动态设置Symfony的数据库配置转化成wouterj_eloquent
     *
     * @param string $connName
     * @return BaseModel
     */
    public function getOrmConnection($connName = 'default')
    {
        $container = self::getContainer();
        $databaseManager = $container->get('wouterj_eloquent.database_manager');
        if ('default' != $connName) {
            //获取wouterj_eloquent数据库配置
            $defaultConf = $databaseManager->getConfig();

            //获取Symfony数据库配置
            $conn = $container->get('doctrine.dbal.'.$connName.'_connection');
            $container->get('wouterj_eloquent')->addConnection([
                'driver' => $defaultConf['driver'] ?? 'mysql',
                'host' => $conn->getHost(),
                'database' => $conn->getDatabase(),
                'username' => $conn->getUsername(),
                'password' => $conn->getPassword(),
                'port' => $conn->getPort(),
                'prefix' => $defaultConf['prefix'] ?? '',
            ], $connName);
        }
        $databaseManager->setDefaultConnection($connName);

        return new static();
    }
}

//利用container调用Symfony的model
User::setContainer($this->container)->get("App\Model\PlatformModel")->getConn();
//切换数据库
User::setContainer($this->container)::getOrmConnection('zx')::where("agent_id" , $id)->get()

Laravel ORM_laravel orm leftjoin-优快云博客