[Java2023] Day9-增删改查基础操作

准备工作

• 1.数据表

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(32) DEFAULT '123456',
  `name` varchar(10) DEFAULT NULL,
  `gender` tinyint(1) unsigned DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `job` tinyint(3) unsigned DEFAULT NULL,
  `entrydate` datetime DEFAULT NULL,
  `dept_id` int(11) DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

• 2.准备工作

• 3.新建SpringBoot项目所需要的依赖

• 4.项目结构

二.代码

1. 实体类Emp.java

package com.emp01.pojo;

import lombok.Data;
import java.time.LocalDate;       // 导入正确的包
import java.time.LocalDateTime;   // 导入正确的包

@Data
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 deptId;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

2. 映射接口 EmpMapper

package com.emp01.mapper;

import com.emp01.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface EmpMapper {

    // 查询用户信息
    @Select("select * from emp")
    public List<Emp> list();

    // 根据ID删除数据
    @Delete("delete from emp where id=#{id}")
    public void delete(Integer id);

    // 增加信息
    @Options(keyProperty = "id", useGeneratedKeys = true) // 增加这个注解, 会自动生成主键值,赋值給emp对象的ID属性
    @Insert("INSERT INTO `emp` (`username`, `password`, `name`, `gender`, `image`, `job`, `entrydate`, `dept_id`, `create_time`, `update_time`) " +
            "VALUES (#{username}, #{password}, #{name}, #{gender}, #{image}, #{job}, #{entryDate}, #{deptId}, #{createTime}, #{updateTime})")
    public void insert(Emp emp);

    // 更新 (注意大小写. 正确:dept_id= #{deptId}, 错误:dept_id= #{dept_id},)
    @Update("UPDATE `emp` SET `username`= #{username}, `password`= #{password}, `name`= #{name}, `gender`= #{gender}, `image`= #{image}, `job`= #{job}, `entrydate`= #{entrydate}, `dept_id`= #{deptId} WHERE `id`= #{id}")
    public void update(Emp emp);

    // 查询.返回对象
    @Select("select * from emp where id = #{id}")
    public Emp getById(Integer id);


    // 根据条件查询
    @Select("select * from emp  where name like concat('%', #{name}, '%') and gender = #{gender} and " +
            "entrydate between #{begin} and #{end} order by update_time desc;")
    public List<Emp> check(String name, Short gender, LocalDate begin, LocalDate end);



}

3. 数据库配置文件

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/ssm
spring.datasource.username=root
spring.datasource.password=root

mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

4. 单元测试类

package com.emp01;

import com.emp01.mapper.EmpMapper;
import com.emp01.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

@SpringBootTest
class Emp01ApplicationTests {

    @Autowired  // 依赖注入
    private EmpMapper empMapper;

    // 测试删除
    @Test
    public void testDelete() {
        empMapper.delete(3);
    }

    /**
     * 如果报错, 一般是因为实体类中的属性漏写, 或者没有使用驼峰命名
     * */
    @Test
    public void testInsert() {
        Emp emp = new Emp();
        emp.setUsername("黄忠");
        emp.setName("黄忠");
        emp.setImage("1.jpg");
        emp.setGender((short)1);
        emp.setJob((short)1);
        emp.setEntrydate(LocalDate.of(2000, 1, 1));
        emp.setCreateTime(LocalDateTime.now());
        emp.setUpdateTime(LocalDateTime.now());
        emp.setDeptId(1);


        // 执行新增操作
        empMapper.insert(emp);

        // 获取新增的ID
        Integer empId = emp.getId();
        System.out.println("新增的员工ID:" + empId);
    }

    // 更新员工
    @Test
    public void testUpdate()
    {
        // 构造员工对象
        Emp emp = new Emp();
        emp.setId(6);
        emp.setUsername("Tom1");
        emp.setName("Tom1");
        emp.setImage("1.jpg");
        emp.setGender((short)1);
        emp.setJob((short)1);
        emp.setEntrydate(LocalDate.of(2020, 1, 1));
        emp.setCreateTime(LocalDateTime.now());
        emp.setUpdateTime(LocalDateTime.now());
        emp.setDeptId(1);

        empMapper.update(emp);
    }

    @Test
    public void testGetById()
    {
        Emp emp = empMapper.getById(5);
        System.out.println(emp);
    }

    @Test
    public void testCheck()
    {
        List<Emp> emp = empMapper.check("张", (short)1, LocalDate.of(2000, 1, 1), LocalDate.of(2030, 1, 1));
        System.out.println(emp);
    }
}

5. pom.xml

<?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.7.14</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.emp01</groupId>
    <artifactId>emp01</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>emp01</name>
    <description>emp01</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.1</version>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </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>2.3.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>