How can I save a SparseArray in a Room database?

Question

I'm trying to persist a SparseArray in a Room database and can not get it to compile. I keep getting the "Not sure how to convert a Cursor to this method's return type" error message along with "The query returns some columns [plannerLineData] which are not use by android.util.SparseArray."

I have tried using a single field in the PlannerLine Entity alone with a separate PlannerLineData class.

I have data converters to convert SparseArray to String and to convert String back to SparseArray.

I have checked several questions on stackoverflow and have successfully used the Date to Long and the Long to Date converters in other projects, but I seem to be missing something somewhere.

Data Files:

@Entity
public class PlannerLine implements Serializable {

private static final long serialVersionUID = 1L;

@TypeConverters(Converters.class)
@PrimaryKey
@SerializedName("planner_line")
@NonNull
public SparseArray plannerLineData;

public SparseArray getPlannerLineData() {
    return plannerLineData;
}

public void setPlannerLineData(SparseArray plannerLineData) {
    this.plannerLineData = plannerLineData;
}


public class PlannerLineData implements Serializable {

@SerializedName("lineId")
public int lineId;

@SerializedName("plan_text")
public String planText;

public int getLineId() {
    return lineId;
}

public void setLineId(int lineId) {
    this.lineId = lineId;
}

public String getPlanText() {
    return planText;
}

public void setPlanText(String planText) {
    this.planText = planText;
}

}

DAO problem area:

@Dao
public interface PlannerDao {

@Query("SELECT * from PlannerLine")  
public SparseArray getPlannerLine();  <---Doesn't like this line

I have also tried returning SparseArray<PlannerLine> and SparseArray<PlannerLineData>, but no joy.

Converters class:

public class Converters {

@TypeConverter
public static String sparseArrayToString(SparseArray sparseArray) {
    if (sparseArray == null) {
        return null;
    }

    int size = sparseArray.size();
    if (size <= 0) {
        return "{}";
    }

    StringBuilder buffer = new StringBuilder(size * 28);
    buffer.append('{');
    for (int i = 0; i < size; i++) {
        if (i > 0) {
            buffer.append("-,- ");
        }
        int key = sparseArray.keyAt(i);
        buffer.append(key);
        buffer.append("-=-");
        Object value = sparseArray.valueAt(i);
        buffer.append(value);
    }
    buffer.append('}');
    return buffer.toString();

}

@TypeConverter
public static SparseArray stringToSparseArray(String string) {
    if (string == null) {
        return null;
    }

    String entrySeparator = "-=-";
    String elementSeparator = "-,-";
    SparseArray sparseArray = new SparseArray();

    String[] entries = StringUtils.splitByWholeSeparator(string, elementSeparator);
    for (int i = 0; i < entries.length; i++) {
        String[] parts = StringUtils.splitByWholeSeparator(entries[i], entrySeparator);
        int key = Integer.parseInt(parts[0]);
        String text = parts[1];
        sparseArray.append(key, text);
    }
    return sparseArray;
}

Suggestions would be appreciated. Thanks

Edit:

My original vision for this app was to store all the plan lines in a single SparseArray, along with two additional SparseIntArrays (which I did not mention before because the solution would be similar to the SparseArray) to hold info on how the plan lines interact with each other.

After reading through @dglozano's helpful responses, I have decided to re-design the app to just store regular DB files in Room and load the data into the SparseArray (and the two SparseIntArrays) at startup, use only the in memory SparseArray and SparseIntArrays while the app is active, then write changes in the Sparse Arrays to the DB during onStop(). I am also considering updating the DB in the background as I work through app.

Because the answers and suggestions provided by @dglozano led me to the re-design decision, I am accepting his answer as the solution.

Thanks for the help.

Answer 1

It seems that you are doing the Conversion properly. However, the problem is in your DAO Query:

@Query("SELECT * from PlannerLine")  // This returns a List of PlannerLine, not a SparseArray
public SparseArray getPlannerLine(); // The return type is SparseArray, not a List of PlannerLine

Therefore, you can try two different things:

1 - Change the Query to @Query("SELECT plannerLineData FROM PlannerLine WHERE lineId == :lineId") , so that the query returns the SparseArray inside the PlannerLine with id lineId. You should change the method signature so it accepts the parameter lineId

@Query("SELECT plannerLineData FROM PlannerLine WHERE lineId == :lineId")
public SparseArray getPlannerLine(int lineId); 

2 - If you want to return the full PlannerLine object and then access to its SparseArray field, then you should change the return type. You should also add the lineId parameter to return just one record and not a list of all the PlannerLine stored in the database table.

@Query("SELECT * FROM PlannerLine WHERE lineId == :lineId")
public PlannerLine getPlannerLine(int lineId); 

---

UPDATE

If you want to get a List<PlannerLine> with all the PlannerLine stored in the database, use the following query in your Dao.

@Query("SELECT * FROM PlannerLine")
public List<PlannerLine> getAllPlannerLines(); 

Then you can access to the SparseArray of each PlannerLine in the list as usual.