Mybatis - Invalid bound statement (not found)

Question

I have created a new project using mybatis to connect to a mysql database. This is my second project using mybatis so I am familiar with it but am getting the following error when I call the save method:

2019-03-05 10:08:01.031 ERROR 86438 --- [nio-9905-exec-1] c.q.f.r.c.ResolveRestController : Error starting preset: Invalid bound statement (not found): com.qlsdistribution.fps.resolverender.data.mybatis.mapper.ResolveJobReportMapper.saveResolveJobReport org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.qlsdistribution.fps.resolverender.data.mybatis.mapper.ResolveJobReportMapper.saveResolveJobReport at org.apache.ibatis.binding.MapperMethod$SqlCommand.(MapperMethod.java:232) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.binding.MapperMethod.(MapperMethod.java:50) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.binding.MapperProxy.lambda$cachedMapperMethod$0(MapperProxy.java:62) ~[mybatis-3.5.0.jar:3.5.0] at java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1660) ~[na:1.8.0_101] at org.apache.ibatis.binding.MapperProxy.cachedMapperMethod(MapperProxy.java:62) ~[mybatis-3.5.0.jar:3.5.0] .....

My mybatis config file is as follows (in src/main/resources):

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <settings>
        <!-- changes from the defaults -->
       <setting name="lazyLoadingEnabled" value="true" />
       <!-- Mybatis logging -->
       <setting name="logImpl" value="LOG4J2"/>
    </settings>
    <typeAliases>
        <package name="com.qlsdistribution.fps.resolverender.data.mybatis.domain"/>

    </typeAliases>

   <mappers>
      <mapper resource="mapper/ResolveJobReport.xml"/>
<!--       <mapper resource="com/qlsdistribution/fps/resolverender/data/mybatis/mapper/ResolveJobReport.xml"/> -->
   </mappers>
</configuration>

As you can see I have tried different locations for the mapper xml file but if I put invalid syntax in the mapper xml file, it fails with invalid syntax (SAXParseException) instead so I know the mapper xml file is being read.

The mapper xml file is as follows (in src/main/resources/mapper):

<?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">

