How to use

SQLiteStatement

in

android.database.sqlite

 String stringValue = "hello";
try {

  db.beginTransaction();
  String sql = "INSERT INTO table_name (column_1, column_2) VALUES (?, ?)";
  SQLiteStatement statement = db.compileStatement(sql);

  for (int i = 0; i < 1000; i++) {
    statement.clearBindings();
    statement.bindLong(1, i);
    statement.bindString(2, stringValue + i);
    statement.executeInsert();
  }

  db.setTransactionSuccessful(); // This commits the transaction if there were no exceptions

} catch (Exception e) {
  Log.w("Exception:", e);
} finally {
  db.endTransaction();
}

@Override
public void close() {
  delegate.close();
}

final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
 final SQLiteStatement statement = db.compileStatement(INSERT_QUERY);
 db.beginTransaction();
 try {
   for(MyBean bean : list){
     statement.clearBindings();
     statement.bindString(1, bean.getName());
     // rest of bindings
     statement.execute(); //or executeInsert() if id is needed
   }
   db.setTransactionSuccessful();
 } finally {
   db.endTransaction();
 }

 String sql = "INSERT INTO table_name (column_1, column_2) VALUES (?, ?)";
SQLiteStatement statement = db.compileStatement(sql);

int intValue = 57;
String stringValue = "hello";

statement.bindLong(1, intValue); // These match to the two question marks in the sql string
statement.bindString(2, stringValue); 

long rowId = statement.executeInsert();

 String sql = "UPDATE table_name SET column_2=? WHERE column_1=?";
SQLiteStatement statement = db.compileStatement(sql);

int id = 7;
String stringValue = "hi there";

statement.bindString(1, stringValue);
statement.bindLong(2, id);

int numberOfRowsAffected = statement.executeUpdateDelete();

private void executeInsert(Element.Type type, long id, ElementGeometry geometry)
{
  insert.bindString(1, type.name());
  insert.bindLong(2, id);
  if (geometry.polygons != null)
    insert.bindBlob(3, serializer.toBytes(geometry.polygons));
  else
    insert.bindNull(3);
  if (geometry.polylines != null)
    insert.bindBlob(4, serializer.toBytes(geometry.polylines));
  else
    insert.bindNull(4);
  insert.bindDouble(5, geometry.center.getLatitude());
  insert.bindDouble(6, geometry.center.getLongitude());
  insert.executeInsert();
  insert.clearBindings();
}

