Saving Arraylists In Sqlite Databases

So I want to save an ordered set of double values, and I want to be able to insert, retrieve or delete any value from this easily. As of such, I'm using a an ArrayList, where I def

Solution 1:

You cannot insert ArrayList directly into Sqlite. Instead, you could use JSONObject (org.json.JSONObject) to insert the ArrayList. Please check below snippet, you can try something like below....

To insert,

JSONObject json = newJSONObject();
json.put("uniqueArrays", newJSONArray(items));
String arrayList = json.toString();

Insert the string into db.

To Read, Read the string from db as String,

JSONObject json = newJSONObject(stringreadfromsqlite);
  ArrayList items = json.optJSONArray("uniqueArrays");

Solution 2:

To Insert :

ArrayList<String> inputArray=newArrayList<String>();

Add Values to inputArray

Gson gson = new Gson();

String inputString= gson.toJson(inputArray);

System.out.println("inputString= " + inputString);

Use "inputString" to save the value of ArrayList<String> in SQLite Database

To retreive:

Get the String from the SQLiteDatabse what you saved and changed into ArrayList type like below:

outputarray is a String which is get from SQLiteDatabase for this example.

Typetype = newTypeToken<ArrayList<String>>() {}.getType();

ArrayList<String>  finalOutputString = gson.fromJson(outputarray, type);

Solution 3:

In my case it was ArrayList of POJO classes Note

private String mNoteTitle;
privateint mFingerIndex;
private Point mNoteCoordinates;

publicNote(String noteTitle, int fingerIndex, Point noteCoordinates){
    this.mNoteTitle = noteTitle;
    this.mFingerIndex = fingerIndex;
    this.mNoteCoordinates = noteCoordinates;
}

As manual says JSONObject supports only following types: Object: a JSONObject, JSONArray, String, Boolean, Integer, Long, Double, NULL, or null. May not be NaNs or infinities. So, I should break my Note class into supported objects.

JSONObject json = newJSONObject();
    JSONArray jsonArray = newJSONArray();

    for(Notenote: chordShape.getNotes()){
        JSONObject singleNoteJsonObject = newJSONObject();

        try {
            singleNoteJsonObject.put(SHAPE_NOTE_TITLE, note.getNoteTitle());
            singleNoteJsonObject.put(SHAPE_NOTE_FINGER_INDEX, note.getFingerIndex());
            singleNoteJsonObject.put(SHAPE_NOTE_X, note.getNoteCoordinates().x);
            singleNoteJsonObject.put(SHAPE_NOTE_Y, note.getNoteCoordinates().y);
        } catch (JSONException e){
            e.printStackTrace();
        }

        jsonArray.put(singleNoteJsonObject);

    }

Pack created array into JSONObject.

try {
        json.put(SHAPE_NOTES, jsonArray);
        Log.i(TAG, json.toString());
    } catch (JSONException e){
        e.printStackTrace();
    }

Create String.

StringnotesList= json.toString();

Put created String in ContentValues, cause in my case it's Android app

if(notesList.length() > 0){
        contentValues.put(DatabaseHelper.SHAPE_NOTES_LIST, notesList);
    }

And when i should read values from SQLite database.

ArrayList<Note> notes = newArrayList<>();
    while(cursor.moveToNext()){
        JSONObject jsonNotes = null;
        try {
           jsonNotes = newJSONObject(cursor.getString(cursor.getColumnIndex(DatabaseHelper.SHAPE_NOTES_LIST)));
        } catch (JSONException e){
            e.printStackTrace();
        }

        if(jsonNotes != null){
            Log.i(TAG, jsonNotes.toString());
            JSONArray jsonArray = jsonNotes.optJSONArray(SHAPE_NOTES);
            for(int i = 0; i < jsonArray.length(); i++){
                Note note = null;
                JSONObject arrayObject = null;

                try {
                    arrayObject = jsonArray.getJSONObject(i);
                } catch (JSONException e){
                    e.printStackTrace();
                }

                if(arrayObject != null){
                    try {
                        note = newNote(
                            arrayObject.getString(SHAPE_NOTE_TITLE),
                                arrayObject.getInt(SHAPE_NOTE_FINGER_INDEX),
                                newPoint(
                                        arrayObject.getInt(SHAPE_NOTE_X),
                                        arrayObject.getInt(SHAPE_NOTE_Y)
                                )
                        );
                    } catch (JSONException e){
                        e.printStackTrace();
                    }
                }


                if(note != null){
                    notes.add(note);
                }
            }
        }
    }
    cursor.close();

Solution 4:

I suggest going through all 3 Notepad tutorials you want to store the values your storing to a database table. you don't store the actual array directly into the database just the data. but you shouldn't actually need to use an array at all instead of adding a new item to the array instead call your db insert method

Solution 5:

I've needed to do something similar in my application, where I have a custom class (Foo, Bar, etc.) and I have an ArrayList of foo, bar, etc. that I persist to SQL. My knowledge of SQL isn't strong, but I'll explain my approach here in case it helps. My understanding is that to store any kind of object, you need to define a particular table for that object type, where the table has separate columns representing the primitive types within that object. Furthermore, to persist and retrieve an ArrayList of those objects, you'll use one table row per ArrayList entry, and iterate over in a loop to store and retrieve.

There are ArrayLists of several custom classes in my application that I wanted to persist to DB. So, to make things tidy (well, to me at least -- I'm still a relatively new Java / Android programmer, so take this with a pinch of salt) I decided to implement a kind of "SQL Serializable Interface" that my DB-persistable objects must implement. Each object (Foo, Bar, etc.) that can be persisted to DB must implement:

1. A public static final TABLE_NAME string, the name of the SQL DB table used for this object type.
2. A public static final TABLE_CREATE_STRING, a complete SQL instruction to create the table for this object.
3. A constructor method to populate its member variables from a ContentValues object.
4. A 'get' method to populate a ContentValues from its member variables.

So, say I have ArrayLists of objects Foo and Bar. When the DB is first created, within my DB helper class I call Foo.TABLE_CREATE_STRING, Bar.TABLE_CREATE_STRING, etc. to create the tables for those objects.

To populate my ArrayList, I use something like:

cursor = dbh.retrieve(Foo.TABLE_NAME);
if(!cursor.moveToFirst()){
  returnfalse
}
do{
  DatabaseUtils.cursorRowToContentValues(cursor, vales);
  FooArrayList.add( new Foo(values) );
} while( cursor.moveToNext() );