<mapper namespace="com.qlsdistribution.fps.resolverender.data.mybatis.ResolveJobReportMapper">

    <resultMap id="resolveJobReport" type="com.qlsdistribution.fps.resolverender.data.mybatis.domain.ResolveJobReport">
        <result property="id" column="id" />
        <result property="fpsProjectName" column="fps_project_name" />
        <result property="inputPath" column="input_path" />
        <result property="destinationPath" column="destination_path" />
        <result property="presetName" column="preset_name" />
        <result property="ipAddress" column="ip_address" />
        <result property="frameRate" column="frame_rate" />
        <result property="resolveProjectName" column="resolve_project_name" />
        <result property="width" column="width" />
        <result property="height" column="height" />
        <result property="renderFormat" column="render_format" />
        <result property="renderCodec" column="render_codec" />
        <result property="scriptPath" column="script_path" />
        <result property="cliOutput" column="cli_output" />
        <result property="jobStartedDate" column="job_started_date" />
        <result property="jobFinishedDate" column="job_finished_date" />
        <result property="createdBy" column="created_by" />
        <result property="createdDate" column="created_date" />
        <result property="modifiedBy" column="modified_by" />
        <result property="modifiedDate" column="modified_date" />
    </resultMap>

    <select id="getAllResolveJobReports" resultMap="resolveJobReport">
        SELECT id, fps_project_name, input_path, destination_path, preset_name, ip_address, frame_rate, resolve_project_name, width, height, 
        render_format, render_codec, script_path, cli_output, job_started_date, job_finished_date, created_by, created_date, modified_by, modified_date
        FROM resolve_job_report
        WHERE fps_setting_id = #{value}
        ORDER by id desc;
    </select>

    <select id="getAllResolveJobReportsById" parameterType="Long"  resultMap="resolveJobReport">
        SELECT id, fps_project_name, input_path, destination_path, preset_name, ip_address, frame_rate, resolve_project_name, width, height, 
        render_format, render_codec, script_path, cli_output, job_started_date, job_finished_date, created_by, created_date, modified_by, modified_date
        FROM resolve_job_report
        WHERE id = #{value};
    </select>

    <insert id="saveResolveJobReport" parameterType="com.qlsdistribution.fps.resolverender.data.mybatis.domain.ResolveJobReport">
        INSERT INTO resolve_job_report 
        (fps_project_name, input_path, destination_path, preset_name, ip_address, frame_rate, resolve_project_name, width, height, 
        render_format, render_codec, script_path, cli_output, job_started_date, job_finished_date, created_by)
        VALUE 
        (#{fpsProjectName},#{inputPath},#{destinationPath},#{presetName},#{ipAddress},#{frameRate},#{resolveProjectName},#{width},#{height},
        #{renderFormat},#{renderCodec}, #{scriptPath},#{cliOutput},#{jobStartedDate},#{jobFinishedDate},#{createdBy});
    </insert>

    <update id="updateResolveJobReportById" parameterType="resolveJobReport">
        UPDATE resolve_job_report
        SET
        fps_project_name = #{fpsProjectName}, 
        input_path = #{inputPath}, 
        destination_path = #{destinationPath}, 
        preset_name = #{presetName}, 
        ip_address = #{ipAddress}, 
        frame_rate = #{frameRate}, 
        resolve_project_name = #{resolveProjectName}, 
        width = #{width}, 
        height = #{height}, 
        render_format = #{renderFormat}, 
        render_codec = #{renderCodec}, 
        script_path = #{scriptPath}, 
        cli_output = #{cliOutput}, 
        job_started_date = #{jobStartedDate}, 
        job_finished_date = #{jobFinishedDate},
        modified_by = #{modifiedBy}
        where id = #{id};
    </update>

    <delete id="deleteResolveJobReporttById" parameterType="Long">
        DELETE FROM resolve_job_report
        WHERE id = #{value}
    </delete>
</mapper>

My application.properties file contains the following (in (src/main/resources):

spring.datasource.url=jdbc:mysql://localhost:3306/fpsresolvetool?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.schema=classpath:schema.sql

mybatis.config-location=classpath:SqlMapConfig.xml

I have even tried using the latest mybatis versions in my pom.xml:

    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis-spring</artifactId>
        <version>2.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.0.0</version>
    </dependency>

My spring boot application is as follows:

@SpringBootApplication
@EnableScheduling
@ComponentScan({"com.qlsdistribution.fps.resolverender"})
@EnableJms
@MapperScan("com.qlsdistribution.fps.resolverender.data.mybatis")
public class FPSResolveRenderApp implements WebMvcConfigurer {
    /** UTF-8 Character set name */
    private static final String UTF_8 = "UTF-8";

    /** Logger */
    private static final Logger logger = LogManager.getLogger(FPSResolveRenderApp.class);

    public static void main(String[] args) {
        new SpringApplicationBuilder(FPSResolveRenderApp.class).run(args);

    }

    /**
     * Creates and gets the FilterRegistrationBean
     * @return
     */
    @Bean
    public FilterRegistrationBean<CharacterEncodingFilter> filterRegistrationBean() {
        FilterRegistrationBean<CharacterEncodingFilter> registrationBean = new FilterRegistrationBean<CharacterEncodingFilter>();
        CharacterEncodingFilter characterEncodingFilter = new CharacterEncodingFilter();
        characterEncodingFilter.setEncoding(UTF_8);
        registrationBean.setFilter(characterEncodingFilter);
        return registrationBean;
    }
}

The mapper interface is as follows:

@Mapper
public interface ResolveJobReportMapper {

    public List<ResolveJobReport> getAllResolveJobReports();

    public List<ResolveJobReport> getAllResolveJobReports(RowBounds rowBounds);

    public List<ResolveJobReport> getAllResolveJobReportsById(Long id);

    public List<ResolveJobReport> getAllResolveJobReportsById(Long id, RowBounds rowBounds);

    public void saveResolveJobReport(ResolveJobReport resolveJobReport);

    public void updateResolveJobReportById(ResolveJobReport resolveJobReport);

    public void deleteResolveJobReporttById(Long id);

}

And the service class is as follows:

@Service("ResolveJobReportService")
public class ResolveJobReportService {

    @Autowired
    private ResolveJobReportMapper resolveJobReportMapper= null;

    public List<ResolveJobReport> getAllResolveJobReports() {
        return resolveJobReportMapper.getAllResolveJobReports();
    }

    public List<ResolveJobReport> getAllResolveJobReports(RowBounds rowBounds) {
        return resolveJobReportMapper.getAllResolveJobReports();
    }

    public List<ResolveJobReport> getAllResolveJobReportsById(Long id) {
        return resolveJobReportMapper.getAllResolveJobReportsById(id);
    }

    public List<ResolveJobReport> getAllResolveJobReportsById(Long id, RowBounds rowBounds) {
        return resolveJobReportMapper.getAllResolveJobReportsById(id);
    }

    public void saveResolveJobReport(ResolveJobReport resolveJobReport) {
        resolveJobReportMapper.saveResolveJobReport(resolveJobReport);
    }

    public void updateResolveJobReportById(ResolveJobReport resolveJobReport) {
        resolveJobReportMapper.updateResolveJobReportById(resolveJobReport);
    }

    public void deleteResolveJobReporttById(Long id) {
        resolveJobReportMapper.deleteResolveJobReporttById(id);
    }

}

Can anyone see what is wrong.

Answer 1

The problem happens because the namespace in mapper xml is com.qlsdistribution.fps.resolverender.data.mybatis.ResolveJobReportMapper but the package the mapper interface is com.qlsdistribution.fps.resolverender.data.mybatis.mapper.ResolveJobReportMapper.

To fix it make sure they match.

Answer 2

For those people using MyBatis without xml in spring boot project:

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>

Please check your spring boot application class, make sure your @MapperScan matches your dao package.

@MapperScan("com.jjs.videoservice.dao") 

The path must be SAME as your dao package (contains your mapper interface), I ignored "dao" and caused the issue.

Hope it helps someone, thanks

Answer 3

Put it in application.properties mybatis.mapper-locations=classpath*:**/mappers/*Mapper.xml