spring boot +mybatis +mysql 常见错误

1 、[Request processing failed; nested exception is org.apache.ibatis.binding.BindingException: Invalid bound statement (not found):

一般是mapper.xml文件写的有问题，注意namespace - 映射接口的的相对路径是不是正确？ 同时，注意检查接口中函数id对应关系检查。

<?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.miller.mybatis.mapper.EmployeeMapper">

    <resultMap id="EmployeeResult" type="com.miller.mybatis.entity.Employee">
        <id property="id" column="ID" jdbcType="INTEGER" javaType="int"/>
        <result property="departmentName" column="DepartmentName" jdbcType="VARCHAR" javaType="String"/>
        <result property="name" column="Name" jdbcType="VARCHAR" javaType="String"/>
        <result property="age" column="Age" jdbcType="INTEGER" javaType="int"/>
    </resultMap>

    <select id="findEmployeeById" parameterType="int" resultMap="EmployeeResult">
        select * from employee where ID = #{id}
    </select>

    <delete id="deleteEmployee" parameterType="int" >
        DELETE FROM employee WHERE ID = #{id};
    </delete>

    <update id="updateEmployee" parameterType="Employee" >
        UPDATE employee SET DepartmentName = #{departmentName}, Name = #{name}, Age = #{age} WHERE ID = #{id}
    </update>
    <!--插入操作-->
<!--    <insert id="addEmployee" parameterType="com.miller.mybatis.entity.Employee">
        INSERT INTO Employee(ID,DepartmentName,Name,Age) values(#{id},#{departmentName},#{name},#{age})
    </insert>-->
</mapper>

2、 Cause: java.lang.IllegalArgumentException: Mapped Statements collection already contains value for com.miller.mybatis.mapper.EmployeeMapper.addEmployee. 

原因：重复出现相同的函数id

 

3、项目目录

4、项目代码

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.miller</groupId>
    <artifactId>mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-websocket</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.kafka</groupId>
            <artifactId>spring-kafka</artifactId>
        </dependency>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.kafka</groupId>
            <artifactId>spring-kafka-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>

        <resources>
            <resource>
                <directory>src/main/java</directory>
                <excludes>
                    <exclude>**/*.java</exclude>
                </excludes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

 

#SET GLOBAL event_scheduler=1;
USE oracle;

CREATE TABLE IF NOT EXISTS `hibernate_sequence` (
    `next_val` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

create table  if not exists employee
(
    ID int not null AUTO_INCREMENT,
    DepartmentName varchar(255) not null,
    Name varchar(100) not null,
    Age int not null,
    PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT = 0;

DROP event IF EXISTS e_delete_employee;
CREATE EVENT e_delete_employee
    ON SCHEDULE EVERY 1 minute
    DO
    DELETE FROM employee WHERE ID < 1000;

DROP VIEW  IF EXISTS view_employee;
CREATE VIEW view_employee AS SELECT ID, NAME FROM employee;

DROP TRIGGER IF EXISTS trigger_employee;
#CREATE TRIGGER trigger_employee AFTER INSERT ON employee FOR EACH ROW  'employee add success';

 

<?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.miller.mybatis.mapper.EmployeeMapper">

    <resultMap id="EmployeeResult" type="com.miller.mybatis.entity.Employee">
        <id property="id" column="ID" jdbcType="INTEGER" javaType="int"/>
        <result property="departmentName" column="DepartmentName" jdbcType="VARCHAR" javaType="String"/>
        <result property="name" column="Name" jdbcType="VARCHAR" javaType="String"/>
        <result property="age" column="Age" jdbcType="INTEGER" javaType="int"/>
    </resultMap>

    <select id="findEmployeeById" parameterType="int" resultMap="EmployeeResult">
        select * from employee where ID = #{id}
    </select>

    <select id="findAll" resultMap="EmployeeResult">
        select * from employee;
    </select>

    <delete id="deleteEmployee" parameterType="int" >
        DELETE FROM employee WHERE ID = #{id};
    </delete>

    <update id="updateEmployee" parameterType="Employee" >
        UPDATE employee SET DepartmentName = #{departmentName}, Name = #{name}, Age = #{age} WHERE ID = #{id}
    </update>
    <!--插入操作-->
    <insert id="addEmployee" parameterType="com.miller.mybatis.entity.Employee">
        INSERT INTO Employee(ID,DepartmentName,Name,Age) values(0,#{departmentName},#{name},#{age})
    </insert>
</mapper>

 

package com.miller.mybatis.mapper;


import com.miller.mybatis.entity.Employee;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmployeeMapper {
    //新增一条员工的记录
    int addEmployee(Employee employee);
    //更新一条员工的记录
    int updateEmployee(Employee e);
    //删除一条员工的记录
    int deleteEmployee(int id);
    //查询员工的记录by ID
    Employee findEmployeeById(int id);
    //返回员工列表
    List<Employee> findAll();
}

package com.miller.mybatis.entity;

import org.springframework.stereotype.Component;

import java.io.Serializable;

/**
 * @program: heigh-lever-mysql
 * @description: 员工类
 * @author: Miller.FAN
 * @create: 2019-10-31 19:02
 **/
@Component
public class Employee implements Serializable {
    private int id;
    private String departmentName;
    private String name;
    private int age;

    public Employee() {
    }

    public Employee(String departmentName, String name, int age) {
        this.departmentName = departmentName;
        this.name = name;
        this.age = age;
    }

    public Employee(int id, String departmentName, String name, int age) {
        this.id = id;
        this.departmentName = departmentName;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

package com.miller.mybatis.service;


import com.miller.mybatis.entity.Employee;
import com.miller.mybatis.mapper.EmployeeMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;

import static com.miller.mybatis.common.Common.*;


/**
 * @program: heigh-lever-mysql
 * @description: 员工服务层
 * @author: Miller.FAN
 * @create: 2019-10-31 19:57
 **/
@Service
public class EmployeeService {
    @Autowired
    EmployeeMapper employeeMapper;

    public HashMap<String,Object> queryEmployeeById(int id) {
        HashMap<String,Object> ret = new HashMap<>();
        Employee employee = employeeMapper.findEmployeeById(id);
        if(null != employee) {
            ret.put(SUCCESS,employee);
        }
        else {
            ret.put(ERROR,"查无结果");
        }
        return ret;
    }

    public HashMap<String,Object> queryAll() {
        HashMap<String,Object> ret = new HashMap<>();
        List<Employee> employee = employeeMapper.findAll();
        if(null != employee) {
            ret.put(SUCCESS,employee);
        }
        else {
            ret.put(ERROR,"查无结果");
        }
        return ret;
    }

    public HashMap<String,Object> deleteEmployeeById(int id) {
        HashMap<String,Object> ret = new HashMap<>();
        int ret_int = 0;
        if(null == employeeMapper.findEmployeeById(id)) {
            ret.put(ERROR, "欲删除的数据不存在，请检查您输入的参数");
        }
        else {
            ret_int = employeeMapper.deleteEmployee(id);
            ret.put(SUCCESS, ret_int);
        }
        return ret;
    }

    public HashMap<String,Object> updateEmployee(Employee e) {
        HashMap<String,Object> ret = new HashMap<>();
        int int_ret = employeeMapper.updateEmployee(e);
        ret.put(SUCCESS, "更新成功 " + int_ret);
        return ret;
    }

    public HashMap<String,Object> addEmployee(Employee e) {
        HashMap<String,Object> ret = new HashMap<>();
        int int_ret = employeeMapper.addEmployee(e);
        ret.put(SUCCESS, "添加成功 "  + int_ret );
        return ret;
    }
}

package com.miller.mybatis.controller;

import com.miller.mybatis.entity.Employee;
import com.miller.mybatis.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.HashMap;

/**
 * @program: heigh-lever-mysql
 * @description: 控制层
 * @author: Miller.FAN
 * @create: 2019-10-31 20:08
 **/
@RestController
@RequestMapping(path = "/employee")
public class EmployeeController {
    @Autowired
    private EmployeeService employeeService;

    @RequestMapping(value = "/query/by/{id}" , method = RequestMethod.GET)
    public HashMap<String,Object> queryById(@PathVariable int id) {
        return employeeService.queryEmployeeById(id);
    }

    @RequestMapping(value = "/query/all" , method = RequestMethod.GET)
    public HashMap<String,Object> queryById() {
        return employeeService.queryAll();
    }

    @RequestMapping(value = "/delete/by/{id}" , method = RequestMethod.DELETE)
    public HashMap<String,Object> deleteById(@PathVariable int id) {
        return employeeService.deleteEmployeeById(id);
    }

    @RequestMapping(value = "/update/employee" , method = RequestMethod.POST)
    public HashMap<String,Object> updateById(@RequestBody Employee e) {
        return employeeService.updateEmployee(e);
    }

    @RequestMapping(value = "/add/employee" , method = RequestMethod.POST)
    public HashMap<String,Object> addById(@RequestBody Employee e) {
        return employeeService.addEmployee(e);
    }
}

package com.miller.mybatis.common;

/**
 * @program: heigh-lever-mysql
 * @description: 常量
 * @author: Miller.FAN
 * @create: 2019-10-31 20:01
 **/
public class Common {
    public static final String SUCCESS = "OK";
    public static final String ERROR = "FAIL";
}

package com.miller.mybatis;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.miller.mybatis.mapper")
public class MybatisApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisApplication.class, args);
    }

}

 

spring:
  application:
    name: high-lever-mysql

  datasource:
    url: jdbc:mysql://localhost:3306/oracle?useUnicode=true&characterEncoding=gbk&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: ####
    driverClass: com.mysql.jdbc.Driver
    initialization-mode: always


mybatis:
  mapper-locations: classpath*:mapping/*.xml
  type-aliases-package: com.miller.mybatis.entity

logging:
  level:
    com:
      example:
        mapper : debug


server:
  port: 7985

整个项目实现了employee的增删改查等简单功能。