ZF3中多数据库连接实现方案咨询（原ZF1迁移场景）

Hey there! I totally get where you're coming from—ZF3's database layer is a pretty big shift from ZF1's Zend_Db_Table approach, but once you wrap your head around the new components, it's just as flexible (if not more). Let's break down how to set up multiple database connections, build models, run cross-database queries, and fetch results step by step.

1. Configure Multiple Database Adapters #

First, you'll define your database connections in PHP config files (usually config/autoload/global.php for shared settings, or local.php for environment-specific values). Unlike ZF1's ini files, ZF3 uses array-based configs, but the core connection details are familiar:

// config/autoload/global.php
return [
    'db' => [
        'adapters' => [
            // MySQL connection
            'MySqlAdapter' => [
                'driver' => 'Pdo_Mysql',
                'hostname' => 'localhost',
                'database' => 'mysql_db',
                'username' => 'mysql_user',
                'password' => 'mysql_pass',
                'driver_options' => [
                    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
                ],
            ],
            // PostgreSQL connection
            'PgSqlAdapter' => [
                'driver' => 'Pdo_Pgsql',
                'hostname' => 'pg_host',
                'database' => 'pgsql_db',
                'username' => 'pgsql_user',
                'password' => 'pgsql_pass',
            ],
            // MS SQL connection
            'MsSqlAdapter' => [
                'driver' => 'Pdo_Sqlsrv',
                'hostname' => 'mssql_host',
                'database' => 'mssql_db',
                'username' => 'mssql_user',
                'password' => 'mssql_pass',
            ],
        ],
    ],
];

Next, register these adapters as services in your Module.php so ZF3's ServiceManager can inject them where needed:

// Module.php
use Zend\Db\Adapter\AdapterInterface;
use Zend\Db\Adapter\AdapterServiceFactory;

public function getServiceConfig()
{
    return [
        'factories' => [
            'MySqlAdapter' => AdapterServiceFactory::class,
            'PgSqlAdapter' => AdapterServiceFactory::class,
            'MsSqlAdapter' => AdapterServiceFactory::class,
        ],
    ];
}

2. Build Models (Replacements for Zend_Db_Table) #

In ZF3, Zend_Db_Table is split into smaller, focused components—TableGateway is the main workhorse for table operations. Here's how to create a model tied to your MySQL adapter:

// module/Application/src/Model/UserTable.php
namespace Application\Model;

use Zend\Db\TableGateway\TableGatewayInterface;

class UserTable
{
    protected $tableGateway;

    // Inject the MySQL adapter via constructor (dependency injection is key here!)
    public function __construct(TableGatewayInterface $tableGateway)
    {
        $this->tableGateway = $tableGateway;
    }

    // Fetch all users (similar to ZF1's fetchAll())
    public function fetchAll()
    {
        return $this->tableGateway->select();
    }

    // Fetch a single user by ID
    public function getUser($id)
    {
        $rowset = $this->tableGateway->select(['id' => $id]);
        return $rowset->current() ?: null;
    }

    // Add/update a user
    public function saveUser(User $user)
    {
        $data = [
            'name' => $user->name,
            'email' => $user->email,
        ];

        $id = (int) $user->id;
        if ($id === 0) {
            $this->tableGateway->insert($data);
            return $this->tableGateway->getLastInsertValue();
        } elseif ($this->getUser($id)) {
            $this->tableGateway->update($data, ['id' => $id]);
            return $id;
        }

        throw new \RuntimeException('User not found');
    }
}

Then register this model in Module.php so it gets the correct TableGateway (linked to your MySQL adapter):

// Module.php
use Application\Model\UserTable;
use Zend\Db\TableGateway\TableGateway;

public function getServiceConfig()
{
    return [
        'factories' => [
            // ... existing adapter factories ...

            UserTable::class => function ($sm) {
                $tableGateway = $sm->get('UserTableGateway');
                return new UserTable($tableGateway);
            },
            'UserTableGateway' => function ($sm) {
                $dbAdapter = $sm->get('MySqlAdapter');
                return new TableGateway('users', $dbAdapter);
            },
        ],
    ];
}

Repeat this pattern for models tied to PostgreSQL or MS SQL—just swap out the adapter reference in the TableGateway factory.

3. Cross-Database Queries #

This depends on whether you're querying same-type databases (e.g., two MySQL instances) or different types (MySQL + PostgreSQL + MS SQL).

Case 1: Same-Type Databases (Cross-DB, Same Driver) #

If your databases are the same type and your user has cross-database permissions, you can directly reference tables from other databases in your SQL:

// In UserTable or a dedicated query service
public function fetchUsersWithOrders()
{
    $sql = new \Zend\Db\Sql\Sql($this->tableGateway->getAdapter());
    $select = $sql->select()
        ->from('users')
        ->join(
            'order_db.orders', // Reference the other database's table
            'users.id = orders.user_id',
            ['order_id', 'order_date']
        );

    $statement = $sql->prepareStatementForSqlObject($select);
    $result = $statement->execute();

    return $result;
}

Case 2: Different-Type Databases (MySQL + PostgreSQL + MS SQL) #

Since different database systems can't natively join tables across each other, you'll need to:

1. Query each database separately to get the data you need
2. Merge/process the results in PHP

Here's an example:

// In a controller or service class
public function getUsersAndTheirOrders()
{
    // Get models for each database
    $userTable = $this->getServiceLocator()->get(UserTable::class);
    $orderTable = $this->getServiceLocator()->get(OrderTable::class); // Tied to PostgreSQL

    // Fetch data from each DB
    $users = $userTable->fetchAll()->toArray();
    $orders = $orderTable->fetchAll()->toArray();

    // Merge results (example: map orders to their users)
    $userOrders = [];
    foreach ($users as $user) {
        $user['orders'] = array_filter($orders, function($order) use ($user) {
            return $order['user_id'] == $user['id'];
        });
        $userOrders[] = $user;
    }

    return $userOrders;
}

4. Fetching & Handling Results #

ZF3's ResultSet objects are iterable (so you can loop through them directly) and have handy methods like toArray() to convert results to a PHP array. You can also use Hydrators to map results directly to entity classes (like the User class in the earlier example):

// Example of iterating over a result set
$users = $userTable->fetchAll();
foreach ($users as $user) {
    echo $user->name . ' | ' . $user->email;
}

// Convert to array
$userArray = $users->toArray();

// Hydrate to an entity (if you set up a HydratingResultSet)
// In your TableGateway factory:
use Zend\Db\ResultSet\HydratingResultSet;
use Zend\Hydrator\ClassMethodsHydrator;

'UserTableGateway' => function ($sm) {
    $dbAdapter = $sm->get('MySqlAdapter');
    $resultSetPrototype = new HydratingResultSet(
        new ClassMethodsHydrator(),
        new \Application\Model\User()
    );
    return new TableGateway('users', $dbAdapter, null, $resultSetPrototype);
},

内容的提问来源于stack exchange，提问作者Linh La