NutzCN Logo
问答 复杂sql,加多条件筛选加分页有没有简便的方法
发布于 7天前 作者 wx_8qrqi2drp753jv8k744t 59 次浏览 复制 上一个帖子 下一个帖子
标签: ssdb
/**
	 * 省 + 市 + 关键字
	 */
	public QueryResult f1(int pageNumber, int pageSize, String name, String p, String c) {
		// 数据
		Sql sql = Sqls.queryEntity("SELECT " + "r.r_id as r_id," + "r.r_name as r_name," + "r.r_theme as r_theme,"
				+ "r.r_type as r_type," + "u.u_name as sfname," + "r.l_uuid as l_uuid,"
				+ "r.r_viewCount as r_viewCount," + "r.r_uuid as r_uuid," + "r.r_enchiridionURL as r_enchiridionURL,"
				+ "l.l_name as jsname," + "r.r_createTime as r_createTime"
				+ " FROM `t_resource_libraryum` r LEFT JOIN 	`t_user` u ON r.u_uuid=u.u_uuid LEFT JOIN   t_lecturer l ON r.l_uuid=l.l_uuid"
				+ " WHERE r.r_isremove = 0 AND u.u_province = '" + p + "' AND u.u_city = '" + c + "'"
				+ " AND r.r_name like '%" + name + "%' GROUP BY r.r_id ");

		Pager pager = dao.createPager(pageNumber, pageSize);
		sql.setPager(pager);
		sql.setEntity(dao.getEntity(Course.class));
		dao.execute(sql);
		// 是否有考题
		List<Course> list1 = sql.getList(Course.class);
		for (Course cobj : list1) {
			String rUuid = cobj.getR_uuid();
			Eexam exam = dao.fetch(Eexam.class, Cnd.where("R_UUID", "=", rUuid).and("E_ISREMOVE", "=", 0));
			if (exam != null) {
				cobj.setInExam(1);
			}
		}
		pager.setRecordCount(list1.size());
		QueryResult queryResult = new QueryResult(list1, pager);
		return queryResult;
	}

	/**
	 * 关键字+省份
	 */
	public QueryResult f2(int pageNumber, int pageSize, String name, String p) {
		// 数据
		Sql sql = Sqls.queryEntity("SELECT " + "r.r_id as r_id," + "r.r_name as r_name," + "r.r_theme as r_theme,"
				+ "r.r_type as r_type," + "u.u_name as sfname," + "r.l_uuid as l_uuid,"
				+ "r.r_viewCount as r_viewCount," + "r.r_uuid as r_uuid," + "r.r_enchiridionURL as r_enchiridionURL,"
				+ "l.l_name as jsname," + "r.r_createTime as r_createTime"
				+ " FROM `t_user` u, `t_resource_libraryum` r, t_lecturer l  WHERE r.r_isremove = 0 AND r.u_uuid is not null AND r.u_uuid = u.u_uuid "
				+ "AND u.u_province = '" + p + "'" + " AND r.l_uuid = l.l_uuid AND l.l_name like '%" + name
				+ "%' GROUP BY r.r_id ORDER BY r.r_createTime DESC ");

		Pager pager = dao.createPager(pageNumber, pageSize);
		sql.setPager(pager);
		sql.setEntity(dao.getEntity(Course.class));
		dao.execute(sql);
		List<Course> list1 = sql.getList(Course.class);
		// 是否有考题
		for (Course cobj : list1) {
			String rUuid = cobj.getR_uuid();
			Eexam exam = dao.fetch(Eexam.class, Cnd.where("R_UUID", "=", rUuid).and("E_ISREMOVE", "=", 0));
			if (exam != null) {
				cobj.setInExam(1);
			}
		}
		pager.setRecordCount(list1.size());
		QueryResult queryResult = new QueryResult(list1, pager);
		return queryResult;
	}

	/**
	 * 省份
	 */
	public QueryResult f3(int pageNumber, int pageSize, String p) {
		// 数据
		Sql sql = Sqls.queryEntity("SELECT " + "r.r_id as r_id," + "r.r_name as r_name," + "r.r_theme as r_theme,"
				+ "r.r_type as r_type," + "u.u_name as sfname," + "r.l_uuid as l_uuid,"
				+ "r.r_viewCount as r_viewCount," + "r.r_uuid as r_uuid," + "r.r_enchiridionURL as r_enchiridionURL,"
				+ "r.r_createTime as r_createTime"
				+ " FROM `t_user` u, `t_resource_libraryum` r  WHERE r.r_isremove = 0 AND r.u_uuid is not null AND r.u_uuid = u.u_uuid AND u.u_province = '"
				+ p + "'" + "GROUP BY r.r_id ORDER BY r.r_createTime DESC ");

		Pager pager = dao.createPager(pageNumber, pageSize);
		sql.setPager(pager);
		sql.setEntity(dao.getEntity(Course.class));
		dao.execute(sql);
		List<Course> list1 = sql.getList(Course.class);

		for (Course c : list1) {
			// 是否有考题
			String rUuid = c.getR_uuid();
			Eexam exam = dao.fetch(Eexam.class, Cnd.where("R_UUID", "=", rUuid).and("E_ISREMOVE", "=", 0));
			if (exam != null) {
				c.setInExam(1);
			}
			// 获取讲师名字
			if (c.getL_uuid() != null && !c.getL_uuid().equals("")) {
				try {
					String lecturer = (dao.fetch(Lecturer.class, Cnd.where("l_uuid", "=", c.getL_uuid()))).getName();
					c.setLecturer(lecturer);
				} catch (Exception e) {
					System.out.println("异常的l_uuid" + c.getL_uuid());
				}
			}
		}
		pager.setRecordCount(list1.size());
		QueryResult queryResult = new QueryResult(list1, pager);
		return queryResult;
	}

	/**
	 * 关键字
	 */
	public QueryResult f4(int pageNumber, int pageSize, String name) {
		Pager pager = dao.createPager(pageNumber, pageSize);
		// 数据
		List<Course> list = dao.query(Course.class,
				Cnd.where("r_isremove", "=", 0).and("r_name", "like", "%" + name + "%").desc("r_createTime"), pager);
		pager.setRecordCount(
				dao.count(Course.class, Cnd.where("r_isremove", "=", 0).and("r_name", "like", "%" + name + "%")));

		for (Course c : list) {
			// 获取司法局名字
			if (c.getU_uuid() != null && !c.getU_uuid().equals("")) {
				String sfname = (dao.fetch(TUser.class, Cnd.where("u_uuid", "=", c.getU_uuid()))).getName();
				c.setSfname(sfname);
			}
			// 是否有考题
			String rUuid = c.getR_uuid();
			Eexam exam = dao.fetch(Eexam.class, Cnd.where("R_UUID", "=", rUuid).and("E_ISREMOVE", "=", 0));
			if (exam != null) {
				c.setInExam(1);
			}
			// 获取讲师名字
			if (c.getL_uuid() != null && !c.getL_uuid().equals("")) {
				try {
					String lecturer = (dao.fetch(Lecturer.class, Cnd.where("l_uuid", "=", c.getL_uuid()))).getName();
					c.setLecturer(lecturer);
				} catch (Exception e) {
					System.out.println("异常的l_uuid" + c.getL_uuid());
				}
			}
		}
		// 页数
		QueryResult queryResult = new QueryResult(list, pager);
		return queryResult;
	}
	/**
	 * 无条件
	 */
	public QueryResult f5(int pageNumber, int pageSize) {
		Pager pager = dao.createPager(pageNumber, pageSize);
		// 数据
		List<Course> list = dao.query(Course.class, Cnd.where("r_isremove", "=", 0).desc("r_createTime"), pager);
		pager.setRecordCount(dao.count(Course.class, Cnd.where("r_isremove", "=", 0)));
		for (Course c : list) {
			// 获取司法局名字
			if (c.getU_uuid() != null && !c.getU_uuid().equals("")) {
				String sfname = (dao.fetch(TUser.class, Cnd.where("u_uuid", "=", c.getU_uuid()))).getName();
				c.setSfname(sfname);
			}
			// 获取讲师名字
			if (c.getL_uuid() != null && !c.getL_uuid().equals("")) {
				try {
					String lecturer = (dao.fetch(Lecturer.class, Cnd.where("l_uuid", "=", c.getL_uuid()))).getName();
					c.setJsname(lecturer);
				} catch (Exception e) {
					System.out.println("异常的l_uuid" + c.getL_uuid());
				}
			}
			String rUuid = c.getR_uuid();
			Eexam exam = dao.fetch(Eexam.class, Cnd.where("R_UUID", "=", rUuid).and("E_ISREMOVE", "=", 0));
			if (exam != null) {
				c.setInExam(1);
			}
		}
		// 页数
		QueryResult queryResult = new QueryResult(list, pager);
		return queryResult;
	}

	/**
	 * 省 + 市
	 */
	public QueryResult f6(int pageNumber, int pageSize, String p, String c) {
		// 数据
		Sql sql = Sqls.queryEntity("SELECT " + "r.r_id as r_id," + "r.r_name as r_name," + "r.r_theme as r_theme,"
				+ "r.r_type as r_type," + "u.u_name as sfname," + "u.u_uuid as u_uuid," + "r.l_uuid as l_uuid,"
				+ "r.r_viewCount as r_viewCount," + "r.r_uuid as r_uuid," + "r.r_enchiridionURL as r_enchiridionURL,"
				+ "l.l_name as lecturer_name," + "r.r_createTime as r_createTime"
				+ " FROM `t_user` u, `t_resource_libraryum` r, t_lecturer l WHERE r.u_uuid = u.u_uuid AND u.u_province = '"
				+ p + "' AND u.u_city = '" + c
				+ "' and r.r_isremove = 0 GROUP BY r.r_id ORDER BY r.r_createTime DESC ");

		Pager pager = dao.createPager(pageNumber, pageSize);
		sql.setPager(pager);
		sql.setEntity(dao.getEntity(Course.class));
		dao.execute(sql);
		List<Course> list1 = sql.getList(Course.class);
		// 是否有考题
		for (Course cobj : list1) {
			String rUuid = cobj.getR_uuid();
			Eexam exam = dao.fetch(Eexam.class, Cnd.where("R_UUID", "=", rUuid).and("E_ISREMOVE", "=", 0));
			if (exam != null) {
				cobj.setInExam(1);
			}
		}
		pager.setRecordCount(list1.size());
		QueryResult queryResult = new QueryResult(list1, pager);
		return queryResult;
	}
	/*
	 * 省+市+区
	 */
	public QueryResult f7(int pageNumber, int pageSize, String p, String c,String d) {

		Sql sql = Sqls.queryEntity("SELECT " + "r.r_id as r_id," + "r.r_name as r_name," + "r.r_theme as r_theme,"
				+ "r.r_type as r_type," + "u.u_name as sfname," + "u.u_uuid as u_uuid," + "r.l_uuid as l_uuid,"
				+ "r.r_viewCount as r_viewCount," + "r.r_uuid as r_uuid," + "r.r_enchiridionURL as r_enchiridionURL,"
				+ "l.l_name as lecturer_name," + "r.r_createTime as r_createTime"
				+ " FROM `t_user` u, `t_resource_libraryum` r, t_lecturer l WHERE r.u_uuid = u.u_uuid AND u.u_province = '"
				+ p + "' AND u.u_city = '" + c + "' AND u.u_county = '"+d
				+ "' and r.r_isremove = 0 GROUP BY r.r_id ORDER BY r.r_createTime DESC ");

		Pager pager = dao.createPager(pageNumber, pageSize);
		sql.setPager(pager);
		sql.setEntity(dao.getEntity(Course.class));
		dao.execute(sql);
		List<Course> list1 = sql.getList(Course.class);
		// 是否有考题
		for (Course cobj : list1) {
			String rUuid = cobj.getR_uuid();
			Eexam exam = dao.fetch(Eexam.class, Cnd.where("R_UUID", "=", rUuid).and("E_ISREMOVE", "=", 0));
			if (exam != null) {
				cobj.setInExam(1);
			}
		}
		pager.setRecordCount(list1.size());
		QueryResult queryResult = new QueryResult(list1, pager);
		return queryResult;
		
	}
	
	/*
	 * 省 + 市 + 区 + 关键字
	 */
	public QueryResult f8(int pageNumber, int pageSize, String p, String c,String d, String name) {

		Sql sql = Sqls.queryEntity("SELECT " + "r.r_id as r_id," + "r.r_name as r_name," + "r.r_theme as r_theme,"
				+ "r.r_type as r_type," + "u.u_name as sfname," + "u.u_uuid as u_uuid," + "r.l_uuid as l_uuid,"
				+ "r.r_viewCount as r_viewCount," + "r.r_uuid as r_uuid," + "r.r_enchiridionURL as r_enchiridionURL,"
				+ "l.l_name as lecturer_name," + "r.r_createTime as r_createTime"
				+ " FROM `t_user` u, `t_resource_libraryum` r, t_lecturer l WHERE r.u_uuid = u.u_uuid AND u.u_province = '"+ p
				+ "' AND u.u_city = '" + c + "' AND u.u_county = '"+d+"AND "+"AND l.l_name like '%" + name + "%'"
				+ "' and r.r_isremove = 0  GROUP BY r.r_id ORDER BY r.r_createTime DESC ");

		Pager pager = dao.createPager(pageNumber, pageSize);
		sql.setPager(pager);
		sql.setEntity(dao.getEntity(Course.class));
		dao.execute(sql);
		List<Course> list1 = sql.getList(Course.class);
		// 是否有考题
		for (Course cobj : list1) {
			String rUuid = cobj.getR_uuid();
			Eexam exam = dao.fetch(Eexam.class, Cnd.where("R_UUID", "=", rUuid).and("E_ISREMOVE", "=", 0));
			if (exam != null) {
				cobj.setInExam(1);
			}
		}
		pager.setRecordCount(list1.size());
		QueryResult queryResult = new QueryResult(list1, pager);
		return queryResult;
		
	}
	
	



	/**
	 * 课程列表
	 */
	public QueryResult getCourseList(String name, int pageNumber, int pageSize, String provinceCode, String cityCode,String districtCode,String column,Date cretaTime) {
		// 省 + 市 + 关键字 搜索
		if (provinceCode != null && !provinceCode.equals("") && cityCode != null && !"".equals(cityCode) && name != null
				&& !"".equals(name)) {
			return f1(pageNumber, pageSize, name, provinceCode, cityCode);
		}
		// 省份 + 关键字 搜索
		if (provinceCode != null && !provinceCode.equals("") && name != null && !"".equals(name)) {
			return f2(pageNumber, pageSize, name, provinceCode);
		}
		// 省 + 市
		if (provinceCode != null && !provinceCode.equals("") && cityCode != null && !"".equals(cityCode)) {
			return f6(pageNumber, pageSize, provinceCode, cityCode);
		}
		//省 + 市 + 区
		if(provinceCode != null && !provinceCode.equals("") && cityCode != null && !"".equals(cityCode)&&districtCode.equals("")&& districtCode!=null) {
		return f7(pageNumber, pageSize, provinceCode, cityCode,districtCode);
		}
		//省 + 市 + 区  + 关键字
		if(provinceCode != null && !provinceCode.equals("") && cityCode != null && !"".equals(cityCode)&&districtCode.equals("")&& districtCode!=null&& name != null
				&& !"".equals(name)) {
			return f8(pageSize, pageNumber, name, provinceCode, cityCode, districtCode);
		}
5 回复

有点写不动了这个

有dao.queryByJoin啊

????????????

dao接口有

添加回复
请先登陆
回到顶部