private boolean insertWithTags(JobHolder jobHolder) {
  final SQLiteStatement stmt = sqlHelper.getInsertStatement();
  final SQLiteStatement tagsStmt = sqlHelper.getInsertTagsStatement();
  db.beginTransaction();
  try {
    stmt.clearBindings();
    bindValues(stmt, jobHolder);
    boolean insertResult = stmt.executeInsert() != -1;
    if (!insertResult) {
      return false;
    }
    for (String tag : jobHolder.getTags()) {
      tagsStmt.clearBindings();
      bindTag(tagsStmt, jobHolder.getId(), tag);
      tagsStmt.executeInsert();
    }
    db.setTransactionSuccessful();
    return true;
  } catch (Throwable t) {
    JqLog.e(t, "error while inserting job with tags");
    return false;
  }
  finally {
    db.endTransaction();
  }
}

 public void insertUser(){
  SQLiteDatabase db               =   dbHelper.getWritableDatabase();

  String delSql                       =   "DELETE FROM ACCOUNTS";
  SQLiteStatement delStmt         =   db.compileStatement(delSql);
  delStmt.execute();

  String sql                      =   "INSERT INTO ACCOUNTS (account_id,account_name,account_image) VALUES(?,?,?)";
  SQLiteStatement insertStmt      =   db.compileStatement(sql);
  insertStmt.clearBindings();
  insertStmt.bindString(1, Integer.toString(this.accId));
  insertStmt.bindString(2,this.accName);
  insertStmt.bindBlob(3, this.accImage);
  insertStmt.executeInsert();
  db.close();
}

 @Test
 public void testCloseShouldCloseUnderlyingPreparedStatement() throws Exception {
  SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name`) VALUES (?)");
  insertStatement.bindString(1, "Hand Press");
  insertStatement.close();
  try {
   insertStatement.executeInsert();
   fail();
  } catch (Exception e) {
   assertThat(e).isInstanceOf(IllegalStateException.class);
  }
 }
}

@Test
public void testExecuteInsert() throws Exception {
 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)");
 insertStatement.bindString(1, "Leg Press");
 insertStatement.bindLong(2, 0);
 long pkeyOne = insertStatement.executeInsert();
 insertStatement.clearBindings();
 insertStatement.bindString(1, "Bench Press");
 insertStatement.bindLong(2, 1);
 long pkeyTwo = insertStatement.executeInsert();
 assertThat(pkeyOne).isEqualTo(1L);
 assertThat(pkeyTwo).isEqualTo(2L);
 Cursor dataCursor = database.rawQuery("SELECT COUNT(*) FROM `routine`", null);
 assertThat(dataCursor.moveToFirst()).isTrue();
 assertThat(dataCursor.getInt(0)).isEqualTo(2);
 dataCursor.close();
 dataCursor = database.rawQuery("SELECT `id`, `name` ,`lastUsed` FROM `routine`", null);
 assertThat(dataCursor.moveToNext()).isTrue();
 assertThat(dataCursor.getInt(0)).isEqualTo(1);
 assertThat(dataCursor.getString(1)).isEqualTo("Leg Press");
 assertThat(dataCursor.getInt(2)).isEqualTo(0);
 assertThat(dataCursor.moveToNext()).isTrue();
 assertThat(dataCursor.getLong(0)).isEqualTo(2L);
 assertThat(dataCursor.getString(1)).isEqualTo("Bench Press");
 assertThat(dataCursor.getInt(2)).isEqualTo(1);
 dataCursor.close();
}

private void updateStorageParams() {
 SQLiteStatement updateInfoStatement = null;
 try {
  updateInfoStatement = database.compileStatement(
      PersistentLogStorageConstants.KAA_UPDATE_STORAGE_INFO);
  updateInfoStatement.bindString(1, PersistentLogStorageConstants.STORAGE_BUCKET_SIZE);
  updateInfoStatement.bindLong(2, maxBucketSize);
  updateInfoStatement.execute();
  updateInfoStatement.bindString(1, PersistentLogStorageConstants.STORAGE_RECORD_COUNT);
  updateInfoStatement.bindLong(2, maxRecordCount);
  updateInfoStatement.execute();
 } catch (SQLiteException ex) {
  Log.e(TAG, "Can't prepare update storage info statement", ex);
  throw new RuntimeException("Can't prepare update storage info statement");
 } finally {
  tryCloseStatement(updateInfoStatement);
 }
}

@Test
public void testExecuteUpdateDelete() throws Exception {
 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name`) VALUES (?)");
 insertStatement.bindString(1, "Hand Press");
 long pkeyOne = insertStatement.executeInsert();
 assertThat(pkeyOne).isEqualTo(1);
 SQLiteStatement updateStatement = database.compileStatement("UPDATE `routine` SET `name`=? WHERE `id`=?");
 updateStatement.bindString(1, "Head Press");
 updateStatement.bindLong(2, pkeyOne);
 assertThat(updateStatement.executeUpdateDelete()).isEqualTo(1);
 Cursor dataCursor = database.rawQuery("SELECT `name` FROM `routine`", null);
 assertThat(dataCursor.moveToNext()).isTrue();
 assertThat(dataCursor.getString(0)).isEqualTo("Head Press");
}

