I have some code that connects to a database using JDBC using a basic URL:
String url = "jdbc:mysql://" + getHostname() + ":" + getPort();
I am using the following arguments:
setProperty("connectTimeout", "2000");
setProperty("autoReconnect", "true");
I don’t include the database name, since it might not exist yet. I check whether one exist upon connection, and create it as needed. Then I select the database.
I use two queries:
CREATE DATABASE IF NOT EXISTS <name>
then
USE <name>
So the database is selected here upon connection.
The issue I’m having is that I saw an error in the log:
...
Caused by: java.sql.SQLException: No database selected
This doesn’t make sense, because the database is always selected upon connection, otherwise an exception would have been thrown. However, this exception about a missing selection occurs hours later.
My guess is that, upon reconnecting, it uses the URL, which doesn’t include the database name. So it reconnects, but now the database isn’t selected.
Can someone confirm whether this is the cause of the behavior I am experiencing?
3
Answers
String url = "jdbc:mysql://" + getHostname() + ":" + getPort();You should include your database name. Your URL followed by a "/DBNAME".
USE databaseis for subsequent statements. The named database remains the default until the end of the session. Your session expires.If you create the database on the fly, you can connect again using the complete URL with the database name at the end, not with
USE databaseYup, hence, don’t connect without a database name.
Try to connect with the name. In the catch block, check if this is the problem (SQLException has a
.getState()method that returns a specific error code, check it against mysql’s table, or just run the code passing a DB URL with a non-existent dbname and just print it out, now you know) – and if so, run a method that will create a new connection (without a dbname), creates the database, closes that connection, and returns. Then you retry with the database name. Remember, exceptions can be caught.There is an option
createDatabaseIfNotExistexactly to do that.Connector will then connect to server without database, create database is not existing and connect to that database.
database must then be explicitly set in connection string, like
"jdbc:mysql://myHost:3306/myDB?createDatabaseIfNotExist=true"
The advantage is that if connection fails, autoreconnect will connect to the database directly.