Java Code Examples for android.database.sqlite.SQLiteDatabase#close()

/**
 * Validate the database header and integrity.  Throw an error when not valid.
 *
 * @param sqliteDatabase    database
 * @param validateHeader    validate the header
 * @param validateIntegrity validate the integrity
 * @param close             close the database after validation
 * @param closeOnError      close the database if validation fails
 */
private void validateDatabase(SQLiteDatabase sqliteDatabase, boolean validateHeader, boolean validateIntegrity, boolean close, boolean closeOnError) {
    try {
        if (validateHeader) {
            validateDatabaseHeader(sqliteDatabase);
        }
        if (validateIntegrity) {
            validateDatabaseIntegrity(sqliteDatabase);
        }
    } catch (Exception e) {
        if (closeOnError) {
            sqliteDatabase.close();
        }
        throw e;
    }

    if (close) {
        sqliteDatabase.close();
    }
}
 

public UserHasSigned findUserByFaceToken(final FaceSignIn faceSignIn, final Handler myHandler) {
	String facetoken = faceSignIn.getFace_token();
	SQLiteDatabase db = databaseHelper.getReadableDatabase();
	String[] arge = {facetoken, facetoken, facetoken, facetoken, facetoken};
	Cursor cursor = db.rawQuery(Sql_findUserByFaceToken, arge);
	final UserHasSigned userHasSigned = new UserHasSigned(context);
	while (cursor.moveToNext()) {
		userHasSigned.setUser_name(cursor.getString(cursor.getColumnIndex("user_name")));
		userHasSigned.setObjectId(cursor.getString(cursor.getColumnIndex("object_id")));
	}
	cursor.close();
	db.close();
	if (userHasSigned.getUser_name() == null) {
		BmobDataHelper bmobDataHelper = new BmobDataHelper(context, myHandler);
		bmobDataHelper.findUserByFaceToken(faceSignIn);
	}
	Log.i(TAG, "findUserByFaceToken: " + userHasSigned.getUser_name());
	return userHasSigned;
}
 

public void truncateMemberTable(String memberTable) {
    try {
        String truncateTable = "DELETE FROM " + memberTable;
        SQLiteDatabase sql = this.getWritableDatabase();
        sql.execSQL(truncateTable);
        String vacuum = "VACUUM";
        sql = this.getWritableDatabase();
        sql.execSQL(vacuum);
        sql.close();
        ;
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
        //Log.e("GroupDBErro", "TruncateTable "+e.getMessage());
    }
}
 

public ArrayList<PageModel> getWatchedNovel() {
	ArrayList<PageModel> watchedNovel = null;
	synchronized (dbh) {
		SQLiteDatabase db = dbh.getReadableDatabase();
		try {
			// watchedNovel = dbh.selectAllByColumn(db,
			// DBHelper.COLUMN_IS_WATCHED + " = ? and ("
			// + DBHelper.COLUMN_PARENT + " = ? or "
			// + DBHelper.COLUMN_PARENT + " = ? )"
			// , new String[] { "1", "Main_Page", "Category:Teasers" }
			// , DBHelper.COLUMN_TITLE );
			watchedNovel = dbh.getAllWatchedNovel(db, true);
		} finally {
			db.close();
		}
	}
	return watchedNovel;
}
 

/**
 * @return Record
 */
public int addRecord() {
    SQLiteDatabase database = storage.getWritableDatabase();
    try {
        ContentValues contentValues = new ContentValues();
        contentValues.put(Storage.Record.COLUMN_UTC, System.currentTimeMillis());
        contentValues.put(Storage.Record.COLUMN_UPLOAD_ID, "");
        try {
            return (int) database.insertOrThrow(Storage.Record.TABLE_NAME, null, contentValues);
        } catch (SQLException sqlException) {
            LOGGER.error(sqlException.getLocalizedMessage(), sqlException);
            return -1;
        }
    } finally {
        database.close();
    }
}
 

