I have a Java program connection to a MySQL database, how can I change the current database to a different one on the same connection?
I connect to MySQL like this:
Statement stat= con.createStatement();
ResultSet r=stat.executeQuery("use mysql");
As described in the MySQL documentation you need to use
Connection.setCatalog() to switch to another database. It also explicitly says that you should not execute a
USE <databasename> to switch.
The reason for this warning is that JDBC is a generic interface to databases and therefor provides methods for most common tasks, including switching
databases as they are in MySQL). The JDBC specification/javadoc also explicitly says that people should use the API over database specific commands (if both are available). There are several reasons for this: 1) it promotes database-independent code, and 2) the driver might do additional things internally in response to one of the API methods. Using database specific commands might cause the driver to misbehave because its internal state does not match the database state.
A call to
setCatalog(String) will not affect existing statements, as specified in the JDBC API documentation:
setCataloghas no effect on previously created or prepared
Statementobjects. It is implementation defined whether a DBMS prepare operation takes place immediately when the
prepareCallis invoked. For maximum portability,
setCatalogshould be called before a
Statementis created or prepared.