ibatis 2张表关联，多对多，学生表，奖项表

一个学生含有多个奖项，一个奖项分配给多个学生（多对多）。一对一，奖项名称通过状态表获得。

people_status:

 

对学生的奖项进行增加修改删除。

增加某个学生的奖项，就是在people_status表中增加一条记录 。其他同理.

 

sql:

	 <select id="getAwardList" parameterClass="youth" resultClass="com.fjxhx.business.people.model.YouthModel">
	  $pageBegin$
		select a.id,a.name,a.identityId,a.tel,a.email,a.nation,
		a.projectDiscribe,a.feature,a.applyDate,a.fundedDate,a.fundedMoney,a.applyTime,a.equipDate,a.trainRecord,
		a.helpYear,a.teacherId,
		getAwardName(a.id) as youthAward, 
		getTeacherName(a.teacherId) as teacherNameTree,
		o.orgName AS projectPlace,
		(select b.value from b_status b where b.content=a.isDisable and b.code='yn') as isDisable,
		(select b.value from b_status b where b.content=a.isUndergrate and b.code='yn') as isUndergrate,
		(select b.value from b_status b where b.content=a.sex and b.code='sex') as sex,
		(select b.value from b_status b where b.content=a.projectCode and b.code='project_industry') as projectCode,
		(select b.value from b_status b where b.content=a.hkType and b.code='hkType') as hkType,
		(select b.value from b_status b where b.content=a.education and b.code='youth_education') as education
		 from ybc_youth a,ybc_org o where a.projectPlace=o.orgCode
		<dynamic prepend="and"> 
			<isNotEmpty prepend="and" property="projectPlace">
				projectPlace = #projectPlace#
			</isNotEmpty>
			 <isNotEmpty  prepend="and " property="name">
				name like concat('%', #name#, '%')
			</isNotEmpty>
			<isNotEmpty prepend="and" property="tel">
				tel like concat('%', #tel#, '%')
			</isNotEmpty>
			<isNotEmpty prepend="and" property="apply_beginTime" >
				 <![CDATA[ applyDate >= #apply_beginTime# ]]>
			 </isNotEmpty>
			 <isNotEmpty prepend="and" property="apply_endTime" >
			          <![CDATA[ applyDate <= #apply_endTime# ]]>
			 </isNotEmpty>
			<isNotEmpty prepend="and" property="funded_beginTime" >
				 <![CDATA[ fundedDate >= #funded_beginTime# ]]>
			 </isNotEmpty>
			 <isNotEmpty prepend="and" property="funded_endTime" >
			          <![CDATA[ fundedDate <= #funded_endTime# ]]>
			 </isNotEmpty>
			  <isNotEmpty prepend="and" property="statusContent">
			  a.id in(SELECT s.peopleId FROM ybc_people_status s WHERE s.statusCode='youth_award' AND s.statusContent=#statusContent#)
			  </isNotEmpty>
		</dynamic>
	  $pageEnd$
	  </select>
	  
	  <select id="getAwardAddList" parameterClass="youth" resultClass="com.fjxhx.business.people.model.YouthModel">
	  $pageBegin$
		select a.id,a.name,a.identityId,a.tel,a.email,a.nation,
		a.projectDiscribe,a.feature,a.applyDate,a.fundedDate,a.fundedMoney,a.applyTime,a.equipDate,a.trainRecord,
		a.helpYear,a.teacherId,
		getAwardName(a.id) as youthAward, 
		getTeacherName(a.teacherId) as teacherNameTree,
		o.orgName AS projectPlace,
		(select b.value from b_status b where b.content=a.isDisable and b.code='yn') as isDisable,
		(select b.value from b_status b where b.content=a.isUndergrate and b.code='yn') as isUndergrate,
		(select b.value from b_status b where b.content=a.sex and b.code='sex') as sex,
		(select b.value from b_status b where b.content=a.projectCode and b.code='project_industry') as projectCode,
		(select b.value from b_status b where b.content=a.hkType and b.code='hkType') as hkType,
		(select b.value from b_status b where b.content=a.education and b.code='youth_education') as education
		 from ybc_youth a,ybc_org o where a.projectPlace=o.orgCode
		<dynamic prepend="and"> 
			<isNotEmpty prepend="and" property="projectPlace">
				projectPlace = #projectPlace#
			</isNotEmpty>
			 <isNotEmpty  prepend="and " property="name">
				name like concat('%', #name#, '%')
			</isNotEmpty>
			<isNotEmpty prepend="and" property="tel">
				tel like concat('%', #tel#, '%')
			</isNotEmpty>
			<isNotEmpty prepend="and" property="apply_beginTime" >
				 <![CDATA[ applyDate >= #apply_beginTime# ]]>
			 </isNotEmpty>
			 <isNotEmpty prepend="and" property="apply_endTime" >
			          <![CDATA[ applyDate <= #apply_endTime# ]]>
			 </isNotEmpty>
			<isNotEmpty prepend="and" property="funded_beginTime" >
				 <![CDATA[ fundedDate >= #funded_beginTime# ]]>
			 </isNotEmpty>
			 <isNotEmpty prepend="and" property="funded_endTime" >
			          <![CDATA[ fundedDate <= #funded_endTime# ]]>
			 </isNotEmpty>
			  <isNotEmpty prepend="and" property="statusContent">
			  a.id not in(SELECT s.peopleId FROM ybc_people_status s WHERE s.statusCode='youth_award' AND s.statusContent=#statusContent#)
			  </isNotEmpty>
		</dynamic>
	  $pageEnd$
	  </select>	  
<insert id="add_people_status" parameterClass="com.fjxhx.business.people.model.YouthModel" >
	  	insert into ybc_people_status (peopleId,statusCode,statusContent)
	  	values(#peopleId#,'youth_award',#statusContent#)
	  </insert>
	  <delete id="del_people_status" parameterClass="com.fjxhx.business.people.model.YouthModel">
	  	delete from ybc_people_status where peopleId=#peopleId# and statusCode='youth_award'
	  	and statusContent=#statusContent#
	  </delete>

 java:

	public void getAwardList() {

		model.setFisrtRow((model.getPage() - 1) * model.getRows());

		model.setLastRow(model.getRows());

		try {
			BaseDao dao = getDao();

			List<YouthModel> peopleIds = dao.selectForList(
					"youth.getAwardList", model);

			HashMap<String, Object> hm = new HashMap<String, Object>();
			hm.put("total", peopleIds.size());
			hm.put("rows", peopleIds);
			hm.put("success", true);
			this.doResponseWithJson(hm);
		} catch (Exception e) {
			e.printStackTrace();
			this.setData("操作失败");
			this.setSuccess(false);
			this.send();
		}

	}

	public void getAwardAddList() {

		model.setFisrtRow((model.getPage() - 1) * model.getRows());

		model.setLastRow(model.getRows());

		try {
			BaseDao dao = getDao();

			List<YouthModel> peopleIds = dao.selectForList(
					"youth.getAwardAddList", model);

			HashMap<String, Object> hm = new HashMap<String, Object>();
			hm.put("total", peopleIds.size());
			hm.put("rows", peopleIds);
			hm.put("success", true);
			this.doResponseWithJson(hm);
		} catch (Exception e) {
			e.printStackTrace();
			this.setData("操作失败");
			this.setSuccess(false);
			this.send();
		}

	}

	public void addAwardConfig() {
		try {
			BaseDao dao = getDao();
			String peopleId[] = model.getPeopleId().split(",");
			for (int i = 0; i < peopleId.length; i++) {
				YouthModel temp = new YouthModel();

				temp.setPeopleId(peopleId[i]);
				temp.setStatusContent(model.getStatusContent());

				dao.insertSql("youth.add_people_status", temp);
			}

		} catch (Exception e) {
			e.printStackTrace();
			this.setData("操作失败");
			this.setSuccess(false);
			this.send();
		}

	}

	public void removeAwardConfig() {
		try {
			BaseDao dao = getDao();
			String peopleId[] = model.getPeopleId().split(",");
			for (int i = 0; i < peopleId.length; i++) {
				YouthModel temp = new YouthModel();

				temp.setPeopleId(peopleId[i]);
				temp.setStatusContent(model.getStatusContent());

				dao.insertSql("youth.del_people_status", temp);
			}

		} catch (Exception e) {
			e.printStackTrace();
			this.setData("操作失败");
			this.setSuccess(false);
			this.send();
		}

	}