根据回复状态、教师姓名、学生姓名（三表连接）实现查询

最新推荐文章于 2024-09-11 18:14:11 发布

原创最新推荐文章于 2024-09-11 18:14:11 发布 · 487 阅读

CC 4.0 BY-SA版权

本文介绍了一种使用MyBatis动态SQL进行复杂条件查询的方法，并结合Spring MVC实现了前端页面的交互式查询功能。具体包括：定义DAO接口进行参数化的SQL查询、配置动态SQL以支持灵活的查询条件组合、编写Controller处理HTTP请求并将查询结果返回给前端展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、实现效果

2、DAO接口写法

List<Interact> selectByStatusAndPerson(@Param("interactStatus")Integer interactStatus,
            @Param("teacherName")String teacherName,@Param("studentName")String studentName);

3、MyBatis动态SQL

<resultMap type="interact" id="interactMap">
        <id column="inteId" property="interactId"/>
        <result column="inteId" property="interactId"/>
        <result column="inteTitle" property="interactTitle"/>
        <result column="inteStatus" property="interactStatus"/>
        <association property="teacher" javaType="teacher"> 
            <id column="teacher_id" property="teacherId"/>
            <result column="teaName" property="teacherName"/>
        </association>
        <association property="student" javaType="student"> 
            <id column="student_id" property="studentId"/>
            <result column="stuName" property="studentName"/>
        </association>        
    </resultMap>
<select id="selectByStatusAndPerson" resultMap="interactMap">
        select inte.interact_id as inteId,
        inte.interact_title as inteTitle,tea.teacher_name as teaName,
        stu.student_name as stuName,inte.interact_status as inteStatus
        from (select * from interact_interact
        <where>
            <if test="interactStatus!=null">
                interact_status = #{interactStatus}
            </if>
        </where>
        ) inte join (select * from interact_teacher
        <where>
            <if test="teacherName!=null">
                teacher_name like '%'||#{teacherName}||'%'
            </if>
        </where> 
        ) tea on inte.teacher_id = tea.teacher_id  
        join (select * from interact_student
        <where>
            <if test="studentName!=null">
                student_name like '%'||#{studentName}||'%'
            </if>
        </where>
        ) stu on inte.student_id = stu.student_id         
        order by inte.create_time desc
    </select>

4、Controller

@RequestMapping("/queryByStatusAndPerson")
    public String queryByStatusAndPerson(Integer interactStatus,
            String teacherName,String studentName,Model model){
        List<Interact> queryByStatusAndPerson = is.queryByStatusAndPerson(interactStatus, teacherName, studentName);
        model.addAttribute("interacts", queryByStatusAndPerson);
        return "teacher/queryInteracts";
    }

5、jsp

<form action="${pageContext.request.contextPath }/teacher/queryByStatusAndPerson.do" method="post" id="queryForm">       
        <div class="input-append" style="padding-left:40px">
        <select class="span3" name="interactStatus" id="sele" style="width: 150px; margin-left: 10px; border-radius:4px;" >
                 <option value="" >请选择回复状态</option>
       			 <option value="0" >未回复</option>
       			 <option value="1">已回复</option>
        </select>      
            <input value="" class="span2" type="text" placeholder="请输入教师姓名" name="teacherName" style="width: 250px; height: 20px;margin-left: 10px; border-radius:4px;"> 
            <input value="" class="span2" type="text" placeholder="请输入学生姓名" name="studentName" style="width: 250px; height: 20px;margin-left: 10px; border-radius:4px;"> 
         	<button class="btn" type="submit" style="margin-left: 10px; border-radius:10px;" >搜索</button>
          </div>
      </form>