Springboot mybatis configuration oracle multi-data source master database can query slave database all the time failed to bind - Codes Helper

springboot mybatis configuration oracle multi-data source master database can query slave database all the time failed to bind

has been messing around for a long time. Please take a look at it.

error message: org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.example.mapper.smsinfo.SmsinfoMapper.countJCTaskNum

I have found more information on the Internet, but I still can"t solve it.

the answer on the Internet is roughly:

1: check whether the package name where the xml file is located corresponds to the package name corresponding to interface

2: check whether the namespace of the xml file corresponds to the package name of the xml file

3: check whether the function name corresponds to

4: remove Chinese comments from xml file

5: feel free to add a space or blank line in the xml file and save

all have been tried, but no problems have been found.

try to remove the @ Primary of datasourceconfig1 (master library) and add it to datasourceconfig2 (slave library), and datasourceconfig2 can query normally, but datasourceconfig1 can"t.
the conclusion is that as long as you add Primary, you can query normally, and if you don"t add it, you will report the error of binding failure.

-mapper SmsmainMapper code-

@ Mapper
public interface SmsmainMapper {

List<Task> getAll();

List<Task> getTaskList();

int pauseTask(@Param("id") int id, @Param("fflag") String fflag);

int restartTask(@Param("id") int id, @Param("fflag") String fflag);

int pauseTaskNum();

int pubTask();//

int pubYJTask();//

int resTask();//

int countTaskNum(); //

List<Task> getPubTaskList();                    //

List<Task> getPubTaskYJList();                    //

List<Task> getPubResTaskList();                    //

List<Task> getPauseTaskList();                    //

List<Tunel> getTunelList();                    //

List<Tunel> getByTunelID(int tunelid);                    //

}

-mapper SmsmainMapper code-

@ Mapper
public interface SmsinfoMapper {

List<Jctask> getJCAll();  //

int countJCTaskNum(); //

}

-mapper smsimain.xml code-

< mapper namespace= "com.example.mapper.smsmain.SmsmainMapper" >

<resultMap id="BaseResultMap" type="com.example.model.Task">
    <result column="ftaskid" property="ftaskid" jdbcType="INTEGER"/>
    <result column="fdesc" property="fdesc" jdbcType="VARCHAR"/>
    <result column="fmsg" property="fmsg" jdbcType="VARCHAR"/>
    <result column="ftasktype" property="ftasktype" jdbcType="VARCHAR"/>
    <result column="fbegintime" property="fbegintime" jdbcType="TIMESTAMP"/>
    <result column="fruntime" property="fruntime" jdbcType="TIMESTAMP"/>
    <result column="ftotal" property="ftotal" jdbcType="INTEGER"/>
    <result column="fflag" property="fflag" jdbcType="INTEGER"/>
    <result column="fcurcount" property="fcurcount" jdbcType="INTEGER"/>
</resultMap>

<resultMap id="TunelResultMap" type="com.example.model.Tunel">
    <result column="ftunelid" property="ftunelid" jdbcType="INTEGER"/>
    <result column="ftuneldesc" property="ftuneldesc" jdbcType="VARCHAR"/>
    <result column="fspeed" property="fspeed" jdbcType="INTEGER"/>
</resultMap>


<sql id="BASE_TABLE">
    task_180811
</sql>

<sql id="BASE_COLUMN">
    ftaskid,fdesc,fmsg,ftasktype,fbegintime,fruntime,ftotal,fcurcount,fflag
</sql>

<sql id="BASE_GROUP">
    fflag,ftasktype,count(*) as tasknum
</sql>

<sql id="BASE_TUNEL">
    p.ftunelid,p.ftuneldesc,p.fspeed,count(*) as tunelnum
</sql>


<!---->
<update id="pauseTask" parameterType="java.lang.String">
    UPDATE
    <include refid="BASE_TABLE"/>
    SET
    <if test="fflag!=null">
        fflag=-sharp{fflag}
    </if>
    WHERE
    ftaskid=-sharp{id}
</update>
<!---->
<update id="restartTask" parameterType="java.lang.String">
    UPDATE
    <include refid="BASE_TABLE"/>
    SET
    <if test="fflag!=null">
        fflag=-sharp{fflag}
    </if>
    WHERE
    ftaskid=-sharp{id}
</update>

<!---->
<select id="getAll" resultMap="BaseResultMap" parameterType="java.lang.String">
    SELECT
    <include refid="BASE_COLUMN"/>
    FROM
    <include refid="BASE_TABLE"/>
    order by fflag desc
</select>

