Dynamically (Runtime) change Datasource credentials in Spring Mybatis

Question

I want to dynamically change Datasource properties in Spring+MyBatis project.

Problem is in Spring + MyBatis integration, we cannot set the datasource properties dynamically during runtime.

Currently I'm using the following code to set the credentials:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" p:driverClassName="${jdbc.driverClassName}"
    p:url="${jdbc.url}" p:username="${jdbc.username}"
    p:password="${jdbc.password}" />

I tried options with UserCredentialsDataSourceAdapter to change the password during runtime but I cannot return back the dataSource object to use for the connection as MyBatis

ApplicationContext context = ApplicationContextUtils.getApplicationContext();
UserCredentialsDataSourceAdapter ds = (UserCredentialsDataSourceAdapter) context.getBean("dataSource");
ds.setCredentialsForCurrentThread("test", "test");

I'm stuck here, I cannot use the dataSource element ds to use for making connection for MyBatis. Please help me in resolving this issue.

Yoy might have a look at this http://stackoverflow.com/questions/13507522/dynamically-change-spring-data-source — Karthik Prasad, Feb 08 '14 at 14:58
No. This will not workout for me AbstractRoutingDataSource is for dynamically routing the data source where data source parameters are already defined but in my case, I want to dynamically determine the userid and password during Runtime based on logged-in user. — user3126519, Feb 08 '14 at 17:15

score 4 · Answer 1 · edited May 23 '17 at 11:45

4

I suppose that you use mybatis-spring.

Your approach with UserCredentialsDataSourceAdapter is not working because you are using connection pool, so connection is not closed after usage but are returned to the pool and reused later even that you've changed username and password. To fix this just get rid of pool:

<bean id="targetDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="${jdbc.driverClassName}"/>
  <property name="url" value="${jdbc.url}"/>
</bean>

<bean id="dataSource" class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
  <property name="targetDataSource" ref="targetDataSource"/>
  <property name="username" value="${jdbc.username}"/>
  <property name="password" value="${jdbc.password}"/>
</bean>

and use the later bean in SqlSessionFactoryBean configuration:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
</bean>

If you do not use mybatis-spring and use mybatis directly then the problem is to make mybatis use configured DataSource. This can be done by registering dataSource in JNDI and configure mybatis to get DataSource from JNDI.

edited May 23 '17 at 11:45

Community

1
1

answered Feb 11 '14 at 12:14

Roman-Stop RU aggression in UA

14,905
3
48
53

Even without connection pool when I use UserCredentialsDataSourceAdapter, I cannot pass that to mybatis. for instance in my code I call DAO from the service layer as follows private AuthDao authDao; rowsAffected = authDao.updateUserDetails(authBean); where in AuthDao is an interface which will call the query/SP – user3126519 Feb 13 '14 at 22:03
I believe you can even if you don't use mybatis-spring. I've updated my answer. – Roman-Stop RU aggression in UA Feb 14 '14 at 10:46
I'm using myBatis-Spring. I tired but it is not working. It was taking the credentials with which it initialized at server start time. – user3126519 Feb 16 '14 at 13:44
Please, provide full configuration of spring beans related to mybatis and db, namely datasources, sqlsessionfactory. – Roman-Stop RU aggression in UA Feb 17 '14 at 12:51
any solution for this issue? – user3126519 Feb 20 '14 at 21:04

score 0 · Answer 2 · answered Feb 17 '14 at 15:18

<bean id="targetDataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${app.jdbc.driverClassName}" />
    <property name="url" value="${app.jdbc.url}" />
</bean>

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
    <property name="targetDataSource" ref="targetDataSource" />
    <property name="username" value="#{app.jdbc.username}" />
    <property name="password" value="#{app.jdbc.password}" />
</bean>

<!-- Declare a transaction manager for Encounter Navigator Authenticator -->
<!-- Enable annotation style of managing transactions -->
<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>

<!-- define the SqlSessionFactory for Encounter Navigator Authenticator, 
    notice that configLocation is not needed when you use MapperFactoryBean -->
<bean id="SqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"
    name="sqlSessionFactory">
    <property name="dataSource" ref="dataSource" />
    <property name="mapperLocations"
        value="file:C:/Program Files/Apache Software Foundation/Tomcat 7.0/config/app_user/*.xml" />
    <property name="configLocation" value="classpath:sqlmap-config.xml" />
</bean>

<!-- scan for MAPPERS and let them be auto-wired - For Encounter Navigator 
    Authenticator -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage"
        value="com.upmc.health.encounternavigator.dao.authentication" />
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>

score 0 · Answer 3 · answered Feb 17 '14 at 15:23

UserCredentialsDataSourceAdapter ds = (UserCredentialsDataSourceAdapter) applicationContext.getBean("dataSource");
ds.removeCredentialsFromCurrentThread();
ds.setCredentialsForCurrentThread("test", "test");
ds.setUsername("test");
ds.setPassword("test");
ds.setTargetDataSource(ds);
ds.afterPropertiesSet();

authDao.getDetails(); //This calls an interface and executes the query present in the xml file

Dynamically (Runtime) change Datasource credentials in Spring Mybatis

3 Answers3