public ArrayList<Quran> getEnglishQuranBySurah(long surah_id) {
  ArrayList<Quran> quranArrayList = new ArrayList<>();
  SQLiteDatabase db = databaseHelper.getReadableDatabase();
  quranCursor =
      db.rawQuery(
          "SELECT quran.arabic,quran.english from quran WHERE quran.surah_id = " + surah_id,
          null);
  quranCursor.moveToFirst();

  while (!quranCursor.isAfterLast()) {
    Quran quran = new Quran();
    quran.setQuranArabic(quranCursor.getString(quranCursor.getColumnIndex(QURAN_ARABIC)));
    quran.setQuranTranslate(quranCursor.getString(quranCursor.getColumnIndex(QURAN_ENGLSIH)));
    quranArrayList.add(quran);
    quranCursor.moveToNext();
  }

  quranCursor.close();
  db.close();
  return quranArrayList;
}
 

/**
 * ɾ�����ݿ�������
 */
public void delete(View v) {
	// 1. ���ڴ��д���һ�����ݿ������Ķ���
	MyDbOpenHelper helper = new MyDbOpenHelper(this);
	// 2. ���ֻ����������ݿ��ļ�
	SQLiteDatabase db = helper.getWritableDatabase();
	// db.execSQL("delete from stu");
	/*
	 * table ������ whereClause��where���� whereArgs����ѯ����
	 */
	int res = db.delete("stu", null, null);
	if (res > 0) {
		Toast.makeText(this, "succ:" + res, Toast.LENGTH_SHORT).show();
	} else {
		Toast.makeText(this, "err", Toast.LENGTH_SHORT).show();
	}

	// ��Ҫ���ͷ���Դ
	db.close();
}
 

public static ArrayList<String> getHistory(Context context) {
    ArrayList<String> history = new ArrayList<String>();
    SQLiteDatabase database = new HistoryDatabase(context).getWritableDatabase();

    Cursor cursor = database.query(SELECTED_HISTORY_TABLE, SELECTED_HISTORY_COLUMNS, null, null, null, null, "last_selected DESC");
    int pathColumnIndex = cursor.getColumnIndex("path");
    ArrayList<String> removeIndexes = new ArrayList<String>();
    if (cursor.moveToFirst()) {
        int count = 0;
        do {
            if (count < 20) {
                count++;
                history.add(cursor.getString(pathColumnIndex));
            } else {
                removeIndexes.add(cursor.getString(pathColumnIndex));
            }
        } while (cursor.moveToNext());
    }
    cursor.close();
    String[] removeIndexesArray = new String[removeIndexes.size()];
    database.delete(SELECTED_HISTORY_TABLE, "path = ?", removeIndexes.toArray(removeIndexesArray));
    database.close();
    return history;
}
 

public void setTablePackageStorage(String packageS){
    clearPackageTable();
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_PACKAGE, packageS);
    db.insert(TABLE_PACKAGE_STORAGE, null, contentValues);
    db.close();
}
 

public ArrayList<Book> getBooks() {
	ArrayList<Book> result = null;
	SQLiteDatabase db = null;
	Cursor cursor = null;

	try {
		db = openHelper.getWritableDatabase();
		
		cursor = db.query(TBL_BOOKS, new String[] { "*" }, null, null,
				null, null, null);
		
		// Second Way:
		// String sql = "select * from " + TBL_BOOKS;
		// cursor = db.rawQuery(sql, null);
		
		if (cursor.moveToFirst()) {
			result = new ArrayList<Book>();
			do {
				result.add(extractBook(cursor));
			} while (cursor.moveToNext());
		}
	} catch (Exception e) {
		Log.e(TAG, "Exception: " + e.getMessage());
	} finally {
		if (cursor != null) {
			cursor.close();
		}
		db.close();
	}
	return result;
}
 

/**
 * Add a debt into the database, linked to a RepayID
 * @param repayID
 * @param amount
 * @param description
 * @throws android.database.SQLException
 * @throws NullPointerException
 */
public void addDebt(final String repayID, final BigDecimal amount, String description)
		throws SQLException, NullPointerException
{
	SQLiteDatabase db = this.getWritableDatabase();
	ContentValues values = new ContentValues();
	values.put(Names.D_REPAYID, repayID);
	values.put(Names.D_DATE, new Date().toString());
	values.put(Names.D_AMOUNT, amount.toString());
	values.put(Names.D_DESCRIPTION, description.replaceAll("[-+.^:,']",""));
	db.insert(Names.D_TABLENAME, null, values);
	db.close();
}
 