<!---->
<select id="getTaskList" resultType="Map">
    SELECT
    <include refid="BASE_GROUP"/>
    FROM
    <include refid="BASE_TABLE"/>
    group by fflag,ftasktype
</select>
<!---->
<select id="pauseTaskNum" resultType="java.lang.Integer">
    SELECT
    count(*)
    FROM
    <include refid="BASE_TABLE"/>
    where fflag=4
</select>
<!---->
<select id="pubTask" resultType="java.lang.Integer">
    SELECT
    count(*)
    FROM
    <include refid="BASE_TABLE"/>
    where fflag=1
</select>
<!---->
<select id="pubYJTask" resultType="java.lang.Integer">
    SELECT
    count(*)
    FROM
    <include refid="BASE_TABLE"/>
    where fflag=1 and ftasktype=1
</select>
<!---->
<select id="resTask" resultType="java.lang.Integer">
    SELECT
    count(*)
    FROM
    <include refid="BASE_TABLE"/>
    where fflag=0
</select>
<!---->
<select id="countTaskNum" resultType="java.lang.Integer">
    SELECT
    count(*)
    FROM
    <include refid="BASE_TABLE"/>
</select>

<!---->
<select id="getPubTaskList" resultMap="BaseResultMap" parameterType="java.lang.String">
    SELECT
    <include refid="BASE_COLUMN"/>
    FROM
    <include refid="BASE_TABLE"/>
    where fflag=1
</select>

<!---->
<select id="getPauseTaskList" resultMap="BaseResultMap" parameterType="java.lang.String">
    SELECT
    <include refid="BASE_COLUMN"/>
    FROM
    <include refid="BASE_TABLE"/>
    where fflag=4
</select>

<!---->
<select id="getPubTaskYJList" resultMap="BaseResultMap" parameterType="java.lang.String">
    SELECT
    <include refid="BASE_COLUMN"/>
    FROM
    <include refid="BASE_TABLE"/>
    where fflag=1 and ftasktype=1
</select>
<!---->
<select id="getPubResTaskList" resultMap="BaseResultMap" parameterType="java.lang.String">
    SELECT
    <include refid="BASE_COLUMN"/>
    FROM
    <include refid="BASE_TABLE"/>
    where fflag=0
</select>


<!---->
<select id="getTunelList"  resultMap="TunelResultMap" parameterType="java.lang.String">
    SELECT
    <include refid="BASE_TUNEL"/>
    FROM
    (select ftunelid from task_180811 ) t
    left join (select ftunelid,ftuneldesc,fspeed from tbl_pubtunel) p
    on p.ftunelid=t.ftunelid
    group by p.ftunelid,p.ftuneldesc,p.fspeed
    order by p.ftunelid
</select>


<!--ID-->
<select id="getByTunelID" resultMap="BaseResultMap" parameterType="java.lang.Integer">
    SELECT
    <include refid="BASE_COLUMN"/>
    FROM
    <include refid="BASE_TABLE"/>
    where ftunelid=-sharp{ftunelid}
</select>

< / mapper >

-mapper smsinfo.xml code-

< mapper namespace= "com.example.mapper.smsinfo.SmsinfoMapper" >

<resultMap id="BaseMap" type="com.example.model.Jctask">
    <result column="ftaskid" property="ftaskid" jdbcType="INTEGER"/>
    <result column="fdesc" property="fdesc" jdbcType="VARCHAR"/>
    <result column="fmsg" property="fmsg" jdbcType="VARCHAR"/>
    <result column="ftasktype" property="ftasktype" jdbcType="INTEGER"/>
    <result column="fbegintime" property="fbegintime" jdbcType="TIMESTAMP"/>
    <result column="fruntime" property="fruntime" jdbcType="TIMESTAMP"/>
    <result column="ftotal" property="ftotal" jdbcType="INTEGER"/>
    <result column="fflag" property="fflag" jdbcType="VARCHAR"/>
    <result column="fcurcount" property="fcurcount" jdbcType="INTEGER"/>
</resultMap>
<sql id="JC_BASE_TABLE">
    tbl_task
</sql>

<sql id="JC_BASE_COLUMN">
    ftaskid,fdesc,fmsg,ftasktype,fbegintime,fruntime,ftotal,fcurcount,fflag
</sql>

<sql id="JC_BASE_GROUP">
    fflag,ftasktype,count(*) as tasknum
</sql>

<sql id="JC_BASE_TUNEL">
    p.ftunelid,p.ftuneldesc,p.fspeed,count(*) as tunelnum
</sql>

<!---->
<select id="countJCTaskNum" resultType="java.lang.Integer">
    SELECT
    count(*)
    FROM
    <include refid="JC_BASE_TABLE"/>
