SpringBoot整合mybatis实现增删改查

淡淡的烟草味﹌ 2024-03-23 16:37 171阅读 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依赖

  1. <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
  2.         <dependency>
  3.             <groupId>com.alibaba</groupId>
  4.             <artifactId>druid</artifactId>
  5.             <version>1.1.10</version>
  6.         </dependency>

application.yml

  1. #端口号
  2. server:
  3.   port: 8080
  5.   #数据源配置
  6. spring:
  7.   datasource:
  8.     driver-class-name: com.mysql.cj.jdbc.Driver
  9.     url: jdbc:mysql://localhost:3306/myjdbc212?characterEncoding=utf-8&serverTimezone=UTC
  10.     username: root
  11.     password: 123456
  12.       #sql:
  13.       #init:
  14.       #指定脚本文件位置
  15.       #schema-locations: classpath:user.sql
  16.       #初始化方式
  17.     #mode: always
  18.     #设置数据源类型C
  19.     type: com.alibaba.druid.pool.DruidDataSource
  20. mybatis:
  21.   configuration:
  22.      map-underscore-to-camel-case: true
  23. #mybatis:
  24.   #指定mybatis配置文件的位置
  25.   #config-location: classpath:mybatis/mybatis-config.xml
  26.   #指定映射文件的位置
  27.   mapper-locations: classpath:mybatis/mapper/*.xml

3，config包下

DruidConfig类

  1. @Configuration
  2. public class DruidConfig {
  3.     @ConfigurationProperties(prefix = "spring.datasource")
  4.     @Bean
  5.     public DruidDataSource getDurid(){
  6.         return new DruidDataSource();
  7.     }
  8. }

MyBatisConfig类

  1. @Configuration
  2. public class MyBatisConfig {
  6.     public ConfigurationCustomizer configurationCustomizer(){
  7.         return new ConfigurationCustomizer() {
  8.             @Override
  9.             public void customize(org.apache.ibatis.session.Configuration configuration) {
  10.                 //下划线与驼峰命名进行自动映射
  11.                 configuration.setMapUnderscoreToCamelCase(true);
  12.             }
  13.         };
  14.     }
  15. }

4，controller类

DepartmentController类：

  1. @RestController
  2. public class DepartmentController {
  3.     @Autowired
  4.     private DepartmentMapper departmentMapper;
  6.     //根据id查询部门
  7.     @RequestMapping("/dept/{id}")
  9.     public Department getDepById(@PathVariable("id") Integer deptId){
  10.         //调用mapper接口中的方法
  11.         Department deptById = departmentMapper.getDeptById(deptId);
  12.         return deptById;
  14.     }
  15.     //添加
  16.     @RequestMapping("/dept")
  17.     public int addDept(Department department){
  18.         int count = departmentMapper.insertDept(department);
  19.         return count;
  20.     }
  21.     @RequestMapping("/update/dept/{id}")
  22.     public int update(Department department){
  23.         int count = departmentMapper.updateDept(department);
  24.         return count;
  25.     }
  26.     @RequestMapping("/delete/dept/{id}")
  27.     public int deleteDept(@PathVariable("id")Integer id){
  28.         int count = departmentMapper.deleteDept(id);
  29.         return count;
  30.     }
  31. }

EmpController类

  1. @RestController
  2. public class EmpController {
  3.     @Autowired
  4.     private EmployeeMapper employeeMapper;
  5.     @RequestMapping("/emp/{id}")
  6.     public Employee getEmpById(@PathVariable("id") Integer empId){
  7.         Employee employee = employeeMapper.queryEmpById(empId);
  8.         return employee;
  9.     }
  10.     @RequestMapping("/emp")
  11.     public int insertEmp(Employee employee){
  12.         int count = employeeMapper.insertEmp(employee);
  13.         return count;
  14.     }
  16.     @RequestMapping("/emp/update/{id}")
  17.     public int update(Employee employee,@PathVariable("id") Integer id){
  18.         int count = employeeMapper.update(employee, id);
  19.         return count;
  20.     }
  22.     @RequestMapping("/emp/delete/{id}")
  23.     public int deleteEmp(@PathVariable("id")Integer id){
  24.         int count = employeeMapper.deleteEmpById(id);
  25.         return count;
  26.     }

5，实体类entity：

Department类：

  1. package com.dzqc.entity;
  3. public class Department {
  4.     private Integer id;
  5.     private String departmentName;
  7.     public Department() {
  8.     }
  10.     public Department(int i, String string) {
  11.         this.id = i;
  12.         this.departmentName = string;
  13.     }
  15.     public Integer getId() {
  16.         return id;
  17.     }
  19.     public void setId(Integer id) {
  20.         this.id = id;
  21.     }
  23.     public String getDepartmentName() {
  24.         return departmentName;
  25.     }
  27.     public void setDepartmentName(String departmentName) {
  28.         this.departmentName = departmentName;
  29.     }
  31.     @Override
  32.     public String toString() {
  33.         return "Department [id=" + id + ", departmentName=" + departmentName + "]";
  34.     }
  35. }

Employee类：

  1. package com.dzqc.entity;
  3. import java.util.Date;
  5. public class Employee {
  7.     private Integer id;
  8.     private String lastName;
  10.     private String email;
  11.     //1 male, 0 female
  12.     private Integer gender;
  13.     private Department department;
  14.     public Employee() {
  15.     }
  17.     public Employee(Integer id, String lastName, String email, Integer gender, Department department, Integer did) {
  18.         this.id = id;
  19.         this.lastName = lastName;
  20.         this.email = email;
  21.         this.gender = gender;
  22.         this.department = department;
  23.     }
  25.     public Integer getId() {
  26.         return id;
  27.     }
  29.     public void setId(Integer id) {
  30.         this.id = id;
  31.     }
  33.     public String getLastName() {
  34.         return lastName;
  35.     }
  37.     public void setLastName(String lastName) {
  38.         this.lastName = lastName;
  39.     }
  41.     public String getEmail() {
  42.         return email;
  43.     }
  45.     public void setEmail(String email) {
  46.         this.email = email;
  47.     }
  49.     public Integer getGender() {
  50.         return gender;
  51.     }
  53.     public void setGender(Integer gender) {
  54.         this.gender = gender;
  55.     }
  57.     public Department getDepartment() {
  58.         return department;
  59.     }
  61.     public void setDepartment(Department department) {
  62.         this.department = department;
  63.     }
  66.     @Override
  67.     public String toString() {
  68.         return "Employee{" +
  69.                 "id=" + id +
  70.                 ", lastName='" + lastName + '\'' +
  71.                 ", email='" + email + '\'' +
  72.                 ", gender=" + gender +
  73.                 ", department=" + department +
  74.                 '}';
  75.     }
  76. }

6，mapper包下

DepartmentMapper：

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

EmpoyeeMapper类：

  1. @Mapper
  2. @Repository
  3. public interface EmployeeMapper {
  5.     //查询
  6.     Employee queryEmpById(Integer empId);
  7.     //添加
  8.     int insertEmp(Employee employee);
  10.     //更新
  11.     int update(@PathVariable("employee") Employee employee, @Param("id") Integer id);
  13.     //删除
  14.     int deleteEmpById(Integer id);
  15. }

524dca6a7bcb49ce9c5729d1ba61e65a.png

7，创建EmployeeMapper要mybatis/mapper/EmployeeMapper

EmployeeMapper.xml

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <!--MyBatis的映射文件 编写sql-->
  6. <!--一个数据表对应一个实体类，一个实体类对应一个mapper映射文件-->
  7. <mapper namespace="com.dzqc.mapper.EmployeeMapper">
  8.     <resultMap id="EmpResultMap" type="com.dzqc.entity.Employee">
  9.         <id column="id" property="id"></id>
  10.         <result column="last_name" property="lastName"></result>
  11.         <result column="email" property="email"></result>
  12.         <result column="gender" property="gender"></result>
  13.         <association property="department" javaType="com.dzqc.entity.Department">
  14.             <id column="id" property="id"></id>
  15.             <result column="department_name" property="departmentName"></result>
  16.         </association>
  18.     </resultMap>
  19.     <!--Employee queryEmpById(@Param("empId") Integer id);-->
  20.     <select id="queryEmpById" resultMap="EmpResultMap">
  21.         select*from employee where id=#{empId}
  22.     </select>
  23.     <!--int insertEmp(Employee employee);-->
  24.     <insert id="insertEmp">
  25.             insert into employee values (#{id},#{lastName},#{email},#{gender},#{department.id})
  26.     </insert>
  28.     <update id="update">
  29.         update employee
  30.         <set>
  31.             <if test="employee.lastName!=null">
  32.                 last_name=#{employee.lastName},
  33.             </if>
  34.             <if test="employee.email!=null">
  35.                 email=#{employee.email},
  36.             </if>
  37.             <if test="employee.gender!=null">
  38.                 gender=#{employee.gender},
  39.             </if>
  40.             <if test="employee.department!=null">
  41.                 d_id=#{employee.department.id}
  42.             </if>
  43.         </set>
  44.         where id = #{id}
  45.     </update>
  46.     <!--int deleteEmpById(Integer id);-->
  47.     <delete id="deleteEmpById">
  48.         delete from employee where id=#{id}
  49.     </delete>
  50. </mapper>

mybatis-config.xml

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE configuration
  3.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <!--根标签-->
  6. <configuration>
  7.     <!--全局设置-->
  8.     <settings>
  9.         <!--开启驼峰命名自动映射  即下划线自动映射成驼峰命名-->
  10.         <setting name="mapUnderscoreToCamelCase" value="true"/>
  11.     </settings>
  13. </configuration>

8d74de7be3cd4a8dabd3fd279c170fee.jpegb16d9685267f473e87eba97f39c3fe1a.jpeg299e989d7ad34d70a37c750530300da8.jpeg8379a3830e3a4ce49ab3b62277e31efa.jpeg

36a005ea9cfa46dba542080b2ffee8d6.jpeg6983a9f8161a4bf1a34754a4bf2b4fc6.jpegcff9f25547f54df685659d12191fa9d0.jpeg15c0f6dc821c4072987b7382a0142b26.jpeg

数据库：department

  1. /*
  2. Navicat MySQL Data Transfer
  4. Source Server         : db
  5. Source Server Version : 50735
  6. Source Host           : localhost:3306
  7. Source Database       : myjdbc212
  9. Target Server Type    : MYSQL
  10. Target Server Version : 50735
  11. File Encoding         : 65001
  13. Date: 2023-04-23 16:06:49
  14. */
  16. SET FOREIGN_KEY_CHECKS=0;
  18. -- ----------------------------
  19. -- Table structure for department
  20. -- ----------------------------
  21. DROP TABLE IF EXISTS `department`;
  22. CREATE TABLE `department` (
  23.   `id` int(11) NOT NULL AUTO_INCREMENT,
  24.   `department_name` varchar(255) DEFAULT NULL,
  25.   PRIMARY KEY (`id`)
  26. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  28. -- ----------------------------
  29. -- Records of department
  30. -- ----------------------------
  31. INSERT INTO `department` VALUES ('1', 'A');
  32. INSERT INTO `department` VALUES ('3', 'E');
  33. INSERT INTO `department` VALUES ('4', null);

employee

  1. /*
  2. Navicat MySQL Data Transfer
  4. Source Server         : db
  5. Source Server Version : 50735
  6. Source Host           : localhost:3306
  7. Source Database       : myjdbc212
  9. Target Server Type    : MYSQL
  10. Target Server Version : 50735
  11. File Encoding         : 65001
  13. Date: 2023-04-13 16:02:30
  14. */
  16. SET FOREIGN_KEY_CHECKS=0;
  18. -- ----------------------------
  19. -- Table structure for employee
  20. -- ----------------------------
  21. DROP TABLE IF EXISTS `employee`;
  22. CREATE TABLE `employee` (
  23.   `id` int(11) NOT NULL AUTO_INCREMENT,
  24.   `last_name` varchar(255) DEFAULT NULL,
  25.   `email` varchar(255) DEFAULT NULL,
  26.   `gender` int(2) DEFAULT NULL,
  27.   `birth` date DEFAULT NULL,
  28.   `d_id` int(11) DEFAULT NULL,
  29.   PRIMARY KEY (`id`)
  30. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  32. -- ----------------------------
  33. -- Records of employee
  34. -- ----------------------------

发表评论

表情：
评论列表 （有 0 条评论，171人围观）

还没有评论，来说两句吧...

相关阅读