mybatis的parameterType使用map实现真正的sql随意写
在dao层给map赋值
纠正一下应该把dd作为传入而不是sbiId;
sqlMap中的parameterType="java.util.Map", 就 OK
package com.ldrc.srm.jczx.web.results.module.screen; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import com.alibaba.citrus.service.requestcontext.parser.ParserRequestContext; import com.alibaba.citrus.turbine.Context; import com.ldrc.srm.common.web.OuUser; import com.ldrc.srm.jczx.biz.common.DateUtil; import com.ldrc.srm.jczx.biz.construction.technology.IndexPlanManager; import com.ldrc.srm.jczx.dal.dataobject.construction.projectinfo.Assessment; import com.ldrc.srm.jczx.dal.dataobject.construction.technology.PatentExecute; import com.ldrc.srm.jczx.dal.dataobject.construction.technology.SmAssessmentPlan; public class Kaimpatent { @Autowired private IndexPlanManager ipm; @Autowired ParserRequestContext parser; public void execute(Context context){ OuUser ou = OuUser.getCurrentUser(); Integer sbiId = ou.getSubjectBaseId(); List<PatentExecute> pes = ipm.selectAllAimpatent(); List<PatentExecute> pesList = new ArrayList<PatentExecute>(); List<Assessment> ass = ipm.getAssessmentBySbiId(sbiId); List<SmAssessmentPlan> assessmentPlanList = new ArrayList<SmAssessmentPlan>(); if (!ass.isEmpty()) { for (Assessment assessment : ass) { List<SmAssessmentPlan> assessmentPlan = assessment.getAssessmentPlan(); if (!assessmentPlan.isEmpty()) { assessmentPlanList.addAll(assessmentPlan); } } if (!assessmentPlanList.isEmpty()) { for (SmAssessmentPlan smAssessmentPlan : assessmentPlanList) { Integer sadId = smAssessmentPlan.getSadId(); if (!pes.isEmpty()) { for (PatentExecute patentExecute : pes) { if (patentExecute.getSadId() != null && patentExecute.getSadId().equals(sadId)) {//使用程序代替sql相当于关联查询 pesList.add(patentExecute); } } } } } } context.put("rUrl", parser.getRequest().getHeader("Referer")); context.put("pes", pesList); context.put("DateUtil", new DateUtil()); /** * 上面的方法写了这么多其实不必 * 一条sql就搞定 * <select id="r" resultMap="BaseResultMap" parameterType="java.util.Map"> select t3.* from sm_s_assessment t1 left join SM_S_ASSESSMENT_PLAN t2 on t1.SASS_ID = t2.SASS_ID left join SM_PATENT_EXECUTE t3 on t2.SAD_ID = t3.SAD_ID where t1.SBI_ID = #{sbiId, jdbcType=INTEGER} </select> 问题是我的resultMap中没有SBI_ID这个列,怎么办 有办法, 注意到parameterType有没有比平时不一样没<select id="r" resultMap="BaseResultMap" parameterType="java.util.Map"> 呵呵, 就是parameterType="java.util.Map" ; 有了这个你可以在dao随意传递多少个参数, 不管下面的resultMap中有没有对应的列或属性 注意的是dao里传参, Integer sbiId = 21; Map<String, Object> m = new HashMap<String, Object>(); m.put("sbiId", sbiId); List<PatentExecute> selectList = sqlSession.selectList("PatentExecuteMapper.r", m); 总结: 其实就是把parameterType平时使用的javaBean或四类八种换了 map, 原理是一样的 */
其实还可以把上面的sql写到 指标 的sqlmap 中resultMap返回PatentExecuteMapper.BaseResultMap即可那么parameterType也不必须使用map类型了
如:<select id="r" resultMap="PatentExecuteMapper.BaseResultMap" parameterType="java.util.Map">
select t3.* from sm_s_assessment t1
left join SM_S_ASSESSMENT_PLAN t2
on t1.SASS_ID = t2.SASS_ID
left join SM_PATENT_EXECUTE t3
on t2.SAD_ID = t3.SAD_ID
where t1.SBI_ID = #{sbiId, jdbcType=INTEGER}
</select>
mybatis是不是特别灵活啊!
}
}
sqlMap文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="PatentExecuteMapper" > <resultMap id="BaseResultMap" type="com.ldrc.srm.jczx.dal.dataobject.construction.technology.PatentExecute" > <id column="SPE_ID" property="speId" jdbcType="INTEGER" /> <result column="SAD_ID" property="sadId" jdbcType="INTEGER" /> <result column="SPE_NUM" property="speNum" jdbcType="VARCHAR" /> <result column="SPE_APPLY_DATE" property="speApplyDate" jdbcType="DATE" /> <result column="SPE_APPLY_REMARK" property="speApplyRemark" jdbcType="VARCHAR" /> <result column="SPE_PEOPLE_NAME" property="spePeopleName" jdbcType="VARCHAR" /> <result column="SPE_INVENT_NAME" property="speInventName" jdbcType="VARCHAR" /> <result column="SPE_STUTAS" property="speStutas" jdbcType="VARCHAR" /> <result column="SPE_PRIORITY" property="spePriority" jdbcType="VARCHAR" /> <result column="SPE_PUBLISH_NUM" property="spePublishNum" jdbcType="VARCHAR" /> <result column="SPE_ISSUE_NUM" property="speIssueNum" jdbcType="VARCHAR" /> <result column="SPE_DIGEST" property="speDigest" jdbcType="VARCHAR" /> <result column="SPE_MAIN_NUM" property="speMainNum" jdbcType="VARCHAR" /> <result column="SPE_AGENT" property="speAgent" jdbcType="VARCHAR" /> <result column="SPE_FILE" property="speFile" jdbcType="VARCHAR" /> <collection property="files" column="SPE_FILE" ofType="com.ldrc.srm.jczx.dal.dataobject.file.SrmFile" select="SrmFileMapper.selectByEgCode" resultMap="SrmFileMapper.BaseResultMap"> </collection> </resultMap> <sql id="Base_Column_List" > SPE_ID, SAD_ID, SPE_NUM, SPE_APPLY_DATE, SPE_APPLY_REMARK, SPE_PEOPLE_NAME, SPE_INVENT_NAME, SPE_STUTAS, SPE_PRIORITY, SPE_PUBLISH_NUM, SPE_ISSUE_NUM, SPE_DIGEST, SPE_MAIN_NUM, SPE_AGENT, SPE_FILE </sql> <select id="r" resultMap="BaseResultMap" parameterType="java.util.Map"> select t3.* from sm_s_assessment t1 left join SM_S_ASSESSMENT_PLAN t2 on t1.SASS_ID = t2.SASS_ID left join SM_PATENT_EXECUTE t3 on t2.SAD_ID = t3.SAD_ID where t1.SBI_ID = #{sbiId, jdbcType=INTEGER} </select> <select id="selectAllAimpatent" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select * from SM_PATENT_EXECUTE </select> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from SM_PATENT_EXECUTE where SPE_ID = #{speId,jdbcType=INTEGER} </select> </mapper>
