Changing databases in a jdbc sql server connection

Question

How do i change the default database a connection accesses? For example, im trying to get a list of every table in the sql server im connected to:

List list = new ArrayList();
for (String db: dbList) {
    conn.prepareStatement("use ["+db+"]").executeQuery();//THIS LINE ISNT WORKING D:
    DatabaseMetaData md = conn.getMetaData();
    rs = md.getTables(null, null, "%", null);
    while (rs.next())
        list.add(rs.getString(3));
}
return list;

The line that does not work throws this error:

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:394)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283)

Creating a new connection for each db is not an option.

Answer 1

You can't use executeQuery() for this, you need to use execute() (and the error message gives you a good hint at that). Using a PreparedStatement also doesn't make sense here.

You should also store the instance of the created Statement in order to be able to close it properly:

Statement stmt = conn.createStatement();
stmt.execute("use [" + db + "]");
stmt.close();

Another (portable) option is using Connection.setCatalog() instead - but I'm not 100% sure if the Microsoft supports that