I don't have code yet, because this is much more a concept-based question than an actual implementation question, so I bid your license.
I'm working on a Spring based web application and it has its own database for accesses, configurations, the works. But this application must connect to other databases and perform SQL select queries. While the application's (let's call it host) own database is MySQL, it should be able to connect to both MySQL and MS_SQL databases (let's call them guests).
The host should be able to instantiate a connection to a guest, retrieve a SQL result set and close the connection.
The question here is how should this be done? Using c3p0? Some connection factory? I have never faced an issue like this before and would like some help.
There is no special relationship between an application and "its" database. For as many databases as you want to access, you have the same basic options.
There is the old-fashioned way, using the static methods of DriverManager, and there is the newer way, of instantiating a DataSource from which Connections may be acquired. An advantage of using a DataSource is that it may pool Connections for you, improving performance for you (at the cost of some extra thread and memory overhead).
It's very likely that your application accesses your "main" database frequently. Unless your DBMS or application server provides its own implementation of a DataSource with transparent Connection pooling, you almost certainly will want to access this database via a library that provides this for you, such as c3p0 or HikariCP.
For the other databases, if you access them infrequently and if your use of them is not particularly performance sensitive, you might consider just using DriverManager.getConnection(...) or a simple, non-pooling implementation of a DataSource like c3p0's DriverManagerDataSource. If you are going to use the other database's frequently, than you will want to access them through a pool-backed DataSource.
(You'll probably want to choose one pooling library and stick with it for accessing all your databases. Nothing prevents you from pooling access to one DBMS with dbcp, another with HikariCP, another with c3p0 etc, but you'll be adding useless complexity.)
The main thing you'll have to think about is the tradeoff between speed and memory/thread overhead. For rarely accessed databases, it's usually better not to bother with a Connection pool. For frequently accessed databases, if speed matters at all, you'll almost certainly want to use a pooling library.
Regardless, you can always access as many databases as you want from your app, either via DriverManager or via a DataSource. JDBC is an elegant, uniform interface to DBMSs. What (little) complexity you face is the decision of which implementations of DataSource to use.