jfinal里面的sql查询拼接实例

	/**
	 * 查看工匠的评分及个人评价
	 */
	@Before(AppException.class)
	public void getWorkScores() {
		// 得到工匠的id
		String id = getPara("wid");
		// String id = getHeader("wid");
		Record worker = Db.findById("workers", "id", id);
		qiniuImages.clear();
		bdImages.clear();

		// 评论或回复人的头像
		qiniuImages.add("wu_pic");
		bdImages.add("wupic");

		qiniuImages.add("workerOrderScore_Pic");
		bdImages.add("workerOrderScorePic");

		if (worker != null) {
			// 四舍五入格式化
			DecimalFormat format = new DecimalFormat("#.00");
			// 工人工程质量平均分数
			Double workerOrderQualityScore = 0.0;
			workerOrderQualityScore = Db
					.queryDouble(
							"SELECT   AVG(workerOrderQualityScore)    FROM workerorderscore WHERE   workerId=?",
							id);
			if (workerOrderQualityScore == null) {
				workerOrderQualityScore = 0.0;
			}
			String workerOrderQualityScore1 = format
					.format(workerOrderQualityScore);
			// 工人施工效率平均分数
			Double workerOrderEfficiencyScore = 0.0;
			workerOrderEfficiencyScore = Db
					.queryDouble(
							"SELECT   AVG(workerOrderEfficiencyScore)    FROM workerorderscore WHERE   workerId=?",
							id);
			if (workerOrderEfficiencyScore == null) {
				workerOrderEfficiencyScore = 0.0;
			}
			String workerOrderEfficiencyScore1 = format
					.format(workerOrderEfficiencyScore);
			// 工人客户服务平均分数
			Double workerOrderServiceScore = 0.0;
			workerOrderServiceScore = Db
					.queryDouble(
							"SELECT   AVG(workerOrderServiceScore)    FROM workerorderscore WHERE   workerId=?",
							id);
			if (workerOrderServiceScore == null) {
				workerOrderServiceScore = 0.0;
			}
			String workerOrderServiceScore1 = format
					.format(workerOrderServiceScore);
			// 工人工地卫生平均分数
			Double workerOrderHealthScore = 0.0;
			workerOrderHealthScore = Db
					.queryDouble(
							"SELECT   AVG(workerOrderHealthScore)    FROM workerorderscore WHERE   workerId=?",
							id);
			if (workerOrderHealthScore == null) {
				workerOrderHealthScore = 0.0;
			}
			String workerOrderHealthScore1 = format
					.format(workerOrderHealthScore);
			// 工人出勤平均分数
			Double workerOrderAttence = 0.0;
			workerOrderAttence = Db
					.queryDouble(
							"SELECT   AVG(workerOrderAttence)  FROM workerorderscore WHERE   workerId=?",
							id);
			if (workerOrderAttence == null) {
				workerOrderAttence = 0.0;
			}
			String workerOrderAttence1 = format.format(workerOrderAttence);
			int counts = JfinalUtils
					.getCounts("SELECT  COUNT(1) FROM `workerorderscore` WHERE workerId="
							+ id);
			Record record = JfinalUtils
					.getPaginate(
							this,
							counts,
							JfinalUtils.init_pageCurrent(this),
							JfinalUtils.init_pageSize(this),
							"SELECT  workerorderscore.workerOrderId, workerorderscore.workerOrderScoreId , workerorderscore.workscordTime  as wTime,    workerorderscore.workerOrderScoreContent  as wcontent  ",
							" FROM `workerorderscore`  WHERE workerId=" + id,
							null, null);
			List<Integer> orderIds = new ArrayList<Integer>();
			List<Object> scoreIds = new ArrayList<Object>();
			List<Record> record2 = record.get("Listdates");
			for (Record record3 : record2) {
				if (!scoreIds.contains(record3.getInt("workerOrderScoreId"))) {//工单评论图片表里会用到这个id
					scoreIds.add(record3.getInt("workerOrderScoreId"));
				}
				if (!orderIds.contains(record3.getInt("workerOrderId"))) {//工单id，在工单中找到下单人的id
					orderIds.add(record3.getInt("workerOrderId"));
				}
			}
			StringBuffer sql = new StringBuffer(//用户表关联工单表查询语句
					" select wo.orderId,  u.unickname,  u.upicadress_pic,u.upicadress from workorders wo inner join userinfo u on u.uid = wo.orderUserId ");
			StringBuffer sqlscore = new StringBuffer(//图片评论表查询语句
					" select * from workerorderscorepic w where 1=1 ");
			List<Object> parms = new ArrayList<Object>();//查询图片用到的这个参数
			List<Object> parmsscore = new ArrayList<Object>();//查询用户用到的这个参数
			//拼接图片评论查询语句
			if (scoreIds.size() > 0) {
				sqlscore.append(" and w.workerOrderScoreId in (");
				for (int i = 0; i < scoreIds.size(); i++) {
					if (i != 0) {
						sqlscore.append(",");
					}
					sqlscore.append("?");
					parmsscore.add(scoreIds.get(i));
				}
				sqlscore.append(")");

			}
			//拼接用户查询参数
			if (orderIds.size() > 0) {
				sql.append(" where wo.orderId in (");
				for (int i = 0; i < orderIds.size(); i++) {
					if (i != 0) {
						sql.append(",");
					}
					sql.append("?");
					parms.add(orderIds.get(i));
				}
				sql.append(")");

			}
			List<Record> find = Db.find(sql.toString(), parms.toArray());
			List<Record> find2 = Db.find(sqlscore.toString(),
					parmsscore.toArray());
			//把查询出来的结果集放到最大的集合里面去
			for (Record record3 : record2) {
				for (Record record4 : find) {
					if (record4.getInt("orderId").equals(
							record3.getInt("workerOrderId"))) {
						record3.set("wu_pic", record4.get("upicadress_pic"));
						record3.set("wu_name", record4.get("unickname"));
					}
				}
				for (Record record4 : find2) {
					if (record4.getInt("workerOrderScoreId").equals(
							record3.getInt("workerOrderScoreId"))) {
						String pics = record4.getStr("workerOrderScore_Pic");
						if (pics != null) {
							String[] split = pics.substring(1).split(";");
							record3.set("wu_image", split);
						}
					}

				}
			}
			Double wstars = Db
					.queryDouble("select wstars from workers where id=" + id);
			// 评价说明
			String scoreInstructions = Db
					.queryStr("SELECT    cqnswitch  FROM config  WHERE cid=22");
			record.set("wstars", wstars)
					.set("counts", counts)
					.set("workerOrderQualityScore", workerOrderQualityScore1)
					.set("workerOrderEfficiencyScore",
							workerOrderEfficiencyScore1)
					.set("workerOrderServiceScore", workerOrderServiceScore1)
					.set("workerOrderHealthScore", workerOrderHealthScore1)
					.set("workerOrderAttence", workerOrderAttence1)
					.set("scoreInstructions", scoreInstructions);
			JsonPrint.print(this, 200, record);
		} else {
			JsonPrint.print(this, 300, "该工匠不存在!");
		}
	}