Multiple Databse Connectivity issue with Spring boot

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Multiple Databse Connectivity issue with Spring boot

Midhun Nair
I am trying to connect database using two data sources in Spring boot (1.5.10) with mybatis (1.3.1). Tried implementing in following manner :
  • Created two separate data sources with the help of Data source Builder. Entries regarding both the databases proved in the application.properties file.
  • In the configuration file created separate Entity manger and transaction manager for both the data sources and marked one of them as @Primary.
  • Created separate mapper files for both the data source.
The above steps didn't accomplish the goal, so tried creating SqlSessionFactory and MapperFactoryBean. This also didn't work. Always the primary Db is getting connected.

Is there any limitation with Mybatis to connect two data sources (Same URL but different user) ?




--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Multiple Databse Connectivity issue with Spring boot

Hodo Info
Hi Midhum Nair:
First you should set two separate data source
e.g.
app.datasource.jdbc-url:jdbc:mysql://192.168.0.21:3306/bizz?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&useSSL=true&tcpRcvBuf=1024000
app.datasource.username:xcoin_online
app.datasource.password:*****
app.datasource.poolName:MySQLPool
app.datasource.max-lifetime:60000
app.datasource.maximum-pool-size:30
app.datasource.connection-timeout:60000
app.datasource.connection-init-sql:SELECT VERSION();
app.ds2.driver-class-name:org.postgresql.Driver
app.ds2.jdbc-url:jdbc:postgresql://127.0.0.1:5432/xcoin
app.ds2.username:asin
app.ds2.password:asinray666888
app.ds2.pool-name:PGPool
app.ds2.max-lifetime:60000
app.ds2.maximum-pool-size:30
app.ds2.connection-timeout:60000
app.ds2.connection-init-sql:SELECT VERSION();
MySQLDataSourceConf.java 
@Configuration
@MapperScan(basePackages = "org.gl.mds.dao.mapper.mysql")
public class DataSourceConf {

@Bean
@Primary
@ConfigurationProperties("app.datasource")
public DataSource dataSource(){
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}

@Bean
@Primary
public JdbcTemplate JdbcTemplate() {
JdbcTemplate template = new JdbcTemplate();
template.setDataSource(dataSource());
return template;
}

@Bean
@Primary
public PlatformTransactionManager TransactionManager(DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}

@Bean
@Primary
public SqlSessionFactory mysqlSessionFactory(DataSource mysqlDataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(mysqlDataSource);
return bean.getObject();
}
}

PGDataSourceConf.java
@Configuration
@MapperScan(basePackages = "org.girllee.mds.dao.mapper.pg",sqlSessionFactoryRef = "pgSessionFactory")
public class PGDataSourceConf {

@Bean("pg")
@ConfigurationProperties("app.ds2")
public DataSource ds2(){
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}

@Bean(name = "pgJdbcTemplate")
public JdbcTemplate pgJdbcTemplate() {
JdbcTemplate template = new JdbcTemplate();
template.setDataSource(ds2());
return template;
}
@Bean("pgTxMgr")
public PlatformTransactionManager pgTransactionManager() {
return new DataSourceTransactionManager(ds2());
}
@Bean
public SqlSessionFactory pgSessionFactory() throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(ds2());
return bean.getObject();
}
When inject a mapper to service, remeber that the Transaction is use  @Primary datasouce as default, and a transaction which with specified transactionManager  will use the transactionManager you specify. 
e.g. ( for specifed transaction manager  only)
@Service
public class PgSQLTxService {
@Resource
PgTxMapper pgTxMapper;

@Transactional("pgTxMgr")
//@Transactional(transactionManager="pgTransactionManager")
public void transfer(Long fromUserId, Long targetUserId, int count) {
try {
            PgTx tt = pgTxMapper.selectByPrimaryKey(fromUserId);
PgTx tt2 = pgTxMapper.selectByPrimaryKey(targetUserId);

tt.setBlc(tt.getBlc() - count);
tt2.setBlc(tt2.getBlc() + count);

pgTxMapper.updateByPrimaryKey(tt);
pgTxMapper.updateByPrimaryKey(tt2);
} catch (Exception e) {
System.out.println(e.getMessage());
throw new RuntimeException(e.getMessage());
}
}
}
Wish to help you !!



