Saving ArrayList in SQLite database in Android

Question

I've been working with SQLite on android and I would like to add an arraylist to a column in a table, and then fetch the data back as an arraylist. The arraylist is a list of Longs. I've noticed that SQL has an option for storing BLOBS, however it looks like I need to convert the arraylist to a byte[] first before being able to store it as a blob in my SQLite database.

If anyone has a solution on how to save arraylists into an SQLite database that would be greatly appreciated. Or is there any other option for saving my array of data, i should consider?

Answer 1

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 save the value of ArrayList 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.

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

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

• In SQLite use text as format to store the string Value.....

Answer 2

Please forgive me for savagely plagiarizing my previous answer to BLOB vs. VARCHAR for storing arrays in a MySQL table. The other answers over there are also very pertinent.

I think Con's approach is probably better than using java serialization since java's builtin serialization will need additional bytes, and non-java applications will have a harder time dealing with the data.

public static void storeInDB(ArrayList<Long> longs) throws IOException, SQLException {

    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    DataOutputStream dout = new DataOutputStream(bout);
    for (long l : longs) {
        dout.writeLong(l);
    }
    dout.close();
    byte[] asBytes = bout.toByteArray();

    PreparedStatement stmt = null;  // however you get this...
    stmt.setBytes(1, asBytes);
    stmt.executeUpdate();
    stmt.close();
}

public static ArrayList<Long> readFromDB() throws IOException, SQLException {

    ArrayList<Long> longs = new ArrayList<Long>();
    ResultSet rs = null;  // however you get this...
    while (rs.next()) {
        byte[] asBytes = rs.getBytes("myLongs");
        ByteArrayInputStream bin = new ByteArrayInputStream(asBytes);
        DataInputStream din = new DataInputStream(bin);
        for (int i = 0; i < asBytes.length/8; i++) {
            longs.add(din.readLong());
        }
        return longs;
    }

}

Note: If your lists will sometimes contain more than 31 longs (248 bytes), then you'll need to use BLOB. You cannot use BINARY() or VARBINARY() in MySQL. I realize you're asking about SQLite, but in the spirit of completely plagiarizing my previous answer, I will pretend you're asking about MySQL:

mysql> CREATE TABLE t (a VARBINARY(2400)) ;
ERROR 1074 (42000): Column length too big for column 'a' (max = 255);
use BLOB or TEXT instead

Answer 3

I had two ArrayList<String>, both will 1000+ entries. I looked at blobs and bytes, but for me the solution to speeding up the process and making it usable was by changing the insert method and getting rid of database.insert - Credit for this is here.

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

public void insertArrayData(ArrayList<String> array1,
            ArrayList<String> array2) {

        try {
            database.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        int aSize = array1.size();

        database.beginTransaction();

        try {
            SQLiteStatement insert = database.compileStatement(INSERT);

            for (int i = 0; i < aSize; i++) {

                insert.bindString(1, array1.get(i));
                insert.bindString(2, array2.get(i));
                insert.executeInsert();

            }

            database.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            database.endTransaction();
        }

        try {
            database.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

It's easily adaptable to Longs and Integers etc and lightening quick. So thankfully I didn't have to scratch my head any longer about blobs and bytes! Hope it helps.

Answer 4

There is an easier way that do such thing in completely another way. you can make an string that consists of all your array values. for that make an StringBuilder and append the values continuously and offcource with a separator (like a simbole you which you won't use in your array values . for example virtual '|' . in code for example :

double[] mylist = new double[]{23, 554, 55};
    StringBuilder str = null;
    str = new StringBuilder();

    for(int i=0;i<mylist.length;i++){
        str.append(mylist[i]+"|");
    }

    String result = str.toString();

    db.open();
    db.insert(result);
    db.close();

when you want to fetch them and use the values. get the column from database pure it to String and with the splite() opration pure each values of array in a column of array than u can easily use it :)

lets do it in code :

String str = db.getdata();
    String[] list = str.split("|");

with a simple convert you can use them as double;

double mydouble = Double.parsDouble(list[1].toString());

maybe it is not standard but it is helpfull, hope help ;)

Answer 5

Sounds like you want to serialize the List. Here is a tutorial/intro to the Java Serialization API.

Answer 6

You'll have to do it manually, go through each item in the list and change it to byte before storing it in the database

for (long l : array<long>){
//change to byte here
//Store in database here
}

Answer 7

    ArrayList<String> list = new ArrayList<String>();
                    list.add("Item 1");
                    list.add("Item 2");
                    list.add("Item 3");

                    String joined = TextUtils.join(",", list);
                    Log.i(TAG, "joined strings: " + joined);

                    String[] array = TextUtils.split(joined, ",");
                    Log.i(TAG, "joined strings: " + array[0] + array[1] + array[2]);