</select>

<!---->
<select id="getJCAll" resultMap="BaseMap">
    SELECT
    <include refid="JC_BASE_COLUMN"/>
    FROM
    <include refid="JC_BASE_TABLE"/>
    order by fflag desc
</select>

< / mapper >

-SmsmainDataSourceConfig code-

@ Configuration
@ MapperScan (basePackages = "com.example.mapper.smsmain.SmsmainMapper", sqlSessionTemplateRef = "smsmainSqlSessionTemplate", sqlSessionFactoryRef = "smsmainSqlSessionFactory")
public class SmsmainDataSourceConfig {

/**
 * DataSource
 *
 * @return
 */
@Bean(name = "smsmainDataSource")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.smsmain")
public DataSource smsmainDataSource() {
    return DataSourceBuilder.create().build();
}

/**
 * 
 *
 * @param dataSource
 * @return
 * @throws Exception
 */

@Bean(name = "smsmainSqlSessionFactory")
@Primary
public SqlSessionFactory smsmainSqlSessionFactory(@Qualifier("smsmainDataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/smsmain/SmsmainMapper.xml"));
    return bean.getObject();
}

/**
 * 
 * @param dataSource
 * @return
 */
@Bean(name = "smsmainTransactionManager")
@Primary
public DataSourceTransactionManager smsmainTransactionManager(@Qualifier("smsmainDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "smsmainSqlSessionTemplate")
@Primary
public SqlSessionTemplate smsmainSqlSessionTemplate(@Qualifier("smsmainSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
    return new SqlSessionTemplate(sqlSessionFactory);
}

-SmsinfoDataSourceConfig code-
@ Configuration
@ MapperScan (basePackages = "com.example.mapper.smsinfo.SmsinfoMapper", sqlSessionTemplateRef = "smsinfoSqlSessionTemplate", sqlSessionFactoryRef = "smsinfoSqlSessionFactory")
public class SmsinfoDataSourceConfig {

/**
 * DataSource
 *
 * @return
 */
@Bean(name = "smsinfoDataSource")
@Qualifier("smsinfoDataSource")
@ConfigurationProperties(prefix = "spring.datasource.smsinfo")
public DataSource smsinfoDataSource() {
    return DataSourceBuilder.create().build();
}

/**
 * 
 *
 * @param dataSource
 * @return
 * @throws Exception
 */

@Bean(name = "smsinfoSqlSessionFactory")
public SqlSessionFactory smsinfoSqlSessionFactory(@Qualifier("smsinfoDataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/smsinfo/SmsinfoMapper.xml"));
    return bean.getObject();
}

/**
 * 
 * @param dataSource
 * @return
 */
@Bean(name = "smsinfoTransactionManager")
public PlatformTransactionManager smsinfoTransactionManager(@Qualifier("smsinfoDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "smsinfoSqlSessionTemplate")
public SqlSessionTemplate smsinfoSqlSessionTemplate(@Qualifier("smsinfoSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
    return new SqlSessionTemplate(sqlSessionFactory);
}

}

-application.yml code-

mybatis:
typeAliasesPackage: com.example.model
mapperLocations: classpath:mapper/ / .xml

spring:
datasource:

smsmain:
  driver-class-name: oracle.jdbc.driver.OracleDriver
  jdbc-url: jdbc:oracle:thin:@*.*.*.*:1521:qxsm1
  username: ****
  password: ****

smsinfo:
  driver-class-name: oracle.jdbc.driver.OracleDriver
  jdbc-url: jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ****)))
  username: ******
  password: ******

server:
port: 8888

-error message-

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.example.mapper.smsinfo.SmsinfoMapper.countJCTaskNum

at org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>(MapperMethod.java:227) ~[mybatis-3.4.6.jar:3.4.6]
at org.apache.ibatis.binding.MapperMethod.<init>(MapperMethod.java:49) ~[mybatis-3.4.6.jar:3.4.6]
at org.apache.ibatis.binding.MapperProxy.cachedMapperMethod(MapperProxy.java:65) ~[mybatis-3.4.6.jar:3.4.6]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58) ~[mybatis-3.4.6.jar:3.4.6]
at com.sun.proxy.$Proxy64.countJCTaskNum(Unknown Source) ~[na:na]
at com.example.controller.MonitorController.run(MonitorController.java:47) ~[classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_121]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_121]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65) ~[spring-context-5.0.6.RELEASE.jar:5.0.6.RELEASE]
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-5.0.6.RELEASE.jar:5.0.6.RELEASE]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_121]
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_121]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_121]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_121]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_121]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_121]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_121]