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     }

 

查询结果如下