JDBC/Connectorj: Understanding connection pooling

I think I need to understand the 开发者_JAVA技巧concept of connection pool a bit better. I'm working in java with ConnectorJ and I deploy my servlet on a Apache Tomcat server. I've been following the doc so my Tomcat context.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="">
  <Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
        maxActive="-1" maxIdle="30"
        maxWait="10000" minEvictableIdleTimeMillis="1200000" name="jdbc/MySQLDB"
        removeAbandoned="true" removeAbandonedTimeout="1200"  timeBetweenEvictionRunsMillis="60000"
        type="javax.sql.DataSource" url="jdbc:mysql://my_host"
        username="my_username" password="my_password"
        factory="org.apache.commons.dbcp.BasicDataSourceFactory" /> 
</Context>

And I get a connection from a datasource using the recommanded way:

InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/MySQLDB");
Connection conn = null;

try {
        conn = ds.getConnection();

        // Do query etc.
        // Close connection, statement and result set if applicable
}
catch (SQLException){
    // Handle exception here
}

My question is: why do I have to specify a user and password for my datasource in context.xml. Correct me if I am wrong, but I thought the point of a connection pool was to reuse the connections that possesses the same connection string ?

I want to be able deal with multiple different login (let's say the servlet receive the DB credentials to use via HTTP), but if I have to define a different datasource for each of the possible connection, doesn't it go against the point of connection pooling ?

When you open a connection to the database directly, by using DriverManager.getConnection, you supply the username and password to log on to the database in that call.

When you use a connection pool, you are not opening the connection yourself directly; instead, the pool opens and manages the connections for you. Ofcourse, the pool needs to know the username and password to be able to log on to the database in that case.

Normally, in a Java web application, you would not use different database login credentials for every user of your application. You'd just have one username and password that the application uses, for anybody who uses the web application. If different users of the web application have different rights, you'd set that up by having a login system for the application itself, but the usernames and passwords that you use for the application are not the same as what you'd use to log on to the database.

Connections to the database are usually controlled by different username and password than what your users will use for authentication on the website. Typically it's only one user (often being a schema name). Connection pool will create required number of connections to the database with this user name and it will be your application responsibility to take incoming credentials from the web user and verify them against stored in the database.

Several things:

1. DataSource API does provide a getConnection(username, password) method. For whatever reason, Apache DBCP that Tomcat uses for itself doesn't implement that method. You might want to look into alternative implementations if DBCP doesn't meet your need.
2. As others stated, changing database credentials per request is a pretty obscure usecase. Maybe you should reconsider your design, so that your user authentication is decoupled from your database access.
3. More generally, pooling is a technique for reusing a number of things that are sufficiently alike that they can be reused multiple times. Clearly, DBCP has decided that "sufficiently alike" doesn't include being reusable across different database credentials. I don't think it's right or wrong on principle.