CODEWITHSUNDEEP
androidsqlite
AG1
AG1

Reputation: 6774

Android sqlite inserting 500 rows does not work or preserve INSERT order

I can not get SQLite to support my begin/end transaction surrounding multiple inserts.

 Multiples INSERTs : 2500ms
 Using BEGIN and COMMIT : 90ms
 Using SELECT and UNION : 40ms

So I looked using begin and commit. What am I doing wrong?

  // pseudocode: 
  ArrayList<Integer> iList = new ArrayList<Integer>();
    for (int i = 1; i <= 500; i++) {
      iList.add(i);
    }
  Collections.shuffle(iList);

  StringBuilder sb = new StringBuilder("begin transaction;");
  for (Integer i: iList) {
    sb.append("insert into \"t_order\" (qid) values(");
    sb.append(i);
    sb.append(");");
  }
  sb.append(" end transaction;");

  // from docs: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL(java.lang.String)
  // Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.
  m_db.execSQL(sb.toString());

OK, I did a bit more research and it seems that "Multiple statements separated by semicolons are not supported." What can I do instead to insert and preserve insert order?

Upvotes: 0

Views: 1079

Answers (4)

Michael Assraf
Michael Assraf

Reputation: 111

This may help :

public void putAll(Context context, LinkedList<HANDLEDOBJECT> objects) {

    if (objects.size() < 1 || objects.get(0) == null)
        return;

    Log.i("Database", "Starting to insert objects to " + getTableName());

    List<String> insertCommands = new ArrayList<String>();
    int t = 0;

    while (t < objects.size()) {
        int k = 0;
        StringBuilder sb = new StringBuilder();

        sb.append("INSERT OR REPLACE INTO ").append(getTableName())
                .append("('k', 'v') ");

        for (t = t + 0; t < objects.size() && k < 450; t++) {
            k++;
            if (t % 450 != 0)
                sb.append("UNION ");
            sb.append("SELECT " + objects.get(t).getId())
                    .append(" AS k, ")
                    .append("'"
                            + GsonSerializer.getInstance().toJson(
                                    objects.get(t), getHandledObjectType())
                            + "'").append(" AS v ");
        }

        insertCommands.add(sb.toString());
    }

    for (String insertCommand : insertCommands)
        SQLiteClient.getConnetion(context).execSQL(insertCommand);

    Log.i("Database", "Successfully inserted " + t + " objects to "
            + getTableName() + "!!!");

    System.gc();
}

Upvotes: 0

Ted Hopp
Ted Hopp

Reputation: 234847

Use the SQLiteDatabase.beginTransaction() and SQLiteDatabase.endTransaction() methods and issue your execSQL call(s) between them. It would also be better style to use a ContentValues structure instead of doing your own string concatenation:

ContentValues cv = new ContenValues();
m_db.beginTransaction();
try {
    for (Integer i: iList) {
        cv.put("qid", i);
        m_db.insert("t_order", null, cv);
    }
    m_db.setTransactionSuccessful();
} finally {
    m_db.endTransaction();
}

Upvotes: 2

NuSkooler
NuSkooler

Reputation: 5525

Have a look at the official Android documentation on beginTransaction(). Replace the "..." portion with a loop doing a separate execSQL() call -- there is no need to truncate the statements together in one buffer.

Also, it's often worth it to use a prepared statement. Prepare the statement, begin the transaction, loop for each item binding and executing the statement and finally commit.

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180868

Start a transaction, execute each of the INSERTs on separate execSQL() calls, and then commit the transaction.

You don't need to cluster the INSERTs together in the same execSQL() call.

Upvotes: 2

Related Questions