Ad

Connecting To "foreign" Database On The Same DBMS

- 1 answer

I am somewhat surprised to see that JDBC allows me to transparently SELECT from a table in a "foreign" database, on the same DBMS, for which I have the requisite privileges, without requiring an explicit connection to the foreign database. Is this how it is supposed to be with MySQL, or is it just a JDBC quirk?

Details:
I created two databases on my DBMS: stkovrflo_1 and stkovrflo_2. I populated tables in these databases from the MySQL World database.

CREATE TABLE stkovrflo_1.Country
SELECT name, region FROM world.Country;

CREATE TABLE stkovrflo_2.City
SELECT world.City.name, world.Country.name AS country
FROM world.City INNER JOIN world.Country ON world.City.CountryCode = world.Country.code;

In JDBC I am able to select entries to the stkovrflo_2.City table via a connection to the stkovrflo_1 database. I have SELECT access to both databases.

Here is my JDBC code:

import java.sql.*;

public class JDBCExample {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost/stkovrflo_1";

    public static void main(String[] args) throws Exception{
        
        String uid = args[0];
        String pswd = args[1];
        
        if(pswd.toUpperCase().equals("NULL"))
            pswd = null;

        Connection conn = null;
        conn = DriverManager.getConnection(DB_URL,uid,pswd);
        
        processTableSameDB(conn);
        System.out.println("\n\n");
        processTableDifferentDB(conn);
        
        if(conn != null)
            conn.close();
    }

    protected static void processTableSameDB(Connection conn) throws Exception {
        Statement stmt = null;
        String tableName = "Country";

        try{
            Class.forName("com.mysql.jdbc.Driver");

            System.out.println("Retrieving from table in same database...");

            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM " + tableName + " LIMIT 10";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next()){
                String name = rs.getString("name");
                String region = rs.getString("region");

                System.out.print("Name: " + name);
                System.out.println(", Region: " + region);
            }
            rs.close();
        }
        catch(SQLException se){
            se.printStackTrace();
        }
        finally{
            if(stmt!=null)
                stmt.close();
        }
    }

    protected static void processTableDifferentDB(Connection conn) throws Exception {
        Statement stmt = null;
        String tableName = "stkovrflo_2.City";

        try{
            Class.forName("com.mysql.jdbc.Driver");

            System.out.println("Retrieving from table in different database...");

            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM " + tableName + " LIMIT 10";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next()){
                String name = rs.getString("name");
                String country = rs.getString("Country");

                System.out.print("Name: " + name);
                System.out.println(", Country: " + country);
            }
            rs.close();
        }
        catch(SQLException se){
            se.printStackTrace();
        }
        finally{
            if(stmt!=null)
                stmt.close();
        }
    }
}  

JDBC output is as follows:

Retrieving from table in same database...
Name: Aruba, Region: Caribbean
Name: Afghanistan, Region: Southern and Central Asia
Name: Angola, Region: Central Africa
Name: Anguilla, Region: Caribbean
Name: Albania, Region: Southern Europe
Name: Andorra, Region: Southern Europe
Name: Netherlands Antilles, Region: Caribbean
Name: United Arab Emirates, Region: Middle East
Name: Argentina, Region: South America
Name: Armenia, Region: Middle East



Retrieving from table in different database...
Name: Oranjestad, Country: Aruba
Name: Kabul, Country: Afghanistan
Name: Qandahar, Country: Afghanistan
Name: Herat, Country: Afghanistan
Name: Mazar-e-Sharif, Country: Afghanistan
Name: Luanda, Country: Angola
Name: Huambo, Country: Angola
Name: Lobito, Country: Angola
Name: Benguela, Country: Angola
Name: Namibe, Country: Angola
Ad

Answer

Read https://dev.mysql.com/doc/refman/8.0/en/identifier-qualifiers.html

This is normal. As long as the database is on the same MySQL instance and you have privileges on the database, it doesn't matter which is your "default" database. You can reference any databasename.tablename.

The default database is analogous to the current working directory (cwd) in the shell. You can always reference a file in another directory by giving a path to it.

Ad
source: stackoverflow.com
Ad