how to insert multiple rows into sqlite android

Question

I have a method to insert values into sqlite in a class. And in the main activity i am parsing json values and trying to insert the values into sqlite in a loop as i have got to insert more than one row. But only one row is getting added into database.

Method to add multiple rows is as below:

public void addSession(String sessionname,String start_time,String end_time,String id) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            db.beginTransaction();
            String sql = "Insert into session (id, sessionname,start_time, end_time) values(?,?,?,?)";
            SQLiteStatement insert = db.compileStatement(sql);
            insert.bindString(1, id);
            insert.bindString(2, sessionname);
            insert.bindString(3, start_time);
            insert.bindString(4, end_time);
            insert.execute();
            db.setTransactionSuccessful();
        } catch (Exception e) {
            Log.w("Appet8:",e );
        } finally {
         db.endTransaction();
        }
    }

In the below for loop i am trying to add values into sqlite table:

JSONObject jsonObject = new JSONObject(response);
JSONArray foodsessions = jsonObject.getJSONArray("foodsessions");
for(int i=0;i<foodsessions.length();i++) {
                   JSONObject session_object = foodsessions.getJSONObject(i);
                   String session = session_object.getString("sessionname");
                   String start_time = session_object.getString("start_time");
                   String end_time = session_object.getString("end_time");
                   String session_id = session_object.getString("id");                                
 db.addSession(session,start_time,end_time,session_id);
                       }

This thread might help you: https://stackoverflow.com/q/1609637/468724 — Inder Kumar Rathore, Nov 10 '17 at 09:11

Alan · Accepted Answer · 2017-03-06T08:00:10.647

A better structure would be create a db reference outside the method, and pass it as a reference:

SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();

// your for loop

db.setTransactionSuccessful();
db.endTransaction();

============== Please check below which is used for insert multi rows:

// adb is SQLiteOpenHelper
JSONObject jsonObject = new JSONObject(response);
JSONArray foodsessions = jsonObject.getJSONArray("foodsessions");
int length = foodsessions.length();

for (int i = 0; i < length; i++) {
    JSONObject o = foodsessions.getJSONObject(i);
    String session = session_object.getString("sessionname");
    String start_time = session_object.getString("start_time");
    String end_time = session_object.getString("end_time");
    String session_id = session_object.getString("id");    

    SQLiteDatabase db = adb.getWritableDatabase();

    ContentValues newValues = new ContentValues();
    newValues.put(adb.ATTRIBUTE_session, session);
    newValues.put(adb.ATTRIBUTE_start_time, start_time);
    newValues.put(adb.ATTRIBUTE_end_time, end_time);
    newValues.put(adb.ATTRIBUTE_session_id, session_id);

    long res = db.insertWithOnConflict(adb.TABLE_NAME, null, newValues, SQLiteDatabase.CONFLICT_REPLACE);
    db.close();
}

A better solution is avoiding to use raw query if it can be done by the method provided by SQLiteDatabase.

Not able to get `db.beginTransaction();` outside the class. For loop is in other class — Varuni N R, Mar 06 '17 at 07:45
please check my update, it should be okay if you use ContentValues — Alan, Mar 06 '17 at 07:55
@VaruniNR what do you mean by "not able to get db.beginTransaction(); outside the class?" If you created db instance you can use it anywhere you want.. — user25, Dec 24 '18 at 16:23

score 1 · Answer 2 · answered Dec 07 '17 at 09:32

this is simpler:

INSERT INTO tb_units (sort, name, isdef) VALUES
(1, "عدد",1),
(2, "کیلوگرم",1),
(3, "گرم",1),
(4, "کیلومتر",1),
(5, "متر",1),
(6, "متراژ",1),
(7, "مگابایت",1),
(8, "کیلوبایت",1),
(9, "مثقال",1),
(10, "شاخه",1),
(11, "صفحه",1),
(12, "رول",1),
(13, "بسته",1),
(14, "کارتن",1),
(15, "دستگاه",1),
(16, "پاکت",1),
(17, "مورد",1),
(18, "جعبه",1),
(19, "تن",1),
(20, "قطعه",1),
(21, "حلقه",1),
(22, "تخته",1),
(23, "جلد",1),
(24, "لیتر",1),
(25, "مترمکعب",1),
(26, "مترمربع",1),
(27, "دست",1),
(28, "جین",1),
(29, "اشتراک",1),
(30, "سری",1),
(31, "گیگابایت",1),
(32, "جلسه",1),
(33, "جفت",1),
(34, "قراض",1),
(35, "ساعت",1),
(36, "تن",1);

and us query with db.execSQL

@user25 don't try to be oversmart and tell me having multiple insert statements is simpler. And this is not how db.execSQL works. — SuperHaker, Apr 07 '19 at 16:45

score 0 · Answer 3 · edited May 22 '22 at 11:48

This how I did it in Kotin

val admin = AdminSQliteOpenHelper(this, "BDQuestionario", null, 1)

val baseDatos = admin.writableDatabase


val fila = baseDatos.rawQuery("select codigo from curso where codigo= 24", null )

if (fila.moveToFirst()){
    baseDatos.close()
    Toast.makeText(this, "Existe la tabla", Toast.LENGTH_SHORT)
        .show()
} else {
    baseDatos.execSQL("insert into curso ('codigo', 'clavemodulo', 'valor') VALUES " +
            "(1,'mod11',1)," +
            "(2,'mod12',1)," +
            "(3,'mod13',1)," +
            "(4,'mod14',1)," +
            "(5,'mod15',1)," +
            "(6,'mod16',1)," +
            "(7,'mod17',1)," +
            "(8,'mod18',1)," +
            "(9,'mod21',1)," +
            "(10,'mod22',1)," +
            "(11,'mod23',1)," +
            "(12,'mod24',1)," +
            "(13,'mod25',1)," +
            "(14,'mod26',1)," +
            "(15,'mod27',1)," +
            "(16,'mod28',1)," +
            "(17,'mod31',1)," +
            "(18,'mod32',1)," +
            "(19,'mod33',1)," +
            "(20,'mod34',1)," +
            "(21,'mod35',1)," +
            "(22,'mod36',1)," +
            "(23,'mod37',1)," +
            "(24,'mod38',1);")
    baseDatos.close()
    Toast.makeText(this, "Se cargaron los datos del questionario", Toast.LENGTH_SHORT).show()
}

how to insert multiple rows into sqlite android

3 Answers3

Linked