Saving Arraylists in SQLite Databases

Saving ArrayLists in SQLite databases

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 = new JSONObject();
json.put("uniqueArrays", new JSONArray(items));
String arrayList = json.toString();

Insert the string into db.

To Read,
Read the string from db as String,

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

Insert ArrayList in SQLite Database

I found the solution without converting it using GSON.
Let the POJO value for the department be an ArrayList.

@SerializedName("departments")
@Expose
private List<String> departments = new ArrayList<String>();

To create the database we will store the column as a string in the table.

public static final String INPUT_DEPARTMENTS = "departments";
public static final String CREATE_TABLE_QUERY = "CREATE TABLE " + TABLE_NAME + "" +
            " (" + ID + " TEXT PRIMARY KEY not null, " +
            INPUT_DEPARTMENTS + " TEXT not null)" ;

Now while storing and retrieving data to and from the SQLite database.Storing ->

public void addProducts(Clients_POJO products) {
    //CRUD , adding Products
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(Constants.ClientsDATABASE.INPUT_DEPARTMENTS, String.valueOf(products.getDepartments()));
    try {

        db.insert(Constants.ClientsDATABASE.TABLE_NAME, null, values);
    } catch (Exception e) {
        Log.d(TAG, e.getMessage());
    }

For retrieving data, we will convert it back to an arraylist to store the value in the POJO.

SQLiteDatabase db = this.getWritableDatabase();
    final ProductFetchListener mListener = listener;
    Cursor cursor = db.rawQuery(Constants.ClientsDATABASE.GET_PRODUCTS_QUERY,null);
    final List<Clients_POJO> productsListDB = new ArrayList<>();
    if (cursor.getCount() > 0) {
        if (cursor.moveToFirst()) {
            do {
                Clients_POJO products = new Clients_POJO();
                products.setFromDatabase(true);

Now we convert it to an arraylist.

String s =  cursor.getString(cursor.getColumnIndex(Constants.ClientsDATABASE.INPUT_DEPARTMENTS));
List<String> myList = new ArrayList<String>(Arrays.asList(s.split(",")));
products.setDepartments(myList);

How to save my Arraylist into SQLite database?

Tutorial Link

Please refer to the link above. I has a working example of using HashMap for SqLite operations using SQliteOpenHelper. It has one insertion at a time, but you might wanna create a loop, if you wish to insert them all in one instance.

How to save a Custom ArrayList to SQLite database and retrieve the values

You can use statements to insert bulk data in an optimized way, can use it like -

private static final String INSERT = "insert into "
            + TABLE_NAME+ " (" + COLUMN_1 + ", "
            + COLUMN_2 + ", "+COLUMN_3+") values (?, ?, ?)";

public void insertReditPosts(ArrayList<RedditPost> redditPosts) {
        SQLiteDatabase database = this.getWritableDatabase();
        int aSize = redditPosts.size();
        database.beginTransaction();
        try {
            SQLiteStatement insert = database.compileStatement(INSERT);
            for (int i = 0; i < aSize; i++) {
                insert.bindString(1, redditPosts.get(i).id);
                insert.bindString(2, redditPosts.get(i).title);
                insert.bindString(3, redditPosts.get(i).url);
                insert.executeInsert();
            }
            database.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            database.endTransaction();
        }
    }

From your activity just call this method and pass the ArrayList it will store it into the database. Add this code to store clicked item on long press in list view

lv.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> arg0, View arg1,
                                           int pos, long id) {
                Log.v("long clicked","pos: " + pos);
                RedditPost redditPost = postsArrayList.get(pos);
                if(redditPost!=null) {
                    addPostData(redditPost);
                }
                return true;
            }
        });

saving multiple arraylist to sqlite database

You have several ways to store this information on a DB. First that come to my mind are:

1. Create a table event with index, x, y, z columns. You iterate through the arrays and for index i, store in event i, x_event.get(i), y_event.get(i), z_event.get(i).
2. Create a table event_array_lists with columns id and arraylist. Serialize the array lists and store them as a BLOB.
3. Convert the arraylists to JSON and store them as strings in a very similar table than 2.

inserting arraylistString values to sqlite in android

I think you should simply try this if both of your array are of same size... and also pass your cv in db.insertorThrow like this

public void addSelected(ArrayList<String> selList, ArrayList<String> selID){

        int size = selID.size();

        SQLiteDatabase db = getWritableDatabase();
       try{
       for (int i = 0; i < size ; i++){
            ContentValues cv = new ContentValues();
           cv.put(KEY_ID, selID.get(i));
             cv.put(KEY_STATUS, selList.get(i));
            Log.d("Added ",""+ cv);
            db.insertOrThrow(TABLE_SELECTED, null, cv);
        }  
db.close();
    }catch (Exception e){
        Log.e("Problem", e + " ");
    }

---

Related Topics