通过sql语句拼接 和在action中写sql语句拼接完成复杂的sql语句

//根据学生的id，查找他数据库中存的导师字段‘1,2,3,4’字符串类型。可以使用in，not in 可以查找出所有对应的老师信息。用 $$

<select id="getTeacherid" parameterClass="java.lang.String" resultClass="java.lang.String">

 select teacherId from ybc_youth where id=#VALUE#

 </select>

 <select id="getTeacherAddList" parameterClass="java.lang.String" resultClass="com.fjxhx.business.people.model.TeacherModel">

select * from ybc_teacher t $wheresql$

 </select>

 <select id="getTeacherList" parameterClass="java.lang.String" resultClass="com.fjxhx.business.people.model.TeacherModel">

select * from ybc_teacher t

<dynamic prepend="where">

<isNotEmpty prepend="and">

t.id in ($teacherId$)

</isNotEmpty>

<isEmpty prepend="and">

t.id in (null)

</isEmpty>

</dynamic>

 </select>

 

Action:

	private TeacherModel model = new TeacherModel();
	public TeacherModel getModel() {
		// TODO Auto-generated method stub
		return model; //这个model可以取到页面传过来的值
	}
	//左边grid导师(未选导师)
public void getTeacherAddList(){
		
		model.setFisrtRow((model.getPage() - 1) * model.getRows());
		
		model.setLastRow(model.getRows());
		
		try {
			BaseDao dao=getDao();
			//已选导师id
			String teacherIds = (String)dao.selectObject("youth.getTeacherid",model.getId());
			
			//拼接sql  orgCode like '%$orgCode$%'
			String s;
			if(teacherIds!=null&&!teacherIds.equals("")){
			s="where t.id not in ("+teacherIds+")"+((model.getName()!=null&&!model.getName().equals(""))?" and name like '%"+model.getName():"")+((model.getName()!=null&&!model.getName().equals(""))?"%'":"")+((model.getIdentitycardId()!=null&&!model.getIdentitycardId().equals(""))?" and identitycardId="+model.getIdentitycardId():"")
					+((model.getCertificateId()!=null&&!model.getCertificateId().equals(""))?" and certificateId="+model.getCertificateId():"")+((model.getProfessionalField()!=null&&!model.getProfessionalField().equals(""))?" and professionalField="+model.getProfessionalField():""); 
			System.out.println(s);
			}else{
				s="where 1=1"+((model.getName()!=null&&!model.getName().equals(""))?" and name like '%"+model.getName():"")+((model.getName()!=null&&!model.getName().equals(""))?"%'":"")+((model.getIdentitycardId()!=null&&!model.getIdentitycardId().equals(""))?" and identitycardId="+model.getIdentitycardId():"")
				+((model.getCertificateId()!=null&&!model.getCertificateId().equals(""))?" and certificateId="+model.getCertificateId():"")+((model.getProfessionalField()!=null&&!model.getProfessionalField().equals(""))?" and professionalField="+model.getProfessionalField():""); 
			}
			model.setWheresql(s);
/*			 Map<String, Object> param = new HashMap<String, Object>();
			 param.put("pageBegin", model.getPageBegin());
			 param.put("pageEnd", model.getPageEnd());
			 param.put("wheresql", model.getWheresql());*/
			List<TeacherModel> tlist = dao.selectForList("youth.getTeacherAddList",model.getWheresql());
			
			HashMap<String ,Object> tm = new HashMap<String ,Object>();
			if(tlist.size()==0){
				tm.put("tatal", 0);
			}else{
				tm.put("total", tlist.size());
			}
			tm.put("rows", tlist);
			tm.put("success", true);
			this.doResponseWithJson(tm);
		} catch (Exception e) {
			e.printStackTrace();
			this.setData("操作失败");
			this.setSuccess(false);
			this.send();
		}
	}

	//右边grid导师（已选导师）
	public void getTeacherList(){
		
		model.setFisrtRow((model.getPage() - 1) * model.getRows());
		
		model.setLastRow(model.getRows());
		
		try {
			BaseDao dao=getDao();
			String teacherIds = (String)dao.selectObject("youth.getTeacherid", model.getId());
			System.out.println(teacherIds);

			List<TeacherModel> tlist = dao.selectForList("youth.getTeacherList", teacherIds);
			HashMap<String ,Object> tm = new HashMap<String ,Object>();
			
			if(tlist.size()==0){
				tm.put("tatal", 0);
			}else{
				tm.put("total", tlist.size());
			}
			tm.put("rows", tlist);
			tm.put("success", true);
			this.doResponseWithJson(tm);
		} catch (Exception e) {
			e.printStackTrace();
			this.setData("操作失败");
			this.setSuccess(false);
			this.send();
		}
	}