Mybatis实现多表关联多条件查询

MyBatis关联查询与结果映射

最新推荐文章于 2022-08-28 21:27:58 发布

原创最新推荐文章于 2022-08-28 21:27:58 发布 · 4.8k 阅读

12 ·

CC 4.0 BY-SA版权

Mybatis 专栏收录该内容

14 篇文章

订阅专栏

第一种实现方式：

1.在实体类中添加需要展示的但在数据库表结构中字段

2.在mapper.xml的

<result column="project_name" jdbcType="VARCHAR" property="projectName" />定义需要展示的列

3.在SQL中关联查询即可

<select id="selectByMap" resultType="com.apply.ism.entity.MaterialInfo">
    SELECT m.id,m.title,c.company,c.hierarchy,m.score,l.file_path,t.project_name,t.contents,m.file_id FROM material_info m
    LEFT JOIN company c ON m.company_id=c.id AND c.data_state='Normal'
    LEFT JOIN t_system t ON m.evaluation_id=t.id AND m.data_state='Normal'
    LEFT JOIN file_message l ON l.id=m.file_id AND l.data_state='Normal'
    <where>
        m.data_state='Normal'
        <if test="nd != null and nd !=''">
            AND t.nd=#{nd}
        </if>
        <if test="projectName != null and projectName !='' and projectName != 'null'">
            AND t.project_name=#{projectName}
        </if>
        
    </where>
</select>

4.在mapper.java 中编写代码:

List<MaterialInfo> selectByMap(String nd,String projectName);

第二种实现方法：

不需要在实体类添加属性，也不需要在<result column="project_name" jdbcType="VARCHAR" property="projectName" />定义列

1.在SQL中关联查询

<select id="getSurveyInfoList" resultType="java.util.Map">
  SELECT s.companyid,s.yz_code,c.company,s.survey_name,s.start_time,s.end_time,s.qr_code FROM survey_info s JOIN company c ON s.companyid=c.id
  WHERE 1 = 1 AND s.data_state = 'Normal'
  <if test="param1 != null and param1 !=''">
    AND c.company LIKE CONCAT('%',#{param1},'%')
  </if>
  <if test="param2 != null and param2 > 0">
    AND s.companyid=#{param2}
  </if>

2.在mapper.java 中编写代码:

List<Map<String, Object>> getSurveyInfoList(String dwmc, Long id);