Mybatis增删改查和智能标签

Mybatis增删改

1.查询所有学生

dao：               public List<StudentInfo> findAll();

dao.xml:           <select id="findAll" resultMap="studentMap">
                                    select <include refid="columns"></include> from studentinfo
                         </select>

测试类：         

public void testAll(){
         String path="MyBatis-config.xml";
         InputStream is= null;
         try {
             is = Resources.getResourceAsStream(path);
             SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
             SqlSession session=factory.openSession();
             List<StudentInfo> list = session.selectList("findAll");
             for (StudentInfo info:list) {
                 System.out.println(info.getStuName());
             }
             session.close();
         } catch (IOException e) {
             e.printStackTrace();
         }

     }

2.根据学生学号查询特定学生对象

dao：   public  StudentInfo getStudentById(int stuid);

dao.xml:  

<select id="getStudentById" resultType="StudentInfo">
        select * from studentinfo WHERE stuid=#{stuId}
</select>

测试类：

public void testSelectOne(){
        String path="MyBatis-config.xml";
        InputStream is= null;
        try {
            is = Resources.getResourceAsStream(path);
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
            SqlSession session=factory.openSession();
            StudentInfo info = session.selectOne("getStudentById",3);
            System.out.println(info.getStuName());

            session.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

}

3.添加学生

dao: public int addStudent(StudentInfo stu);

dao.xml:

<insert id="addStudent">
        insert into studentinfo( stuName,stuAge,stuDate) VALUES (#{stuName},#{stuAge},#{stuDate})
</insert>

测试类：

public void testAdd(){
            SqlSession session= MyBatisUtil.getSession();
            IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);

            StudentInfo stu=new StudentInfo();
            stu.setStuName("小丽");
            stu.setStuAge(20);
            stu.setStuDate(new Date());
            dao.addStudent(stu);
            session.commit();;//提交事务，增删改必须运行在事务的环境中，程序员必须手动的提交事务

            System.out.println("ok!");
            session.close();
        }

4.修改学生 根据学号

dao:  public int updateStudent(StudentInfo info);

dao.xml:

<update id="updateStudent">
       update studentinfo set  stuName= #{stuName} WHERE  stuId=#{stuId}
    </update>

测试类：

public void testUpdate(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);

        StudentInfo stu=new StudentInfo();
        stu.setStuName("星星");
        stu.setStuId(6);

        dao.updateStudent(stu);
        session.commit();;//提交事务，增删改必须运行在事务的环境中，程序员必须手动的提交事务

        System.out.println("update ok!");
        session.close();
    }

5.删除学生

dao: public int deleteStudent(int stuid);

dao.xml:

<delete id="deleteStudent">
       delete from  studentinfo WHERE  stuId=#{stuId}
    </delete>

测试类:

public void testDelete(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);

        dao.deleteStudent(3);
        session.commit();;//提交事务，增删改必须运行在事务的环境中，程序员必须手动的提交事务

        System.out.println("del ok!");
        session.close();
    }

6.多条件模糊查询 查询学生姓名中包含"星"的 ，并且年里在20岁以上的学生信息

dao:   public List<StudentInfo> findStudentListLike(StudentInfo info);

dao.xml:

<select id="findStudentListLike" resultType="StudentInfo">
        <!--select * from studentinfo where stuname like concat('%',#{stuName},'%') and stuAge>#{stuAge}-->
            select * from studentinfo where stuname like '%${stuName}%' and stuAge>#{stuAge}
    </select>

测试类：

public void testSelectLike(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);
        StudentInfo stu=new StudentInfo();
        stu.setStuName("星");
        stu.setStuAge(19);
        List<StudentInfo> list = dao.findStudentListLike(stu);
        for (StudentInfo info:list) {
            System.out.println(info.getStuName());
        }

        session.close();
    }

7.多条件模糊查询（Map） 查询姓名中包含“雨”，并且年龄>20的学生信息

dao：public  List<StudentInfo> findStudentsByCondition(Map<String,Object> map);

daoxml: 

<select id="findStudentsByCondition" resultType="StudentInfo">
         select * from  studentinfo where stuname like '%' #{stuName} '%' and stuAge>#{stuAge}
    </select>

测试类：

public void testSelectLike(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);
        Map<String,Object> map=new HashMap<String,Object>();
        map.put("stuName","雨");
        map.put("stuAge",20);
        List<StudentInfo> list = dao.findStudentsByCondition(map);
        for (StudentInfo stu:list) {
            System.out.println(stu.getStuName());
        }
        session.close();
    }

8.多条件模糊查询（第三种） 查询姓名中包含“雨”，并且年龄>20的学生信息

dao：public  List<StudentInfo> findStudentsByConditionMutliArgs(String stuName,int stuAge);

dao.xml:

<select id="findStudentsByConditionMutliArgs" resultType="StudentInfo">
        select * from  studentinfo where stuname like '%' #{0} '%' and stuAge>#{1}
    </select>

测试类：

public void testSelectLikeMulti(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);

        List<StudentInfo> list = dao.findStudentsByConditionMutliArgs("雨",20);
        for (StudentInfo stu:list) {
            System.out.println(stu.getStuName());
        }
        session.close();
    }

