Spring boot + MyBatis, multiple datasources and mappers (java and xml), getting "Invalid bound statement (not found)" error

Question

I am trying to create a maven project to access Oracle database with more than one datasource configurations. Here is my code:

First DataSource Config:

package com.business.data.datasource;

import javax.sql.DataSource;

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
public class FirstDbConfig {
 
    @Primary
    @Bean(name = "firstDataSourceProperties")
    @ConfigurationProperties("first.datasource")
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }
    
    @Primary
    @Bean(name = "firstDataSource")
    @ConfigurationProperties(prefix = "first.datasource")
    public DataSource dataSource(@Qualifier("firstDataSourceProperties") DataSourceProperties properties) {
        return DataSourceBuilder.create().build();
    }
  
    @Bean(name = "firstSessionFactory")
    @Primary
    public SqlSessionFactoryBean firstSessionFactory(@Qualifier("firstDataSource") final DataSource firstDataSource)
            throws Exception {
        
        final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(firstDataSource);

        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:./mapper/FirstDbMapper.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("com.business.data.model");

        return sqlSessionFactoryBean;
    }
}

Second DataSource Config:

package com.business.data.datasource;

import javax.sql.DataSource;

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
public class SecondDbConfig {
 
    @Primary
    @Bean(name = "secondDataSourceProperties")
    @ConfigurationProperties("second.datasource")
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }
    
    @Bean(name = "secondDataSource")
    @ConfigurationProperties(prefix = "second.datasource")
    public DataSource dataSource(@Qualifier("secondDataSourceProperties") DataSourceProperties properties) {
        return DataSourceBuilder.create().build();
    }
  
    @Bean(name = "secondSessionFactory")
    public SqlSessionFactoryBean secondSessionFactory(@Qualifier("secondDataSource") final DataSource firstDataSource)
            throws Exception {
        
        final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(firstDataSource);

        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:./mapper/SecondDbMapper.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("com.business.data.model");

        return sqlSessionFactoryBean;
    }
}

First Mapper interface:

package com.business.data.repository;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import com.business.data.model.PersonDetail;
import org.springframework.stereotype.Repository;


@Repository
@Mapper
public interface FirstDbMapper {    
    public List<PersonDetail> getUserData(@Param("firstName") String firstName, @Param("lastName") String lastName);
}

Second Mapper interface:

package com.business.data.repository;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import com.business.data.model.PersonDetail;
import org.springframework.stereotype.Repository;


@Repository
@Mapper
public interface SecondDbMapper {   
    public List<PersonDetail> getStaffData(@Param("firstName") String firstName, @Param("lastName") String lastName);
}

src/main/resources/mapper/FirstMapper.xml

<?xml version = "1.0" encoding = "UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- namespace must indicate mapper interface full package path . It is an alias here-->    
<mapper namespace = "com.business.data.repository.FirstDbMapper">
   
    <select id = "getUserData" parameterType = "map" resultMap = "personDetailMap">
        SELECT  *
        FROM    user
        WHERE UPPER(first_name) LIKE UPPER(#{firstName}||'%')
        AND UPPER(last_name) LIKE UPPER(#{lastName}||'%')
    </select>
    
    ...
</mapper>

src/main/resources/mapper/SecondMapper.xml

<?xml version = "1.0" encoding = "UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- namespace must indicate mapper interface full package path . It is an alias here-->    
<mapper namespace = "com.business.data.repository.SecondDbMapper">
   
    <select id = "getStaffData" parameterType = "map" resultMap = "personDetailMap">
        SELECT  *
        FROM    staff
        WHERE UPPER(first_name) LIKE UPPER(#{firstName}||'%')
        AND UPPER(last_name) LIKE UPPER(#{lastName}||'%')
    </select>
    
    ...
</mapper>

Service class is like

    @Autowired 
    FirstDbMapper firstDbMapper;

    public List<PersonDetail> getUser(String fName, String lName) throws MyServiceException {
        ... 
        try {
        
            userList = firstDbMapper.getUserData(fName, lName); 
            
        } catch (Exception e) {
            ...  
        }
        return userList;
    }

application.properties:

first.datasource.jdbc-url=jdbc:oracle:thin:@host01:1561:db1
first.datasource.username=user1
first.datasource.password=password1

second.datasource.jdbc-url=jdbc:oracle:thin:@host02:1561:db2
second.datasource.username=user2
second.datasource.password=password2

I also have @MapperScan("com.business.data.repository") in my spring boot application java.

I can only make one datasource work, which is the one with @Primary annotation. I swapped @Primary between the two configuration, always the one with @Primary worked, the other got "Invalid bound statement (not found)" error.

Can anyone help me out?

Thanks!

Jeff Butler · Accepted Answer · 2021-05-19 11:35:43Z

1

You can use the @MapperScan annotation on a configuration class to attach mappers to the different session factories. I find it convenient to place mappers in different packages like this:

@MapperScan(basePackages="mapper.package1", sqlSessionFactoryRef="firstSessionFactory")
@MapperScan(basePackages="mapper.package2", sqlSessionFactoryRef="secondSessionFactory")

answered May 19, 2021 at 11:35

Jeff Butler

1,0117 silver badges11 bronze badges

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

3 Comments

marcobug Over a year ago

Thanks! Tried it but didn't fix my problem. Any more suggestion?

Jeff Butler Over a year ago

Did you move the mappers into different packages and change the namespaces in XML? You should also remove any other @MapperScan you might have.

marcobug Over a year ago

Yes I did but with no luck. I found the solution, will post it now.

marcobug · Accepted Answer · 2021-05-20 15:38:52Z

0

The issue was resolved by using HikariDataSource.

    @Primary
    @Bean(name = FIRST_DATASOURCE)
    @ConfigurationProperties(prefix = "first.datasource")
    public DataSource dataSourceFirst() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

answered May 20, 2021 at 15:38

marcobug

11 silver badge2 bronze badges

Collectives™ on Stack Overflow

Spring boot + MyBatis, multiple datasources and mappers (java and xml), getting "Invalid bound statement (not found)" error

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related