【MyBatis】学生表格操作

目录

文件目录

Maven依赖

配置application.properties

StudentMapper.java

Student.java

stu-mapper.xml

HomeworkApplicationTests.java 

运行结果

---

学生表：字段包括，id、学生学号、姓名、班级、生日日期，其中id为主键

请按照要求完成以下任务。

1） 编写实体类；

2） 编写接口；

接口中包括如下相关方法：

public void insertStudent(Student s);

public void deleteStudent(int stuId);

public void updateStudent(Student s);

public Student selectStudentById(int stuId);

public List<Student> selectStudent();//返回所有的学生信息

public int getCount();//返回记录条数

public Integer selectStudentMaxId ();//获取最大的id

public List<Student> selectStudent2(String startDate,String endDate);//查询生日指定日期范围的学生记录

public List<Student> selectStudentByCondition(Student s);//判断传入的属性，编写查询条件，用if

public List<Student> queryByInList(List ids);//编写foreach查询条件的功能ids为id列表

public void updateStudentSet(Student s);//用set来确定更新部分字段

public void updateStudentTrim(Student s);//用trim来确定更新部分字段

public void updateStudentWhere(Student s);//用where来确定更新部分字段

3）编写测试代码。

---

文件目录

Maven依赖

<dependencies>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</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</artifactId>
			<version>3.0.1</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.32</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.13.2</version>
			<scope>test</scope>
		</dependency>
	</dependencies>

 配置application.properties

# DataSourceProperties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/javaee?characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong
spring.datasource.username=root
spring.datasource.password=123456

# MybatisProperties
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.school.homework.entity
mybatis.configuration.useGeneratedKeys=true
mybatis.configuration.mapUnderscoreToCamelCase=true
mybatis.configuration.use-actual-param-name=true

# logger
logging.level.com.school.homework=debug

StudentMapper.java

@Mapper
public interface StudentMapper {
    public void insertStudent(Student s);

    public void deleteStudent(int stuId);

    public void updateStudent(Student s);

    public Student selectStudentById(int stuId);

    public List<Student> selectStudent();//返回所有的学生信息

    public int getCount();//返回记录条数

    public Integer selectStudentMaxId ();//获取最大的id

    public List<Student> selectStudent2(String startDate,String endDate);//查询生日指定日期范围的学生记录

    public List<Student> selectStudentByCondition(Student s);//判断传入的属性，编写查询条件，用if

    public List<Student> queryByInList(List ids);//编写foreach查询条件的功能ids为id列表

    public void updateStudentSet(Student s);//用set来确定更新部分字段

    public void updateStudentTrim(Student s);//用trim来确定更新部分字段

    public void updateStudentWhere(Student s);//用where来确定更新部分字段
}

Student.java

