Symfony4 实现从数据库获取数据的方法

从数据库获取数据的方法

$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'));

查询

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中使用

$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();
    }

    /**
     * 过滤数据库没有的字段
     *
     * @param $data
     * @return mixed
     */
    public static function filterFields($data)
    {
        $fields = array_keys($data);

        $list = self::getDbFields();
        foreach ($fields as $key) {
            if (!array_key_exists($key, $list)) {
                unset($data[$key]);
            }
        }

        return $data;
    }

    /**
     * 取得数据表的字段信息.
     *
     * @return array
     */
    public static function getDbFields()
    {
        $table = (new static())->getTable();
        $result = DB::select('SHOW FULL COLUMNS FROM '.$table);

        $columns = [];
        foreach ($result as $val) {
            $val = (array) $val;
            $columns[$val['Field']] = [
                'name' => $val['Field'],
                'type' => $val['Type'],
                'notnull' => (bool) ('' === $val['Null']), // not null is empty, null is yes
                'default' => $val['Default'],
                'comment' => $val['Comment'],
                'primary' => ('pri' == strtolower($val['Key'])),
                'autoinc' => ('auto_increment' == strtolower($val['Extra'])),
            ];
        }

        return $columns;
    }

    /**
     * @param string $tablePref
     * @return array
     * Illuminate\Support\Facades\DB;
     */
    public static function checkTables($tablePref = '')
    {
        $config = DB::connection()->getConfig();
        $key = 'Tables_in_'.$config['database'];

        $sql = "SHOW TABLES";
        if ($tablePref) {
            $sql .= " LIKE '%$tablePref'";
            $key .= " (%$tablePref)";
        }
        $list = DB::select($sql);

        return array_column($list, $key);
    }

    /**
     * 新增
     *
     * @param array $params
     * @return mixed
     */
    public static function store(array $params = [])
    {
        $params = self::filterFields($params);

        return self::create($params);
    }

    /**
     * 修改数据
     *
     * @param array $where
     * @param array $params
     * @return mixed
     */
    public static function modify(array $where = [], array $params = [])
    {
        $params = self::filterFields($params);
 
        return self::whereMap($where)->update($params);
    }
}

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