Manage database connection using connection pool or session. How?

Question

I have a simple task to accomplish, but I am not sure what is the best way to go by.

Each user has their own username and password to connect to a database with different privilege. Once the user connect, he will do multiple query base on what action he want to perform. Therefore I want to retain the connection with the database. So here is my question?

Is it better to achieve what I want to do with connection pooling or session or both? Sample code would be appreciated!!! thank you very much.

Answer 1

Define a container managed connection pooled DataSource with "root" privileges in the server and for each user make use of the DataSource#getConnection() method which takes an username and password as arguments.

Answer 2

I faced the same issue with an Oracle DB, where every transaction has to be record for possible audits. So, every time a user authenticates in the web application, it puts an object like this:

public class ConnectionUser implements Serializable {

private String userId;
private String password;
private String transactionKey;
//setters and getters

public boolean equals(Object object) {
    if(!(object instanceof org.ampf.af.jdbc.connection.ConnectionUser)) {
        return false;
    }
    if(object == null) {
        return false;
    }
    return (this.getUserId().equals(((ConnectionUser)object).getUserId()) && 
            this.getPassword().equals(((ConnectionUser)object).getPassword()));
}

Then I implemented a connection pool (based on a Map) where the key is the ConnectionUser object and the value is the open connection. If the key doesn't exist in the map, then it creates a connection and allocates it into the map.

I hope it helps you.