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

使用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);
 }