GreenDao-自定义SQL查询-拼接多个查询条件-AndroidStudio

  1     //获取本地Pad（离线工作票列表）
  2     public static List<WTDetailTableBean> getPadWTList(String token,
  3                                                  String userId,
  4                                                  String org,
  5                                                  String dept,
  6                                                  String strClass,
  7                                                  String ticketNo,
  8                                                  String type,
  9                                                  String status,
 10                                                  String isEvaluated,
 11                                                  String exceedTimeLimit,
 12                                                  String error,
 13                                                  String workLeader,
 14                                                  String signatory,
 15                                                  String content,
 16                                                  String createTimeLimit,
 17                                                  String year,
 18                                                  String workLeaderOpenTime,//工作负责人签字时间从
 19                                                  String workLeaderCloseTime,//工作负责人签字时间到
 20                                                  String allowEndOpenTime,
 21                                                  String allowEndCloseTime,
 22                                                  String page_number,
 23                                                  String each_page){
 24 
 25         List<WTDetailTableBean> lWTDetailTableBeanLt = new ArrayList<WTDetailTableBean>();
 26         
 27         //请求参数
 28         ArrayList<String> strParamLt = new ArrayList<String>();
 29         
 30         String queryString =
 31                 "SELECT DISTINCT *  FROM " + WTDetailTableBeanDao.TABLENAME + " where 1==1 ";
 32         //组织
 33         if (!CommonUtil.isNull(org)){
 34             queryString = queryString + " and "
 35                     + WTDetailTableBeanDao.Properties.ORG_ID.columnName + " =  ?";
 36             strParamLt.add(org);
 37         }
 38         //部门
 39         if (!CommonUtil.isNull(dept)){
 40             queryString = queryString + " and "
 41                     + WTDetailTableBeanDao.Properties.UNIT_ID.columnName + " =  ?";
 42             strParamLt.add(dept);
 43         }
 44         //班组
 45         if (!CommonUtil.isNull(strClass)){
 46             queryString = queryString + " and "
 47                     + WTDetailTableBeanDao.Properties.CLASS_ID.columnName + " =  ?";
 48             strParamLt.add(strClass);
 49         }
 50         //票号
 51         if (!CommonUtil.isNull(ticketNo)){
 52             queryString = queryString + " and "
 53                     + WTDetailTableBeanDao.Properties.TICKET_NO.columnName + " like '%" + ticketNo + "%' ";
 54         }
 55 
 56         //状态
 57         if (!CommonUtil.isNull(status)){
 58             queryString = queryString + " and "
 59                     + WTDetailTableBeanDao.Properties.STATUS.columnName + " =  ?";
 60             strParamLt.add(status);
 61         }
 62 
 63         //工作负责人
 64         if (!CommonUtil.isNull(workLeader)){
 65             queryString = queryString + " and "
 66                     + WTDetailTableBeanDao.Properties.WORK_RESPONSOR_NAME.columnName + " like '%" + workLeader + "%' ";
 67         }
 68 
 69         //签字人
 70         if (!CommonUtil.isNull(signatory)){
 71             queryString = queryString + " and "
 72                     + WTDetailTableBeanDao.Properties.WORK_SIGN_FIRST_NAME.columnName + " like '%" + signatory + "%' ";
 73 
 74             queryString = queryString + " or "
 75                     + WTDetailTableBeanDao.Properties.WORK_SIGN_SECOND_NAME.columnName + " like '%" + signatory + "%' ";
 76 
 77         }
 78 
 79         //工作内容
 80         if (!CommonUtil.isNull(content)){
 81             queryString = queryString + " and "
 82                     + WTDetailTableBeanDao.Properties.WORK_RESPONSOR_JOB.columnName + " like '%" + content + "%' ";
 83         }
 84 
 85         //工作负责人签字时间从 和 工作负责人签字时间到 都不为空
 86         if (!CommonUtil.isNull(workLeaderOpenTime) && !CommonUtil.isNull(workLeaderCloseTime)){
 87             queryString = queryString + " and "
 88                     + WTDetailTableBeanDao.Properties.WORK_SIGN_THRID_DATE.columnName + " BETWEEN '" + workLeaderOpenTime + "' AND '" + workLeaderCloseTime + "' ";
 89         //工作负责人签字时间从 不为空 和 工作负责人签字时间到  为空
 90         }else if (!CommonUtil.isNull(workLeaderOpenTime) && CommonUtil.isNull(workLeaderCloseTime)){
 91             queryString = queryString + " and "
 92                     + WTDetailTableBeanDao.Properties.WORK_SIGN_THRID_DATE.columnName + " BETWEEN '" + workLeaderOpenTime + "' AND '" + CommonUtil.getCurrentTime() + "' ";
 93         //工作负责人签字时间从 为空 和 工作负责人签字时间到  不为空
 94         }else if (!CommonUtil.isNull(workLeaderOpenTime) && CommonUtil.isNull(workLeaderCloseTime)){
 95             queryString = queryString + " and "
 96                     + WTDetailTableBeanDao.Properties.WORK_SIGN_THRID_DATE.columnName + " BETWEEN '2000-01-01 00:00:00' AND '" + workLeaderCloseTime + "' ";
 97         }
 98 
 99         Object [] objs=strParamLt.toArray();
100         String [] strs=new String[objs.length];
101         for(int i=0;i<objs.length;i++){
102             strs[i]=objs[i].toString();
103         }
104 
105         Cursor cursor = getDaoSession().getWTDetailTableBeanDao().getDatabase().rawQuery(queryString,  strs);
106 
107         int intTicketIdIndex = cursor.getColumnIndex(WTDetailTableBeanDao.Properties.TICKET_ID.columnName);
108         int intTicketNoIndex = cursor.getColumnIndex(WTDetailTableBeanDao.Properties.TICKET_NO.columnName);
109         int intTicketTypeIndex = cursor.getColumnIndex(WTDetailTableBeanDao.Properties.TICKET_TYPE.columnName);
110         int intStatusIndex = cursor.getColumnIndex(WTDetailTableBeanDao.Properties.STATUS.columnName);
111         int intWorkResponsorJobIndex = cursor.getColumnIndex(WTDetailTableBeanDao.Properties.WORK_RESPONSOR_JOB.columnName);
112         int intWorkSignThridDateIndex = cursor.getColumnIndex(WTDetailTableBeanDao.Properties.WORK_SIGN_THRID_DATE.columnName);
113 
114         while (cursor.moveToNext()) {
115             WTDetailTableBean lWTDetailTableBean = new WTDetailTableBean();
116             lWTDetailTableBean.setTICKET_ID(cursor.getString(intTicketIdIndex));
117             lWTDetailTableBean.setTICKET_NO(cursor.getString(intTicketNoIndex));
118             lWTDetailTableBean.setTICKET_TYPE(cursor.getString(intTicketTypeIndex));
119             lWTDetailTableBean.setSTATUS(cursor.getString(intStatusIndex));
120             lWTDetailTableBean.setWORK_RESPONSOR_JOB(cursor.getString(intWorkResponsorJobIndex));
121             lWTDetailTableBean.setWORK_SIGN_THRID_DATE(cursor.getString(intWorkSignThridDateIndex));
122 
123             lWTDetailTableBeanLt.add(lWTDetailTableBean);
124 
125         }
126         return lWTDetailTableBeanLt;
127     }
页面结果：