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: stackoverflow.com/q/1609637/468724 — Inder Kumar Rathore
– Inder Kumar Rathore, Commented Nov 10, 2017 at 9:11

Alan · Accepted Answer · 2017-03-06 08:00:10Z

8

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.

edited Mar 6, 2017 at 8:00

answered Mar 6, 2017 at 7:38

Alan

2961 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Varuni N R Over a year ago

Not able to get db.beginTransaction(); outside the class. For loop is in other class

Alan Over a year ago

please check my update, it should be okay if you use ContentValues

user25 Over a year ago

Who opens and closes db in loop? It's crazy!

user25 Over a year ago

@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..

juztcode Over a year ago

I think this is inserting multiple 'columns' isn't it?

abbasalim · Accepted Answer · 2017-12-07 09:32:31Z

1

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

answered Dec 7, 2017 at 9:32

abbasalim

3,3622 gold badges29 silver badges49 bronze badges

2 Comments

SuperHaker Over a year ago

@user25 don't try to be oversmart and tell me having multiple insert statements is simpler. And this is not how db.execSQL works.

user924 Over a year ago

you have to use Android API wrappers and db transactions

VLAZ · Accepted Answer · 2022-05-22 11:48:09Z

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()
}

Collectives™ on Stack Overflow

how to insert multiple rows into sqlite android

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related