복잡한 Join문 mybatis 예시

신입때 나중에 필요하면 다시 보려고 evernote에 정리해둔 복잡한 join 쿼리입니다. 혹시나 mybatis를 사용하는 초보분들에게 좋은 자료가 되었으면 좋겠습니다.

	<resultMap id="SurveyVo" type="com.example.survey.database.survey.dto.SurveyVo">
		<id property="surveyKey"	column="survey_key"	jdbcType="BIGINT"	javaType="java.lang.Long"/>
		<result property="surveyTitle"	column="survey_title"	jdbcType="VARCHAR"	javaType="java.lang.String"/>
		<result property="surveyDesc"	column="survey_desc"	jdbcType="VARCHAR"	javaType="java.lang.String"/>
		<result property="surveyType"	column="survey_type"	jdbcType="VARCHAR"	javaType="java.lang.String"/>
		<result property="surveyStatus"	column="survey_status"	jdbcType="VARCHAR"	javaType="java.lang.String"/>
		<result property="startDt"	column="start_dt"	jdbcType="TIMESTAMP"	javaType="java.sql.Timestamp"/>
		<result property="endDt"	column="end_dt"	jdbcType="TIMESTAMP"	javaType="java.sql.Timestamp"/>
		<result property="visibleYn"	column="visible_yn"	jdbcType="VARCHAR"	javaType="java.lang.String"/>
		<result property="regDt"	column="reg_dt"	jdbcType="TIMESTAMP"	javaType="java.sql.Timestamp"/>
		<result property="modDt"	column="mod_dt"	jdbcType="TIMESTAMP"	javaType="java.sql.Timestamp"/>
		<result property="adminKey"	column="admin_key"	jdbcType="BIGINT"	javaType="java.lang.Long"/>
		<result property="adminName"	column="admin_name"	jdbcType="VARCHAR"	javaType="java.lang.String"/>
		<result property="cursor"	column="custom_cursor"	jdbcType="VARCHAR"	javaType="java.lang.String"/>
		<collection property="surveyQuestionVos"	columnPrefix="sq1_"	resultMap="com.example.survey.database.survey.mappers.SurveyQuestionMapper.SurveyQuestionVo"/>
		<collection property="surveyReportVos"	columnPrefix="sr1_"	resultMap="com.example.survey.database.survey.mappers.SurveyReportMapper.SurveyReportVo"/>
	</resultMap>

	<sql id="surveyColumns">
		${alias}.survey_key ${prefix}survey_key,
		${alias}.survey_title ${prefix}survey_title,
		${alias}.survey_desc ${prefix}survey_desc,
		${alias}.survey_type ${prefix}survey_type,
		${alias}.survey_status ${prefix}survey_status,
		${alias}.start_dt ${prefix}start_dt,
		${alias}.end_dt ${prefix}end_dt,
		${alias}.visible_yn ${prefix}visible_yn,
		${alias}.reg_dt ${prefix}reg_dt,
		${alias}.mod_dt ${prefix}mod_dt,
		${alias}.admin_key ${prefix}admin_key,
		${alias}.admin_name ${prefix}admin_name
	</sql>
    
    
    <select  id="selectSurveyBySearchMapWithSqNSqaNSrNSrs" resultMap="SurveyVo">
        SELECT 
            t1.*,
            <include refid="com.example.survey.database.survey.mappers.SurveyQuestionMapper.surveyQuestionColumns"><property name="alias" value="sq1"/><property name="prefix" value="sq1_"/></include>,
            <include refid="com.example.survey.database.survey.mappers.SurveyQuestionAnswerMapper.surveyQuestionAnswerColumns"><property name="alias" value="sqa1"/><property name="prefix" value="sq1_sqa1_"/></include>,
            <include refid="com.example.survey.database.survey.mappers.SurveyReportSheetMapper.surveyReportSheetColumns"><property name="alias" value="srs1"/><property name="prefix" value="sq1_sqa1_srs1_"/></include>,
            <include refid="com.example.survey.database.survey.mappers.SurveyReportMapper.surveyReportColumns"><property name="alias" value="sr1"/><property name="prefix" value="sr1_"/></include>,
            <include refid="com.example.survey.database.survey.mappers.SurveyReportSheetMapper.surveyReportSheetColumns"><property name="alias" value="srs1"/><property name="prefix" value="sr1_srs1_"/></include>
        FROM(
            SELECT
                <include refid="surveyColumns"><property name="alias" value="s1"/><property name="prefix" value=""/></include>
            FROM 
                tb_survey s1
            WHERE
                1=1                 
                AND s1.survey_key = #{surveyKey}
            ) t1
        LEFT JOIN tb_survey_question sq1 ON t1.survey_key = sq1.survey_key
        LEFT JOIN tb_survey_question_answer sqa1 ON sq1.question_key = sqa1.question_key
        LEFT JOIN tb_survey_report_sheet srs1 ON sqa1.answer_key = srs1.answer_key
        LEFT JOIN tb_survey_report sr1 ON srs1.report_key = sr1.report_key
    </select>