Re: How To connect to server root with jdbc driver
Posted by: Filipe Silva
Date: February 18, 2022 06:13AM

Well, there are several misconceptions here. Let me start from the beginning:

Quote

using the com.mysql.jdbc.Driver from mysql-connector-java-8.0.27.jar , how do you connect to a server's "ROOT" so that you can browse and/or select different schemas ?

You authenticate with an account that has the minimum required privileges to read from each one of the databases/schemas. https://dev.mysql.com/doc/refman/8.0/en/access-control.html.

Quote

It should work something similar to workbench when you make a connection and see all the different databases in the left pane and can use;/select; on that connection and other DB's that connect you to the root if no database is selected

You can issue the command "SHOW DATABASES" or "SHOW SCHEMAS" (https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_schema) to list existing databases. The SHOW statement can be used to get the list of tables, columns, procedures, and so on (https://dev.mysql.com/doc/refman/8.0/en/show.html). You don't need to be "in" any database/schema to issue SHOW statements. In what SHOW statement can't provide, you may query the INFORMATION_SCHEMA tables for that (https://dev.mysql.com/doc/refman/8.0/en/extended-show.html) but, in this case, you have to either be "in" the INFORMATION_SCHEMA database or to use qualified identifiers in the table names.

Underneath the nice GUI, this is exactly what Workbench does. If you don't start a connection with a default schema, Workbench also returns an error telling you to select the database/schema first: "Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar." Double-clicking on a schema name is the equivalent to issuing the MySQL command "USE [schema_name]" (https://dev.mysql.com/doc/refman/8.0/en/use.html) in any interactive client. If you are using Java and Connector/J, then the correct way of doing the same would be by calling the method Connection.setCatalog(String).

Quote

the closest I have come is connecting to the information_schema but that has problems too trying to use a "Real" table from the information you can get from it.

If the goal is to get all sorts of metadata, then you can just issue SHOW statements for the most part. You don't required to be "in" any database for that. A connection is just a network link between a client application and a MySQL server, that keeps and maintains some session state. Any database object can be accessed from any connection, as long as the authenticated user has the required privileges. Accessing database object may just required qualified identifiers if the currently "selected" schema is not the same as the one where those objects were created.

From a Java application, then the option is to use the metadata object, which you get from calling Connection.getMetaData().

Quote

is there some hidden table to connect to because it seems short sighted to have to connect like this.

No. And you don't "connect to tables". Client applications connect to MySQL servers, which creates a server session, and may choose to "select" a default database/schema to work with on that session. There are multiple client programs that ship with a MySQL distribution, and also the newest MySQL Shell. All operate the same way.

Quote

I feel like I am missing the obvious here either with the driver or the server set up not to mention having to know the data structure in advance to do anything.

You don't have to know the data structure in advance. Suffice it to know the SHOW command and that all MySQL installations contain a few standard schemas you should never mess with: information_schema, mysql, performance_schema and sys.

Quote

so assuming a basic connection string is ...
jdbc:mysql://localhost:3306/WHAT-GOES-HERE? other then a table name to get the top level of the server if i do not put a table name in then any query's fail with cannot find schema.....even though there is a good active connection.

"WHAT-GOES-HERE?" - Any database/schema name or nothing, whatever better suites your needs, but never a table name. So, no, you don't put a table name there, unless you created one database per table with exactly the same name and, by putting the "table" name -- actually the database name -- you end up selecting the correct database for the table you want to query. This is not how to work with a database management system.

Quote

i.e. jdbc:mysql://localhost:3306/
then depending on software ...
use table1; select * from table1 ;
ERROR cannot find schema information null or similar message...

"use table1" -- is there a database/schema with the name "table1"? If so, then this will work fine. - the correct way of doing this in Java would be by calling the method Connection.setCatalog(String).

"select * from table1" -- is there a table or view with name "table1" inside the database/schema table1? If so, then this will work fine.

It seems to me you are mixing concepts here. Databases are not the same as tables. Databases aggregate multiple tables and other database objects, while tables live inside databases. One database typically contains multiple tables, one table belongs to a single database. https://dev.mysql.com/doc/refman/8.0/en/database-use.html

Quote

what i do now the head-ache it is:

jdbc:mysql://localhost:3306/information_schema
... query get table names
then
jdbc:mysql://localhost:3306/table1 i need data from
jdbc:mysql://localhost:3306/table2..3..4..etc i need data from

is just a pain when i know there should be a better way to do this with out making a ton of separate connections. Some of this canned software i use, I literally have to make a separate connection for each and every database on the server i need to use.

do i have to structure the queries in some way to actually select a certain schema after a generic connection ( no schema in connection string ) or am i missing something obvious in the connector string to get the "ROOT" and access to all the databases on the server that the user/me has rights too ....

In many cases, establishing different connections for different databases may make sense, especially if the application needs to work with data from those databases concurrently without the need to switch frequently. What doesn't make sense is to have every single table on a different database.

Assuming you actually need to have multiple databases and to work with them from the same connection, then just don't specify the database in the connection string and start qualifying table name references, like in "SELECT * FROM mydb.mytable", or else, switch to the corresponding database before starting to use it - USE statement vs "Connection.setCatalog(String)".


Quote

like i said it works in workbench and and mysql client so shouldn't it should work in the jdbc driver as well ? what magic am i missing ?

Workbench, mysql client, Connector/J, Connector/NET and any other client or driver work exactly the same. You just need to learn the commands and the APIs for each language. For Java, your reference should be the JDBC documentation and the MySQL Connector/J Developer Guide.

IHTH

Options: ReplyQuote


Subject
Written By
Posted
Re: How To connect to server root with jdbc driver
February 18, 2022 06:13AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.