How to insert to into many to many relationships. SQLite

Question

In my android app I have an SQLite database. with this structure:

PrivateList(idList INTEGER PRIMARY KEY, name TEXT, creationDate TEXT, active INTEGER, deactivationDate TEXT);

PrivateProduct (idProduct INTEGER PRIMARY KEY, description TEXT, quantity INTEGER, active INTEGER, additionDate TEXT);

List_Product(idList INTEGER NOT NULL, idProduct INTEGER NOT NULL, PRIMARY KEY (idList, idProduct), FOREIGN KEY(idList) REFERENCES PrivateList(idList), FOREIGN KEY(idProduct) REFERENCES PrivateProduct(idProduct));

I have an autogenerator list and elements using for to try the app:

localDB = new LocalDB(this, "localBD", null, 1);
        SQLiteDatabase sqLiteDatabase = localDB.getWritableDatabase();

        if (sqLiteDatabase != null){

            for (int i = 0; i < 10; i++){
                String a = "List" + i;
                String b = "Product" + i;
                Log.i("execfor", "INSERT INTO PrivateList (name, creationDate, active, deactivationDate) " + " VALUES ('" + a + "', CURRENT_TIMESTAMP, 1, null);");
                sqLiteDatabase.execSQL("INSERT INTO PrivateList (name, creationDate, active, deactivationDate) " + " VALUES ('" + a + "', CURRENT_TIMESTAMP, 1, null);");
                sqLiteDatabase.execSQL("INSERT INTO PrivateProduct (description, quantity, active, additionDate) " + " VALUES ('" + b + "', 3, 1, CURRENT_TIMESTAMP);");
                //sqLiteDatabase.execSQL("INSERT INTO List_Product (idList, idProduct) values ");//

            }

        }

