SpringBoot整合mybatis实现增删改查

淡淡的烟草味﹌ 2024-03-23 16:37 38阅读 0赞

步骤：  
1、创建项目；  
2、添加mybatis依赖；  
3、在application.yml进行数据源属性配置；  
4、定义数据源对象与配置文件中前缀是spring.datasource的配置进行绑定；  
5、创建数据表（department，employee）;  
6、创建实体类；  
7、创建mapper接口；  
8、编写controller处理请求，调用mapper对象执行查询方法。

**1.创建项目**

![dad619768480446a91f7928870b22dfb.png][]

**项目结构：**

![a98fd8441137416789ef8d31e6047194.png][]

**2，添加Druid依赖**

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.10</version>
            </dependency>

**application.yml**

#端口号
    server:
      port: 8080
    
      #数据源配置
    spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/myjdbc212?characterEncoding=utf-8&serverTimezone=UTC
        username: root
        password: 123456
          #sql:
          #init:
          #指定脚本文件位置
          #schema-locations: classpath:user.sql
          #初始化方式
        #mode: always
        #设置数据源类型C
        type: com.alibaba.druid.pool.DruidDataSource
    mybatis:
      configuration:
         map-underscore-to-camel-case: true
    #mybatis:
      #指定mybatis配置文件的位置
      #config-location: classpath:mybatis/mybatis-config.xml
      #指定映射文件的位置
      mapper-locations: classpath:mybatis/mapper/*.xml

**3，config包下**

DruidConfig类

@Configuration
    public class DruidConfig {
        @ConfigurationProperties(prefix = "spring.datasource")
        @Bean
        public DruidDataSource getDurid(){
            return new DruidDataSource();
        }
    }

MyBatisConfig类

@Configuration
    public class MyBatisConfig {
    
    
    
        public ConfigurationCustomizer configurationCustomizer(){
            return new ConfigurationCustomizer() {
                @Override
                public void customize(org.apache.ibatis.session.Configuration configuration) {
                    //下划线与驼峰命名进行自动映射
                    configuration.setMapUnderscoreToCamelCase(true);
                }
            };
        }
    }

**4，controller类**

DepartmentController类：

@RestController
    public class DepartmentController {
        @Autowired
        private DepartmentMapper departmentMapper;
    
        //根据id查询部门
        @RequestMapping("/dept/{id}")
    
        public Department getDepById(@PathVariable("id") Integer deptId){
            //调用mapper接口中的方法
            Department deptById = departmentMapper.getDeptById(deptId);
            return deptById;
    
        }
        //添加
        @RequestMapping("/dept")
        public int addDept(Department department){
            int count = departmentMapper.insertDept(department);
            return count;
        }
        @RequestMapping("/update/dept/{id}")
        public int update(Department department){
            int count = departmentMapper.updateDept(department);
            return count;
        }
        @RequestMapping("/delete/dept/{id}")
        public int deleteDept(@PathVariable("id")Integer id){
            int count = departmentMapper.deleteDept(id);
            return count;
        }
    }

EmpController类

@RestController
    public class EmpController {
        @Autowired
        private EmployeeMapper employeeMapper;
        @RequestMapping("/emp/{id}")
        public Employee getEmpById(@PathVariable("id") Integer empId){
            Employee employee = employeeMapper.queryEmpById(empId);
            return employee;
        }
        @RequestMapping("/emp")
        public int insertEmp(Employee employee){
            int count = employeeMapper.insertEmp(employee);
            return count;
        }
    
        @RequestMapping("/emp/update/{id}")
        public int update(Employee employee,@PathVariable("id") Integer id){
            int count = employeeMapper.update(employee, id);
            return count;
        }
    
        @RequestMapping("/emp/delete/{id}")
        public int deleteEmp(@PathVariable("id")Integer id){
            int count = employeeMapper.deleteEmpById(id);
            return count;
        }

**5，实体类entity：**

**Department类：**

package com.dzqc.entity;
    
    public class Department {
        private Integer id;
        private String departmentName;
    
        public Department() {
        }
    
        public Department(int i, String string) {
            this.id = i;
            this.departmentName = string;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getDepartmentName() {
            return departmentName;
        }
    
        public void setDepartmentName(String departmentName) {
            this.departmentName = departmentName;
        }
    
        @Override
        public String toString() {
            return "Department [id=" + id + ", departmentName=" + departmentName + "]";
        }
    }

**Employee类：**

package com.dzqc.entity;
    
    import java.util.Date;
    
    public class Employee {
    
    	private Integer id;
        private String lastName;
    
        private String email;
        //1 male, 0 female
        private Integer gender;
        private Department department;
        public Employee() {
        }
    
        public Employee(Integer id, String lastName, String email, Integer gender, Department department, Integer did) {
            this.id = id;
            this.lastName = lastName;
            this.email = email;
            this.gender = gender;
            this.department = department;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getLastName() {
            return lastName;
        }
    
        public void setLastName(String lastName) {
            this.lastName = lastName;
        }
    
        public String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    
        public Integer getGender() {
            return gender;
        }
    
        public void setGender(Integer gender) {
            this.gender = gender;
        }
    
        public Department getDepartment() {
            return department;
        }
    
        public void setDepartment(Department department) {
            this.department = department;
        }
    
    
        @Override
        public String toString() {
            return "Employee{" +
                    "id=" + id +
                    ", lastName='" + lastName + '\'' +
                    ", email='" + email + '\'' +
                    ", gender=" + gender +
                    ", department=" + department +
                    '}';
        }
    }

**6，mapper包下**

**DepartmentMapper：**

package com.dzqc.mapper;
    
    import com.dzqc.entity.Department;
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Select;
    import org.apache.ibatis.annotations.Update;
    import org.springframework.stereotype.Repository;
    
    //可以在mapper接口中使用注解编写sql语句，访问数据库进行操作
    //可以在启动类前使用@MapperScane扫描指定包下所有的mapper接口
    @Mapper
    @Repository
    public interface DepartmentMapper {
        //根据id查询部门
        @Select("select*from department where id=#{deptId}")
        public Department getDeptById(Integer deptId);
    
        //插入部门
        @Insert("insert into department(department_name) values(#{departmentName})")
        public int insertDept(Department department);
        //更新
        @Update("update department set department_name=#{departmentName} where id=#{id}")
        public int updateDept(Department department);
        //删除
        @Update("delete from department where id=#{deptId}")
        public int deleteDept(Integer deptId);
    }

**EmpoyeeMapper类：**

@Mapper
    @Repository
    public interface EmployeeMapper {
    
        //查询
        Employee queryEmpById(Integer empId);
        //添加
        int insertEmp(Employee employee);
    
        //更新
        int update(@PathVariable("employee") Employee employee, @Param("id") Integer id);
    
        //删除
        int deleteEmpById(Integer id);
    }

![524dca6a7bcb49ce9c5729d1ba61e65a.png][]

7，创建EmployeeMapper要mybatis/mapper/EmployeeMapper

**EmployeeMapper.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">
    
    
    <mapper namespace="com.dzqc.mapper.EmployeeMapper">
        <resultMap id="EmpResultMap" type="com.dzqc.entity.Employee">
            <id column="id" property="id"></id>
            <result column="last_name" property="lastName"></result>
            <result column="email" property="email"></result>
            <result column="gender" property="gender"></result>
            <association property="department" javaType="com.dzqc.entity.Department">
                <id column="id" property="id"></id>
                <result column="department_name" property="departmentName"></result>
            </association>
    
        </resultMap>
        
        <select id="queryEmpById" resultMap="EmpResultMap">
            select*from employee where id=#{empId}
        </select>
        
        <insert id="insertEmp">
                insert into employee values (#{id},#{lastName},#{email},#{gender},#{department.id})
        </insert>
    
        <update id="update">
            update employee
            <set>
                <if test="employee.lastName!=null">
                    last_name=#{employee.lastName},
                </if>
                <if test="employee.email!=null">
                    email=#{employee.email},
                </if>
                <if test="employee.gender!=null">
                    gender=#{employee.gender},
                </if>
                <if test="employee.department!=null">
                    d_id=#{employee.department.id}
                </if>
            </set>
            where id = #{id}
        </update>
        
        <delete id="deleteEmpById">
            delete from employee where id=#{id}
        </delete>
    </mapper>

**mybatis-config.xml**

<?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>
            
            <setting name="mapUnderscoreToCamelCase" value="true"/>
        </settings>
    
    </configuration>

![8d74de7be3cd4a8dabd3fd279c170fee.jpeg][]![b16d9685267f473e87eba97f39c3fe1a.jpeg][]![299e989d7ad34d70a37c750530300da8.jpeg][]![8379a3830e3a4ce49ab3b62277e31efa.jpeg][]

![36a005ea9cfa46dba542080b2ffee8d6.jpeg][]![6983a9f8161a4bf1a34754a4bf2b4fc6.jpeg][]![cff9f25547f54df685659d12191fa9d0.jpeg][]![15c0f6dc821c4072987b7382a0142b26.jpeg][]

数据库：department

/*
    Navicat MySQL Data Transfer
    
    Source Server         : db
    Source Server Version : 50735
    Source Host           : localhost:3306
    Source Database       : myjdbc212
    
    Target Server Type    : MYSQL
    Target Server Version : 50735
    File Encoding         : 65001
    
    Date: 2023-04-23 16:06:49
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for department
    -- ----------------------------
    DROP TABLE IF EXISTS `department`;
    CREATE TABLE `department` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `department_name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of department
    -- ----------------------------
    INSERT INTO `department` VALUES ('1', 'A');
    INSERT INTO `department` VALUES ('3', 'E');
    INSERT INTO `department` VALUES ('4', null);

employee

/*
    Navicat MySQL Data Transfer
    
    Source Server         : db
    Source Server Version : 50735
    Source Host           : localhost:3306
    Source Database       : myjdbc212
    
    Target Server Type    : MYSQL
    Target Server Version : 50735
    File Encoding         : 65001
    
    Date: 2023-04-13 16:02:30
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for employee
    -- ----------------------------
    DROP TABLE IF EXISTS `employee`;
    CREATE TABLE `employee` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `last_name` varchar(255) DEFAULT NULL,
      `email` varchar(255) DEFAULT NULL,
      `gender` int(2) DEFAULT NULL,
      `birth` date DEFAULT NULL,
      `d_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of employee
    -- ----------------------------

[dad619768480446a91f7928870b22dfb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/9744c50d341f470c80ff3dc84968b53e.png
[a98fd8441137416789ef8d31e6047194.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/4f8fa4e8f32f4faaac691e85c03d19bc.png
[524dca6a7bcb49ce9c5729d1ba61e65a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/343de8a68e3f495bbdb9ff59fed33442.png
[8d74de7be3cd4a8dabd3fd279c170fee.jpeg]: https://img-blog.csdnimg.cn/8d74de7be3cd4a8dabd3fd279c170fee.jpeg
[b16d9685267f473e87eba97f39c3fe1a.jpeg]: https://img-blog.csdnimg.cn/b16d9685267f473e87eba97f39c3fe1a.jpeg
[299e989d7ad34d70a37c750530300da8.jpeg]: https://img-blog.csdnimg.cn/299e989d7ad34d70a37c750530300da8.jpeg
[8379a3830e3a4ce49ab3b62277e31efa.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/8e43564f1903493ea5c5f4f2e924ac25.png
[36a005ea9cfa46dba542080b2ffee8d6.jpeg]: https://img-blog.csdnimg.cn/36a005ea9cfa46dba542080b2ffee8d6.jpeg
[6983a9f8161a4bf1a34754a4bf2b4fc6.jpeg]: https://img-blog.csdnimg.cn/6983a9f8161a4bf1a34754a4bf2b4fc6.jpeg
[cff9f25547f54df685659d12191fa9d0.jpeg]: https://img-blog.csdnimg.cn/cff9f25547f54df685659d12191fa9d0.jpeg
[15c0f6dc821c4072987b7382a0142b26.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/5965403b50104d07b36c0b3ac089400f.png