mybatis与spring整合 实现模糊查询、动态SQL查询以及一对一查询、一对多查询

mybatis与spring整合 实现模糊查询、动态SQL查询以及一对一查询、一对多查询

需要导入的jar包

1-配置文件(Resources目录下的配置文件)

1.1 mapper目录下配置文件

1.1.1 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="com.zelin.mapper.StudentMapper">
    <!--1.动态sql查询：指定条件下的模糊查询 查询所有学生 方式一-->
    <select id="findAllByKeyWords1" resultType="student" parameterType="studentVo">
        select st.*,cname from student st,classes c where c.cid = st.cid and sname like '%${sname}%' and addr like '%${addr}%'
    </select>
    <!--2.动态sql查询：指定条件下的模糊查询 查询所有学生 方式二-->
    <select id="findAllByKeyWords2" resultType="student" parameterType="studentVo">
        select st.*,cname from student st,classes c where c.cid = st.cid and sname like #{sname} and addr like #{addr}
    </select>

    <!--3.动态sql查询：查询指定班级编号下的学生信息-->
    <select id="findAllByKeyWords3" resultType="student" parameterType="studentVo">
        select st.*,cname from student st,classes c
        <where>
            c.cid = st.cid and st.cid in 
            <foreach collection="cids" separator="," open="(" close=")" item="id">
                #{id}
            </foreach>
        </where>
        
    </select>
</mapper>

1.1.2 ClassesMapper.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="com.zelin.mapper.ClassesMapper">

    <!--1.0-一对多查询：方式一 使用自定义的结果集映射实现查询对应班级下的学生-->
    <select id="findAllClasses1" resultMap="classesMap">
        select st.*,cname from student st,classes c where st.cid = c.cid
    </select>
    <!--1.1-自定义的结果集映射-->
    <resultMap id="classesMap" type="classes">
        <id property="cid" column="cid"/>
        <result property="cname" column="cname"/>
        <collection property="students" ofType="student">
            <id property="sid" column="sid"/>
            <result property="sname" column="sname"/>
            <result property="sex" column="sex"/>
            <result property="age" column="age"/>
            <result property="addr" column="addr"/>
            <result property="cid" column="cid"/>
        </collection>
    </resultMap>


    <!--2.0-一对多查询：方式二 使用子查询实现查询对应班级下的学生-->
    <select id="findAllClasses2" resultMap="classesMap2">
        select * from classes
    </select>
    <!--2.1-自定义的结果集映射-->
    <resultMap id="classesMap2" type="classes">
        <id property="cid" column="cid"/>
        <result property="cname" column="cname"/>
        <collection property="students" column="cid" ofType="student" select="findStudentByCid"/>
    </resultMap>
    <!--2.2-定义通过Cid查询出来的学生-->
    <select id="findStudentByCid" resultType="student">
        select * from student where cid=#{value}
    </select>
</mapper>

1.1.3 UserDao.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="com.zelin.mapper.UserMapper">
    <!--1.一对一查询方式一：使用自定义的UserCustom类-->
    <select id="findAllUser1" resultType="userCustom">
        select u.*,cno,id from tb_user u,tb_idcard c where c.uid = u.uid
    </select>
    <!--2.一对一查询：使用自定义的结果集进行查询-->
    <select id="findAllUser2" resultMap="userMap" >
        select u.*,cno,id from tb_user u,tb_idcard c where c.uid = u.uid
    </select>
    <!--2.1)定义结果集映射-->