On Thu, Dec 6, 2018 at 8:52 PM Midhun Nair <[hidden email]> wrote:
I am trying to connect database using two data sources in Spring boot (1.5.10) with mybatis (1.3.1). Tried implementing in following manner :
  • Created two separate data sources with the help of Data source Builder. Entries regarding both the databases proved in the application.properties file.
  • In the configuration file created separate Entity manger and transaction manager for both the data sources and marked one of them as @Primary.
  • Created separate mapper files for both the data source.
The above steps didn't accomplish the goal, so tried creating SqlSessionFactory and MapperFactoryBean. This also didn't work. Always the primary Db is getting connected.

Is there any limitation with Mybatis to connect two data sources (Same URL but different user) ?




--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Multiple Databse Connectivity issue with Spring boot

Hodo Info
In reply to this post by Midhun Nair


在 2018年12月6日星期四 UTC+8下午8:52:03,Midhun Nair写道:
I am trying to connect database using two data sources in Spring boot (1.5.10) with mybatis (1.3.1). Tried implementing in following manner :
  • Created two separate data sources with the help of Data source Builder. Entries regarding both the databases proved in the application.properties file.
  • In the configuration file created separate Entity manger and transaction manager for both the data sources and marked one of them as @Primary.
  • Created separate mapper files for both the data source.
The above steps didn't accomplish the goal, so tried creating SqlSessionFactory and MapperFactoryBean. This also didn't work. Always the primary Db is getting connected.

Is there any limitation with Mybatis to connect two data sources (Same URL but different user) ?




--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Multiple Databse Connectivity issue with Spring boot

Hodo Info
In reply to this post by Midhun Nair
Hi Midhum Nair:
First you should set two separate data source
e.g.
app.datasource.jdbc-url:jdbc:mysql://192.168.0.21:3306/bizz?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&useSSL=true&tcpRcvBuf=1024000
app.datasource.username:xcoin_online
app.datasource.password:*****
app.datasource.poolName:MySQLPool
app.datasource.max-lifetime:60000
app.datasource.maximum-pool-size:30
app.datasource.connection-timeout:60000
app.datasource.connection-init-sql:SELECT VERSION();
app.ds2.driver-class-name:org.postgresql.Driver
app.ds2.jdbc-url:jdbc:postgresql://127.0.0.1:5432/xcoin
app.ds2.username:asin
app.ds2.password:asinray666888
app.ds2.pool-name:PGPool
app.ds2.max-lifetime:60000
app.ds2.maximum-pool-size:30
app.ds2.connection-timeout:60000
app.ds2.connection-init-sql:SELECT VERSION();

DataSourceConf.java     (default datasource configuration )
 
@Configuration
@MapperScan(basePackages = "org.gl.mds.dao.mapper.mysql")
public class DataSourceConf {

@Bean
@Primary
@ConfigurationProperties("app.datasource")
public DataSource dataSource(){
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}

@Bean
@Primary
public PlatformTransactionManager TransactionManager(DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}

@Bean
@Primary
public SqlSessionFactory mysqlSessionFactory(DataSource mysqlDataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(mysqlDataSource);
return bean.getObject();
}
}

PGDataSourceConf.java

@Configuration
@MapperScan(basePackages = "org.gl.mds.dao.mapper.pg",sqlSessionFactoryRef = "pgSessionFactory")
public class PGDataSourceConf {

@Bean("pg")
@ConfigurationProperties("app.ds2")
public DataSource ds2(){
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}

@Bean("pgTxMgr")
public PlatformTransactionManager pgTransactionManager() {
return new DataSourceTransactionManager(ds2());
}
@Bean
public SqlSessionFactory pgSessionFactory() throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(ds2());
return bean.getObject();
}
When inject a mapper to service, remeber that the Transaction is use  @Primary datasouce configuration as default, and a transaction which with specified transactionManager  will use the transactionManager you specify. 

