mybatis 03: student表的CRUD操作

创建实体类Student，用来封装数据

• 对照数据表构建实体类

package com.example.pojo;

public class Student {
    private Integer id;
    private String name;
    private String email;
    private Integer age;

    public Student() {
    }

    public Student(Integer id, String name, String email, Integer age) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.age = age;
    }

    public Student(String name, String email, Integer age) {
        this.name = name;
        this.email = email;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }
}

• mybatis和java数据类型映射表

将StudentMapper.xml文件(添加到/src/main/resources/目录下)

• 通过mapper文件中的sql标签完成数据库的CRUD操作
• 必须在mybatis核心配置文件中注册mapper文件

查询全部学生信息

• sql标签示例

<!-- 映射文件的头信息 -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--
    mapper:是整个文件的大标签，用来开始和结束标签
    属性仅1个:
        namespace:指定命名空间(相当于包名)，用来区分不同的mapper.xml文件中相同的id属性
-->
<mapper namespace="wangxun">
    <!-- 完成查询全部学生的功能
         原先操作：List<Student> getAll();
         现在:
         通过namespace和id指定要操作的sql语句
         resultType:指定返回的查询结果集的类型，如果是集合，则必须是泛型 
		 且必须用该类型所在的具体包名来定位该类型(为了后期利用反射生成该类型的数据对象)
         parameterType:如果有参数，则通过他来指定参数的类型
    -->
    <select id="getAll" resultType="com.example.pojo.Student">
        select
            id, name, email, age
        from
            student
    </select>
</mapper>

• 测试示例

package com.example.test;

import com.example.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestStudent {
    @Test
    public void testStudent() throws IOException {
        //使用文件流获取核心配置文件SqlMapConfig.xml
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //取出sqlSession对象
        SqlSession sqlSession = factory.openSession();
        //完成查询操作
        List<Student> list = sqlSession.selectList("wangxun.getAll");
        list.forEach(System.out::println);
        //关闭sqlSession
        sqlSession.close();
    }
}

/*
输出结果:
Student{id=1, name='张三', email='zhangsan@126.com', age=22}
Student{id=2, name='李四', email='lisi@126.com', age=21}
Student{id=3, name='王五', email='wangwu@163.com', age=22}
Student{id=4, name='赵六', email='zhaoliun@qq.com', age=24}

Process finished with exit code 0
*/

sql标签分析

• 为什么resultType是实体类而不是集合？

  <select id="getAll" resultType="com.example.pojo.Student">
        select
              id, name, email, age
        from
              student
  </select>
  

  虽然最后的查询结果集是包含了所有实体对象的集合，但是考虑到数据表中的数据与实体对象的映射关系
  确保数据表中每条数据记录的语义，不能简单的把从数据表中查询的数据直接装到集合中
  必须将每条记录中的数据项，对应到实体类中的成员变量，以单个实体对象为单位，来进行数据封装
  最后将所有实体对象封装到集合中并返回

• mapper映射文件中的namespace存在的意义？

  • 为了区分不同mapper文件中id相同的sql标签，使得sql标签的使用唯一，无歧义

按照主键查询学生信息

• sql标签示例

    <!-- 按照主键查询学生信息
         传统方法:
         Student getById(Integer id);
    -->
    <select id="getById" parameterType="int" resultType="com.example.pojo.Student">
        select 
            id, name, email, age 
        from 
            student
        where 
            id=#{id}
    </select>

• 测试示例

    @Test
    public void testGetById() throws IOException {
        //读取核心配置文件
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //获取SqlSession
        SqlSession sqlSession = factory.openSession();
        //按主键查询
        Student student = sqlSession.selectOne("wangxun.getById", 3);
        System.out.println(student);
        //关闭SqlSession
        sqlSession.close();
    }
	
	/*
	输出结果:
	Student{id=3, name='王五', email='wangwu@163.com', age=22}

	Process finished with exit code 0
	*/

按照学生姓名模糊查询

• sql标签示例

    <!-- 按照学生姓名模糊查询
         传统方法:
         List<Student> getByName(String name);
    -->
    <select id="getByName" parameterType="string" resultType="com.example.pojo.Student">
        select
            id, name, email, age
        from
            student
        where 
            name like '%${name}%'
    </select>

• 测试示例

    @Test
    public void testGetByName() throws IOException {
        //读取核心配置文件
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //获取SqlSession
        SqlSession sqlSession = factory.openSession();
        //按照姓名模糊查询
        List<Student> students = sqlSession.selectList("wangxun.getByName", "三");
        students.forEach(System.out::println);
        //关闭SqlSession
        sqlSession.close();
    }
	
	/*
	输出结果:
	Student{id=1, name='张三', email='zhangsan@126.com', age=22}
	Student{id=5, name='吴三贵', email='masan@qq.com', age=70}

	Process finished with exit code 0
	*/

插入学生信息

• sql标签示例

    <!-- 插入学生数据
         传统方法:
         int insert(Student stu);
    -->
    <insert id="insert" parameterType="com.example.pojo.Student" >
        insert into
                student(name, email, age)
        values
                (#{name}, #{email}, #{age})
    </insert>

• 测试代码

    @Test
    public void testInsert() throws IOException {
        //读取核心配置文件
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //获取SqlSession
        SqlSession sqlSession = factory.openSession();
        //插入数据
        int num = sqlSession.insert("wangxun.insert", new Student("小涵", "0321@qq.com", 20));
        //切记：在所有的增删改后，必须手动提交
        sqlSession.commit();
        if (num == 1) {
            System.out.println("插入成功!");
        }else{
            System.out.println("插入失败!");
        }
        //关闭SqlSession
        sqlSession.close();
    }
	/*
	输出结果:
	插入成功!
	*/

切记

• 目前框架设置为事务手动提交
  • 在所有的增删改后：sqlSession.commit();

根据主键删除学生

• sql标签

    <!-- 根据主键删除学生
         int delete(Integer id);
    -->
    <delete id="delete" parameterType="int">
        delete from
                student
        where
            id=#{id}
    </delete>

• 测试代码

    @Test
    public void testDelete() throws IOException {
        //读取核心配置文件
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //获取SqlSession对象
        SqlSession sqlSession = factory.openSession();
        //执行删除语句
        int num = sqlSession.delete("wangxun.delete", 5);
        sqlSession.commit();
        if(num == 1){
            System.out.println("删除成功!");
        }else{
            System.out.println("删除失败!");
        }
        //关闭SqlSession
        sqlSession.close();
    }
	/*
	输出结果:
	删除成功!
	*/

根据主键更新学生信息

• sql标签示例

    <!-- 根据主键更新学生信息
         int update(Student stu);
    -->
    <update id="update" parameterType="com.example.pojo.Student">
        update
            student
        set
            name=#{name}, email=#{email}, age=#{age}
        where
            id=#{id}
    </update>

• 测试代码

    @Test
    public void testUpdate() throws IOException {
        //读取核心配置文件
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //获取SqlSession
        SqlSession sqlSession = factory.openSession();
        //执行更新语句
        int num = sqlSession.update("wangxun.update", new Student(1, "小何", "hehe@qq.com", 20));
        sqlSession.commit();
        if(num == 1){
            System.out.println("更新成功!");
        }else{
            System.out.println("更新失败!");
        }
        //关闭SqlSession
        sqlSession.close();
    }
	/*
	输出结果:
	更新成功!
	*/