里面包括了分页\and\ or \多表联接查询\总条数等一些查询

<!-- 查询所有学生医保信息  -->
   <select id="queryStudent_insure" parameterClass="com.gosoft.sms.insure.po.Insure_manPo" resultClass="com.gosoft.sms.insure.po.Insure_manPo" >
  select * from(
        select ROWNUM AS rowIndex, ID, STUDENT_NAME, SEX, CLASS_NAME, STATE, STATE_CODE, SPECIALTY_NAME, ACADEMY_NAME, ACADEMY_CODE, CLASS_CODE, GRADE, NUMBER_NO,
     STUDENT_ID, INSURE_STATE, BEGIN_DATE, END_DATE, INSURE_CODE, FEE, IS_BOOR, IS_SOCIALIZED, IS_OUTLANDER, IS_NOFEE, END_AUDITING_STATE, DEF_FEE
        from (
        select s.ID, s.STUDENT_NAME, s.SEX, s.CLASS_NAME, s.STATE, s.STATE_CODE, s.SPECIALTY_NAME, s.ACADEMY_NAME, s.ACADEMY_CODE, s.CLASS_CODE, s.GRADE, s.NUMBER_NO,
     i.STUDENT_ID, i.INSURE_STATE, i.BEGIN_DATE, i.END_DATE, i.INSURE_CODE, i.FEE, i.IS_BOOR, i.IS_SOCIALIZED, i.IS_OUTLANDER, i.IS_NOFEE, i.END_AUDITING_STATE, i.RECEIVE_STATE,
     (select FEE from T_INSURE_FEE where ROWNUM = 1) as DEF_FEE
     from S_V_STUDENTS s
        left join T_INSURE_MAN i on s.ID = i.STUDENT_ID
        left join T_CLASSES c on c.CLASS_CODE = s.CLASS_CODE
        where 1 = 1
     <dynamic>
      <isNotEmpty prepend="and" property="id">
          <![CDATA[ s.ID = #id:VARCHAR# ]]>
         </isNotEmpty>
   <isNotEmpty prepend="and" property="student_name" >
    <![CDATA[ s.STUDENT_NAME like '%'||#student_name:VARCHAR#||'%' ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="class_code" >
          <![CDATA[ s.CLASS_CODE = #class_code:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="academy_name" >
          <![CDATA[ s.ACADEMY_NAME = #academy_name:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="academy_code" >
          <![CDATA[ s.ACADEMY_CODE = #academy_code:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="class_name" >
          <![CDATA[ s.CLASS_NAME like '%'||#class_name:VARCHAR#||'%' ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="state_code" >
          <![CDATA[ s.STATE_CODE = #state_code:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="grade" >
          <![CDATA[ s.GRADE = #grade:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="insure_state" >
          <![CDATA[ nvl(i.INSURE_STATE, '-1') = #insure_state:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="insure_code" >
          <![CDATA[ (case nvl(i.INSURE_CODE, '0') when '0' then 0 else 1 end ) = #insure_code:VARCHAR#  ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="end_auditing_state" >
          <![CDATA[ i.END_AUDITING_STATE in($end_auditing_state$) ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="receive_state" >
          <![CDATA[ nvl(i.RECEIVE_STATE, '0') = #receive_state:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="ids" >
          <![CDATA[ s.ID in($ids$) ]]>
         </isNotEmpty>
         <isNotEmpty property="teacher_id" prepend="and">
    <![CDATA[ c.TEACHER_ID = #teacher_id:VARCHAR# ]]>
   </isNotEmpty>
       </dynamic>
       <dynamic prepend="and" open="(" close=")">
         <isNotEmpty prepend="or" property="is_boor" >
          <![CDATA[ i.IS_BOOR = #is_boor:VARCHAR#  ]]>
         </isNotEmpty>
         <isNotEmpty prepend="or" property="is_socialized" >
          <![CDATA[ i.IS_SOCIALIZED = #is_socialized:VARCHAR#  ]]>
         </isNotEmpty>
         <isNotEmpty prepend="or" property="is_outlander" >
          <![CDATA[ i.IS_OUTLANDER = #is_outlander:VARCHAR#  ]]>
         </isNotEmpty>
         <isNotEmpty prepend="or" property="is_nofee" >
          <![CDATA[ i.IS_NOFEE = #is_nofee:VARCHAR#  ]]>
         </isNotEmpty>
       </dynamic>
       <![CDATA[ order by s.STATE_CODE asc, s.CLASS_CODE desc, s.NUMBER_NO desc) ]]>
  <dynamic prepend="WHERE">
         <isNotEmpty property="endRowNum">
                <![CDATA[ ROWNUM <= #endRowNum#) ]]>
            </isNotEmpty>
        </dynamic>
  <dynamic prepend="WHERE">
            <isNotEmpty property="startRowNum">
                <![CDATA[ rowIndex >= #startRowNum# ]]>
            </isNotEmpty>
        </dynamic>
   </select>
   
   <!-- 获取所有学生医保信息数量  -->
   <select id="queryStudent_insureCount" parameterClass="com.gosoft.sms.insure.po.Insure_manPo" resultClass="java.lang.Long">
     select count(s.ID)
     from S_V_STUDENTS s
        left join T_INSURE_MAN i on s.ID = i.STUDENT_ID
        left join T_CLASSES c on c.CLASS_CODE = s.CLASS_CODE
        where 1 = 1
     <dynamic>
      <isNotEmpty prepend="and" property="id">
          <![CDATA[ s.ID = #id:VARCHAR# ]]>
         </isNotEmpty>
   <isNotEmpty prepend="and" property="student_name" >
    <![CDATA[ s.STUDENT_NAME like '%'||#student_name:VARCHAR#||'%' ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="class_code" >
          <![CDATA[ s.CLASS_CODE = #class_code:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="academy_name" >
          <![CDATA[ s.ACADEMY_NAME = #academy_name:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="academy_code" >
          <![CDATA[ s.ACADEMY_CODE = #academy_code:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="class_name" >
          <![CDATA[ s.CLASS_NAME like '%'||#class_name:VARCHAR#||'%' ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="state_code" >
          <![CDATA[ s.STATE_CODE = #state_code:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="grade" >
          <![CDATA[ s.GRADE = #grade:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="insure_state" >
          <![CDATA[ nvl(i.INSURE_STATE, '-1') = #insure_state:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="insure_code" >
          <![CDATA[ (case nvl(i.INSURE_CODE, '0') when '0' then 0 else 1 end ) = #insure_code:VARCHAR#  ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="end_auditing_state" >
          <![CDATA[ i.END_AUDITING_STATE in($end_auditing_state$) ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="receive_state" >
          <![CDATA[ nvl(i.RECEIVE_STATE, '0') = #receive_state:VARCHAR# ]]>
         </isNotEmpty>
         <isNotEmpty prepend="and" property="ids" >
          <![CDATA[ s.ID in($ids$) ]]>
         </isNotEmpty>
         <isNotEmpty property="teacher_id" prepend="and">
    <![CDATA[ c.TEACHER_ID = #teacher_id:VARCHAR# ]]>
   </isNotEmpty>
       </dynamic>
       <dynamic prepend="and" open="(" close=")">
         <isNotEmpty prepend="or" property="is_boor" >
          <![CDATA[ i.IS_BOOR = #is_boor:VARCHAR#  ]]>
         </isNotEmpty>
         <isNotEmpty prepend="or" property="is_socialized" >
          <![CDATA[ i.IS_SOCIALIZED = #is_socialized:VARCHAR#  ]]>
         </isNotEmpty>
         <isNotEmpty prepend="or" property="is_outlander" >
          <![CDATA[ i.IS_OUTLANDER = #is_outlander:VARCHAR#  ]]>
         </isNotEmpty>
         <isNotEmpty prepend="or" property="is_nofee" >
          <![CDATA[ i.IS_NOFEE = #is_nofee:VARCHAR#  ]]>
         </isNotEmpty>
       </dynamic>
  </select>