e.g. ( for specifed transaction manager  only)

@Service
public class PgSQLTxService {
@Resource
PgTxMapper pgTxMapper;

@Transactional("pgTxMgr")
public void transfer(Long fromUserId, Long targetUserId, int count) {
try {
            PgTx tt = pgTxMapper.selectByPrimaryKey(fromUserId);
PgTx tt2 = pgTxMapper.selectByPrimaryKey(targetUserId);

tt.setBlc(tt.getBlc() - count);
tt2.setBlc(tt2.getBlc() + count);

pgTxMapper.updateByPrimaryKey(tt);
pgTxMapper.updateByPrimaryKey(tt2);
} catch (Exception e) {
System.out.println(e.getMessage());
throw new RuntimeException(e.getMessage());
}
}
}
Wish to help you !!


在 2018年12月6日星期四 UTC+8下午8:52:03,Midhun Nair写道:
I am trying to connect database using two data sources in Spring boot (1.5.10) with mybatis (1.3.1). Tried implementing in following manner :
  • Created two separate data sources with the help of Data source Builder. Entries regarding both the databases proved in the application.properties file.
  • In the configuration file created separate Entity manger and transaction manager for both the data sources and marked one of them as @Primary.
  • Created separate mapper files for both the data source.
The above steps didn't accomplish the goal, so tried creating SqlSessionFactory and MapperFactoryBean. This also didn't work. Always the primary Db is getting connected.

Is there any limitation with Mybatis to connect two data sources (Same URL but different user) ?




--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Multiple Databse Connectivity issue with Spring boot

Hodo Info


在 2018年12月7日星期五 UTC+8下午12:36:46,Asin Liu写道:
Hi Midhum Nair:
First of all, you should set two separate data source
e.g.
app.datasource.jdbc-url:jdbc:mysql://<a href="http://192.168.0.21:3306/bizz?useUnicode=true&amp;characterEncoding=utf-8&amp;zeroDateTimeBehavior=convertToNull&amp;autoReconnect=true&amp;useSSL=true&amp;tcpRcvBuf=1024000" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2F192.168.0.21%3A3306%2Fbizz%3FuseUnicode%3Dtrue%26characterEncoding%3Dutf-8%26zeroDateTimeBehavior%3DconvertToNull%26autoReconnect%3Dtrue%26useSSL%3Dtrue%26tcpRcvBuf%3D1024000\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGorThbLwxD14kp2SD22BeMXnLF_w&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2F192.168.0.21%3A3306%2Fbizz%3FuseUnicode%3Dtrue%26characterEncoding%3Dutf-8%26zeroDateTimeBehavior%3DconvertToNull%26autoReconnect%3Dtrue%26useSSL%3Dtrue%26tcpRcvBuf%3D1024000\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGorThbLwxD14kp2SD22BeMXnLF_w&#39;;return true;">192.168.0.21:3306/biz?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=true
app.datasource.username:xcoin_online
app.datasource.password:*****
app.datasource.poolName:MySQLPool
app.datasource.max-lifetime:60000
app.datasource.maximum-pool-size:30
app.datasource.connection-timeout:60000
app.datasource.connection-init-sql:SELECT VERSION();
app.ds2.driver-class-name:org.postgresql.Driver
app.ds2.jdbc-url:jdbc:postgresql://<a href="http://127.0.0.1:5432/xcoin" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2F127.0.0.1%3A5432%2Fxcoin\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEDiY240ERchfy1THT2vuuAkeCPtw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2F127.0.0.1%3A5432%2Fxcoin\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEDiY240ERchfy1THT2vuuAkeCPtw&#39;;return true;">127.0.0.1:5432/xcoin
app.ds2.username:asin
app.ds2.password:asinray666888
app.ds2.pool-name:PGPool
app.ds2.max-lifetime:60000
app.ds2.maximum-pool-size:30
app.ds2.connection-timeout:60000
app.ds2.connection-init-sql:SELECT VERSION();

DataSourceConf.java     (default datasource configuration )
 
