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

最新推荐文章于 2024-06-12 09:56:08 发布

原创最新推荐文章于 2024-06-12 09:56:08 发布 · 388 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #数据库

ibatis 同时被 2 个专栏收录

5 篇文章

订阅专栏

sql

5 篇文章

订阅专栏

本文提供了一段使用Java实现的学生ID查询对应导师信息的代码示例，包括获取导师列表和未选导师列表。代码通过SQL查询实现了导师ID的筛选，并根据传入的参数动态构建查询条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

select teacherId from ybc_youth where id=#VALUE#

</select>

select * from ybc_teacher t $wheresql$

</select>

select * from ybc_teacher t

t.id in ($teacherId$)

</isNotEmpty>

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();
		}
	}