CODEWITHSUNDEEP
androidsqlsqlite
AllOutOfSalt
AllOutOfSalt

Reputation: 1492

multiple insert statements android sqlite

I have 800 insert statements downloaded from network in the following format:

insert into mytable(col1,..,coln) values(val1,..valn);insert into mytable col...

I have heard of transactions ,ContentValues , and using union but I can't figure out which one is better for this case.

What is the best way to insert them in sqlite database after truncating mytable?

Upvotes: 6

Views: 8051

Answers (2)

Do Xuan Nguyen
Do Xuan Nguyen

Reputation: 199

I use code below, run once only query.

 public void addListRecord(List<Province> list) {
    SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
    try {
        String sql = " INSERT INTO " + TABLE_PROVINCE + " ("
                + COL_PROVINCE_ID + ","
                + COL_PROVINCE_NAME + ","
                + COL_PROVINCE_STATUS + ") VALUES ";
        String value = "";
        for (Province item : list) {
            value += "('" + item.getId() + "',"
                    + "'" + item.getName() + "',"
                    + "'" + item.getStatus() + "')";
            value += ",";
        }
        value = Utils.removeLastChar(value);
        value += ";";
        String mQuery = sql + value;
        Logger.debug("mQuery: " + mQuery);
        SQLiteStatement statement = db.compileStatement(mQuery);
        statement.execute();
    } catch (SQLException e) {
        Logger.debug("SQLException Error: " + e);
    } finally {
        DatabaseManager.getInstance().closeDatabase();
    }
}
public static String removeLastChar(String s) {
    if (s == null || s.length() == 0) {
        return s;
    }
    return s.substring(0, s.length() - 1);
}

Upvotes: 0

Szymon
Szymon

Reputation: 43023

In case of multiple queries to run, in order to improve data integrity and performance, you should use transactions. This is a code to give you an idea how to do it:

    SQLiteDatabase db = sqlHelper.getWritableDatabase(); // get a writable database here
    db.beginTransaction();

    try {
        for (int insertQuery : listOfQueries) {  // loop through your records 
            db.insert(...);
        }

        db.setTransactionSuccessful();
    }
    finally {
        db.endTransaction();
    }

    db.close();

Upvotes: 19

Related Questions