智能标签：

9.智能标签if

dao:public List<StudentInfo> findByIf(StudentInfo stu);

dao.xml:

<select id="findByIf" resultType="StudentInfo">
        select * from studentinfo
        <where>
            <if test="stuName!=null"><!--用户录入的姓名字段-->
                 and stuName like '%' #{stuName} '%'
            </if>
            <if test="stuAge!=null">
                and stuAge>#{stuAge}
            </if>
        </where>
    </select>

测试类：

public void testIf(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);
        StudentInfo stu=new StudentInfo();
       // stu.setStuName("雨");
       stu.setStuAge(20);
        List<StudentInfo> list = dao.findByIf(stu);
        for (StudentInfo stuinfo:list) {

            System.out.println(stuinfo.getStuName());
        }
        session.close();
    }

10.智能标签choose

dao:public List<StudentInfo> findByChoose(StudentInfo stu);

dao.xml:

<select id="findByChoose" resultType="StudentInfo">
        select * from studentinfo
        <where>
            <choose>
                <when test="stuName!=null">
                    and stuName like '%' #{stuName} '%'
                </when>
                <when test="stuAge!=null">
                    and stuAge>#{stuAge}
                </when>
                <otherwise>
                     and 1=2
                </otherwise>
            </choose>
        </where>
    </select>

测试类：

public void testChoose(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);
        StudentInfo stu=new StudentInfo();
        // stu.setStuName("雨");
       // stu.setStuAge(20);
        List<StudentInfo> list = dao.findByChoose(stu);
        for (StudentInfo stuinfo:list) {

            System.out.println(stuinfo.getStuName());
        }
        session.close();
    }

11.智能标签foreach  array

dao:public  List<StudentInfo> findByForeachArray(int[] ids);

dao.xml:

select id="findByForeachArray" resultType="StudentInfo">
        select * from studentinfo
        <where>
            <if test="array.length>0">
                stuid in
                <foreach collection="array" open="(" close=")" separator="," item="stuno">
                    #{stuno}
                </foreach>
            </if>
        </where>
    </select>

测试类：

public void testForeachArray(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);
        int[] ids={2,5};
        List<StudentInfo> list = dao.findByForeachArray(ids);
        for (StudentInfo stuinfo:list) {

            System.out.println(stuinfo.getStuName());
        }
        session.close();
    }

12.智能标签Foreach List<Integer>

dao:public  List<StudentInfo> findByForeachList(List<Integer> list);

dao.xml:

<select id="findByForeachList" resultType="StudentInfo">
        select * from studentinfo
        <where>
            <if test="list.size>0">
                stuid in
                <foreach collection="list" open="(" close=")" separator="," item="stuno">
                    #{stuno}
                </foreach>
            </if>
        </where>
    </select>

测试类:

public void testForeachList(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);
        List<Integer> list=new ArrayList<Integer>();
        list.add(2);
        list.add(5);
        List<StudentInfo> list2 = dao.findByForeachList(list);
        for (StudentInfo stuinfo:list2) {

            System.out.println(stuinfo.getStuName());
        }
        session.close();
    }

13.智能标签foreach List<StudentInfo>

dao:public  List<StudentInfo> findByForeachListStudent(List<StudentInfo> list);

dao.xml:

<select id="findByForeachListStudent" resultType="StudentInfo">
        select * from studentinfo
        <where>
            <if test="list.size>0">
                stuid in
                <foreach collection="list" open="(" close=")" separator="," item="stu">
                    #{stu.stuId}
                </foreach>
            </if>
        </where>
    </select>

测试类:

public void testForeachListStudent(){
        SqlSession session= MyBatisUtil.getSession();
        IStudentInfoDAO dao = session.getMapper(IStudentInfoDAO.class);
        List<StudentInfo> list=new ArrayList<StudentInfo>();
        StudentInfo s1=new StudentInfo();
        s1.setStuId(2);
        StudentInfo s2=new StudentInfo();
        s2.setStuId(5);
        list.add(s1);
        list.add(s2);
        List<StudentInfo> list2 = dao.findByForeachListStudent(list);
        for (StudentInfo stuinfo:list2) {

            System.out.println(stuinfo.getStuName());
        }
        session.close();
    }

14.多表连接查询 一对多  单挑SQL语句      根据部门编号，检索部门名称以及该部门下所有员工的信息

dao：public Dept getEmpsByDeptNo(int deptNo);

dao.xml:

<select id="getEmpsByDeptNo" resultMap="deptMapper">
        select dept.deptNo,deptName,empNo,empName
        from dept,emp
        where dept.deptNo=emp.deptNo
        and dept.deptNo=#{deptNo}

    </select>

测试类：

public void testOneToMany(){
        SqlSession session= MyBatisUtil.getSession();
        IDeptDAO dao = session.getMapper(IDeptDAO.class);
        Dept dept = dao.getEmpsByDeptNo(5);
        System.out.println(dept.getDeptName());
        for (Emp emp:dept.getEmps()) {
            System.out.println(emp.getEmpName());
        }

        session.close();
    }