public static void insertRow(String title, String body, String url, CharSequence topicId, String topic,
                             String postId, String userId, String user) {
    SQLiteDatabase db = null;

    try {
        NotesDbHelper dbHelper = new NotesDbHelper(App.getInstance());
        db = dbHelper.getWritableDatabase();
        // db.beginTransaction();

        ContentValues values = new ContentValues();

        values.put(COLUMN_TITLE, title);
        values.put(COLUMN_BODY, body);
        values.put(COLUMN_URL, url);
        values.put(COLUMN_TOPIC_ID, topicId.toString());
        values.put(COLUMN_POST_ID, postId);
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_USER, user);
        values.put(COLUMN_TOPIC, topic);
        values.put(COLUMN_DATE, DbHelper.DateTimeFormat.format(new Date()));


        db.insertOrThrow(TABLE_NAME, null, values);

    } catch (Exception ex) {
        AppLog.e(App.getInstance(), ex);
    } finally {
        if (db != null) {
            // db.endTransaction();
            db.close();
        }
    }
}
 

private Collection<LogEntry> getDbEntries(int limit, int fromLogEntryId, Level minLevel) throws SQLException {
    Collection<LogEntry> entries = new ArrayList<LogEntry>();
    SQLiteDatabase db = openDatabase();
    Cursor cursor = null;

    try {
        DefaultDBNameResolver dbNameResolver = getDbNameResolver();
        QueryBuilder qb = new QueryBuilder(dbNameResolver);
        cursor = db.rawQuery(qb.buildQuery(limit, fromLogEntryId, minLevel), new String[] {});
        while (cursor.moveToNext()) {
            LogEntry entry = new LogEntry();
            entry.setContext(0);
            entry.setId(cursor.getInt(cursor.getColumnIndex(mDbNameResolver.getColumnName(ColumnName.EVENT_ID))));
            entry.setLevel(cursor.getString(cursor.getColumnIndex(dbNameResolver.getColumnName(ColumnName.LEVEL_STRING))));
            entry.setMessage(cursor.getString(cursor.getColumnIndex(dbNameResolver.getColumnName(ColumnName.FORMATTED_MESSAGE))));
            entry.setTimestamp(cursor.getLong(cursor.getColumnIndex(dbNameResolver.getColumnName(ColumnName.TIMESTMP))));
            entry.setLoggerName(cursor.getString(cursor.getColumnIndex(dbNameResolver.getColumnName(ColumnName.LOGGER_NAME))));
            if ("ERROR".equals(entry.getLevel())) {
                entry.setStackTrace(getStackTrace(entry.getId()));
            }
            entries.add(entry);
        }
    } catch (SQLiteException e) {
        throw new SQLException("Cannot retrieve log entries", e);
    } finally {
        if (cursor != null) {
            cursor.close();
        }
        if (db != null) {
            db.close();
        }
    }

    return entries;
}
 

/**
 * 比较查询  此处查询单笔数据中OrderPrice最高的
 */
public Order getMaxOrderPrice(){
    SQLiteDatabase db = null;
    Cursor cursor = null;

    try {
        db = ordersDBHelper.getReadableDatabase();
        // select Id, CustomName, Max(OrderPrice) as OrderPrice, Country from Orders
        cursor = db.query(OrderDBHelper.TABLE_NAME, new String[]{"Id", "CustomName", "Max(OrderPrice) as OrderPrice", "Country"}, null, null, null, null, null);

        if (cursor.getCount() > 0){
            if (cursor.moveToFirst()) {
                return parseOrder(cursor);
            }
        }
    }
    catch (Exception e) {
        Log.e(TAG, "", e);
    }
    finally {
        if (cursor != null) {
            cursor.close();
        }
        if (db != null) {
            db.close();
        }
    }

    return null;
}
 

public void clearPackageTable(){
    SQLiteDatabase db = this.getWritableDatabase();
    int status = db.delete(TABLE_PACKAGE_STORAGE,1+"="+1 , null);
    db.close();
}
 

public void deleteWeekById(Week week) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TIMETABLE, WEEK_ID + " = ? ", new String[]{String.valueOf(week.getId())});
    db.close();
}
 

/**
 * This test uses the database directly to insert a row of test data and then uses the
 * ContentProvider to read out the data. We access the database directly to insert the data
 * because we are testing our ContentProvider's query functionality. If we wanted to use the
 * ContentProvider's insert method, we would have to assume that that insert method was
 * working, which defeats the point of testing.
 * <p>
 * If this test fails, you should check the logic in your
 * {@link WeatherProvider#insert(Uri, ContentValues)} and make sure it matches up with our
 * solution code.
 * <p>
 * Potential causes for failure:
 * <p>
 *   1) There was a problem inserting data into the database directly via SQLite
 * <p>
 *   2) The values contained in the cursor did not match the values we inserted via SQLite
 */