But I can`t find the way to get rowIds from each list and product to insert both, idlist and idproduct, into List_Product.

Thank you in advance.

Typically you'd only insert when there is a List/product relationship in which case, as you would then know the product and list, you can get the respective id's and then insert the list_product row. — MikeT, Aug 02 '17 at 03:28
However, if you wanted every single permutation then you could get the id if you used `long id = sqLiteDatabase.query(.....` i.e. query will return the id of the inserted row. However, I'd suggest that your test would then not cater for no relationship situations. — MikeT, Aug 02 '17 at 03:38
oops sorry you'd use `long id = sqLiteDatabase.insert(...` not query. — MikeT, Aug 02 '17 at 03:48

MikeT · Accepted Answer · 2017-08-02T07:34:13.573

The main change to facilitate grabbing the id's would be to swap from using execSQL to using insert as insert returns the id of the inserted row, execsql does not.

A little more on this here Using execSQL for INSERT operation in Android SQLite.

However, I'm not sure if you can pass CURRENT_TIMESTAMP via a ContentValues and it would result getting the current timestamp as opposed to just setting the value to the literal CURRENT_TIMESTAMP. You could use DEFAULT CURRENT_TIMEPSTAMP in the respective column definitions (as I have in the code below).

I'd suggest that you would not want a link between every list/product permutation (that would be 100 rows for you 10 List rows and 10 Product rows) as in real life you would probably not have such a scenario rather you'd have some links between the two. So in the code below I've randomly created links.

First some code from the Database Helper (for my convenience named SO45449914) for performing the inserts:-

public long insertListRow(String name,
                          int active) {
    ContentValues cv = new ContentValues();
    cv.put(LISTNAME_COL,name);
    cv.put(LISTACTIVE_COL,active);
    cv.put(LISTDEACTIVATIONDATE_COL,"");
    return this.getWritableDatabase().insert(PRIVATELISTTABLE,null,cv);
}

public long insertProductRow(String description,int quantity, int active) {
    ContentValues cv = new ContentValues();
    cv.put(PRODUCTDESCRIPTION_COL,description);
    cv.put(PRODUCTQUANTITY_COL,quantity);
    cv.put(PRODUCTACTIVE_COL,active);
    return this.getWritableDatabase().insert(PRIVATEPRODUCTTABLE,null,cv);
}

public void insertListProductLink(long listid, long productid) {
    ContentValues cv = new ContentValues();
    cv.put(LISTPRODUCTLIST_COL,listid);
    cv.put(LISTPRODUCTPRODUCT_COL,productid);
     if (this.getWritableDatabase().insertOrThrow(LISTPRODUCTTABLE,null,cv) <0) {
        //handle failed insert
    }
}

Notes - I've used class variables for all columns names. - Columns that have the current time stamp get this via the default, so there is no need to have a cv.put for those columns.

In the activity is the following code :-

        void doSO45449914() {
            SO45449914 dbhelper = new SO45449914(this);
            int loopcount = 10;
            long[] listids = new long[loopcount];
            long[] productids = new long [loopcount];


            for (int i=0; i < 10; i++) {
                listids[i] = dbhelper.insertListRow("a" + i,1);
                productids[i] = dbhelper.insertProductRow("b" + i,3,1);
            }

            Cursor csra = dbhelper.getWritableDatabase().query(SO45449914.PRIVATELISTTABLE,
                     null,null,null,null,null,null
            );
            Cursor csrb = dbhelper.getWritableDatabase().query(SO45449914.PRIVATEPRODUCTTABLE,
                    null,null,null,null,null,null
            );
            Log.d("SO45449914","Number of rows in LIST TABLE = " + csra.getCount());
            Log.d("SO45449914","Number of rows in PRODUCTS TABLE = " + csrb.getCount());
            for (long aid: listids) {
                Log.d("SO45449914","LIST ID from store = " + Long.toString(aid));
            }
            for (long bid: productids) {
                Log.d("SO45449914","PRODUCT ID from store = " + Long.toString(bid));
            }
            for (long lid: listids) {
                for (long prdid: productids) {
                    if ((Math.random() * 100) > 60) {
                        dbhelper.insertListProductLink(lid,prdid);
                        Log.d("SO45449914",
                                "Adding link between List id(" +
                                        Long.toString(lid) +
                                        ") and product id(" +
                                        Long.toString(prdid) +
                                        ")"
                        );
                    }
                }
            }

            csra.close();
            csrb.close();
        }

Exlapnation

The first few lines prepare long arrays based upon the number of Lists and products to be created (same number of both). Integer loopcount determines how many.

The first loop, inserts Lists and Products which use the insert method storing the returned id in the respective array element.

Two Cursors are then created for obtaining row counts, which are then written to the log. The id's as stored in the arrays are output to the log.

Two nested loops are then invoked with Products being the inner (not that it matters) and randomly (about 40% of the time) a row will be inserted into the link table. I've assumed random but you always easily adjust the algorithm to follow a pattern. It's if ((Math.random() * 100) > 60) { that determines whether or not to insert a link.

The two Cursors are then closed.

Results

Here are screen shots of the resultant tables :-

PrivateList Table

PrivateProduct Table

List_Product Table

..... (44 rows in the List_Product table)

Wow! Such a nice answer! Thank you!. But I only don't understand one thing. It is supposed that I have 10 products in each list but with your method you just have 44 rows in List_Product, what means that there are 56 products that haven't a relation with there list. How would you select all the products in each list? — Kanayel, Aug 02 '17 at 15:07
The link List_Product is mapping a privateList to a privateProduct. For example if List were person Fred, Bert and Tom and Products were Bread, Mike and Honey. You would give Fred a list of products to chose from (all rows from lists) and Fred could say select to have Bread and Honey. So there would be two links Fred-Bread and Fred-Honey and **no link between Fred and Milk** (that's how link are commonly used). If you wanted links between all i.e. 100 links all you'd have to do is remove `if ((Math.random() * 100) > 60) {` so that the link is always created. — MikeT, Aug 02 '17 at 20:30
For 100 links it would be easier to change the 60 to 0 in`if ((Math.random() * 100) > 60) {`. — MikeT, Aug 02 '17 at 20:49

score 0 · Answer 2 · answered Aug 02 '17 at 16:16

Well, this is what I did. Despite of the fact that there is a way do the same without so many rows in List_Product table; I'd like to understand the way. (Also I had problem in the for so it didnt do what I wanted exactly).

localDB = new LocalDB(this, "localBD", null, 1);
    SQLiteDatabase sqLiteDatabase = localDB.getWritableDatabase();

    if (sqLiteDatabase != null){

        long idList;
        long idProduct;

        for (int i = 0; i < 10; i++){
            String a = "List" + i;
            String b = "Product" + i;
            ContentValues contentValuesList = new ContentValues();
            contentValuesList.put("name", a);
            contentValuesList.put("active", 1);
            contentValuesList.put("creationDate", "CreationDate");
            contentValuesList.put("deactivationDate", "");
            idList = sqLiteDatabase.insert("PrivateList", null, contentValuesList);

            for (int j = 0; j < 10; j++){
                ContentValues contentValuesProduct = new ContentValues();
                contentValuesProduct.put("description", b);
                contentValuesProduct.put("active", 1);
                contentValuesProduct.put("quantity", 1);
                contentValuesProduct.put("additionDate", "additionDdate");
                idProduct = sqLiteDatabase.insert("PrivateProduct", null, contentValuesProduct);

                ContentValues contentValuesListProduct = new ContentValues();
                contentValuesListProduct.put("idList", idList);
                contentValuesListProduct.put("idProduct", idProduct);
                sqLiteDatabase.insert("List_Product", null, contentValuesListProduct);
            }

I know it could be more efficient, but that it doesn't matter now.

This is the result in the database:

PrivateList:

with 10 rows

PrivateProduct:

with 100 rows.

List_Product:

The problem was that I didn't know the existance of sqlLiteDatabase.insert(...) method.

Thanks you all.

How to insert to into many to many relationships. SQLite

2 Answers2

Exlapnation

Results