动态sql 2

动态sql 2

代码实例：
实体类 StudentInfo

    private String studentid;
    private String studentName;
    private String studentSex;
    private String studentPhone;
    private String studentAddress;
    private int stuAge;
    private int stuclassid;

//    一定要有一个空的无参的构造方法
    private ClassInfo classInfo;

    public String getStudentid() {
        return studentid;
    }

    public void setStudentid(String studentid) {
        this.studentid = studentid;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public String getStudentSex() {
        return studentSex;
    }

    public void setStudentSex(String studentSex) {
        this.studentSex = studentSex;
    }

    public String getStudentPhone() {
        return studentPhone;
    }

    public void setStudentPhone(String studentPhone) {
        this.studentPhone = studentPhone;
    }

    public String getStudentAddress() {
        return studentAddress;
    }

    public void setStudentAddress(String studentAddress) {
        this.studentAddress = studentAddress;
    }

    public int getStuAge() {
        return stuAge;
    }

    public void setStuAge(int stuAge) {
        this.stuAge = stuAge;
    }

    public int getStuclassid() {
        return stuclassid;
    }

    public void setStuclassid(int stuclassid) {
        this.stuclassid = stuclassid;
    }

    public ClassInfo getClassInfo() {
        return classInfo;
    }

    public void setClassInfo(ClassInfo classInfo) {
        this.classInfo = classInfo;
    }

    public StudentInfo(String studentid, String studentName, String studentSex, String studentPhone, String studentAddress, int stuAge, int stuclassid, ClassInfo classInfo) {
        this.studentid = studentid;
        this.studentName = studentName;
        this.studentSex = studentSex;
        this.studentPhone = studentPhone;
        this.studentAddress = studentAddress;
        this.stuAge = stuAge;
        this.stuclassid = stuclassid;
        this.classInfo = classInfo;
    }

    public StudentInfo() {
    }

实体类 ClassInfo

package cn.zjw.entity;

import java.util.List;

public class ClassInfo {
    private int classid;
    private String classname;
    private List<StudentInfo> studentInfoList;

    public List<StudentInfo> getStudentInfoList() {
        return studentInfoList;
    }

    public void setStudentInfoList(List<StudentInfo> studentInfoList) {
        this.studentInfoList = studentInfoList;
    }

    public int getClassid() {
        return classid;
    }

    public void setClassid(int classid) {
        this.classid = classid;
    }

    public String getClassname() {
        return classname;
    }

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

    public ClassInfo(int classid, String classname) {
        this.classid = classid;
        this.classname = classname;
    }

    public ClassInfo() {
    }
}

接口StudentInfoMapper

package cn.zjw.mapper;

import cn.zjw.entity.StudentInfo;
import org.apache.ibatis.annotations.Param;

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

public interface StudentInfoMapper {
    public List<StudentInfo> findStudentByArray (int[] array);

    public List<StudentInfo> findStudentByList(List<StudentInfo> list);

    public List<StudentInfo> findStudentByMap(Map<String,Object> map);

    public List<StudentInfo> findStudentByChoose(@Param("studentName")String studentName,@Param("stuclassid")int stuclassid);

    public List<StudentInfo> findStudentByPage(@Param("pageSize")int pageSize,@Param("pageCode")int pageCode);






}

StudentInfoMapper.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头部-->

<mapper namespace="cn.zjw.mapper.StudentInfoMapper">
    <select id="findStudentByArray" resultType="StudentInfo">
        select * from  student where stuclassid in
        <foreach collection="array" item="stuclassid" open="(" separator="," close=")">
            #{stuclassid}
        </foreach>
    </select>

    
    <select id="findStudentByList" resultType="StudentInfo">
        select * from  student where stuclassid in
        <foreach collection="list" item="stuclassid" open="(" separator="," close=")">
                #{stuclassid}
        </foreach>
    </select>

    <select id="findStudentByMap" resultType="StudentInfo">
        select *from student where studentName like concat(concat(#{studentName}),'%') and stuclassid in
        <foreach collection="list" item="map" open="(" separator="," close=")">
--             上方"后空格不能省
            #{map}
        </foreach>
    </select>


    <select id="findStudentByChoose" resultType="StudentInfo">

        select*from student where 1=1
        <choose>
            <when test="studentName!=null and studentName!=''">
                and studentName  like concat(concat(#{studentName}),'%')
            </when>
            <otherwise>
                and stuclassid=#{stuclassid}
            </otherwise>
        </choose>
    </select>

    <select id="findStudentByPage" resultType="StudentInfo">
        select * from student
        <if test="pageCode!=0">
            limit #{pageSize},#{pageCode}

        </if>

    </select>

</mapper>

Mybitis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <typeAliases>
        <package name="cn.zjw.entity"></package>
    </typeAliases>

    <environments default="zjw">
        <environment id="zjw">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"></property>
                <property name="url" value="jdbc:mysql://localhost:3306/school?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC"></property>
                <property name="username" value="root"></property>
                <property name="password" value="123456"></property>
            </dataSource>

        </environment>
    </environments>

    <mappers>
        <!--注意：此处路径用/不用点-->
        <mapper resource="cn/zjw/mapper/StudentInfoMapper.xml"/>
    </mappers>

</configuration>

测试类

package cn.zjw.test;

import cn.zjw.entity.StudentInfo;
import cn.zjw.mapper.StudentInfoMapper;
import cn.zjw.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

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

public class MyTest {
    public static void main(String[] args) {
//        test1();
//        test2();
//         test3();
//        test4();
        test5();
    }

    private static void test5() {
        SqlSession session=MybatisUtil.getSession();
        int pageSize=2; int pageCode=2;
        int before=(pageCode-1)*pageSize;
        int after=pageSize;
        List<StudentInfo> lists = session.getMapper(StudentInfoMapper.class).findStudentByPage(pageSize, pageCode);
        for(StudentInfo s:lists){
            System.out.println(s.getStudentName()+"\t"+s.getStuclassid());
        }


    }

    private static void test4() {
        SqlSession session=MybatisUtil.getSession();
        List<StudentInfo> list = session.getMapper(StudentInfoMapper.class).findStudentByChoose(null, 1);
        for (StudentInfo l:list){
            System.out.println(l.getStudentName());
        }

    }

    private static void test3() {
        SqlSession session = MybatisUtil.getSession();
        Map<String, Object> map = new HashMap<String, Object>();
        List list = new ArrayList();
        list.add(1);
        list.add(2);
        map.put("studentName", "李");
        map.put("list", list);
        List<StudentInfo> lists = session.getMapper(StudentInfoMapper.class).findStudentByMap(map);
        for (StudentInfo l : lists) {
            System.out.println(l.getStudentName()+"\t"+l.getStuclassid());

        }
    }

    private static void test2() {
        SqlSession session =MybatisUtil.getSession();
        List list =new ArrayList();
        list.add(1);
        list.add(2);

        List<StudentInfo> lists=session.getMapper(StudentInfoMapper.class).findStudentByList(list);
        for(StudentInfo l:lists){
            System.out.println(l.getStudentName()+"\t"+l.getStuclassid());
        }


    }

    private static void test1() {
        SqlSession session= MybatisUtil.getSession();
        int[] array={1,2};
        List <StudentInfo> list=session.getMapper(StudentInfoMapper.class).findStudentByArray(array);
        for (StudentInfo l:list){
            System.out.println(l.getStudentName()+"\t"+l.getStuclassid());

        }

    }



}

运行结果：
test1到test3

test4

test5