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();
}
@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();
}
}
protected void saveBookSeriesInfo(long bookId, SeriesInfo seriesInfo) {
if (myGetSeriesIdStatement == null) {
myGetSeriesIdStatement =
myDatabase.compileStatement("SELECT series_id FROM Series WHERE name = ?");
myInsertSeriesStatement =
myDatabase.compileStatement("INSERT OR IGNORE INTO Series (name) VALUES (?)");
myInsertBookSeriesStatement =
myDatabase.compileStatement(
"INSERT OR REPLACE INTO BookSeries (book_id,series_id,book_index) VALUES (?,?,?)");
myDeleteBookSeriesStatement =
myDatabase.compileStatement("DELETE FROM BookSeries WHERE book_id = ?");
}
if (seriesInfo == null) {
myDeleteBookSeriesStatement.bindLong(1, bookId);
myDeleteBookSeriesStatement.execute();
} else {
long seriesId;
try {
myGetSeriesIdStatement.bindString(1, seriesInfo.Name);
seriesId = myGetSeriesIdStatement.simpleQueryForLong();
} catch (SQLException e) {
myInsertSeriesStatement.bindString(1, seriesInfo.Name);
seriesId = myInsertSeriesStatement.executeInsert();
}
myInsertBookSeriesStatement.bindLong(1, bookId);
myInsertBookSeriesStatement.bindLong(2, seriesId);
SQLiteUtil.bindString(
myInsertBookSeriesStatement,
3,
seriesInfo.Index != null ? seriesInfo.Index.toString() : null);
myInsertBookSeriesStatement.execute();
}
}
@TargetApi(Build.VERSION_CODES.JELLY_BEAN)
public TileCache(Context context, String cacheDirectory, String dbName) {
if (dbg) log.debug("open cache {}, {}", cacheDirectory, dbName);
dbHelper = new SQLiteHelper(context, dbName);
if (Build.VERSION.SDK_INT >= android.os.Build.VERSION_CODES.JELLY_BEAN)
dbHelper.setWriteAheadLoggingEnabled(true);
mDatabase = dbHelper.getWritableDatabase();
mStmtGetTile =
mDatabase.compileStatement(
"" + "SELECT " + COLUMN_DATA + " FROM " + TABLE_NAME + " WHERE x=? AND y=? AND z = ?");
mStmtPutTile =
mDatabase.compileStatement(
""
+ "INSERT INTO "
+ TABLE_NAME
+ " (x, y, z, time, last_access, data)"
+ " VALUES(?,?,?,?,?,?)");
// mStmtUpdateTile = mDatabase.compileStatement("" +
// "UPDATE " + TABLE_NAME +
// " SET last_access=?" +
// " WHERE x=? AND y=? AND z=?");
mCacheBuffers = new ArrayList<ByteArrayOutputStream>();
}
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 importContactMethods() {
SQLiteStatement emailInsert = mTargetDb.compileStatement(EmailInsert.INSERT_SQL);
SQLiteStatement imInsert = mTargetDb.compileStatement(ImInsert.INSERT_SQL);
SQLiteStatement postalInsert = mTargetDb.compileStatement(PostalInsert.INSERT_SQL);
Cursor c =
mSourceDb.query(
ContactMethodsQuery.TABLE, ContactMethodsQuery.COLUMNS, null, null, null, null, null);
try {
while (c.moveToNext()) {
int kind = c.getInt(ContactMethodsQuery.KIND);
switch (kind) {
case android.provider.Contacts.KIND_EMAIL:
insertEmail(c, emailInsert);
break;
case android.provider.Contacts.KIND_IM:
insertIm(c, imInsert);
break;
case android.provider.Contacts.KIND_POSTAL:
insertPostal(c, postalInsert);
break;
}
}
} finally {
c.close();
}
}
private long getTagId(Tag tag) {
if (myGetTagIdStatement == null) {
myGetTagIdStatement =
myDatabase.compileStatement("SELECT tag_id FROM Tags WHERE parent_id = ? AND name = ?");
myCreateTagIdStatement =
myDatabase.compileStatement("INSERT OR IGNORE INTO Tags (parent_id,name) VALUES (?,?)");
}
{
final Long id = myIdByTag.get(tag);
if (id != null) {
return id;
}
}
if (tag.Parent != null) {
myGetTagIdStatement.bindLong(1, getTagId(tag.Parent));
} else {
myGetTagIdStatement.bindNull(1);
}
myGetTagIdStatement.bindString(2, tag.Name);
long id;
try {
id = myGetTagIdStatement.simpleQueryForLong();
} catch (SQLException e) {
if (tag.Parent != null) {
myCreateTagIdStatement.bindLong(1, getTagId(tag.Parent));
} else {
myCreateTagIdStatement.bindNull(1);
}
myCreateTagIdStatement.bindString(2, tag.Name);
id = myCreateTagIdStatement.executeInsert();
}
myIdByTag.put(tag, id);
myTagById.put(id, tag);
return id;
}
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;
}
// opens the database
public POIDataHelper open() {
db = helper.getWritableDatabase();
this.insertMapStmt = db.compileStatement(INSERT_MAP);
this.insertGroupStmt = db.compileStatement(INSERT_GROUP);
this.insertPOIStmt = db.compileStatement(INSERT_POI);
this.updatePOIStmt = db.compileStatement(UPDATE_POI);
this.updatePOISelectedStmt = db.compileStatement(UPDATE_POI_SELECTED);
return this;
}
/**
* 批量增加产品组合主表记录数据
*
* @param list
*/
public boolean batchAddProductGroupHead(List<ProductGroupHeadData> list) {
boolean flag = false;
String headSql =
"insert into ProductGroupHead(PG1_ID,PG1_M02_ID,PG1_CU1_ID,PG1_CODE,PG1_Name,PG1_CreateUser,PG1_CreateTime,PG1_ModifyUser,PG1_ModifyTime,PG1_RowVersion)"
+ "values(?,?,?,?,?,?,?,?,?,?)";
String detailSql =
"insert into ProductGroupDetail(PG2_ID,PG2_M02_ID,PG2_PG1_ID,PG2_PD1_ID,PG2_GroupQty,PG2_CreateUser,PG2_CreateTime,PG2_ModifyUser,PG2_ModifyTime,PG2_RowVersion)"
+ "values(?,?,?,?,?,?,?,?,?,?)";
SQLiteDatabase db = AssetsDatabaseManager.getManager().getDatabase();
try {
// 开启事务
db.beginTransaction();
for (ProductGroupHeadData productGroupHead : list) {
SQLiteStatement stat = db.compileStatement(headSql);
stat.bindString(1, productGroupHead.getPg1Id());
stat.bindString(2, productGroupHead.getPg1M02Id());
stat.bindString(3, productGroupHead.getPg1Cu1Id());
stat.bindString(4, productGroupHead.getPg1Code());
stat.bindString(5, productGroupHead.getPg1Name());
stat.bindString(6, productGroupHead.getPg1CreateUser());
stat.bindString(7, productGroupHead.getPg1CreateTime());
stat.bindString(8, productGroupHead.getPg1ModifyUser());
stat.bindString(9, productGroupHead.getPg1ModifyTime());
stat.bindString(10, productGroupHead.getPg1RowVersion());
stat.executeInsert();
List<ProductGroupDetailData> children = productGroupHead.getChildren();
if (children != null) {
for (ProductGroupDetailData productGroupDetail : children) {
SQLiteStatement detail_stat = db.compileStatement(detailSql);
detail_stat.bindString(1, productGroupDetail.getPg2Id());
detail_stat.bindString(2, productGroupDetail.getPg2M02Id());
detail_stat.bindString(3, productGroupDetail.getPg2Pg1Id());
detail_stat.bindString(4, productGroupDetail.getPg2Pd1Id());
detail_stat.bindLong(5, productGroupDetail.getPg2GroupQty());
detail_stat.bindString(6, productGroupDetail.getPg2CreateUser());
detail_stat.bindString(7, productGroupDetail.getPg2CreateTime());
detail_stat.bindString(8, productGroupDetail.getPg2ModifyUser());
detail_stat.bindString(9, productGroupDetail.getPg2ModifyTime());
detail_stat.bindString(10, productGroupDetail.getPg2RowVersion());
detail_stat.executeInsert();
}
}
}
// 数据插入成功，设置事物成功标志
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 importPhotos() {
SQLiteStatement insert = mTargetDb.compileStatement(PhotoInsert.INSERT_SQL);
SQLiteStatement photoIdUpdate = mTargetDb.compileStatement(PhotoIdUpdate.UPDATE_SQL);
Cursor c =
mSourceDb.query(PhotosQuery.TABLE, PhotosQuery.COLUMNS, null, null, null, null, null);
try {
while (c.moveToNext()) {
insertPhoto(c, insert, photoIdUpdate);
}
} finally {
c.close();
}
}
private void importPhones() {
SQLiteStatement phoneInsert = mTargetDb.compileStatement(PhoneInsert.INSERT_SQL);
SQLiteStatement phoneLookupInsert = mTargetDb.compileStatement(PhoneLookupInsert.INSERT_SQL);
SQLiteStatement hasPhoneUpdate = mTargetDb.compileStatement(HasPhoneNumberUpdate.UPDATE_SQL);
Cursor c =
mSourceDb.query(PhonesQuery.TABLE, PhonesQuery.COLUMNS, null, null, null, null, null);
try {
while (c.moveToNext()) {
insertPhone(c, phoneInsert, phoneLookupInsert, hasPhoneUpdate);
}
} finally {
c.close();
}
}
@Override
public void changeStoredSamplesType(
int timestampFrom, int timestampTo, byte kind, SampleProvider provider) {
try (SQLiteDatabase db = this.getReadableDatabase()) {
String sql =
"UPDATE "
+ TABLE_GBACTIVITYSAMPLES
+ " SET "
+ KEY_TYPE
+ "= ? WHERE "
+ KEY_PROVIDER
+ " = ? AND "
+ KEY_TIMESTAMP
+ " >= ? AND "
+ KEY_TIMESTAMP
+ " < ? ;"; // do not use BETWEEN because the range is inclusive in that case!
SQLiteStatement statement = db.compileStatement(sql);
statement.bindLong(1, kind);
statement.bindLong(2, provider.getID());
statement.bindLong(3, timestampFrom);
statement.bindLong(4, timestampTo);
statement.execute();
}
}
@Override
public void addGBActivitySamples(ActivitySample[] activitySamples) {
try (SQLiteDatabase db = this.getWritableDatabase()) {
String sql =
"INSERT INTO "
+ TABLE_GBACTIVITYSAMPLES
+ " ("
+ KEY_TIMESTAMP
+ ","
+ KEY_PROVIDER
+ ","
+ KEY_INTENSITY
+ ","
+ KEY_STEPS
+ ","
+ KEY_TYPE
+ ")"
+ " VALUES (?,?,?,?,?);";
SQLiteStatement statement = db.compileStatement(sql);
db.beginTransaction();
for (ActivitySample activitySample : activitySamples) {
statement.clearBindings();
statement.bindLong(1, activitySample.getTimestamp());
statement.bindLong(2, activitySample.getProvider().getID());
statement.bindLong(3, activitySample.getRawIntensity());
statement.bindLong(4, activitySample.getSteps());
statement.bindLong(5, activitySample.getRawKind());
statement.execute();
}
db.setTransactionSuccessful();
db.endTransaction();
}
}
@MediumTest
public void testSimpleQuery() throws Exception {
mDatabase.execSQL("CREATE TABLE test (num INTEGER NOT NULL, str TEXT NOT NULL);");
mDatabase.execSQL("INSERT INTO test VALUES (1234, 'hello');");
SQLiteStatement statement1 = mDatabase.compileStatement("SELECT num FROM test WHERE str = ?");
SQLiteStatement statement2 = mDatabase.compileStatement("SELECT str FROM test WHERE num = ?");
try {
statement1.bindString(1, "hello");
long value = statement1.simpleQueryForLong();
assertEquals(1234, value);
statement1.bindString(1, "world");
statement1.simpleQueryForLong();
fail("shouldn't get here");
} catch (SQLiteDoneException e) {
// expected
}
try {
statement2.bindLong(1, 1234);
String value = statement1.simpleQueryForString();
assertEquals("hello", value);
statement2.bindLong(1, 5678);
statement1.simpleQueryForString();
fail("shouldn't get here");
} catch (SQLiteDoneException e) {
// expected
}
statement1.close();
statement2.close();
}
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);
}
}
private void importCities(InputStream in) {
CityJsonParser parser = createParser();
insertStatement =
db.compileStatement(
"INSERT INTO "
+ CityContract.Cities.TABLE
+ " ("
+ CityContract.Cities.CITY_ID
+ ", "
+ CityContract.Cities.NAME
+ ", "
+ CityContract.Cities.COUNTRY
+ ", "
+ CityContract.Cities.LATITUDE
+ ", "
+ CityContract.Cities.LONGITUDE
+ ") VALUES"
+ "(?, ?, ?, ?, ?)");
db.beginTransaction();
try {
parser.parseCities(in, this);
} catch (Exception e) {
Log.e(LOG_TAG, "Failed to parse cities: " + e, e);
} finally {
db.endTransaction();
insertStatement.close();
}
}
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;
}
/*package*/ SQLiteStatement createInsertStatement(SQLiteDatabase database) {
return database.compileStatement(
"INSERT INTO "
+ TABLE_NAME
+ " ("
+ ADDRESS
+ ", "
+ PERSON
+ ", "
+ DATE_SENT
+ ", "
+ DATE_RECEIVED
+ ", "
+ PROTOCOL
+ ", "
+ READ
+ ", "
+ STATUS
+ ", "
+ TYPE
+ ", "
+ REPLY_PATH_PRESENT
+ ", "
+ SUBJECT
+ ", "
+ BODY
+ ", "
+ SERVICE_CENTER
+ ", "
+ THREAD_ID
+ ") "
+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
}
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 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();
}
}
/**
* 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();
}
}
@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();
}
@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();
}
@MediumTest
public void testStatementConstraint() throws Exception {
mDatabase.execSQL("CREATE TABLE test (num INTEGER NOT NULL);");
SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)");
// Try to insert NULL, which violates the constraint
try {
statement.clearBindings();
statement.execute();
fail("expected exception not thrown");
} catch (SQLiteConstraintException e) {
// expected
}
// Make sure the statement can still be used
statement.bindLong(1, 1);
statement.execute();
statement.close();
Cursor c = mDatabase.query("test", null, null, null, null, null, null);
int numCol = c.getColumnIndexOrThrow("num");
c.moveToFirst();
long num = c.getLong(numCol);
assertEquals(1, num);
c.close();
}
public void addTerms(ArrayList<Terms> termList, User usr) {
System.out.println("Terms StartUp : " + System.currentTimeMillis());
if (!isTermsAvailableForCompany(usr.getCompanyId())) {
deleteTerms();
SQLiteDatabase db = this.getWritableDatabase();
try {
db.beginTransaction();
SQLiteStatement statement = db.compileStatement(this.TERMS_QUERY);
for (Terms terms : termList) {
statement.bindString(1, terms.description);
statement.bindLong(2, terms.languageId);
statement.bindString(3, terms.term);
statement.bindString(4, usr.getCompanyId());
statement.execute();
statement.clearBindings();
}
db.setTransactionSuccessful();
} catch (Exception e) {
// TODO: handle exception
} finally {
db.endTransaction();
db.close();
}
}
System.out.println("Terms End : " + System.currentTimeMillis());
}
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");
}
// @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();
}
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();
}
}