CODEWITHSUNDEEP
androidsqliteandroid-sqlitebulkinsertsqliteopenhelper
divyang7
divyang7

Reputation: 309

Efficient way to insert multiple records in sqlite database

I need to populate my sqlite database. I need to do this using multiple records insertion. As I explored, i found out that there are various ways of accomplishing this, like bulkinsert() function, ContentValues[] array etc. But I chose to go with the SELECT and UNION SELECT. I have created SQLiteOpenHelper class and now I have to add multiple records at once by the following query:

 db.execSQL("INSERT INTO "+tourEntry.TABLE+
        " SELECT "+tourEntry._ID+" AS 'Tour_ID', "+tourEntry.TOURTYPE+" AS 'Tour_Type', "+tourEntry.TOURNAME+" AS 'Tour_Name', "+tourEntry.NIGHTS+" AS 'Nights', "+tourEntry.DAYS+" AS 'Days', "+tourEntry.PIC+" AS Pic" +
        " UNION SELECT '1', '3', 'Delhi Tour', '1', '2', 'img/mini-vacation/lotus.png'"+
        " UNION SELECT '2', '3', 'Taj Mahal Tour By Train (Same Day Return)', '1', '1', 'img/mini-vacations/taj-train.jpg'"+
        " UNION SELECT '3', '3', 'Taj Mahal Trip By Car (Same Day Return)', '1', '1', 'img/mini-vacations/'"+
        " UNION SELECT '4', '3', 'Taj Mahal Trip', '1', '2', 'img/mini-vacations/noimg.png'"+
        " UNION SELECT '5', '3', 'Pink City Tour (Same Day Return)', '1', '1', 'img/mini-vacations/noimg.png'");

Problems:

1.Is the above query appropriate for multiple records insertion?

2.Is database adapter class always a necessity for interacting with the database or just write a method in MainActivity to execute this insert query and call the method in oncreate method of MainActivity will work fine?

3.I was unable to understand the implementation of ContentValues[] array and bulkinsert() function which was also explained based on the ContentValues array. Where and How to use them?

4.What is the best way to test if the connectivity of database and project is successfull and we can retrieve data successfully?

If someone can enlighten the concepts, I would be very thankful.

Upvotes: 1

Views: 6848

Answers (1)

Alex
Alex

Reputation: 271

are you just trying to insert multiple records from memory?

try something like this (for a single item):

SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put("Tour_ID", 1);
values.put("Tour_Type", 3);
values.put("Tour_Name", "Delhi Tour");
values.put("Nights", 1);
values.put("Days", 2);  
final Bitmap pic=BitmapFactory.decodeStream(getContentResolver().openInputStream(img/mini-vacation/lotus.png));
values.put("Pic", pic);    

db.insert(tourEntry.TABLE, null, values);
db.close();

db.insert will return a boolean telling you if the insert was successful.

If you want to insert multiple records at once use a transaction. See Android Database Transaction

Upvotes: 3

Related Questions