基于上一篇文章,这里就不重新配置
- StudentMapper.xml
package com.xiao.mapper;
import com.xiao.entity.StudentInfo;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface StudentMapper {
// 查询所有的学生
public List<StudentInfo> findAll();
// 查询某个学生信息
public StudentInfo findOneStuById(String sid);
// 根据学生姓名模糊查询 (如果这个不加注解,报异常会找不到字段)
public List<StudentInfo> findStuByName(@Param("sname")String name);
// 根据学生年龄和姓名查询学生信息
public List<StudentInfo> findStuByAgeName(@Param("age") String age, @Param("name") String name);
public List<StudentInfo> findStuByAgeNameSex(@Param("age") String age, @Param("name") String name,@Param("sex") String sex);
// 修改某个学生信息
public int updateStu(StudentInfo stuid);
// foreach
public List<StudentInfo> findAllByArray(int[] array);
}
- StudentMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiao.mapper.StudentMapper">
<resultMap id="studentlist" type="StudentInfo">
<result property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="ssex" column="ssex"/>
<result property="sage" column="sage"/>
<result property="scid" column="scid"/>
<association property="classInfo" javaType="ClassInfo" resultMap="class"></association>
</resultMap>
<resultMap id="class" type="ClassInfo">
<result property="cid" column="cid"/>
<result property="cname" column="cname"/>
</resultMap>
<!--查询所有的学生-->
<select id="findAll" resultMap="studentlist">
SELECT student.*,class.cname FROM student ,class WHERE student.scid= class.cid
</select>
<!--查询某个学生信息-->
<select id="findOneStuById" parameterType="String" resultType="StudentInfo">
SELECT * FROM student where sid=#{sid}
</select>
<!--根据学生姓名模糊查询-->
<select id="findStuByName" parameterType="String" resultType="StudentInfo">
SELECT * FROM student
<where>
<if test="sname!=null and sname !=''">
sname LIKE concat('%',concat(#{sname},'%'))
</if>
</where>
<!-- where 和trim在这里用法效果一样-->
<!--<trim prefix="where" prefixOverrides="and">-->
<!--<if test="sname!=null and sname !=''">-->
<!--sname LIKE concat('%',concat(#{sname},'%'))-->
<!--</if>-->
<!--</trim>-->
</select>
<!--根据学生年龄和姓名查询学生信息-->
<select id="findStuByAgeName" parameterType="String" resultType="StudentInfo">
SELECT * FROM student where 1=1
<if test="name!=null and name !='' ">
and sname LIKE concat('%',concat(#{name},'%'))
</if>
<if test="age!=null and age !='' ">
AND sage>#{age}
</if>
</select>
<!--switch...catch-->
<select id="findStuByAgeNameSex" resultType="StudentInfo">
SELECT * FROM student where 1=1
<choose>
<when test="name!=null and name !=''">
and sname LIKE concat('%',concat(#{name},'%'))
</when>
<when test="age!=null and age !=''">
AND sage>#{age}
</when>
<when test="sex!=null and sex !=''">
AND ssex=#{sex}
</when>
</choose>
</select>
<!--修改某个学生信息-->
<update id="updateStu" parameterType="StudentInfo">
UPDATE student
<set>
<if test="sname!=null and sname!=''"> sname=#{sname},</if>
<if test="sage!=null and sage!=''"> sage=#{sage},</if>
<if test="scid!=null and scid!=''"> scid=#{scid}</if>
<!-- where 和if在这里用法效果一样-->
<!--<if test="sid!=null and sid!=''"> where sid=#{sid}</if>-->
<where>
<if test="sid!=null and sid!=''">sid=#{sid}</if>
</where>
</set>
</update>
<!--foreach-->
<select id="findAllByArray" resultType="StudentInfo">
SELECT * FROM student where scid in
<foreach collection="array" item="scid" open="(" separator="," close=")">
#{scid}
</foreach>
</select>
</mapper>
- 测试类
package com.xiao.test;
import com.xiao.entity.ClassInfo;
import com.xiao.entity.StudentInfo;
import com.xiao.mapper.ClassMapper;
import com.xiao.mapper.StudentMapper;
import com.xiao.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class Test01 {
public static void main(String[] args) {
// test01();
// test02();
// test03();
// test04();
// test05();
// test06();
test07();
}
private static void test07() {
int [] array = {2,1};
SqlSession session = MybatisUtils.getSession();
List<StudentInfo> allByArray = session.getMapper(StudentMapper.class).findAllByArray(array);
for (StudentInfo a : allByArray) {
System.out.println(a.getSid()+"\t"+a.getSname());
}
}
private static void test06() {
SqlSession session = MybatisUtils.getSession();
StudentInfo oneStuById = session.getMapper(StudentMapper.class).findOneStuById("6");
oneStuById.setSage("17");
int i = session.getMapper(StudentMapper.class).updateStu(oneStuById);
if (i>0){
System.out.println("OOOKKKupdate");
}
session.commit();
MybatisUtils.closeSession(session);
}
private static void test05() {
SqlSession session = MybatisUtils.getSession();
List<StudentInfo> nameStu = session.getMapper(StudentMapper.class).findStuByName("胡");
for (StudentInfo s : nameStu) {
System.out.println(s.getSid() + "\t" + s.getSname() + "\t" + s.getSage() + "\t");
}
}
private static void test04() {
SqlSession session = MybatisUtils.getSession();
List<StudentInfo> stu = session.getMapper(StudentMapper.class).findStuByAgeNameSex(null, null, "女");
for (StudentInfo s : stu) {
System.out.println(s.getSid()+"\t"+s.getSname());
}
}
private static void test03() {
SqlSession session = MybatisUtils.getSession();
List<StudentInfo> ageName = session.getMapper(StudentMapper.class).findStuByAgeName("23", null); //name="胡"
for (StudentInfo s : ageName) {
System.out.println(s.getSid()+"\t"+s.getSname());
}
}
private static void test02() {
SqlSession session = MybatisUtils.getSession();
List<ClassInfo> allClass = session.getMapper(ClassMapper.class).findAllClass();
for (ClassInfo aClass : allClass) {
System.out.println(aClass.getCid()+"\t"+aClass.getCname()+"\t"+aClass.getStudentInfos().size());
List<StudentInfo> studentInfos = aClass.getStudentInfos();
for (StudentInfo studentInfo : studentInfos) {
System.out.println(studentInfo.getSname()+"\t"+studentInfo.getScid());
}
}
}
private static void test01() {
SqlSession session = MybatisUtils.getSession();
List<StudentInfo> all = session.getMapper(StudentMapper.class).findAll();
for (StudentInfo info : all) {
System.out.println(info.getSid()+"\t"+info.getClassInfo().getCname());
}
}
}