[ACCEPTED]-Saving ArrayLists in SQLite databases-sqlite

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

To insert,

JSONObject json = new JSONObject();
json.put("uniqueArrays", new JSONArray(items));
String arrayList = json.toString();

Insert the string 2 into db.

To Read, Read the string 1 from db as String,

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

To Insert :

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

Add Values to inputArray

Gson gson = new Gson();

String inputString= gson.toJson(inputArray);

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

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

To 5 retreive:

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

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

Type type = new TypeToken<ArrayList<String>>() {}.getType();

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

In my case it was ArrayList of POJO classes 8 Note

private String mNoteTitle;
private int mFingerIndex;
private Point mNoteCoordinates;

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

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

 JSONObject json = new JSONObject();
    JSONArray jsonArray = new JSONArray();

    for(Note note: chordShape.getNotes()){
        JSONObject singleNoteJsonObject = new JSONObject();

        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 3 String.

String notesList = json.toString();

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

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

And when i should 1 read values from SQLite database.

ArrayList<Note> notes = new ArrayList<>();
    while(cursor.moveToNext()){
        JSONObject jsonNotes = null;
        try {
           jsonNotes = new JSONObject(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 = new Note(
                            arrayObject.getString(SHAPE_NOTE_TITLE),
                                arrayObject.getInt(SHAPE_NOTE_FINGER_INDEX),
                                new Point(
                                        arrayObject.getInt(SHAPE_NOTE_X),
                                        arrayObject.getInt(SHAPE_NOTE_Y)
                                )
                        );
                    } catch (JSONException e){
                        e.printStackTrace();
                    }
                }


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

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

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

There are ArrayLists of several 14 custom classes in my application that I 13 wanted to persist to DB. So, to make things 12 tidy (well, to me at least -- I'm still 11 a relatively new Java / Android programmer, so 10 take this with a pinch of salt) I decided 9 to implement a kind of "SQL Serializable 8 Interface" that my DB-persistable objects 7 must implement. Each object (Foo, Bar, etc.) that 6 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 5 I have ArrayLists of objects Foo and Bar. When 4 the DB is first created, within my DB helper 3 class I call Foo.TABLE_CREATE_STRING, Bar.TABLE_CREATE_STRING, etc. to 2 create the tables for those objects.

To 1 populate my ArrayList, I use something like:

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