How to create and maintain one jdbc connection per user in web app (Spring/Vaadin)?

Question

How to create an application which can handle thousand of jdbc connection at runtime without implementing connection pool ? AFAIK to establish connection pool, we need username, passowrd and required dbinstance url but here all of them will be provided at runtime to connect particular database, and there would be more than 1000 user at one time to connect to set of databases.(memory intensive !)
So typically it going to be like this:

Users: User-A,User-B,User-C.....User-n
db: DB1, DB2, DB3....DBn

Can anyone please guide me how can I achieve this task ?
I only have one thing in my mind, i.e. to create single connection with each session and use it whereever required specific to that user.

I've used Apache Commons DBCP2 for connection pooling, MyBatis-Spring implementation, Spring and Vaadin for different application but not sure if anyone of them gonna help me !

Answer 1

Finally, I had to settle down with following approach. Though I am not sure if its a good approach.

I created a SqlSessionFactory by providing DataSource with dynamic Username, Password and Database.

public SqlSessionFactory build() throws IOException, SQLException
{       
    OracleDataSource dataSource = new OracleDataSource();
    dataSource.setURL(this.dbUrl);
    dataSource.setUser(this.dbUsername);
    dataSource.setPassword(this.dbPassword);
    dataSource.setDriverType(properties.getProperty("db.driver"));
    TransactionFactory transactionFactory = new JdbcTransactionFactory();
    Environment environment = new Environment(properties.getProperty("db.environment"), transactionFactory, dataSource);
    Configuration configuration = new Configuration(environment);
    configuration.addMappers("com.app.dao");
    SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(configuration);
    // final test connection to db
    sessionFactory.openSession().getConnection();
    return sessionFactory;
}  

Then I am getting one SqlSession out of factory:

SqlSession session = sessionFactory.openSession();  

and I am setting it across Vaadin session :(, so that it would be available throughout session. Hence I can use it whenever I need by taking it from session.

UI.getCurrent().getSession().setAttribute(SqlSession.class,session);  

I am discarding it when logout:

UI.getCurrent().getSession().setAttribute(SqlSession.class, null);  

I feel its dirty and may create memory issue. but didn't find any other easy solution. Please feel free to comment or answer.

Answer 2

Here's another approach:

Oracle supports proxy authentication. It would work something like this:

1. setup limited rights user for your application (say webgui)
2. connect to database as webgui (w connection pooling)
3. authenticate the real user (say JoeSmith) by simply trying to connect as him (JoeSmith/password), perhaps w a second connection
4. in first connection change user to JoeSmith (not sure what oracle syntax is, in postgres it's SET ROLE)
5. reset user at end of database session

EclipseLink has a postAcquireClientSession method, not sure about MyBatis

You might have to wipe any caching in your ORM if it uses it