Delete all records using ORMlite before a particular date

Question

I am using Ormlite database in one of my app. Now I want to delete all records from the database which are stored before a particular date. Let's take an example:-

Today is 17th April. When user selected One week in one of the option through app, then all the records which are before one week ie before and on 10th April should be deleted from database.

Following is my Object bean class :-

public class AlertsDataClass extends BaseDataClass implements Parcelable {

public static final Creator<AlertsDataClass> CREATOR = new Creator<AlertsDataClass>() {
    @Override
    public AlertsDataClass createFromParcel(Parcel in) {
        return new AlertsDataClass(in);
    }

    @Override
    public AlertsDataClass[] newArray(int size) {
        return new AlertsDataClass[size];
    }
};
@DatabaseField
private int alertId;
@DatabaseField
private String alertTitle;
@DatabaseField
private int alertLevel;
@DatabaseField
private String alertLog;
@DatabaseField
private int alertAssignee;
@DatabaseField
private String alertLocationId;
@DatabaseField
private int alertStatus;
@DatabaseField
private String alertStartTime;
@DatabaseField
private String alertUpdatedTime;
@DatabaseField
private String alertDeviceId;
@DatabaseField
private int alertActionNeeded;
@DatabaseField
private String alertAssigneeName;

public AlertsDataClass() {
}

protected AlertsDataClass(Parcel in) {
    alertId = in.readInt();
    alertTitle = in.readString();
    alertLevel = in.readInt();
    alertLog = in.readString();
    alertAssignee = in.readInt();
    alertAssigneeName = in.readString();
    alertLocationId = in.readString();
    alertStatus = in.readInt();
    alertStartTime = in.readString();
    alertUpdatedTime = in.readString();
    alertDeviceId = in.readString();
    alertActionNeeded = in.readInt();
}

public int getAlertId() {
    return alertId;
}

public void setAlertId(int alertId) {
    this.alertId = alertId;
}

public String getAlertTitle() {
    return alertTitle;
}

public void setAlertTitle(String alertTitle) {
    this.alertTitle = alertTitle;
}

public int getAlertLevel() {
    return alertLevel;
}

public void setAlertLevel(int alertLevel) {
    this.alertLevel = alertLevel;
}

public String getAlertLog() {
    return alertLog;
}

public void setAlertLog(String alertLog) {
    this.alertLog = alertLog;
}

public int getAlertAssignee() {
    return alertAssignee;
}

public void setAlertAssignee(int alertAssignee) {
    this.alertAssignee = alertAssignee;
}

public String getAlertLocationId() {
    return alertLocationId;
}

public void setAlertLocationId(String alertLocationId) {
    this.alertLocationId = alertLocationId;
}

public int getAlertStatus() {
    return alertStatus;
}

public void setAlertStatus(int alertStatus) {
    this.alertStatus = alertStatus;
}

public String getAlertStartTime() {
    return alertStartTime;
}

public void setAlertStartTime(String alertStartTime) {
    this.alertStartTime = alertStartTime;
}

public String getAlertUpdatedTime() {
    return alertUpdatedTime;
}

public void setAlertUpdatedTime(String alertUpdatedTime) {
    this.alertUpdatedTime = alertUpdatedTime;
}

public String getAlertDeviceId() {
    return alertDeviceId;
}

public void setAlertDeviceId(String alertDeviceId) {
    this.alertDeviceId = alertDeviceId;
}

public int getAlertActionNeeded() {
    return alertActionNeeded;
}

public void setAlertActionNeeded(int alertActionNeeded) {
    this.alertActionNeeded = alertActionNeeded;
}

public String getAlertAssigneeName() {
    return alertAssigneeName;
}

public void setAlertAssigneeName(String alertAssigneeName) {
    this.alertAssigneeName = alertAssigneeName;
}

@Override
public int describeContents() {
    return 0;
}

@Override
public void writeToParcel(Parcel dest, int flags) {
    dest.writeInt(alertId);
    dest.writeString(alertTitle);
    dest.writeInt(alertLevel);
    dest.writeString(alertLog);
    dest.writeInt(alertAssignee);
    dest.writeString(alertAssigneeName);
    dest.writeString(alertLocationId);
    dest.writeInt(alertStatus);
    dest.writeString(alertStartTime);
    dest.writeString(alertUpdatedTime);
    dest.writeString(alertDeviceId);
    dest.writeInt(alertActionNeeded);
}

}

In the above data class ,"alertStartTime" is the field which contains the date in the format of "04/17/17 16:29". Now when user select one week, all the records before and on "04/10/17 16:29" should be removed from database.

I am deleting the record on the basis of two fields. One is "alertStatus" whose value should be 5 and other one is "alertStartTime" in which one week before date shuold be given to delete all the records which comes before and on "04/10/17 16:29". I hope you guys could understood my question clearly.

My database delete records method as follows :-

public void deleteCompleteAlerts(int alertStatus, String alertStartTime) {

    DeleteBuilder<AlertsDataClass, Integer> deleteBuilder = getAlertsDetailDao().deleteBuilder();
    try {
        Where<AlertsDataClass, Integer> where = deleteBuilder.where();
        int condCount = 0;
        if(alertStatus == 5){
            where.eq("alertStatus", alertStatus);
            condCount++;
        }
        if(alertStartTime.equalsIgnoreCase("04/14/17")){
            where.like("alertStartTime", "%" + alertStartTime + "%");
            condCount++;
        }
        if (condCount > 0) {
            where.and(condCount);

        }

        deleteBuilder.delete();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

In the above method, we have to modify this if statement as follows:-

if(alertStartTime.equalsIgnoreCase("04/14/17")){
            where.like("alertStartTime", "%" + alertStartTime + "%");
            condCount++;
        }

Please help me to sort out this problem. Thanks in Advance guys!!

score 2 · Answer 1 · edited May 23 '17 at 12:34

In the above data class ,"alertStartTime" is the field which contains the date in the format of "04/17/17 16:29". Now when user select one week, all the records before and on "04/10/17 16:29" should be removed from database.

You should be able to do something like the following:

DeleteBuilder<AlertsDataClass, Integer> db = dao.deleteBuilder();
db.where().le("alertStartTime", "2017-04-10 16:29");
db.delete();

Sqlite handles very specific timestamp formats and in your case it needs to be in "YYYY-MM-DD HH:MM" format. Here's more from Sqlite's docs about time formats.

Delete all records using ORMlite before a particular date

1 Answers1