jfinal里面的sql查询拼接实例

/** * 查看工匠的评分及个人评价 */@Before(AppException.class)public void getWorkScores() {// 得到工匠的idString 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"))) {//工单评论图片表里会用到这个idscoreIds.add(record3.getInt("workerOrderScoreId"));}if (!orderIds.contains(record3.getInt("workerOrderId"))) {//工单id，在工单中找到下单人的idorderIds.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, "该工匠不存在!");}}