怎么调用两个数据库的数据库

怎么调用两个数据库的数据库

  • admin admin
  • 2025-07-14
  • 4517
  • 0

调用两个数据库的数据，可以使用多数据库连接技术，在Python中，使用SQLAlchemy或直接通过数据库驱动分别连接两个数据库，然后执行查询并整合...

优惠价格：¥ 0.00
购买链接 租用托管
当前位置：首页 > 数据库 > 怎么调用两个数据库的数据库
详情介绍
调用两个数据库的数据，可以使用多数据库连接技术，在Python中，使用SQLAlchemy或直接通过数据库驱动分别连接两个数据库，然后执行查询并整合

现代软件开发中，经常会遇到需要同时调用两个或多个数据库的情况，这可能是因为业务需求涉及到多个数据源的整合、数据迁移、或者是为了实现更复杂的数据处理逻辑，下面将详细介绍如何调用两个数据库,包括不同的技术栈和实现方式。

使用PHP连接两个数据库

使用mysqli扩展

<?php
// 连接第一个数据库
$mysqli1 = new mysqli("localhost", "user1", "password1", "db1");
if ($mysqli1->connect_error) {
    die("连接第一个数据库失败: " . $mysqli1->connect_error);
}
// 连接第二个数据库
$mysqli2 = new mysqli("localhost", "user2", "password2", "db2");
if ($mysqli2->connect_error) {
    die("连接第二个数据库失败: " . $mysqli2->connect_error);
}
// 查询第一个数据库的数据
$query1 = "SELECT  FROM table1";
$result1 = $mysqli1->query($query1);
// 查询第二个数据库的数据
$query2 = "SELECT  FROM table2";
$result2 = $mysqli2->query($query2);
// 处理结果
while ($row1 = $result1->fetch_assoc()) {
    echo "数据1: " . $row1["column1"] . "n";
}
while ($row2 = $result2->fetch_assoc()) {
    echo "数据2: " . $row2["column1"] . "n";
}
// 关闭数据库连接
$mysqli1->close();
$mysqli2->close();
?>

使用PDO扩展

<?php
try {
    // 连接第一个数据库
    $dbh1 = new PDO('mysql:host=localhost;dbname=db1', 'user1', 'password1');
    $dbh1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // 连接第二个数据库
    $dbh2 = new PDO('mysql:host=localhost;dbname=db2', 'user2', 'password2');
    $dbh2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // 查询第一个数据库的数据
    $stmt1 = $dbh1->prepare("SELECT  FROM table1");
    $stmt1->execute();
    $result1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
    // 查询第二个数据库的数据
    $stmt2 = $dbh2->prepare("SELECT  FROM table2");
    $stmt2->execute();
    $result2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
    // 处理结果
    foreach ($result1 as $row1) {
        echo "数据1: " . $row1["column1"] . "n";
    }
    foreach ($result2 as $row2) {
        echo "数据2: " . $row2["column1"] . "n";
    }
} catch (PDOException $e) {
    echo '连接失败: ' . $e->getMessage();
}
// 关闭数据库连接
$dbh1 = null;
$dbh2 = null;
?>

使用Python连接两个数据库

使用ODBC连接

import pyodbc
# 连接第一个数据库
conn1 = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=db1_name;UID=user;PWD=password')
cursor1 = conn1.cursor()
# 连接第二个数据库
conn2 = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=db2_name;UID=user;PWD=password')
cursor2 = conn2.cursor()
# 查询第一个数据库的数据
cursor1.execute("SELECT  FROM table1")
rows1 = cursor1.fetchall()
for row in rows1:
    print("数据1: ", row)
# 查询第二个数据库的数据
cursor2.execute("SELECT  FROM table2")
rows2 = cursor2.fetchall()
for row in rows2:
    print("数据2: ", row)
# 关闭数据库连接
conn1.close()
conn2.close()

使用JDBC连接（Java）

import java.sql.;
public class ConnectDatabase {
    public static void main(String[] args) {
        try {
            // 连接第一个数据库
            Connection conn1 = DriverManager.getConnection("jdbc:mysql://server_name/db1_name", "user", "password");
            Statement stmt1 = conn1.createStatement();
            ResultSet rs1 = stmt1.executeQuery("SELECT  FROM table1");
            while (rs1.next()) {
                System.out.println("数据1: " + rs1.getString("column1"));
            }
            rs1.close();
            stmt1.close();
            conn1.close();
            // 连接第二个数据库
            Connection conn2 = DriverManager.getConnection("jdbc:mysql://server_name/db2_name", "user", "password");
            Statement stmt2 = conn2.createStatement();
            ResultSet rs2 = stmt2.executeQuery("SELECT  FROM table2");
            while (rs2.next()) {
                System.out.println("数据2: " + rs2.getString("column1"));
            }
            rs2.close();
            stmt2.close();
            conn2.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

使用Spring Boot配置两个数据源

在Spring Boot项目中，可以通过配置文件和注解来配置多个数据源,以下是一个示例：

修改application.yml文件

server:
  port: 7101
spring:
  jpa:
    show-sql: true
  datasource:
    test1:
      driver-class-name: org.postgresql.Driver
      jdbc-url: jdbc:postgresql://127.0.0.1:5432/test # 测试数据库1
      username: root
      password: root
    test2:
      driver-class-name: oracle.jdbc.driver.OracleDriver
      jdbc-url: jdbc:oracle:thin:@127.0.0.1:8888:orcl # 测试数据库2
      username: root
      password: root

创建配置类DataSource1Config.java和DataSource2Config.java

package com.test.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.test.dao.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSource1Config {
    @Bean(name = "test1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    @Primary
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "test1SqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:test1/.xml"));
        return bean.getObject();
    }
    @Bean(name = "test1TransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    @Bean(name = "test1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}
package com.test.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.test.dao.test2", sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DataSource2Config {
    @Bean(name = "test2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:test2/.xml"));
        return bean.getObject();
    }
    @Bean(name = "test2TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    @Bean(name = "test2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

使用MyBatis同时连接两个数据库

创建两个数据源和SqlSessionFactory

SqlSessionFactory firstSessionFactory = createFirstSessionFactory();
SqlSessionFactory secondSessionFactory = createSecondSessionFactory();

使用多个SqlSession操作多个数据库

SqlSession firstSqlSession = firstSessionFactory.openSession();
SqlSession secondSqlSession = secondSessionFactory.openSession();
FirstMapper firstMapper = firstSqlSession.getMapper(
数据库调用 多数据库连接 数据库集成
0

相关文章