Insert ArrayList in SQLite Database

Question

I have an ArrayList coming from an API and my POJO (Plain Old Java Object) has the required getters and setters required.

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

I want to ad the staff and department into an SQLite Table. My existing table has the rest of the strings values. But i am not able to add the ArrayList into the table.

public static final String CREATE_TABLE_QUERY = "CREATE TABLE " + TABLE_NAME + "" +
            " (" + ID + " TEXT PRIMARY KEY not null, " +
            //need to change product id into String or TEXT
            CLIENTS_NAME + " TEXT not null," +
            CLIENTS_ADDRESS1 + " TEXT not null," +
            CLIENTS_ADDRESS2 + " TEXT not null," +
            CLIENTS_ADDRESS3 + " TEXT not null," +
            CLIENTS_ADDRESS4 + " TEXT not null," +
            TYPE + " TEXT not null," +
            CLIENTS_CONTACT + " TEXT not null)" ;

How do i add the Arraylist ? I have tried this but its not working. Any Links or Hints will be appreciated.

This is the insertion of the rest of the string values into the database.Note, i have still not added the staff into the database table yet as i dont know how to insert a Arraylist in the table.

 public void addProducts(Clients_POJO products) {
    //CRUD , adding Products

    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(Constants.ClientsDATABASE.ID, products.getId());
    values.put(Constants.ClientsDATABASE.CLIENTS_NAME, products.getName());
    values.put(Constants.ClientsDATABASE.CLIENTS_ADDRESS1, products.getAddress1());
    values.put(Constants.ClientsDATABASE.CLIENTS_ADDRESS2, products.getAddress2());
    values.put(Constants.ClientsDATABASE.CLIENTS_ADDRESS3, products.getAddress3());
    values.put(Constants.ClientsDATABASE.CLIENTS_ADDRESS4, products.getAddress4());
    values.put(Constants.ClientsDATABASE.TYPE, products.getType());
    values.put(Constants.ClientsDATABASE.CLIENTS_CONTACT, products.getContact());

    try {

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

Answer 1

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);