2、简单测试

  • 新建 Student
package bean;

/**
 * TODO
 *
 * @author asus
 * @version 1.0
 * @date 2021/1/16 11:04
 */
public class Student {
    private Integer id;
    private String sname;
    private String address;
    private String sex;
    private Integer tid;

    public Student() {

    }

    public Student(String sname, String address, String sex, Integer tid) {
        this.sname = sname;
        this.address = address;
        this.sex = sex;
        this.tid = tid;
    }

    public Student(Integer id, String sname, String address, String sex, Integer tid) {
        this.id = id;
        this.sname = sname;
        this.address = address;
        this.sex = sex;
        this.tid = tid;
    }

    ...
}

  • 新建参数类
package bean.params;

/**
 * TODO
 *
 * @author asus
 * @version 1.0
 * @date 2021/1/16 22:06
 */
public class StudentParams {
    private String sname;
    private String sex;
    private Integer tid_begin;
    private Integer tid_end;

    public StudentParams() {
    }

    public StudentParams(String sname, String sex, Integer tid_begin, Integer tid_end) {
        this.sname = sname;
        this.sex = sex;
        this.tid_begin = tid_begin;
        this.tid_end = tid_end;
    }

   ...
}

  • 新建 StudentMapper.xml
<?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 namespace="bean.Student">
    <!--
        mybaits通过自己手动写sql语句来对数据库进行操作,select就是查询操作
        同样有update,delete,insert
        id:标识符,测试时调用的标识符来调用sql语句,相当于方法名
        resultType:返回值类型
        parameterType:传入的参数类型
        如果是引用数据类型的话,需要传入完整的路径.如:java.lang.String以及对象com.oralc.pojo.Student
        在下方的条件需要以#{}来占位,如果传入类型是对象型,需要与对象的属性名一致
     -->
    <resultMap id="student_info" type="java.util.HashMap">
        <result column="code" property="key"></result>
        <result column="name" property="value"></result>
    </resultMap>

    <select id="getStudentMap" resultType="java.util.HashMap">
        select * from student a where a.id = #{id}
    </select>

    <select id="findStudentById" resultType="bean.Student" parameterType="int">
        select * from student where id=#{id}
    </select>

    <select id="findAll" resultType="bean.Student">
        select * from student
    </select>

    <select id="findWithParameters" resultType="bean.Student" parameterType="bean.params.StudentParams">
        select * from student a where 1 = 1
        <if test="sname != null and sname != ''">
            and a.sname like concat('%', #{sname}, '%')
        </if>

        <if test="sex != null and sex != ''">
            and a.sex = #{sex}
        </if>

        <if test="tid_begin != null and tid_begin != ''">
            and a.tid <![CDATA[>= ]]> #{tid_begin}
        </if>

        <if test="tid_end != null and tid_end != ''">
            and a.tid <![CDATA[<= ]]> #{tid_end}
        </if>
    </select>

    <select id="queryList" resultType="java.util.HashMap">
        select * from student limit 2
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="bean.Student">
        insert into student
            (sname, address, sex, tid)
        values (#{sname}, #{address}, #{sex}, #{tid})
    </insert>

    <insert id="insertMore" parameterType="java.util.List" >
        insert into student
            (sname, address, sex, tid)
        values
        <foreach collection="list" item="student" separator=",">
            (
             #{student.sname, jdbcType=VARCHAR},
            #{student.address, jdbcType=VARCHAR},
            #{student.sex, jdbcType=VARCHAR},
            #{student.tid, jdbcType=INTEGER}
            )
        </foreach>
    </insert>

    <update id="updateById" parameterType="bean.Student">
        update student a
        <set>
            <if test="sname != null">a.sname=#{sname},</if>
            <if test="address != null">a.address=#{address},</if>
            <if test="sex != null">a.sex=#{sex},</if>
            <if test="tid != null">a.tid=#{tid},</if>
        </set>
        where a.id = #{id}
    </update>


</mapper>
  • 新建公共类
package util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;

/**
 * TODO
 *
 * @author asus
 * @version 1.0
 * @date 2021/1/16 15:24
 */
public class MybatisUtil {
    private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            // 读取mybatis核心配置文件
            Reader reader = Resources.getResourceAsReader("mappers/mysql.xml");
            // 创建会话工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static SqlSession getSession() {
        // 获取会话
        SqlSession sqlSession = threadLocal.get();
        if (sqlSession == null) {
            // 从会话工厂获取session
            sqlSession = sqlSessionFactory.openSession();
            // 绑定会话工厂
            threadLocal.set(sqlSession);
        }
        return sqlSession;
    }

    public static void close() {
        SqlSession sqlSession = threadLocal.get();
        if (sqlSession != null) {
            sqlSession.close();
        }
    }

    /**
     * 重新加载xml
     */
    public void refreshMapper() {
        Configuration configuration = this.sqlSessionFactory.getConfiguration();
    }
}

  • 新建测试类
import bean.Student;
import bean.params.StudentParams;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import util.MybatisUtil;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * TODO
 *
 * @author asus
 * @version 1.0
 * @date 2021/1/16 15:27
 */
public class Test {
    private SqlSession session = null;

    @Before
    public void before() {
        //获取会话
        session = MybatisUtil.getSession();
    }

    @org.junit.Test
    public void insert() {
        /**
         * 从会话中调用映射文件中的sql语句,为了防止不同的映射文件标识符重复,通过namespace.标识符来调用
         * 如果后面有参数的话,在后面添加参数
         */
        Student newSudent = new Student("小明", "苏州", "男", 22);
        session.insert("bean.Student.insert", newSudent);
        session.commit();
        System.out.println(newSudent.getId());//当前插入的ID
    }

    @org.junit.Test
    public void insertMore() {
        List<Student> studentList = new ArrayList<Student>();
        studentList.add(new Student("小红", "盐城", "女", 20));
        studentList.add(new Student("小蓝", "泰州", "男", 25));
        studentList.add(new Student("小美", "安徽", "女", 20));
        studentList.add(new Student("小黄", "北京", "男", 24));

        session.insert("bean.Student.insertMore", studentList);
        session.commit();

        for (Student student : studentList) {
            System.out.println(student.getId());//null
        }
    }

    @org.junit.Test
    public void batchInsert() {
        Student student1 = new Student("小红", "盐城", "女", 20);
        Student student2 = new Student("小蓝", "泰州", "男", 25);

        session.insert("bean.Student.insert", student1);
        session.insert("bean.Student.insert", student2);
        session.commit();

        System.out.println(student1.getId());
        System.out.println(student2.getId());
    }

    @org.junit.Test
    public void getStudentMap() {
        Map<String, Object> map = session.selectMap("bean.Student.getStudentMap", 2, "id");
        System.out.println(map);
    }

    @org.junit.Test
    public void findStudentById() {
        Student student = session.selectOne("bean.Student.findStudentById",1);
        System.out.println(student);
    }

    @org.junit.Test
    public void findAll() {
        List<Student> studentList = session.selectList("bean.Student.findAll");
        for (Student student2 : studentList) {
            System.out.println(student2);
        }
    }

    @org.junit.Test
    public void findWithParameters() {
        StudentParams params = new StudentParams("明", null, null, null);
        //params = new StudentParams("", "男", null, null);
        //params = new StudentParams("", null, 20, null);
        params = new StudentParams("", null, null, 30);
        List<Student> studentList = session.selectList("bean.Student.findWithParameters", params);
        for (Student student2 : studentList) {
            System.out.println(student2);
        }
    }

    @org.junit.Test
    public void queryList() {
        System.out.println("begin");

        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        List<Map<String, Object>> studentList = session.selectList("bean.Student.queryList");
        System.out.println(studentList.size());
        for (Map<String, Object> studentInfo : studentList) {
            System.out.println(studentInfo);
        }
    }

    @org.junit.Test
    public void updateById() {
        Student student = new Student(2, "小张", "无锡", "女", 23);
        int res = session.update("bean.Student.updateById", student);
        System.out.println(res);
    }

    @After
    public void after() {
        session.commit();
        MybatisUtil.close();
    }
}

上一篇:Delphi Lookup函数-根据条件查找数据


下一篇:阿里Java学习路线:阶段 2:数据库开发-SQL进阶及查询练习:课时6:非空和唯一约束