@Configuration
@MapperScan(basePackages = "org.gl.mds.dao.mapper.mysql")
public class DataSourceConf {

@Bean
@Primary
@ConfigurationProperties("app.datasource")
public DataSource dataSource(){
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}

@Bean
@Primary
public PlatformTransactionManager TransactionManager(DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}

@Bean
@Primary
public SqlSessionFactory mysqlSessionFactory(DataSource mysqlDataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(mysqlDataSource);
return bean.getObject();
}
}

PGDataSourceConf.java

@Configuration
@MapperScan(basePackages = "<a href="http://org.girllee.mds.dao.mapper.pg/" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Forg.girllee.mds.dao.mapper.pg%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFeYhb2vTrFZ5AMRildUMtoIv7Sxw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Forg.girllee.mds.dao.mapper.pg%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFeYhb2vTrFZ5AMRildUMtoIv7Sxw&#39;;return true;">org.gl.mds.dao.mapper.pg",sqlSessionFactoryRef = "pgSessionFactory")
public class PGDataSourceConf {

@Bean("pg")
@ConfigurationProperties("app.ds2")
public DataSource ds2(){
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}

@Bean("pgTxMgr")
public PlatformTransactionManager pgTransactionManager() {
return new DataSourceTransactionManager(ds2());
}
@Bean
public SqlSessionFactory pgSessionFactory() throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(ds2());
return bean.getObject();
}
When inject a mapper to service, remeber that the Transaction is use  @Primary datasouce configuration as default, and a transaction which with specified transactionManager  will use the transactionManager you specify. 

e.g. ( for specifed transaction manager  only)

@Service
public class PgSQLTxService {
@Resource
PgTxMapper pgTxMapper;

@Transactional("pgTxMgr")
public void transfer(Long fromUserId, Long targetUserId, int count) {
try {
            PgTx tt = pgTxMapper.selectByPrimaryKey(fromUserId);
PgTx tt2 = pgTxMapper.selectByPrimaryKey(targetUserId);

tt.setBlc(tt.getBlc() - count);
tt2.setBlc(tt2.getBlc() + count);

pgTxMapper.updateByPrimaryKey(tt);
pgTxMapper.updateByPrimaryKey(tt2);
} catch (Exception e) {
System.out.println(e.getMessage());
throw new RuntimeException(e.getMessage());
}
}
}
Wish to help you !!


在 2018年12月6日星期四 UTC+8下午8:52:03,Midhun Nair写道:
I am trying to connect database using two data sources in Spring boot (1.5.10) with mybatis (1.3.1). Tried implementing in following manner :
  • Created two separate data sources with the help of Data source Builder. Entries regarding both the databases proved in the application.properties file.
  • In the configuration file created separate Entity manger and transaction manager for both the data sources and marked one of them as @Primary.
  • Created separate mapper files for both the data source.
The above steps didn't accomplish the goal, so tried creating SqlSessionFactory and MapperFactoryBean. This also didn't work. Always the primary Db is getting connected.

Is there any limitation with Mybatis to connect two data sources (Same URL but different user) ?




--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Multiple Databse Connectivity issue with Spring boot

Midhun Nair
In reply to this post by Midhun Nair
Hi Asin Liu,

Thanx for the hep! It worked for me. I was missing the 'sqlSessionFatoryRef'.
Also found that:
  • @Mapperscan should have different base package.
  • Xml Mapper files and java mapper file should be in same package.
Happy Coding !

Regards
Midhun

On Thursday, December 6, 2018 at 6:22:03 PM UTC+5:30, Midhun Nair wrote:
I am trying to connect database using two data sources in Spring boot (1.5.10) with mybatis (1.3.1). Tried implementing in following manner :
  • Created two separate data sources with the help of Data source Builder. Entries regarding both the databases proved in the application.properties file.
  • In the configuration file created separate Entity manger and transaction manager for both the data sources and marked one of them as @Primary.
  • Created separate mapper files for both the data source.
The above steps didn't accomplish the goal, so tried creating SqlSessionFactory and MapperFactoryBean. This also didn't work. Always the primary Db is getting connected.

Is there any limitation with Mybatis to connect two data sources (Same URL but different user) ?




--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.