mytabis-增删改查之模糊查询、删除、修改、添加

1. 根据学生某个条件查询相关数据
2. 查询某个学生信息
3. 根据学生姓名模糊查询
4. 根据学生年龄和姓名查询学生信息
5. 查询学生信息，参数是map
6. 删除某个学生信息
7. 修改某个学生信息
8. 添加某个学生信息

在mytabis-增删改查之查询的基础上进行详细的讲解

• StudentMapper接口

package com.xiao.mapper;

import com.xiao.entity.StudentInfo;
import org.apache.ibatis.annotations.Param;

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

public interface StudentMapper {

//    查询所有的学生
    public List<StudentInfo> findAll();

//    查询大于某个年龄的学生
    public List<StudentInfo> findStuByAge(String sage);

//    查询某个学生信息
    public StudentInfo findOneStuById(String sid);

//    根据学生姓名模糊查询
    public List<StudentInfo> findStuByName(String name);

//    根据学生年龄和姓名查询学生信息
    public List<StudentInfo> findStuByAgeName(@Param("age" )String age,@Param("name" )String name);

//    查询学生信息，参数是map
    public List<StudentInfo> findStuByMap(Map<String,Object>map);

//    删除某个学生信息
    public int deleteStu(String id);

//    修改某个学生信息
    public int updateStu(StudentInfo stuid);

//    添加某个学生信息
    public int insertStu(StudentInfo stuid);
}

• StudentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiao.mapper.StudentMapper">
    <resultMap id="studentlist" type="StudentInfo">
        <result property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="ssex" column="ssex"/>
        <result property="sage" column="sage"/>
        <result property="scid" column="scid"/>
        <result property="cname" column="cname"/>
    </resultMap>

    <!--查询所有的学生-->
    <select id="findAll" resultMap="studentlist">
        SELECT student.*,class.cname FROM student ,class  WHERE student.scid= class.cid
    </select>

    <!--查询大于某个年龄的学生-->
    <select id="findStuByAge" parameterType="String" resultType="StudentInfo">
       SELECT * FROM student where sage>#{sage}
    </select>

    <!--查询某个学生信息-->
    <select id="findOneStuById" parameterType="String" resultType="StudentInfo">
        SELECT * FROM student where sid=#{sid}
    </select>

    <!--根据学生姓名模糊查询-->
    <select id="findStuByName" parameterType="String" resultType="StudentInfo">
        SELECT * FROM student where sname LIKE concat('%',concat(#{sname},'%'))
    </select>

    <!--根据学生年龄和姓名查询学生信息-->
    <select id="findStuByAgeName" parameterType="String" resultType="StudentInfo">
        SELECT * FROM student where sname LIKE concat('%',concat(#{name},'%')) AND sage>#{age}
    </select>

    <!--查询学生信息，参数是map-->
    <select id="findStuByMap" parameterType="map" resultType="StudentInfo">
        SELECT * FROM student WHERE ssex=#{ssex} and sage>#{sage}
    </select>

    <!--删除某个学生信息-->
    <delete id="deleteStu" parameterType="String">
        DELETE FROM student WHERE sid=#{sid}
    </delete>

    <!--修改某个学生信息-->
    <update id="updateStu" parameterType="StudentInfo">
      UPDATE student set sname=#{sname},ssex=#{ssex},sage=#{sage},scid=#{scid} WHERE sid=#{sid}
    </update>

    <!--添加某个学生信息-->
    <insert id="insertStu" parameterType="StudentInfo">
        INSERT INTO student VALUES (NULL,#{sname},#{ssex},#{sage},#{scid})
    </insert>
</mapper>

• 测试类

package com.xiao.test;

import com.xiao.entity.StudentInfo;
import com.xiao.mapper.StudentMapper;
import com.xiao.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;

import java.util.HashMap;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        test01();
//        test02();
//        test03();
//        test04();
//        test05();
//        test06();
//        test07();
//        test08();
//        test09();
    }

    //    查询所有的学生
    private static void test01() {
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> allStu = session.getMapper(StudentMapper.class).findAll();
        for (StudentInfo s : allStu) {
            System.out.println(s.getSid() + "\t" + s.getSname() + "\t" + s.getCname() + "\t");
        }
    }

    //    查询大于某个年龄的学生
    private static void test02() {
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> ageStu = session.getMapper(StudentMapper.class).findStuByAge("25");
        for (StudentInfo s : ageStu) {
            System.out.println(s.getSid() + "\t" + s.getSname() + "\t" + s.getSage() + "\t");
        }
    }

    //    查询某个学生信息
    private static void test03() {
        SqlSession session = MybatisUtils.getSession();
        StudentInfo idStu = session.getMapper(StudentMapper.class).findOneStuById("6");
        System.out.println(idStu.getSid() + "\t" + idStu.getSname() + "\t" + idStu.getSage() + "\t");

    }

    //   根据学生姓名模糊查询
    private static void test04() {
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> nameStu = session.getMapper(StudentMapper.class).findStuByName("胡");
        for (StudentInfo s : nameStu) {
            System.out.println(s.getSid() + "\t" + s.getSname() + "\t" + s.getSage() + "\t");
        }
    }

    //     根据学生年龄和姓名查询学生信息
    private static void test05() {
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> stu = session.getMapper(StudentMapper.class).findStuByAgeName("25", "胡");
        for (StudentInfo s : stu) {
            System.out.println(s.getSid() + "\t" + s.getSname() + "\t" + s.getSage() + "\t");
        }
    }

    //    查询学生信息，参数是map
    private static void test06() {
        SqlSession session = MybatisUtils.getSession();
        HashMap<String, Object> map = new HashMap<>();
        map.put("ssex", "男");
        map.put("sage", "27");
        List<StudentInfo> stuMap = session.getMapper(StudentMapper.class).findStuByMap(map);
        for (StudentInfo s : stuMap) {
            System.out.println(s.getSid() + "\t" + s.getSname() + "\t" + s.getSage() + "\t" + s.getSsex() + "\t");
        }
    }

    //    删除某个学生信息
    private static void test07() {
        SqlSession session = MybatisUtils.getSession();
        int stuId = session.getMapper(StudentMapper.class).deleteStu("11");
        if (stuId > 0) {
            System.out.println("OOOKKKKdelete");
        }
        session.commit();
    }

    //    修改某个学生信息
    private static void test08() {
        SqlSession session = MybatisUtils.getSession();
        StudentInfo stuid = session.getMapper(StudentMapper.class).findOneStuById("4");
        stuid.setSage("21");
        int updateStu = session.getMapper(StudentMapper.class).updateStu(stuid);
        if (updateStu > 0) {
            System.out.println("OOOKKKKupdate");
        }
        session.commit();
    }

    //    添加某个学生信息
    private static void test09() {
        SqlSession session = MybatisUtils.getSession();
        int i = session.getMapper(StudentMapper.class).insertStu(new StudentInfo("美羊羊", "女", "18", 3));
        if (i > 0) {
            System.out.println("OOOKKKKinsert");
        }
        session.commit();
    }
}