mybatis教程之增删改查_动力节点Java学院整理

<!-- 查询学生，根据id --> 
<select id="getstudent" parametertype="string" resultmap="studentresultmap"> 
  select st.student_id, 
        st.student_name, 
        st.student_sex, 
        st.student_birthday, 
        st.class_id 
     from student_tbl st 
     where st.student_id = #{studentid} 
</select> 

<!-- 插入学生 --> 
<insert id="insertstudent" parametertype="studententity"> 
    insert into student_tbl (student_id, 
                     student_name, 
                     student_sex, 
                     student_birthday, 
                     class_id) 
       values  (#{studentid}, 
             #{studentname}, 
             #{studentsex}, 
             #{studentbirthday}, 
             #{classentityclassid}) 
</insert> 

<!-- 插入学生 自动主键--> 
<insert id="insertstudentautokey" parametertype="studententity"> 
  <selectkey keyproperty="studentid" resulttype="string" order="before"> 
      select nextval('student') 
  </selectkey> 
    insert into student_tbl (student_id, 
                 student_name, 
                 student_sex, 
                 student_birthday, 
                 class_id) 
       values  (#{studentid}, 
            #{studentname}, 
            #{studentsex}, 
            #{studentbirthday}, 
            #{classentityclassid})   
</insert> 

<!-- 更新学生信息 --> 
<update id="updatestudent" parametertype="studententity"> 
    update student_tbl 
      set student_tbl.student_name = #{studentname},  
        student_tbl.student_sex = #{studentsex}, 
        student_tbl.student_birthday = #{studentbirthday}, 
        student_tbl.class_id = #{classentity.classid} 
     where student_tbl.student_id = #{studentid};   
</update> 

<!-- 删除学生 --> 
<delete id="deletestudent" parametertype="studententity"> 
    delete from student_tbl where student_id = #{studentid} 
</delete> 

<!-- 复用sql语句 查询student表所有字段 --> 
<sql id="selectstudentall"> 
    select st.student_id, 
          st.student_name, 
          st.student_sex, 
          st.student_birthday, 
          st.class_id 
       from student_tbl st 
</sql> 

<!-- 查询学生，根据id --> 
<select id="getstudent" parametertype="string" resultmap="studentresultmap"> 
  <include refid="selectstudentall"/> 
      where st.student_id = #{studentid}  
</select> 

<!-- 查询学生list，根据入学时间 --> 
<select id="getstudentlistbydate" parametertype="date" resultmap="studentresultmap"> 
  select * 
   from student_tbl st left join class_tbl ct on st.class_id = ct.class_id 
   where ct.class_year = #{classyear};   
</select> 

list<studententity> studentlist = studentmapper.getstudentlistbyclassyear(stringutil.parse("2007-9-1")); 
for (studententity entitytemp : studentlist) { 
  system.out.println(entitytemp.tostring()); 
} 

<!-- 查询学生list，like姓名、=性别，参数entity类型 --> 
<select id="getstudentlistwhereentity" parametertype="studententity" resultmap="studentresultmap"> 
  select * from student_tbl st 
    where st.student_name like concat(concat('%', #{studentname}),'%') 
     and st.student_sex = #{studentsex} 
</select> 

studententity entity = new studententity(); 
entity.setstudentname("李"); 
entity.setstudentsex("男"); 
list<studententity> studentlist = studentmapper.getstudentlistwhereentity(entity); 
for (studententity entitytemp : studentlist) { 
  system.out.println(entitytemp.tostring()); 
} 

<!-- 查询学生list，=性别，参数map类型 --> 
<select id="getstudentlistwheremap" parametertype="map" resultmap="studentresultmap"> 
  select * from student_tbl st 
   where st.student_sex = #{sex} 
     and st.student_sex = #{sex} 
</select> 

map<string, string> map = new hashmap<string, string>(); 
map.put("sex", "女"); 
map.put("name", "李"); 
list<studententity> studentlist = studentmapper.getstudentlistwheremap(map); 
for (studententity entitytemp : studentlist) { 
  system.out.println(entitytemp.tostring()); 
} 

<!-- 查询学生list，like姓名、=性别、=生日、=班级，多参数方式 --> 
<select id="getstudentlistwhereparam" resultmap="studentresultmap"> 
  select * from student_tbl st 
  <where> 
    <if test="name!=null and name!='' "> 
      st.student_name like concat(concat('%', #{name}),'%') 
    </if> 
    <if test="sex!= null and sex!= '' "> 
      and st.student_sex = #{sex} 
    </if> 
    <if test="birthday!=null"> 
      and st.student_birthday = #{birthday} 
    </if> 
    <if test="classentity!=null and classentityclassid !=null and classentityclassid!='' "> 
      and st.class_id = #{classentity.classid} 
    </if> 
  </where> 
</select> 
 

list<studententity> studentlist = studentmapper.getstudentlistwhereparam("", "",stringutil.parse("1985-05-28"), classmapper.getclassbyid("20000002")); 
for (studententity entitytemp : studentlist) { 
  system.out.println(entitytemp.tostring()); 
} 

<cache eviction="fifo" flushinterval="60000" size="512" readonly="true"> 
</cache> 

<cache-ref namespace="com.liming.manager.data.studentmapper"/>