Configuring multiple data sources in spring-web.xml

Question

Multiple data sources in spring

My spring-web.xml is like this

<!-- Data source Bean -->
<bean id="dataSource"
     class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName" value="com.ibm.as400.access.AS400JDBCDriver" />
    <property name="url" value="jdbc:as400://localhost/BB" />
    <property name="username" value="ROOT" />
    <property name="password" value="ROOT" />
</bean>

My DAOImpl is like this

public class BBDAOImpl extends JdbcDaoSupport implements BBDao {    

@Autowired
DataSource dataSource;

@Override
public List<Map<String, Object>> getDetails(String customerId) {
    String sql = "<SQL Query>";
    if(BBUtil.getInstance().isNotEmpty(customerId)) {
        try {
            return getJdbcTemplate().queryForList(sql,customerId);
        }  catch (EmptyResultDataAccessException e) {
            logger.error("Empty result data - getDetails");
        }
    } else {
        // Want to configure here from second data source
    }

    return null;
}

Here getJdbcTemplate() method is pointing directly to the AS400 DB(But how). Now, my another requirement has come up. In the else block I want to do some data manipulation from another SQL Server.

Can any one tell me how to configure multiple DB's in this and how to use them?

This project is already developed using XML configuration and now I cannot go back to annotations config.

Thanks in advance guys.

Pramod S. Nikam · Accepted Answer · 2017-10-31 05:31:33Z

1

Define two sets of jdbc templates like below:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

<bean id="jdbcTemplate2" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource2"/>
</bean>

then ahead inject them in repository like below:

    @Resource("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Resource("jdbcTemplate2")
    private JdbcTemplate jdbcTemplate2;

Define two data sources in your spring data configuration file like below:

<!-- Data source Bean 1 -->
 <bean id="dataSource"
 class="org.springframework.jdbc.datasource.DriverManagerDataSource">

   <property name="driverClassName" value="com.ibm.as400.access.AS400JDBCDriver" />
   <property name="url" value="jdbc:as400://localhost/BB" />
   <property name="username" value="ROOT" />
   <property name="password" value="ROOT" />
</bean>

<bean id="dataSource2"
     class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName" value="com.ibm.as400.access.AS400JDBCDriver" />
    <property name="url" value="jdbc:as4002://localhost/BB2" />
    <property name="username" value="ROOT" />
    <property name="password" value="ROOT" />
</bean>

and then inject those data sources like below in your repository class and use wherever you want to :

@Autowired
DataSource dataSource;

@Autowired
DataSource dataSource1;

Now use 2nd JDBC template for your if else requirement it will use datasource 2.

edited Oct 31, 2017 at 5:31

answered Oct 31, 2017 at 5:23

Pramod S. Nikam

4,5628 gold badges41 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Yakhoob Over a year ago

How come getJdbcTemplate() will understand which data source to use?

Pramod S. Nikam Over a year ago

Answer Updated to add jdbc template explanation . Please have look.

Yakhoob Over a year ago

It is accepting only @Resource but not @Resource("jdbcTemplate"). Please check syntax

Pramod S. Nikam Over a year ago

only resource also do

Gaurav · Accepted Answer · 2017-10-31 05:34:03Z

1

You can configure an additional data source like

<bean id="dataSource1"
     class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName" value="com.ibm.as400.access.AS400JDBCDriver" />
    <property name="url" value="jdbc:as400://localhost/BB" />
    <property name="username" value="ROOT" />
    <property name="password" value="ROOT" />
</bean>

<bean id="dataSource2"
     class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName" value="com.ibm.as400.access.AS400JDBCDriver" />
    <property name="url" value="jdbc:as400://localhost/BB" />
    <property name="username" value="ROOT" />
    <property name="password" value="ROOT" />
</bean>

And then while you want to inject, you can use @Qualifier annotation.

@Autowired
@Qualifier("dataSource1")
DataSource dataSource1;

@Autowired
@Qualifier("dataSource2")
DataSource dataSource2;

Now, whenever in a class you want to use first datasource, you will use variable dataSource1, and when you want to use second datasource, you will have to use variable dataSource2.

However, now you will have to modifiy all the data access layers with @Qualifier annotation, because Spring's will have two datasource candidates to inject to in Datasource, And it is likely to ask you which one to inject.

edited Oct 31, 2017 at 5:34

answered Oct 31, 2017 at 5:26

Gaurav

3,7693 gold badges33 silver badges52 bronze badges

2 Comments

Yakhoob Over a year ago

How come getJdbcTemplate() will understand which data source to use?

Gaurav Over a year ago

Updated answer. Please check.

Collectives™ on Stack Overflow

Configuring multiple data sources in spring-web.xml

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related