CODEWITHSUNDEEP
androidmysqljsonsqlite
Varuni N R
Varuni N R

Reputation: 802

how to insert multiple rows into sqlite android

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

Upvotes: 5

Views: 16458

Answers (3)

Ivan Soria
Ivan Soria

Reputation: 1

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

Upvotes: 0

abbasalim
abbasalim

Reputation: 3232

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

Upvotes: 1

Alan
Alan

Reputation: 296

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.

Upvotes: 8

Related Questions