MyBatis多表模糊查询

#MyBatis多表模糊查询

讲了一下多表模糊查询，sql语句书写，这里我们将学生表里的班级id（cl_id）和学院id（co_id）设置为了班级表里的cla_id与学院表里的col_id的外键关联，下面看代码具体

**Mapper.xml

下面展示一些 内联代码片。

<resultMap id="WithClaColResultMap" type="com.hwadee.uscs.bean.Student">
    <id column="stu_id" jdbcType="INTEGER" property="stuId" />
    <result column="stu_num" jdbcType="VARCHAR" property="stuNum" />
    <result column="stu_name" jdbcType="VARCHAR" property="stuName" />
    <result column="stu_gender" jdbcType="CHAR" property="stuGender" />
    <result column="stu_idcard" jdbcType="CHAR" property="stuIdcard" />
    <result column="stu_birth" jdbcType="DATE" property="stuBirth" />
    <result column="stu_phone" jdbcType="CHAR" property="stuPhone" />
    <result column="co_id" jdbcType="INTEGER" property="coId" />
    <result column="cl_id" jdbcType="INTEGER" property="clId" />
    <association property="cla" javaType="com.hwadee.uscs.bean.Cla">
      <id column="cla_id" property="claId"/>
      <result column="cla_num" property="claNum"/>
      <result column="cla_name" property="claName"/>
      <result column="cla_master" property="claMaster"/>
    </association>
    <association property="college" javaType="com.hwadee.uscs.bean.College">
      <id column="col_id" property="colId"/>
      <result column="col_num" property="colNum"/>
      <result column="col_name" property="colName"/>
    </association>
  </resultMap>
  <select id="searchByKeyword" resultMap="WithClaColResultMap">
    select s.stu_id,s.stu_num,s.stu_name,s.stu_gender,s.stu_idcard,s.stu_birth,s.stu_phone,s.co_id,s.cl_id,c.cla_id,c.cla_name,c.cla_num,co.col_id,co.col_name,co.col_num
    from tb_student s
    left join tb_class c on s.cl_id = c.cla_id
    left join tb_college co on s.co_id =co.col_id
    where
    stu_num like concat('%',#{keyword},'%') or
    stu_name like concat('%',#{keyword},'%') or
    c.cla_name like concat('%',#{keyword},'%') or
    co.col_name like concat('%',#{keyword},'%')
    <if test='keyword == "男"'>
      or stu_gender = '男'
    </if>
    <if test='keyword == "女"'>
      or stu_gender = '女'
    </if>
  </select>

dao层接口

//根据关键字模糊查询
public interface StudentMapper{
	List<Student> searchByKeyword(@Param("keyword") String keyword);
}   

service层方法

@Service
public class StudentService {
 @Autowired
    StudentMapper studentMapper;
public List<Student> selectByKeyword(String keyword){
		List<Student> students = studentMapper.searchByKeyword(keyword);
        return students;
    }
}