Android: SQLite saving string array?

Question

I need to save a string array to the database but it won't let me. This is what I have:

 public long createEntry(String startTime, String endTime, String[] states) {
         ContentValues initialValues = new ContentValues();
         initialValues.put(START_KEY_TIME , startTime);
         initialValues.put(END_KEY_TIME , endTime);
         initialValues.put(KEY_STATE, states );

         return databaseConnect.insert(DATABASE_TABLE, null, initialValues);
}

But if I put string[] states in, it says that content values is not able to take an argument. How do I get around that? I was thinking I have 7 things in states, could I like have 7 separate strings and store stuff in each and then afterwards put all the strings back into an string array? Or would that be bad practice?

Why you need to save the array in the database? there must be some other good solution. — Yaqub Ahmad, Jan 29 '12 at 13:55
i have 7 days of the week but and it takes it in as boolean(true or false) and it NEEDS to be boolean and since you cant save boolean to database i was going to use string array but it wont let me because of cotentvalues any ideas on what i can do? — user1175899, Jan 29 '12 at 13:59
You can store the Boolean values as integers 0 (false) and 1 (true). — Yaqub Ahmad, Jan 29 '12 at 14:03
You *could* use an integer value to store the flag values. E.g. for Sunday and Tuesday the value would be 01010000 (binary), or something like that. With that said, you should really be storing the seven booleans in separate columns instead. — dmon, Jan 29 '12 at 16:05
Another approach may be using JSONObject as described [here]( http://stackoverflow.com/questions/5703330/saving-arraylists-in-sqlite-databases) — Dexter, Mar 02 '14 at 06:39

Muhammad Nabeel Arif · Accepted Answer · 2013-11-24T12:06:49.760

89

You cannot save String array into Database. But you can use this trick.

1) So you have to convert it into simple String using convertArrayToString(String[] array) method. This will concatenate all elements of string using 'comma'.

2) When you would retrieve this String back from Database you could convert it back to String array using convertStringToArray(String str) method. This method will split the string from 'comma' and you will get your original array back.

public static String strSeparator = "__,__";
public static String convertArrayToString(String[] array){
    String str = "";
    for (int i = 0;i<array.length; i++) {
        str = str+array[i];
        // Do not append comma at the end of last element
        if(i<array.length-1){
            str = str+strSeparator;
        }
    }
    return str;
}
public static String[] convertStringToArray(String str){
    String[] arr = str.split(strSeparator);
    return arr;
}

edited Nov 24 '13 at 12:06

answered Jan 29 '12 at 14:15

Muhammad Nabeel Arif

19,140
8
51
70

It is the same thing. When you will concatinate String with boolean. Boolean will automatically be converted to String. When you will retrieve back the Array of Booleans you have to type cast each item into Boolean by Boolean.parseBoolean(str[i]). – Muhammad Nabeel Arif Jan 29 '12 at 14:31
@MuhammadNabeelArif hey i am getting checkbox checked value in array.now i want to store it in database but different row.so how is it posible? for ex: [1,2,4] this is my array nd i want to store it in like: 1 then new row and store 2 then new row and 4.how is it posible?i am new in android so help – Google Jan 01 '13 at 06:01
It is simple, consider each record 1,2,4 etc individual entry, now prepare a row for each of these and insert it into db. You need to understand basics of db. Here is a quick tutorial ' http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ ' – Muhammad Nabeel Arif Jan 01 '13 at 07:21
Technically I think it is better to use StringBuilder to build the String of numbers, though perhaps compiler will optimize it anyway. – Tom Sep 13 '13 at 16:49
6

this wont work if the any of the elements of the array contain a ',' – pellucide Nov 24 '13 at 08:29

score 12 · Answer 2 · edited Nov 15 '18 at 17:04

Based on Muhammed's answer but handles List of Strings instead of array and uses StringBuilder instead of allocating many Strings during values concatenation:

private static final String LIST_SEPARATOR = "__,__";

public static String convertListToString(List<String> stringList) {
    StringBuilder stringBuilder = new StringBuffer();
    for (String str : stringList) {
        stringBuilder.append(str).append(LIST_SEPARATOR);
    }

    // Remove last separator
    stringBuilder.setLength(stringBuilder.length() - LIST_SEPARATOR.length());

    return stringBuilder.toString();
}

public static List<String> convertStringToList(String str) {
    return Arrays.asList(str.split(LIST_SEPARATOR));
}

score 9 · Answer 3 · answered Oct 12 '14 at 21:44

9

Convert it to single String to save it in sqlite. Later, retrieve it back from sqlite as a single string and convert it back to Array of String.

String ARRAY_DIVIDER = "#a1r2ra5yd2iv1i9der";

public String serialize(String content[]){      
    return TextUtils.join(ARRAY_DIVIDER, content);
}

public String[] derialize(String content){
    return content.split(ARRAY_DIVIDER);
}

answered Oct 12 '14 at 21:44

Aung Thiha

1,195
3
13
23

It might be late but ... I can't think of a downside to this lazy method – Lobstw Jul 03 '16 at 01:59
@Lobstw it could lead to unexpected behavior if one of the elements of ```content[]``` happened to be ```"#a1r2ra5yd2iv1i9der"```. This would be serialized, but upon deserialization, the ambiguity would result in two empty string elements in place of the offending one string element. If other areas of the program have strict expecations of the array size, this could lead to undesired behavior. – snapfractalpop May 14 '17 at 10:15
@snapfractalpop yeah that's right but making the divider a long alphanumeric string would avoid this behaviour in ~100% of cases – Lobstw May 15 '17 at 00:56
1

@Lobstw in most cases, I agree. I comment to note that the context for these 0.001% cases can be important. For example, if this leads to some bug that then leads to some kind of security exploit in other areas of code that rely on faulty guarantees, the 0.001% can quickly grow to include script kiddies and such. – snapfractalpop May 15 '17 at 02:06

score 6 · Answer 4 · answered Aug 29 '15 at 17:24

6

It would be easier if you convert the array of Strings to JSONArray, use toString and then, retrieve it by parsing first into JSONArray and then, convert it into array of Strings

answered Aug 29 '15 at 17:24

jiahao

3,373
2
35
36

1

This is a better method than the accepted answer because you will never be bitten by the separator string (like `__,__` or `#a1r2ra5yd2iv1i9der` etc.) found in one of the array elements – Alexander Farber Sep 04 '17 at 14:11

score 1 · Answer 5 · answered Jan 29 '12 at 14:05

1

It looks like your database is not designed as it should be. If you want to store the boolean data in SQLite database you can store the Boolean values as integers 0 (false) and 1 (true)

answered Jan 29 '12 at 14:05

Yaqub Ahmad

27,569
23
102
149

1

yai can store as 0, 1 but since there more than one i have to use int array and contentvalues does not allow that anyway around that? than having to make new int for each one – user1175899 Jan 29 '12 at 14:11

Android: SQLite saving string array?

5 Answers5

Linked