Autoincrement value in transactions body

Question

How can i get the autoincrement value in thansaction body?

Code

public void insertAllStudents(List<Student> students) {
    String sql = "INSERT INTO "+ StudentEntry.TABLE_NAME +" VALUES (?,?,?,?,?);";
    SQLiteDatabase db = this.getWritableDatabase();
    SQLiteStatement statement = db.compileStatement(sql);
    db.beginTransaction();
    for (Student student: students) {
        statement.clearBindings();
        statement.bindString(2, student.getId());
        statement.bindString(3, student.getFirstName());
        statement.bindString(4, student.getLastName());
        statement.bindLong(5, student.getBirthday());
        statement.execute();
    }
    db.setTransactionSuccessful();
    db.endTransaction();
}

The first column (_ID) is autoincrement field. Is it opportunity to get this value? student.getId() -that's not id from database, that's different id.

score 1 · Answer 1 · edited May 23 '17 at 11:46

1

If you change your code to use db.insert(), this method returns the id of the inserted row - see Get generated id after insert.

There is also a specialised SQLite function to get the last inserted row if you'd prefer to keep compiling statements, see Best way to get the ID of the last inserted row on SQLite

edit: example using db.insert(). This isn't tested but should be pretty close to functional.

db.beginTransaction();
boolean success = true;
final ContentValues values = new ContentValues();
for (final Student student: students) {
    values.put("student_id", student.getId());
    values.put("first_name", student.getFirstName());
    values.put("last_name", student.getLastName());
    values.put("birthday", student.getBirthday());
    final long id = db.insert("my_table", null, values);
    if (id == -1) {
        success = false;
        break;
    }

    // TODO do your thing with id here.
}
if (success) {
    db.setTransactionSuccessful();
}
db.endTransaction();

edited May 23 '17 at 11:46

Community

1
1

answered Feb 20 '17 at 23:55

Tom

6,946
2
47
63

db.insert() takes a lot of time so i using transactions for inserting data. But can i get id value before i finish transaction? – Andrew Feb 21 '17 at 00:01
Yes, the `SELECT LAST_INSERT_ROWID()` should work, even within a transaction - http://stackoverflow.com/questions/376773/in-sqlite3-can-a-select-succeed-within-a-transaction-of-insert – Tom Feb 21 '17 at 00:06
@Andrew by the way, you should be able to use db.insert() inside a transaction - it just compiles a statement under the hood. – Tom Feb 21 '17 at 00:08
How can i use db.insert() ? Can you show an example please. – Andrew Feb 21 '17 at 00:13
Added an example to my answer. – Tom Feb 21 '17 at 00:36

score 1 · Answer 2 · answered Feb 21 '17 at 00:36

Instead of statement.execute(), you can do statement.executeInsert(). This returns the row ID of the inserted row. Or, as @Tom suggested, you can use db.insert() instead, and it will also return the inserted row ID. Using a compiled statement like you are doing now is faster though.

If you want to try the db.insert() approach, it would look something like this:

ContentValues values = new ContentValues();
for (Student student: students) {
    // use whatever constants you have for column names instead of these:
    values.put(COLUMN_STUDENT_ID, student.getId());
    values.put(COLUMN_STUDENT_FIRSTNAME, student.getFirstName());
    values.put(COLUMN_STUDENT_LASTNAME, student.getLastName());
    values.put(COLUMN_STUDENT_BIRTHDAY, student.getBirthday());
    db.insert(StudentEntry.TABLE_NAME, null, values);
}

Autoincrement value in transactions body

2 Answers2