当前位置：首页 > article >正文

【mybatis-plus问题集锦系列】使用mybatis实现数据的基础增删改查

article 2025/1/7 15:02:50

使用mybatis实现数据的基础增删改查,简单的增删改查操作方法步骤

代码实现

pom.xml

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

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter-test</artifactId>
            <version>3.0.4</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

EmpMapper.interface

/**
 * @author gaofeng
 * @date 2025-01-04 - 18:02
 */
@Mapper
public interface EmpMapper {

    @Select("select * from tb_emp")
    public List<Emp> getAllEmpList();

    @Delete("delete from tb_emp where id = #{id}")
    public int deleteEmp(Integer id);
}

pojo.java

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDate;
import java.time.LocalDateTime;

/**
 * @author gaofeng
 * @date 2025-01-04 - 17:58
 */

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {
    private Integer id;
    private String username;
    private String password;
    private String name;
    private Short gender;
    private String image;
    private Short job;
    private LocalDate entrydate;
    private Integer geptId;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

application.properties文件

server.port=9090
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/javaweb
spring.datasource.username=root
spring.datasource.password=123456

# 控制台输出mybatis的日志信息
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

测试效果

查询所有数据

@Test
 public void getAllEmpList(){
     List<Emp> allEmpList = empMapper.getAllEmpList();
     allEmpList.stream().forEach(emp -> {
         System.out.println(emp);
     });
 }

在这里插入图片描述

根据id删除记录

@Test
public void deleteEmpById(){
    int count = empMapper.deleteEmp(17);
    System.out.println(count);
}

在这里插入图片描述

增加

@Test
public void insertEmp(){
    Emp emp = new Emp();
    emp.setUsername("Tom");
    emp.setName("汤姆");
    emp.setImage("1.jpg");
    emp.setGender((short)1);
    emp.setJob((short)1);
    emp.setEntrydate(LocalDate.of(2000,01,01));
    emp.setCreateTime(LocalDateTime.now());
    emp.setUpdateTime(LocalDateTime.now());
    emp.setDeptId(1);
    empMapper.insert(emp);
}

在这里插入图片描述

新增返回主键

	@Options(keyProperty = "id",useGeneratedKeys = true)  // 加上这个注解
    @Insert("insert into tb_emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time) VALUES\n" +
            "            (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")
    public void insert(Emp emp);

@Test
public void insertEmp(){
    Emp emp = new Emp();
    emp.setUsername("Tom2");
    emp.setName("汤姆2");
    emp.setImage("1.jpg");
    emp.setGender((short)1);
    emp.setJob((short)1);
    emp.setEntrydate(LocalDate.of(2000,01,01));
    emp.setCreateTime(LocalDateTime.now());
    emp.setUpdateTime(LocalDateTime.now());
    emp.setDeptId(1);
    empMapper.insert(emp);

    System.out.println(emp.getId());
}

在这里插入图片描述这样在数据库中就对应上了

在这里插入图片描述

更新

    @Update("update tb_emp set username = #{username},name = #{name},gender = #{gender},\n" +
            "                  image=#{image},job=#{job},entrydate=#{entrydate},dept_id=#{deptId},\n" +
            "                  update_time=#{updateTime} where id = #{id}")
    public void update(Emp emp);

 @Test
  public void updateEmp(){
      Emp emp = new Emp();
      emp.setId(18);
      emp.setUsername("Tom3");
      emp.setName("汤姆3");
      emp.setImage("1.jpg");
      emp.setGender((short)1);
      emp.setJob((short)1);
      emp.setEntrydate(LocalDate.of(2000,01,01));
      emp.setUpdateTime(LocalDateTime.now());
      emp.setDeptId(1);
      empMapper.update(emp);
//        System.out.println(emp.getId());
  }

在这里插入图片描述

根据id查询

  @Select("select * from tb_emp where id = #{id}")
  public Emp getById(Integer id);

// 根据id
  @Test
  public  void getById(){
      Emp emp = empMapper.getById(19);
      System.out.println(emp);
  }

在这里插入图片描述

解决方法一：就是起别名，需要列出所有的字段，不能用*代替了
解决方法二：使用Results注解

 	@Results({
           @Result(column = "dept_id",property = "deptId"),
           @Result(column = "create_time",property = "createTime"),
           @Result(column = "update_time",property = "updateTime")
   })
   @Select("select * from tb_emp where id = #{id}")
   public Emp getById(Integer id);

在这里插入图片描述
解决方法三：使用mybatis的配置，开启识别驼峰命名

mybatis.configuration.map-underscore-to-camel-case = true

条件查询
- 方式1

 @Select("select * from tb_emp where name like '%${name}%' and gender = #{gender}\n" +
           "and entrydate between #{begin} and #{end} order by update_time desc;\n")
 public List<Emp> listEmp(String name,Short gender, LocalDate begin,LocalDate end);

方式2

 @Select("select * from tb_emp where name like concat('%',#{name},'%') and gender = #{gender}\n" +
           "and entrydate between #{begin} and #{end} order by update_time desc;\n")
   public List<Emp> listEmp(String name,Short gender, LocalDate begin,LocalDate end);

@Test
 public void testList(){
     List<Emp> empList = empMapper.listEmp("张",(short)1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
     System.out.println(empList);
 }