GreenDao-自定义SQL查询-AndroidStudio
1 /** 2 * 功能：员工查询 3 * 方法参数： 4 * strEmpIdOrEmpName：员工ID 或者 员工名称 5 * strQueryType：员工查询类型 "0": "员工编号"; "1": "员工名称" 6 * strOrgId:当前所在组织 7 * strQualifType:资格条件 8 * strTicketType:票型 9 */ 10 public static List<EmployeeTableBean> getEmployeeInfo(String strEmpIdOrEmpName, String strEmployeeQueryType, 11 String strOrgId, String strQualifyType, String strTicketType) { 12 13 List<EmployeeTableBean> lResutEmployeeTableBeanLt = new ArrayList<EmployeeTableBean>(); 14 15 //拼接sql 16 String queryString = "SELECT DISTINCT * FROM " + EmployeeTableBeanDao.TABLENAME + " where 1==1 AND "+ 17 EmployeeTableBeanDao.Properties.ORG_ID.columnName + " = ? AND " + 18 EmployeeTableBeanDao.Properties.QUALIF_TYPE.columnName + " = ? AND " + 19 EmployeeTableBeanDao.Properties.TICKET_TYPE.columnName + " = ? " ; 20 21 //如果输入查询条件--员工编号 22 if (strEmployeeQueryType.equals(EmployeeQueryType.EMP_ID.getTypeId())) { 23 queryString = queryString + " and " 24 + EmployeeTableBeanDao.Properties.EMP_ID.columnName + " like '%" + strEmpIdOrEmpName + "%' "; 25 //如果输入查询条件--员工名称 26 }else if (strEmployeeQueryType.equals(EmployeeQueryType.EMP_NAME.getTypeId())){ 27 queryString = queryString + " and " 28 + EmployeeTableBeanDao.Properties.EMP_NAME.columnName + " like '%" + strEmpIdOrEmpName + "%' "; 29 } 30 31 queryString = queryString + " and "+ 32 EmployeeTableBeanDao.Properties.EMP_ID.columnName + " IN ( SELECT " + 33 DigitalSignatureTableBeanDao.Properties.PERSON_ID.columnName + " FROM " + 34 DigitalSignatureTableBeanDao.TABLENAME + " ) "; 35 36 String [] strsParam=new String[3]; 37 strsParam[0] = strOrgId; 38 strsParam[1] = strQualifyType; 39 strsParam[2] = strTicketType; 40 41 Cursor cursor = getDaoSession().getEmployeeTableBeanDao().getDatabase().rawQuery(queryString, strsParam); 42 43 int intEMP_NAMEIndex = cursor.getColumnIndex(EmployeeTableBeanDao.Properties.EMP_NAME.columnName); 44 int intEMP_IDIndex = cursor.getColumnIndex(EmployeeTableBeanDao.Properties.EMP_ID.columnName); 45 int intQUALIF_TYPE_NAMEIndex = cursor.getColumnIndex(EmployeeTableBeanDao.Properties.QUALIF_TYPE_NAME.columnName); 46 int intTICKET_TYPE_NAMEIndex = cursor.getColumnIndex(EmployeeTableBeanDao.Properties.TICKET_TYPE_NAME.columnName); 47 48 while (cursor.moveToNext()) { 49 EmployeeTableBean lEmployeeTableBean = new EmployeeTableBean(); 50 lEmployeeTableBean.setEMP_NAME(cursor.getString(intEMP_NAMEIndex)); 51 lEmployeeTableBean.setEMP_ID(cursor.getString(intEMP_IDIndex)); 52 lEmployeeTableBean.setQUALIF_TYPE_NAME(cursor.getString(intQUALIF_TYPE_NAMEIndex)); 53 lEmployeeTableBean.setTICKET_TYPE_NAME(cursor.getString(intTICKET_TYPE_NAMEIndex)); 54 lResutEmployeeTableBeanLt.add(lEmployeeTableBean); 55 } 56 return lResutEmployeeTableBeanLt; 57 }
查询结果如下
