Saving ArrayList in SQLite database in Android

Question

Hello all,

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

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

Answer 2

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 3

Please forgive me for savagely plagiarizing my previous answer to http://stackoverflow.com/questions/3106548/blob-vs-varchar-for-storing-arrays-in-a-mysql-table/3107250#3107250. 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