@Test
public void testBasicWeatherQuery() {

    /* Use WeatherDbHelper to get access to a writable database */
    WeatherDbHelper dbHelper = new WeatherDbHelper(mContext);
    SQLiteDatabase database = dbHelper.getWritableDatabase();

    /* Obtain weather values from TestUtilities */
    ContentValues testWeatherValues = TestUtilities.createTestWeatherContentValues();

    /* Insert ContentValues into database and get a row ID back */
    long weatherRowId = database.insert(
            /* Table to insert values into */
            WeatherContract.WeatherEntry.TABLE_NAME,
            null,
            /* Values to insert into table */
            testWeatherValues);

    String insertFailed = "Unable to insert into the database";
    assertTrue(insertFailed, weatherRowId != -1);

    /* We are done with the database, close it now. */
    database.close();

    /*
     * Perform our ContentProvider query. We expect the cursor that is returned will contain
     * the exact same data that is in testWeatherValues and we will validate that in the next
     * step.
     */
    Cursor weatherCursor = mContext.getContentResolver().query(
            WeatherContract.WeatherEntry.CONTENT_URI,
            /* Columns; leaving this null returns every column in the table */
            null,
            /* Optional specification for columns in the "where" clause above */
            null,
            /* Values for "where" clause */
            null,
            /* Sort order to return in Cursor */
            null);

    /* This method will ensure that we  */
    TestUtilities.validateThenCloseCursor("testBasicWeatherQuery",
            weatherCursor,
            testWeatherValues);
}
 

@Override
public void deleteAllStories() {
    SQLiteDatabase database = this.mDbHelper.getWritableDatabase();
    database.delete(StoriesPersistenceContract.StoryEntry.TABLE_NAME, null, null);
    database.close();
}
 

public boolean addBadgeNumber(BadgeNumber badgeNumber) {
    SQLiteDatabase db = null;
    Cursor cursor = null;

    try {
        db = dbHelper.getWritableDatabase();
        db.beginTransaction();

        cursor = db.query(MyDatabaseOpenHelper.BADGE_NUMBER_TABLE_NAME,
                new String[]{"count"},
                "type = ?",
                new String[] {String.valueOf(badgeNumber.getType())},
                null, null, null);
        int oldCount = 0;
        if (cursor.moveToFirst()) {
            oldCount = cursor.getInt(cursor.getColumnIndex("count"));
        }
        int newCount = oldCount + badgeNumber.getCount();

        ContentValues values = new ContentValues();
        values.put("type", badgeNumber.getType());
        values.put("count", newCount);
        values.put("display_mode", badgeNumber.getDisplayMode());
        //插入新的或替换旧的值
        db.insertWithOnConflict(MyDatabaseOpenHelper.BADGE_NUMBER_TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_REPLACE);

        db.setTransactionSuccessful();
        return true;
    }
    catch (Exception e) {
        Log.e(TAG, "", e);
    }
    finally {
        if (db != null) {
            db.endTransaction();
        }
        if (cursor != null) {
            cursor.close();
        }
        if (db != null) {
            db.close();
        }
    }

    return false;
}
 

/**
 * This method will clear all rows from the weather table in our database.
 * <p>
 * Please note:
 * <p>
 * - This does NOT delete the table itself. We call this method from our @Before annotated
 * method to clear all records from the database before each test on the ContentProvider.
 * <p>
 * - We don't use the ContentProvider's delete functionality to perform this row deletion
 * because in this class, we are attempting to test the ContentProvider. We can't assume
 * that our ContentProvider's delete method works in our ContentProvider's test class.
 */
private void deleteAllRecordsFromWeatherTable() {
    /* Access writable database through WeatherDbHelper */
    WeatherDbHelper helper = new WeatherDbHelper(InstrumentationRegistry.getTargetContext());
    SQLiteDatabase database = helper.getWritableDatabase();

    /* The delete method deletes all of the desired rows from the table, not the table itself */
    database.delete(WeatherContract.WeatherEntry.TABLE_NAME, null, null);

    /* Always close the database when you're through with it */
    database.close();
}