public void hapusBukmakByAri(int ari, int jenis) {
SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();
try {
long _id;
SQLiteStatement stmt =
db.compileStatement(
"select _id from "
+ Db.TABEL_Bukmak2
+ " where "
+ Db.Bukmak2.jenis
+ "=? and "
+ Db.Bukmak2.ari
+ "=?"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
try {
stmt.bindLong(1, jenis);
stmt.bindLong(2, ari);
_id = stmt.simpleQueryForLong();
} finally {
stmt.close();
}
String[] params = {String.valueOf(_id)};
db.delete(Db.TABEL_Bukmak2_Label, Db.Bukmak2_Label.bukmak2_id + "=?", params); // $NON-NLS-1$
db.delete(Db.TABEL_Bukmak2, "_id=?", params); // $NON-NLS-1$
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}
private int update(String statement, Object[] args, FieldType[] argFieldTypes, String label)
throws SQLException {
SQLiteStatement stmt = null;
try {
stmt = db.compileStatement(statement);
bindArgs(stmt, args, argFieldTypes);
stmt.execute();
} catch (android.database.SQLException e) {
throw SqlExceptionUtil.create("updating database failed: " + statement, e);
} finally {
closeQuietly(stmt);
stmt = null;
}
int result;
try {
stmt = db.compileStatement("SELECT CHANGES()");
result = (int) stmt.simpleQueryForLong();
} catch (android.database.SQLException e) {
// ignore the exception and just return 1
result = 1;
} finally {
closeQuietly(stmt);
}
logger.trace("{} statement is compiled and executed, changed {}: {}", label, result, statement);
return result;
}
public long isTagPostExists(int post_id) {
SQLiteDatabase database = null;
long count = 0;
try {
String sql =
"SELECT COUNT(*) FROM "
+ DbAdapter.TAG_POSTS_TABLE_NAME
+ " where "
+ DbAdapter.TP_ID
+ "="
+ post_id;
dbHelper = new DbAdapter(context);
database = dbHelper.getReadableDatabase();
SQLiteStatement statement = database.compileStatement(sql);
count = statement.simpleQueryForLong();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
database.close();
} catch (Exception e) {
}
}
return count;
}
/**
* Adds or updates room.
*
* @param account
* @param room
* @param nickname
* @param password
* @param join
*/
void write(String account, String room, String nickname, String password, boolean join) {
synchronized (writeLock) {
if (writeStatement == null) {
SQLiteDatabase db = databaseManager.getWritableDatabase();
writeStatement =
db.compileStatement(
"INSERT OR REPLACE INTO "
+ NAME
+ " ("
+ Fields.ACCOUNT
+ ", "
+ Fields.ROOM
+ ", "
+ Fields.NICKNAME
+ ", "
+ Fields.PASSWORD
+ ", "
+ Fields.NEED_JOIN
+ ") VALUES (?, ?, ?, ?, ?);");
}
writeStatement.bindString(1, account);
writeStatement.bindString(2, room);
writeStatement.bindString(3, nickname);
writeStatement.bindString(4, password);
writeStatement.bindLong(5, join ? 1 : 0);
writeStatement.execute();
}
}
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(
"create table if not exists urlpermission("
+ " id integer primary key autoincrement,"
+ " service_name text,"
+ " url text,"
+ " rule text);");
SQLiteStatement statement =
database.compileStatement(
"insert into urlpermission(service_name, url, rule) values(?,?,?)");
int index = 1;
statement.bindString(index++, "service1");
statement.bindString(index++, "http://www.baidu.com/1");
statement.bindString(index++, "YES");
statement.execute();
index = 1;
statement.bindString(index++, "service2");
statement.bindString(index++, "http://www.baidu.com/2");
statement.bindString(index++, "NO");
statement.execute();
statement.close();
}
/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, ConquestWarriorSpecialties entity) {
stmt.clearBindings();
stmt.bindLong(1, entity.getWarriorId());
stmt.bindLong(2, entity.getTypeId());
stmt.bindLong(3, entity.getSlot());
}
@MediumTest
public void testStatementMultipleBindings() throws Exception {
mDatabase.execSQL("CREATE TABLE test (num INTEGER, str TEXT);");
SQLiteStatement statement =
mDatabase.compileStatement("INSERT INTO test (num, str) VALUES (?, ?)");
for (long i = 0; i < 10; i++) {
statement.bindLong(1, i);
statement.bindString(2, Long.toHexString(i));
statement.execute();
}
statement.close();
Cursor c = mDatabase.query("test", null, null, null, null, null, "ROWID");
int numCol = c.getColumnIndexOrThrow("num");
int strCol = c.getColumnIndexOrThrow("str");
assertTrue(c.moveToFirst());
for (long i = 0; i < 10; i++) {
long num = c.getLong(numCol);
String str = c.getString(strCol);
assertEquals(i, num);
assertEquals(Long.toHexString(i), str);
c.moveToNext();
}
c.close();
}
private void bindStringOrNull(SQLiteStatement statement, int index, String str) {
if (str == null) {
statement.bindNull(index);
} else {
statement.bindString(index, str);
}
}
private void updateTables4() {
final FileInfoSet fileInfos = new FileInfoSet();
final Cursor cursor = myDatabase.rawQuery("SELECT file_name FROM Books", null);
while (cursor.moveToNext()) {
fileInfos.check(ZLFile.createFileByPath(cursor.getString(0)).getPhysicalFile(), false);
}
cursor.close();
fileInfos.save();
myDatabase.execSQL(
"CREATE TABLE RecentBooks("
+ "book_index INTEGER PRIMARY KEY,"
+ "book_id INTEGER REFERENCES Books(book_id))");
final ArrayList<Long> ids = new ArrayList<Long>();
final SQLiteStatement statement =
myDatabase.compileStatement("SELECT book_id FROM Books WHERE file_name = ?");
for (int i = 0; i < 20; ++i) {
final ZLStringOption option = new ZLStringOption("LastOpenedBooks", "Book" + i, "");
final String fileName = option.getValue();
option.setValue("");
try {
statement.bindString(1, fileName);
final long bookId = statement.simpleQueryForLong();
if (bookId != -1) {
ids.add(bookId);
}
} catch (SQLException e) {
}
}
saveRecentBookIds(ids);
}
public long save(int id_sesion, int id_moderador) {
// TODO Auto-generated method stub
insertStatement.clearBindings();
insertStatement.bindString(1, String.valueOf(id_sesion));
insertStatement.bindString(2, String.valueOf(id_moderador));
return insertStatement.executeInsert();
}
/**
* Pulled partially from code, it runs a "PRAGMA quick_check(1)" to see if the database is ok.
* This method will {@link #restoreBackUp()} if they are enabled on the database if this check
* fails. So use with caution and ensure that you backup the database often!
*
* @return true if the database is ok, false if the consistency has been compromised.
*/
public boolean isDatabaseIntegrityOk() {
boolean integrityOk = true;
SQLiteStatement prog = null;
try {
prog = getWritableDatabase().compileStatement("PRAGMA quick_check(1)");
String rslt = prog.simpleQueryForString();
if (!rslt.equalsIgnoreCase("ok")) {
// integrity_checker failed on main or attached databases
FlowLog.log(
FlowLog.Level.E,
"PRAGMA integrity_check on "
+ databaseDefinition.getDatabaseName()
+ " returned: "
+ rslt);
integrityOk = false;
if (databaseDefinition.backupEnabled()) {
integrityOk = restoreBackUp();
}
}
} finally {
if (prog != null) {
prog.close();
}
}
return integrityOk;
}
private void insertStructuredName(Cursor c, SQLiteStatement insert) {
String name = c.getString(PeopleQuery.NAME);
if (TextUtils.isEmpty(name)) {
return;
}
long id = c.getLong(PeopleQuery._ID);
insert.bindLong(StructuredNameInsert.RAW_CONTACT_ID, id);
insert.bindLong(StructuredNameInsert.MIMETYPE_ID, mStructuredNameMimetypeId);
bindString(insert, StructuredNameInsert.DISPLAY_NAME, name);
NameSplitter.Name splitName = new NameSplitter.Name();
mNameSplitter.split(splitName, name);
bindString(insert, StructuredNameInsert.PREFIX, splitName.getPrefix());
bindString(insert, StructuredNameInsert.GIVEN_NAME, splitName.getGivenNames());
bindString(insert, StructuredNameInsert.MIDDLE_NAME, splitName.getMiddleName());
bindString(insert, StructuredNameInsert.FAMILY_NAME, splitName.getFamilyName());
bindString(insert, StructuredNameInsert.SUFFIX, splitName.getSuffix());
if (mPhoneticNameAvailable) {
// TODO: add the ability to insert an unstructured phonetic name
String phoneticName = c.getString(PeopleQuery.PHONETIC_NAME);
}
long dataId = insert(insert);
mContactsProvider.insertNameLookupForStructuredName(id, dataId, name);
}
private void bindString(SQLiteStatement insert, int index, String string) {
if (string == null) {
insert.bindNull(index);
} else {
insert.bindString(index, string);
}
}
/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, ConquestMoveDisplacements entity) {
stmt.clearBindings();
stmt.bindLong(1, entity.getId());
stmt.bindString(2, entity.getIdentifier());
stmt.bindLong(3, entity.getAffectsTarget() ? 1L : 0L);
}
private void bindString(SQLiteStatement stmt, int index, String value) {
if (value == null) {
stmt.bindNull(index);
} else {
stmt.bindString(index, value);
}
}
private void updateTables18() {
myDatabase.execSQL("ALTER TABLE BookSeries RENAME TO BookSeries_Obsolete");
myDatabase.execSQL(
"CREATE TABLE BookSeries("
+ "series_id INTEGER NOT NULL REFERENCES Series(series_id),"
+ "book_id INTEGER NOT NULL UNIQUE REFERENCES Books(book_id),"
+ "book_index TEXT)");
final SQLiteStatement insert =
myDatabase.compileStatement(
"INSERT INTO BookSeries (series_id,book_id,book_index) VALUES (?,?,?)");
final Cursor cursor =
myDatabase.rawQuery("SELECT series_id,book_id,book_index FROM BookSeries_Obsolete", null);
while (cursor.moveToNext()) {
insert.bindLong(1, cursor.getLong(0));
insert.bindLong(2, cursor.getLong(1));
final float index = cursor.getFloat(2);
final String stringIndex;
if (index == 0.0f) {
stringIndex = null;
} else {
if (Math.abs(index - Math.round(index)) < 0.01) {
stringIndex = String.valueOf(Math.round(index));
} else {
stringIndex = String.format("%.1f", index);
}
}
final BigDecimal bdIndex = SeriesInfo.createIndex(stringIndex);
SQLiteUtil.bindString(insert, 3, bdIndex != null ? bdIndex.toString() : null);
insert.executeInsert();
}
cursor.close();
myDatabase.execSQL("DROP TABLE BookSeries_Obsolete");
}
private void bindLong(SQLiteStatement stmt, int index, Number value) {
if (value == null) {
stmt.bindNull(index);
} else {
stmt.bindLong(index, value.longValue());
}
}
protected void saveBookAuthorInfo(long bookId, long index, Author author) {
if (myGetAuthorIdStatement == null) {
myGetAuthorIdStatement =
myDatabase.compileStatement(
"SELECT author_id FROM Authors WHERE name = ? AND sort_key = ?");
myInsertAuthorStatement =
myDatabase.compileStatement("INSERT OR IGNORE INTO Authors (name,sort_key) VALUES (?,?)");
myInsertBookAuthorStatement =
myDatabase.compileStatement(
"INSERT OR REPLACE INTO BookAuthor (book_id,author_id,author_index) VALUES (?,?,?)");
}
long authorId;
try {
myGetAuthorIdStatement.bindString(1, author.DisplayName);
myGetAuthorIdStatement.bindString(2, author.SortKey);
authorId = myGetAuthorIdStatement.simpleQueryForLong();
} catch (SQLException e) {
myInsertAuthorStatement.bindString(1, author.DisplayName);
myInsertAuthorStatement.bindString(2, author.SortKey);
authorId = myInsertAuthorStatement.executeInsert();
}
myInsertBookAuthorStatement.bindLong(1, bookId);
myInsertBookAuthorStatement.bindLong(2, authorId);
myInsertBookAuthorStatement.bindLong(3, index);
myInsertBookAuthorStatement.execute();
}
private static void migrateConversation(
Context context,
MasterSecret masterSecret,
Handler handler,
long theirThreadId,
long ourThreadId) {
SmsDatabase ourSmsDatabase = DatabaseFactory.getSmsDatabase(context);
Cursor cursor = null;
try {
Uri uri = Uri.parse("content://sms/conversations/" + theirThreadId);
cursor = context.getContentResolver().query(uri, null, null, null, null);
SQLiteDatabase transaction = ourSmsDatabase.beginTransaction();
SQLiteStatement statement = ourSmsDatabase.createInsertStatement(transaction);
while (cursor != null && cursor.moveToNext()) {
getContentValuesForRow(context, masterSecret, cursor, ourThreadId, statement);
statement.execute();
Message msg =
handler.obtainMessage(SECONDARY_PROGRESS_UPDATE, 10000 / cursor.getCount(), 0);
handler.sendMessage(msg);
}
ourSmsDatabase.endTransaction(transaction);
DatabaseFactory.getThreadDatabase(context).update(ourThreadId);
DatabaseFactory.getThreadDatabase(context).notifyConversationListeners(ourThreadId);
} finally {
if (cursor != null) cursor.close();
}
}
/** {@inheritDoc} */
@Override
public int count() {
SQLiteStatement stmt = sqlHelper.getCountStatement();
stmt.clearBindings();
stmt.bindLong(1, sessionId);
return (int) stmt.simpleQueryForLong();
}
public boolean deleteAll() {
boolean flag = false;
String deleteHeadSql = "DELETE FROM ProductGroupHead";
String deleteDetailSql = "DELETE FROM ProductGroupDetail";
SQLiteDatabase db = AssetsDatabaseManager.getManager().getDatabase();
try {
// 开启事务
db.beginTransaction();
SQLiteStatement detail_stat = db.compileStatement(deleteDetailSql);
detail_stat.executeUpdateDelete();
SQLiteStatement head_stat = db.compileStatement(deleteHeadSql);
head_stat.executeUpdateDelete();
// 数据插入成功，设置事物成功标志
db.setTransactionSuccessful();
// 保存数据
db.endTransaction();
flag = true;
} catch (SQLException e) {
// 结束事物，在这里没有设置成功标志，结束后不保存
ZillionLog.e(this.getClass().getName(), e.getMessage(), e);
db.endTransaction();
e.printStackTrace();
}
return flag;
}
private void addStation(Integer code, Double lat, Double lon, String label) {
insertStatement.bindLong(1, code);
insertStatement.bindDouble(2, lat);
insertStatement.bindDouble(3, lon);
insertStatement.bindString(4, label);
insertStatement.executeInsert();
}
/** Insert a set of flashcards (linked list) in a single transaction. */
private void insertFlashcardSet(Flashcard head) {
openDB();
SQLiteStatement stmt = getCompiledStatement(SQL_INSERT_FC);
boolean gotError = false;
_curDB.beginTransaction();
try {
for (Flashcard fc = head; fc != null; fc = Flashcard.Chain.getNext(fc)) {
stmt.bindLong(1, _importState.getRandomId());
stmt.bindString(2, fc.getLang1Str());
stmt.bindString(3, fc.getLang2Str());
stmt.execute();
}
_curDB.setTransactionSuccessful();
} catch (SQLException e) {
gotError = true;
MsgDispatcher.sendMessageToUI(MsgType.MSG_SHOW_ERROR_MSG, 0, 0, "unable to insert fc");
} finally {
_curDB.endTransaction();
}
Flashcard.Chain.releaseChain(head);
// tell controller to send us more data if insert went fine.
if (!gotError) {
MsgDispatcher.sendMessageToController(MsgType.MSG_CONTINUE_IMPORT, 0, 0, null);
}
}
private void createVarnaStations(SQLiteDatabase db, String tableName, InputStream openRawResource)
throws JsonParseException, JsonMappingException, IOException {
final ObjectMapper OBJECT_MAPPER = new ObjectMapper();
final BusStopVarnaTraffic[] all =
OBJECT_MAPPER.readValue(openRawResource, BusStopVarnaTraffic[].class);
final String FORMAT_SQL_INSERT =
"INSERT INTO %s (%s, %s, %s, %s, %s) VALUES (?, ?, ?, ?, '%s')";
final SQLiteStatement insertStatement =
db.compileStatement(
String.format(
FORMAT_SQL_INSERT,
tableName,
Station.CODE,
Station.LAT,
Station.LON,
Station.LABEL,
Station.PROVIDER,
FavoritiesService.PROVIDER_VARNATRAFFIC));
for (BusStopVarnaTraffic busStopVarnaTraffic : all) {
insertStatement.bindLong(1, busStopVarnaTraffic.getId());
insertStatement.bindDouble(2, busStopVarnaTraffic.getPosition().getLat());
insertStatement.bindDouble(3, busStopVarnaTraffic.getPosition().getLon());
insertStatement.bindString(4, busStopVarnaTraffic.getText());
insertStatement.executeInsert();
}
}
public int insert(
String statement, Object[] args, FieldType[] argFieldTypes, GeneratedKeyHolder keyHolder)
throws SQLException {
SQLiteStatement stmt = null;
try {
stmt = db.compileStatement(statement);
bindArgs(stmt, args, argFieldTypes);
long rowId = stmt.executeInsert();
if (keyHolder != null) {
keyHolder.addKey(rowId);
}
/*
* I've decided to not do the CHANGES() statement here like we do down below in UPDATE because we know that
* it worked (since it didn't throw) so we know that 1 is right.
*/
int result = 1;
logger.trace(
"{}: insert statement is compiled and executed, changed {}: {}", this, result, statement);
return result;
} catch (android.database.SQLException e) {
throw SqlExceptionUtil.create("inserting to database failed: " + statement, e);
} finally {
closeQuietly(stmt);
}
}
public void insertFiles(File[] files) {
SQLiteDatabase database = dbHelper.getWritableDatabase();
SQLiteStatement statement =
database.compileStatement(
"INSERT INTO "
+ TABLE_NAME
+ " ("
+ COL_FILENAME
+ ", "
+ COL_FILESIZE
+ ", "
+ COL_TIMESTAMP
+ ") VALUES (?, ?, ?)");
database.beginTransaction();
try {
for (File file : files) {
statement.bindString(1, file.getName());
statement.bindLong(2, file.length());
statement.bindLong(3, file.lastModified());
statement.executeInsert();
}
database.setTransactionSuccessful();
} finally {
database.endTransaction();
}
}
/** @inheritdoc */
@Override
protected void bindValues(SQLiteStatement stmt, ConquestWarriorNames entity) {
stmt.clearBindings();
stmt.bindLong(1, entity.getWarriorId());
stmt.bindLong(2, entity.getLocalLanguageId());
stmt.bindString(3, entity.getName());
}
@Override
public void onCreate(SQLiteDatabase db) {
db.beginTransaction();
try {
SQLiteStatement stmt;
db.execSQL("create table capitals (prefecture text primary key, capital text not null);");
stmt = db.compileStatement("insert into capitals values (?, ?);");
for (String[] capital : CAPITALS) {
stmt.bindString(1, capital[0]);
stmt.bindString(2, capital[1]);
stmt.executeInsert();
}
db.execSQL(
"create table local_dishes (prefecture text not null, local_dish text not null);");
stmt = db.compileStatement("insert into local_dishes values (?, ?);");
for (String[] localDish : LOCAL_DISHES) {
stmt.bindString(1, localDish[0]);
stmt.bindString(2, localDish[1]);
stmt.executeInsert();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}
// @Large
@Suppress
public void testLoadingThreadDelayRegisterData() throws Exception {
mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data INT);");
final int count = 505;
String sql = "INSERT INTO test (data) VALUES (?);";
SQLiteStatement s = mDatabase.compileStatement(sql);
for (int i = 0; i < count; i++) {
s.bindLong(1, i);
s.execute();
}
int maxRead = 500;
int initialRead = 5;
SQLiteCursor c =
(SQLiteCursor) mDatabase.rawQuery("select * from test;", null, initialRead, maxRead);
TestObserver observer = new TestObserver(count, c);
c.getCount();
c.registerDataSetObserver(observer);
if (!observer.quit) {
Looper.loop();
}
c.close();
}
/**
* Called by ManageLocations screen to add a location to the database
*
* @param locationName
* @param coordinates
* @param description
* @return
*/
public boolean addLocationData(String locationName, String coordinates, String description) {
boolean success = false;
SQLiteDatabase db = getReadableDatabase();
SQLiteStatement sqlStatement =
db.compileStatement(
"INSERT INTO locations (locationName, coordinates, description) VALUES (?, ?, ?)");
sqlStatement.bindString(1, locationName);
sqlStatement.bindString(2, coordinates);
sqlStatement.bindString(3, description);
db.beginTransaction();
try {
sqlStatement.execute();
db.setTransactionSuccessful();
success = true;
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
db.endTransaction();
sqlStatement.close();
db.close();
}
return success;
}
