一、使用MyBatis

定义sql映射xml文件

　　userMapper.xml文件的内容如下：

<!--头文件-->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <!--namespace：
     命名空间：区分不同空间下的同名SQLID
     A： findlAll
     B:  findAll
    -->
<mapper namespace="cn.happy.dao.IStudentInfoDAO">
    <!--SQL标签
      id:唯一锁定到SQL标识
      paramenterType:SQL语句的入参  可以省略
      resultType：
      增删除操作：不能 写
      查询：单个实体的类型
    -->
    <sql id="columns">
        stuid,stuname,stuage,studate
    </sql>
    <resultMap id="studentMap" type="StudentInfo">
      <!--   <result column="stuname2" property="stuName"></result>-->
    </resultMap>

    <select id="findAll" resultMap="studentMap">
        /*SQL文：SQL语句*/
        select <include refid="columns"></include> from studentinfo
    </select>
    <!--按主键查询-->
    <select id="getStudentById" resultType="StudentInfo">
        select * from studentinfo WHERE stuid=#{stuId}
    </select>

　　

 <!--添加学生-->
    <insert id="addStudent">
        insert into studentinfo( stuName,stuAge,stuDate) VALUES (#{stuName},#{stuAge},#{stuDate})
    </insert>

    <!--修改学生-->
    <update id="updateStudent">
       update studentinfo set  stuName= #{stuName} WHERE  stuId=#{stuId}
    </update>

    <!--删除学生-->
    <delete id="deleteStudent">
       delete from  studentinfo WHERE  stuId=#{stuId}
    </delete>

    <!--模糊查询-->
    <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>

    <!--多条件查询-->
    <select id="findStudentsByCondition" resultType="StudentInfo">
         select * from  studentinfo where stuname like '%' #{stuName} '%' and stuAge>#{stuAge}
    </select>

    <!--多条件查询使用索引-->
    <select id="findStudentsByConditionMutliArgs" resultType="StudentInfo">
        select * from  studentinfo where stuname like '%' #{0} '%' and stuAge>#{1}
    </select>


    <!--智能标签foreach List-->
    <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>
    <!--智能标签foreach List-->
    <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>

　　

 <!--智能标签foreach Array-->
    <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>

    <!--智能标签choose-->
    <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>


    <!--智能标签if-->
    <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>
</mapper>