How to save ArrayList in SQLite android?

In order to write a structure into a relational database as a multidimensional array, a one-to-many connection is required.

Let's look at an example. We have a list of students, each student needs to attach documents available to them (there may be a different number and name). Each document has a name and a series number — the conditions are equivalent to attaching to the record (students) an ArrayList collection (list of documents) with several fields (name and series).

Create two tables - the names table with the names of the students and the documents table with the list of documents:

table names :

| _id | name | |-----|-------| | 1 | Настя | | 2 | Петя | | 3 | Вася | 

Table documents :

 | _id | document | number | key | |-----|----------|--------|-----| | 1 | Паспорт | 156 | 1 | | 2 | Права | 187 | 1 | | 3 | Полис | 109 | 1 | | 4 | Паспорт | 205 | 2 | | 5 | Полис | 267 | 2 | | 6 | Паспорт | 387 | 3 | | 7 | Страхока | 345 | 3 | 

In the documents table, add the service key field, which will link our tables. This field indicates the student ID from the names table to which this document belongs (for example, documents numbered 205 and 267 belong to Peter).

All is ready. Now, in order to get which documents belong to Nastya (to get the contents of an ArrayList assigned to a specific record), we make up the following query:

  SELECT d.number, d.document FROM names n, documents d WHERE n.[name] = "Настя" AND n.[_id] = d.[key] 

Get the sample view:

 | number | document | |--------|----------| | 156 | Паспорт | | 187 | Права | | 109 | Полис | 

The line with the request is read as follows: Select the number and document columns from the documents table by searching the tables documents and names for which the condition is the same: in the names table, the name = "Nastya" column and in the documents table the key column matches the _id column of the name table. On a simple human, this means that we ask to select from two tables those values ​​where for the first one there is the name Nastya, and for the second column her key = column _id selected for the first table.
Substituting in the request other names (Peter, Vasya ..) we will get the documents of these students.
PS: n and d - aliases of the tables, to shorten the record of queries, you can not specify them, but write the full names of the tables.

We can include in the sample the name of the student (add data from the record to which the list is attached):

 SELECT d.number, d.document, n.name FROM names n, documents d WHERE n.[name] = "Настя" AND n.[_id] = d.[key] 

We get:

 | number | document | name | |--------|----------|-------| | 156 | Паспорт | Настя | | 187 | Права | Настя | | 109 | Полис | Настя | 

To "attach" a new set of documents to a specific record (write an ArrayList ), you need to get the _id of this record ( names table) and add the data from the list to the documents table, indicate the received _id of the desired record in the key column of this table.

To learn a lot more I recommend reading a great book:

Bailey L. - Learning SQL (Bestsellers O'Reilly) - 2012.

This book is very accessible explains the organization of the database, all types of relationships and the preparation of various queries. Unlike other publications on this topic, confusing and incomprehensible, this book will be simply indispensable for a beginner.