Project requirements

There are four kinds of query conditions for a list, and not only online query but also offline query is needed. Online direct request interface helps us to do it. Offline must be realized by ourselves. There are many ways to realize it. I use ORM Lite here to realize it. ORM Lite provides many query methods. If you want to know more, please refer to it Complex condition query

I need four query conditions here, and these four conditions can be combined to query

• Multiple item IDS
• Status of records
• Time interval
• Fuzzy number query

thinking

Important: I used the method of query from the incoming sql statement to the ORM Lite.

1. Step 1: spell the sql statement by yourself
2. Step 2: query with the queryRaw method of ORM Lite

private String getSql(String userId, FilterRequestEntity entity) {

        String table = DBConstant.TableName.TB_OFFLINE_ASSGININFO;
        
        String sql = "select * from " + table + " where userId = " +
                "'" + userId;
               
        StringBuffer stringBuffer = new StringBuffer(sql);
        if (entity.getProjectIds() != null && entity.getProjectIds().length != 0) {
        	//Item id combination query, the format is as follows
            //select * from table1 where a [not] in ('value 1 ',' value 2 ',' value 4 ',' value 6 ')
            String[] projectIds = entity.getProjectIds();
            StringBuffer buffer = new StringBuffer();
            for (int i = 0; i < projectIds.length; i++) {
                buffer.append("'" + projectIds[i] + "'");
                if (i != projectIds.length - 1) {
                    buffer.append(",");
                } else {
                    buffer.append(")");
                }
            }
            stringBuffer.append(" and projectId in (" + buffer);
        }
        if (!StringUtils.isEmpty(entity.getReformOrderCode())) {
        	
            String code = "reformOrderCode";
            //Search query by number, the format is as follows
            //select * from table1 where field1 like '%value1%'
            stringBuffer.append(" and " + code + " like " + "'%" + entity.getReformOrderCode() + "%'");
        }
        if (!StringUtils.isEmpty(entity.getStartTime())) {
            String startTime = entity.getStartTime() + " 00:00:00";
            //According to the time section query, the format is as follows
            //select * from table1 where time between time1 and time2
            stringBuffer.append(" and time >= '" + startTime + "'");
        }
        if (!StringUtils.isEmpty(entity.getEndTime())) {
            String endTime = entity.getEndTime() + " 23:59:59";
            stringBuffer.append(" and time <= '" + endTime + "'");
        }
        return stringBuffer.toString();
    }

Empty judgment has been made here, and single and multiple condition queries in the combination can be satisfied

//First step
String sql = getSql(userId, entity);
//Step 2 (if not, please check the usage of queryRaw)
List<AssignLocalInfo> results = SimpleDao.Factory.create(AssignLocalInfo.class).getDao().queryRaw(sql, new RawRowMapper<AssignLocalInfo>() {
                @Override
                public AssignLocalInfo mapRow(String[] columnNames, String[] resultColumns) throws SQLException {
                    AssignLocalInfo localInfo = new AssignLocalInfo();
                    for (int i = 0; i < columnNames.length; i++) {
                        if (StringUtils.equals(columnNames[i], "assignLoacalInfo")) {
                            localInfo.setAssignLoacalInfo(resultColumns[i]);
                        }
                    }
                    return localInfo;
                }
            }).getResults();

Class bean

public class FilterRequestEntity {
    private String status; //Status of rectification
    private String[] projectIds;//Rectified project id combination
    private String reformOrderCode; //Rectification No
    private String startTime; //Time of rectification
    private String endTime;

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public String[] getProjectIds() {
        return projectIds;
    }

    public void setProjectIds(String[] projectIds) {
        this.projectIds = projectIds;
    }

    public String getReformOrderCode() {
        return reformOrderCode;
    }

    public void setReformOrderCode(String reformOrderCode) {
        this.reformOrderCode = reformOrderCode;
    }

    public String getStartTime() {
        return startTime;
    }

    public void setStartTime(String startTime) {
        this.startTime = startTime;
    }

    public String getEndTime() {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }
}

@DatabaseTable(tableName = DBConstant.TableName.TB_OFFLINE_ASSGININFO)
public class AssignLocalInfo {
    @DatabaseField(generatedId = true)
    private int id;

    @DatabaseField(columnName = "userId")
    private String userId; //Rectification ID

    @DatabaseField(columnName = "assignProblemId")
    private String assignProblemId; //Rectification ID

    @DatabaseField(columnName = "projectId")
    private String projectId; //Project ID

    @DatabaseField(columnName = "reformOrderCode")
    private String reformOrderCode;//Question number

    @DatabaseField(columnName = "time")
    private String time;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getAssignProblemId() {
        return assignProblemId;
    }

    public void setAssignProblemId(String assignProblemId) {
        this.assignProblemId = assignProblemId;
    }

    public String getProjectId() {
        return projectId;
    }

    public void setProjectId(String projectId) {
        this.projectId = projectId;
    }

    public String getReformOrderCode() {
        return reformOrderCode;
    }

    public void setReformOrderCode(String reformOrderCode) {
        this.reformOrderCode = reformOrderCode;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

}