public class Student {
    private int id;
    private int stuId;
    private String name;
    private String classname;
    private Date birthday;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", stuId=" + stuId +
                ", name='" + name + '\'' +
                ", classname='" + classname + '\'' +
                ", birthday=" + birthday +
                '}';
    }
    public Student(){}
    public Student(int id,int stuId,String name,String classname,Date birthday){
        super();
        this.id=id;
        this.stuId=stuId;
        this.name=name;
        this.birthday=birthday;
        this.classname=classname;
    }
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getStuId() {
        return stuId;
    }

    public void setStuId(int stuId) {
        this.stuId = stuId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getClassname() {
        return classname;
    }

    public void setClassname(String classname) {
        this.classname = classname;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

stu-mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.school.homework.dao.StudentMapper">
    <sql id="selectFields">
        id,stu_id,name,classname,birthday
    </sql>
    <sql id="insertFields">
        stu_id,name,classname,birthday
    </sql>
    <select id="selectStudentById" resultType="Student">
        select <include refid="selectFields"></include>
        from student
        where stu_id=#{stuId}
    </select>

    <insert id="insertStudent" parameterType="Student" keyProperty="id">
        insert into student (<include refid="insertFields"></include>)
        values (#{stuId},#{name},#{classname},#{birthday})
    </insert>

    <update id="updateStudent">
        update student set stu_id=#{stuId},name=#{name},classname=#{classname},birthday=#{birthday} where id =#{id}
    </update>

    <select id="getCount" resultType="int">
        select count(*) from student
    </select>

    <select id="selectStudentMaxId" resultType="Integer">
        select max(id) from student
    </select>
    <select id="selectStudent" resultType="Student">
        select * from student
    </select>

    <select id="selectStudent2" resultType="Student">
        select * from student where
        <if test="startDate!=null">
            birthday>=#{startDate}
        </if>
        <if test="endDate!=null">
            <![CDATA[
           and birthday<=#{endDate}
          ]]>
        </if>
    </select>

    <select id="selectStudentByCondition" resultType="Student">
        select * from student where 1=1
        <if test="id!=null and id !=''">
            and id=#{id}
        </if>
        <if test="stuId!=null and stuId!=''">
            and stu_id=#{stuId}
        </if>
        <if test="name!=null and name!=''">
           and name=#{name}
        </if>
        <if test="classname!=null and classname!=''">
           and classname=#{classname}
        </if>
        <if test="birthday!=null">
           and birthday=#{birthday}
        </if>
    </select>

    <select id="queryByInList" resultType="Student">
        select * from student where stu_id in
        <foreach collection="list" item="i" open="(" separator="," close=")">
            #{i}
        </foreach>
    </select>

    <update id="updateStudentSet">
        update student
        <set>
            <if test="stuId!=null and stuId!=''">
                stu_id=#{stuId},
            </if>
            <if test="name!=null and name!=''">
                name=#{name},
            </if>
            <if test="classname!=null and classname!=''">
                classname=#{classname},
            </if>
            <if test="birthday!=null">
                birthday=#{birthday}
            </if>
        </set>
        where id =#{id}
    </update>

    <update id="updateStudentTrim" parameterType="Student">
        update student
        <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
            <if test="stuId!=null and stuId!=''">
                stu_id=#{stuId},
            </if>
            <if test="name!=null and name!=''">
                name=#{name},
            </if>
            <if test="classname!=null and classname!=''">
                classname=#{classname},
            </if>
            <if test="birthday!=null">
                birthday=#{birthday}
            </if>
        </trim>
    </update>

    <update id="updateStudentWhere" parameterType="Student">
        update student
        <set>

            <if test="name!=null and name!=''">
                name=#{name},
            </if>
            <if test="classname!=null and classname!=''">
                classname=#{classname},
            </if>

        </set>
        <where>
            <if test="id!=null and id !=''">
                and id=#{id}
            </if>
            <if test="stuId!=null and stuId!=''">
            stu_id=#{stuId}
            </if>
           <if test="birthday!=null">
           and birthday=#{birthday}
          </if>
    </where>
    </update>

    <delete id="deleteStudent" parameterType="Student">
        delete from student where stu_id=#{stuId}
    </delete>
</mapper>

HomeworkApplicationTests.java 

@RunWith(SpringRunner.class)
@SpringBootTest
@ContextConfiguration(classes = HomeworkApplication.class)
public class HomeworkApplicationTests {

	@Autowired
	private StudentMapper studentMapper;

	@Test
	public void testInsertStudent(){
		Student s=new Student();
		s.setStuId(27);
		s.setBirthday(new Date());
		s.setClassname("计科201");
		s.setName("小李同学");

		studentMapper.insertStudent(s);
		System.out.println(s);
	}

	@Test
	public void testDeleteStudent(){
		int sStuId=21;
		studentMapper.deleteStudent(sStuId);
		System.out.println(studentMapper.selectStudentById(21));
	}

	@Test
	public void testUpdateStudent() throws ParseException {
		Date date1 = new SimpleDateFormat("yyyy-MM-dd").parse("2002-01-05");
		Student s=new Student(1,19,"小李同学","计科201",date1);
		studentMapper.updateStudent(s);
	}

	@Test
	public void testSelectStudentById(){
		Student s=studentMapper.selectStudentById(19);
		System.out.println(s);
	}

	@Test
	public void testSelectStudent(){
		System.out.println(studentMapper.selectStudent());
	}

	@Test
	public void testGetCount(){
		System.out.println(studentMapper.getCount());
	}

	@Test
	public void testSelectStudentMaxId(){
		System.out.println(studentMapper.selectStudentMaxId());
	}

	@Test
	public void testSelectStudent2(){
		System.out.println(studentMapper.selectStudent2("2002-01-04","2023-03-28"));
	}

	@Test
	public void testSelectStudentByCondition() throws ParseException {
		Date date1 = new SimpleDateFormat("yyyy-MM-dd").parse("2002-01-03");
		Student s=new Student(1,19,"","",date1);
		System.out.println(studentMapper.selectStudentByCondition(s));
	}

	@Test
	public void testQueryByInList(){
		List ids=new ArrayList<>();
		ids.add(19);
		//ids.add(23);
		ids.add(21);
		List<Student> stus=studentMapper.queryByInList(ids);
		for (Student stu:stus) {
			System.out.println(stu);
		}
	}

	@Test
	public void testUpdateStudentSet() throws ParseException {
		Date date1 = new SimpleDateFormat("yyyy-MM-dd").parse("2002-01-08");
		Student s=new Student(4,0,"小h同学","医学117",date1);
		studentMapper.updateStudentSet(s);
		System.out.println(studentMapper.selectStudentById(s.getStuId()));
	}

	@Test
	public void testUpdateStudentTrim() throws ParseException {
		Student s=new Student(2,21,"王二虎","网络181",new SimpleDateFormat("yyyy-MM-dd").parse("2003-11-05"));
		studentMapper.updateStudentTrim(s);
		System.out.println(studentMapper.selectStudentById(s.getStuId()));
	}

	@Test
	public void testUpdateStudentWhere(){
		Student s=new Student(4,0,"王丽","建工138",null);
		studentMapper.updateStudentWhere(s);
		System.out.println(studentMapper.selectStudent());

	}


}

运行结果