@Test
public void testExecuteInsertShouldCloseGeneratedKeysResultSet() throws Exception {
 // NOTE:
 // As a side-effect we will get "database locked" exception
 // on rollback if generatedKeys wasn't closed
 //
 // Don't know how suitable to use Mockito here, but
 // it will be a little bit simpler to test ShadowSQLiteStatement
 // if actualDBStatement will be mocked
 database.beginTransaction();
 try {
  SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` " +
    "(`name` ,`lastUsed`) VALUES ('test',0)");
  try {
   insertStatement.executeInsert();
  } finally {
   insertStatement.close();
  }
 } finally {
  database.endTransaction();
 }
}

@Override
protected final void bindValues(SQLiteStatement stmt, ExtendsImplementsEntity entity) {
  stmt.clearBindings();
  Long id = entity.getId();
  if (id != null) {
    stmt.bindLong(1, id);
  }
  String text = entity.getText();
  if (text != null) {
    stmt.bindString(2, text);
  }
}

@Override
protected final void bindValues(SQLiteStatement stmt, AutoincrementEntity entity) {
  stmt.clearBindings();
  Long id = entity.getId();
  if (id != null) {
    stmt.bindLong(1, id);
  }
}

@Override
public void ensureSqlCompiles(String sql) {
  if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {
    SQLiteStatement statement = null;
    try {
      statement = db.compileStatement(sql);
    } finally {
      if (statement != null) {
        statement.close();
      }
    }
  } else {
    Cursor c = db.rawQuery(sql, null);
    if (c != null) {
      c.close();
    }
  }
}

@Test
public void simpleQueryTest() throws Exception {
 SQLiteStatement stmt = database.compileStatement("SELECT count(*) FROM `countme`");
 assertThat(stmt.simpleQueryForLong()).isEqualTo(0L);
 assertThat(stmt.simpleQueryForString()).isEqualTo("0");
 SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `countme` (`name` ,`lastUsed` ) VALUES (?,?)");
 insertStatement.bindString(1, "Leg Press");
 insertStatement.bindLong(2, 0);
 insertStatement.executeInsert();
 assertThat(stmt.simpleQueryForLong()).isEqualTo(1L);
 assertThat(stmt.simpleQueryForString()).isEqualTo("1");
 insertStatement.bindString(1, "Bench Press");
 insertStatement.bindLong(2, 1);
 insertStatement.executeInsert();
 assertThat(stmt.simpleQueryForLong()).isEqualTo(2L);
 assertThat(stmt.simpleQueryForString()).isEqualTo("2");
}

private void bindArgsToStatement(SQLiteStatement myStatement, ReadableArray sqlArgs) {
  for (int i = 0; i < sqlArgs.size(); i++) {
    ReadableType type = sqlArgs.getType(i);
    if (type == ReadableType.Number){
      double tmp = sqlArgs.getDouble(i);
      if (tmp == (long) tmp) {
        myStatement.bindLong(i + 1, (long) tmp);
      } else {
        myStatement.bindDouble(i + 1, tmp);
      }
    } else if (sqlArgs.isNull(i)) {
      myStatement.bindNull(i + 1);
    } else {
      myStatement.bindString(i + 1, SQLitePluginConverter.getString(sqlArgs,i,""));
    }
  }
}

@Override protected long executeInsert(Quest quest, boolean replace)
{
  String orWhat = replace ? "REPLACE" : "IGNORE";
  SQLiteDatabase db = dbHelper.getWritableDatabase();
  SQLiteStatement insert = db.compileStatement(
      "INSERT OR "+orWhat+" INTO " + TABLE_NAME +
          "("+ID_COL+","+QS_COL+","+LAT_COL+","+LON_COL+","+LAST_UPDATE_COL+
          ") VALUES (?,?,?,?,?)");
  insert.bindLong(1, quest.getId());
  insert.bindString(2, quest.getStatus().name());
  insert.bindDouble(3, quest.getCenter().getLatitude());
  insert.bindDouble(4, quest.getCenter().getLongitude());
  insert.bindDouble(5, quest.getLastUpdate().getTime());
  return insert.executeInsert();
}

/**
 * This method is called when a job is pulled to run.
 * It is properly marked so that it won't be returned from next job queries.
 * <p/>
 * Same mechanism is also used for cancelled jobs.
 *
 * @param jobHolder The job holder to update session id
 */
private void setSessionIdOnJob(JobHolder jobHolder) {
  SQLiteStatement stmt = sqlHelper.getOnJobFetchedForRunningStatement();
  jobHolder.setRunCount(jobHolder.getRunCount() + 1);
  jobHolder.setRunningSessionId(sessionId);
  stmt.clearBindings();
  stmt.bindLong(1, jobHolder.getRunCount());
  stmt.bindLong(2, sessionId);
  stmt.bindString(3, jobHolder.getId());
  stmt.execute();
}