4

I'm trying to access data in multiple databases on a single running instance. the table structures of these databases are all the same; As far as I know, create a new connnection using jdbc is very expensive. But the connection string of jdbc require format like this jdbc:mysql://hostname/ databaseName, which needs to specify a specific database. So I'm wondering is there any way to query data in multiple databases using one connection?

Multiple databases on a single running instance

zonyang
  • 828
  • 3
  • 10
  • 24

1 Answers1

1

The MySQL documentation is badly written on this topic.

The SELECT Syntax page refers to the JOIN Syntax page for how a table name can be written, even if you don't use JOIN clauses. The JOIN Syntax page simply says tbl_name, without further defining what that is. There is even a comment at the bottom calling this out:

This page needs to make it explicit that a table reference can be of the form schema_name.tbl_name, and that joins between databases are therefore posible.

The Schema Object Names page says nothing about qualifying names, but does have a sub-page called Identifier Qualifiers, which says that a table column can be referred to using the syntax db_name.tbl_name.col_name. The page says nothing about the ability to refer to tables using db_name.tbl_name.

But, if you can refer to a column using db_name.tbl_name.col_name, it would only make sense if you can also refer to a table using db_name.tbl_name, which means that you can access all your databases using a single Connection, if you're ok with having to qualify the table names in the SQL statements.

As mentioned by @MarkRotteveel in a comment, you can also switch database using the Connection.setCatalog(String catalog) method.

This is documented in the MySQL Connector/J 5.1 Developer Guide:

Initial Database for Connection

If the database is not specified, the connection is made with no default database. In this case, either call the setCatalog() method on the Connection instance, or fully specify table names using the database name (that is, SELECT dbname.tablename.colname FROM dbname.tablename...) in your SQL. Opening a connection without specifying the database to use is generally only useful when building tools that work with multiple databases, such as GUI database managers.

Note: Always use the Connection.setCatalog() method to specify the desired database in JDBC applications, rather than the USE database statement.

Community
  • 1
  • 1
Andreas
  • 154,647
  • 11
  • 152
  • 247
  • You can also switch between databases by using `Connection.setCatalog(..)`; it might make life easier in some situations. – Mark Rotteveel Jun 18 '16 at 06:41
  • @MarkRotteveel I haven't tried with MySQL, but are you sure it's [`setCatalog()`](https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setCatalog(java.lang.String)) and not [`setSchema()`](https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setSchema(java.lang.String))? – Andreas Jun 18 '16 at 06:43
  • See the implementation: [`ConnectionImpl.setCatalog`](https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/ConnectionImpl.java#L4869) and [`ConnectionImpl.setSchema`](https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/ConnectionImpl.java#L5439). See also the comment on `setSchema` that says _"until we flip catalog/schema, this is a no-op"_. Databases in MySQL seems to be a mix of catalog (stored separately) and schema (handling in queries). – Mark Rotteveel Jun 18 '16 at 06:53
  • @MarkRotteveel Thanks. Added `setCatalog` to answer. – Andreas Jun 18 '16 at 06:55
  • Funny, I was looking for that quote you added, but I couldn't find it :). – Mark Rotteveel Jun 18 '16 at 06:56