Java 在Android的SQLite数据库中保存ArrayList
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3142285/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Saving ArrayList in SQLite database in Android
提问by Mango
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.
我一直在 android 上使用 SQLite，我想向表中的列添加一个数组列表，然后将数据作为数组列表取回。arraylist 是一个 Long 列表。我注意到 SQL 有一个用于存储 BLOBS 的选项，但是看起来我需要先将 arraylist 转换为 byte[]，然后才能将其作为 Blob 存储在我的 SQLite 数据库中。
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?
如果有人有关于如何将数组列表保存到 SQLite 数据库的解决方案，将不胜感激。或者还有其他选择来保存我的数据数组，我应该考虑吗？
回答by matt b
Sounds like you want to serialize the List. Here is a tutorial/introto the Java Serialization API.
回答by Con
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
}
回答by Julius Musseau
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.
请原谅我野蛮地抄袭了我之前对BLOB 与 VARCHAR 的回答，用于将数组存储在 MySQL 表中。那边的其他回答也很中肯。
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.
我认为 Con 的方法可能比使用 java 序列化更好，因为 java 的内置序列化需要额外的字节，而非 java 应用程序将更难处理数据。
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:
注意：如果您的列表有时会包含超过 31 个长（248 个字节），那么您将需要使用 BLOB。您不能在 MySQL 中使用 BINARY() 或 VARBINARY()。我知道你在问 SQLite，但本着完全抄袭我之前的回答的精神，我会假装你在问 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
回答by brandall
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.
我有两个ArrayList<String>，都将有 1000 多个条目。我查看了 blob 和字节，但对我来说，加快进程并使其可用的解决方案是通过更改插入方法并摆脱database.insert- 这就是这里的功劳 。
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.
它很容易适应 Longs 和 Integers 等，并且可以快速减轻。谢天谢地，我不必再为 blob 和字节烦恼了！希望能帮助到你。
回答by Ashok
To Insert :
插入：
ArrayList<String> inputArray=new ArrayList<String>();
//....Add Values to inputArray
//....将值添加到 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
使用“inputString”将 ArrayList 的值保存在 SQLite 数据库中
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.
从 SQLiteDatabse 中获取您保存并更改为 ArrayList 类型的字符串，如下所示： outputarray 是一个从 SQLiteDatabase 获取的字符串，用于此示例。
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.....
- 在 SQLite 中使用文本作为格式来存储字符串 Value.....
回答by Mohammad Hadi
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 :
有一种更简单的方法可以完全以另一种方式做这样的事情。您可以创建一个包含所有数组值的字符串。为此，创建一个 StringBuilder 并连续附加值并使用分隔符在场外附加值（就像你不会在数组值中使用的 simbole 。例如虚拟 '|' 。例如在代码中：
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 :)
当您想要获取它们并使用这些值时。将列从数据库中提取为字符串，并使用 splite() 操作将数组的每个值都放在数组列中，这样您就可以轻松使用它了 :)
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 ;)
也许它不是标准的，但它很有帮助，希望有所帮助；）
回答by JamisonMan111
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]);
