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

/**
 * SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。
 * SQLITE_MASTER 表看起来如下：
 * CREATE TABLE sqlite_master (
 * type TEXT,
 * name TEXT,
 * tbl_name TEXT,
 * rootpage INTEGER,
 * sql TEXT
 * );
 * 对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。
 */
public static boolean isTableExists(SQLiteDatabase db, String tableName) {
    if (tableName == null || db == null || !db.isOpen()) return false;

    Cursor cursor = null;
    int count = 0;
    try {
        cursor = db.rawQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?", new String[]{"table", tableName});
        if (!cursor.moveToFirst()) {
            return false;
        }
        count = cursor.getInt(0);
    } catch (Exception e) {
        OkLogger.printStackTrace(e);
    } finally {
        if (cursor != null) cursor.close();
    }
    return count > 0;
}
 

public int deletePerson(long id) {
    int noOfDeletedRecords = 0;
    String whereClause = "id=?";
    String[] whereArgs = new String[]{String.valueOf(id)};

    SQLiteDatabase database = null;

    try {
        database = sqLiteOpenHelper.getWritableDatabase();
        noOfDeletedRecords = database.delete("tbl_persons", whereClause, whereArgs);
    } catch (Exception ex) {
        Log.d("Database", "Exception:" + ex.getMessage());
    } finally {
        if (database != null && database.isOpen()) {
            database.close();
        }
    }

    return noOfDeletedRecords;
}
 

@Override
protected String[] doInBackground(Object... params) {
    if (localLyricsFragment == null || localLyricsFragment.get().getActivity() == null)
        return new String[0];
    String[] columns = new String[]{DatabaseHelper.columns[0], DatabaseHelper.columns[1]};
    String query = String.format("LTRIM(Replace(%s, 'The ', '')) COLLATE NOCASE DESC,%s COLLATE NOCASE ASC", columns[0], columns[1]);
    SQLiteDatabase database = DatabaseHelper.getInstance(localLyricsFragment.get().getActivity()).getReadableDatabase();
    if (database != null) {
        Cursor cursor = database.query(DatabaseHelper.TABLE_NAME, new String[] {DatabaseHelper.columns[0]},
                null, null, null, null, query);
        cursor.moveToFirst();
        Collator collator = Collator.getInstance();
        collator.setStrength(Collator.PRIMARY);
        TreeSet<String> set = new TreeSet<>(collator);
        if (cursor.moveToFirst())
            do {
                if (!set.contains(cursor.getString(0)))
                    set.add(cursor.getString(0));
            } while (!cursor.isClosed() && database.isOpen() && cursor.moveToNext());
        List<String> artists = Arrays.asList(set.toArray(new String[set.size()]));
        Collections.sort(artists, String.CASE_INSENSITIVE_ORDER);
        cursor.close();
        return (String[]) artists.toArray();
    } else
        return new String[0];
}
 

/**
 * Count cache tiles: helper method
 * @since 6.0.2
 * @return the number of tiles, or -1 if a problem occurred
 */
protected long getRowCount(final String pWhereClause, final String[] pWhereClauseArgs) {
    Cursor cursor = null;
    try {
        final SQLiteDatabase db = getDb();
        if (db == null || !db.isOpen()) {
            return -1;
        }
        cursor = db.rawQuery(
                "select count(*) from " + TABLE
                        + (pWhereClause == null ? "" : " where " + pWhereClause), pWhereClauseArgs);
        if (cursor.moveToFirst()) {
            return cursor.getLong(0);
        }
    } catch (Exception ex) {
        catchException(ex);
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
    return -1;
}
 

public void dataRead(long syncStart) {
    if (isConnected()) {
        if (!refreshInProgress) {
            // TODO: Add timeout limit to refreshInProgress
            refreshInProgress = true;
            Context context = getApplicationContext();
            if (context != null) {
                SQLiteDatabase db = getDatabase();
                if (db != null && db.isOpen()) {
                    //db.execSQL("DELETE FROM " + Workout.class.getSimpleName()); // This deletes all data
                    cupboard().withDatabase(db).delete(Workout.class, "start >= ?", "" + syncStart);
                }
                //closeDatabase();
                //UserPreferences.setBackgroundLoadComplete(context, false);
                UserPreferences.setLastSync(context, syncStart);
                populateHistoricalData();
            }
        }
    } else {
        refreshInProgress = false;
    }
}
 

/**
 * SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。
 * SQLITE_MASTER 表看起来如下：
 * CREATE TABLE sqlite_master (
 * type TEXT,
 * name TEXT,
 * tbl_name TEXT,
 * rootpage INTEGER,
 * sql TEXT
 * );
 * 对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。
 */
public static boolean isTableExists(SQLiteDatabase db, String tableName) {
    if (tableName == null || db == null || !db.isOpen()) return false;

    Cursor cursor = null;
    int count = 0;
    try {
        cursor = db.rawQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?", new String[]{"table", tableName});
        if (!cursor.moveToFirst()) {
            return false;
        }
        count = cursor.getInt(0);
    } catch (Exception e) {
        OkLogger.printStackTrace(e);
    } finally {
        if (cursor != null) cursor.close();
    }
    return count > 0;
}
 

/**
 * SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。
 * SQLITE_MASTER 表看起来如下：
 * CREATE TABLE sqlite_master (
 * type TEXT,
 * name TEXT,
 * tbl_name TEXT,
 * rootpage INTEGER,
 * sql TEXT
 * );
 * 对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。
 */
public static boolean isTableExists(SQLiteDatabase db, String tableName) {
    if (tableName == null || db == null || !db.isOpen()) return false;

    Cursor cursor = null;
    int count = 0;
    try {
        cursor = db.rawQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?", new String[]{"table", tableName});
        if (!cursor.moveToFirst()) {
            return false;
        }
        count = cursor.getInt(0);
    } catch (Exception e) {
        OkLogger.printStackTrace(e);
    } finally {
        if (cursor != null) cursor.close();
    }
    return count > 0;
}
 

public static boolean isFieldExists(SQLiteDatabase db, String tableName, String fieldName) {
    if (tableName == null || db == null || fieldName == null || !db.isOpen()) return false;

    Cursor cursor = null;
    try {
        cursor = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 0", null);
        return cursor != null && cursor.getColumnIndex(fieldName) != -1;
    } catch (Exception e) {
        OkLogger.printStackTrace(e);
        return false;
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}
 

/**
 * Add a new log entry.
 *
 * @param logMessage the message to insert in the log.
 */
public static void addLogEntry(String logMessage) {
    try {
        Date date = new Date();
        SQLiteDatabase sqliteDatabase = GPApplication.getInstance().getDatabase();
        if (sqliteDatabase != null && sqliteDatabase.isOpen()) {
            ContentValues values = new ContentValues();
            long time = date.getTime();
            values.put(COLUMN_DATAORA, time);
            values.put(COLUMN_LOGMSG, logMessage);
            insertOrThrow(sqliteDatabase, TABLE_LOG, values);
        }

        if (LOG_ANDROID) {
            StringBuilder sb = new StringBuilder();
            sb.append(TimeUtilities.INSTANCE.iso8601Format.format(date));
            sb.append(": ");
            sb.append(logMessage);
            String string = sb.toString();
            log(GLOBAL_LOG_TAG, string);
        }
    } catch (Exception e) {
        Log.e(GLOBAL_LOG_TAG, logMessage, e);
    }
}
 

/**
 * purges and deletes everything from the cache database
 *
 * @return
 * @since 5.6
 */
public boolean purgeCache() {
    final SQLiteDatabase db = getDb();
    if (db != null && db.isOpen()) {
        try {
            db.delete(TABLE, null, null);
            return true;
        } catch (Exception e) {
            Log.w(IMapView.LOGTAG, "Error purging the db", e);
            catchException(e);
        }
    }
    return false;
}
 

/**
 * 删除一个联系人
 * @param username
 */
public void deleteContact(String username){
	SQLiteDatabase db = dbHelper.getWritableDatabase();
	if(db.isOpen()){
		db.delete(TABLE_NAME, COLUMN_NAME_ID + " = ?", new String[]{username});
	}
}
 

/**
 * 获取好友list
 * 
 * @return
 */
synchronized public Map<String, EaseUser> getContactList() {
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Map<String, EaseUser> users = new Hashtable<String, EaseUser>();
    if (db.isOpen()) {
        Cursor cursor = db.rawQuery("select * from " + UserDao.TABLE_NAME /* + " desc" */, null);
        while (cursor.moveToNext()) {
            String username = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_ID));
            String nick = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_NICK));
            String avatar = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_AVATAR));
            EaseUser user = new EaseUser(username);
            user.setNick(nick);
            user.setAvatar(avatar);
            String headerName = null;
            if (!TextUtils.isEmpty(user.getNick())) {
                headerName = user.getNick();
            } else {
                headerName = user.getUsername();
            }
            
            if (username.equals(Constant.NEW_FRIENDS_USERNAME) || username.equals(Constant.GROUP_USERNAME)
                    || username.equals(Constant.CHAT_ROOM)|| username.equals(Constant.CHAT_ROBOT)) {
                user.setInitialLetter("");
            } else if (Character.isDigit(headerName.charAt(0))) {
                user.setInitialLetter("#");
            } else {
                user.setInitialLetter(HanziToPinyin.getInstance().get(headerName.substring(0, 1))
                        .get(0).target.substring(0, 1).toUpperCase());
                char header = user.getInitialLetter().toLowerCase().charAt(0);
                if (header < 'a' || header > 'z') {
                    user.setInitialLetter("#");
                }
            }
            users.put(username, user);
        }
        cursor.close();
    }
    return users;
}
 

public int update(SQLiteDatabase db, String table, ContentValues cv, String whereClause, String[] whereParams) {
	Object lock = new Object();
	synchronized (lock) {
		if (!db.isOpen())
			db = getWritableDatabase();
		return db.update(table, cv, whereClause, whereParams);
	}
}
 

public int delete(SQLiteDatabase db, String table, String whereClause, String[] whereParams) {
	Object lock = new Object();
	synchronized (lock) {
		if (!db.isOpen())
			db = getWritableDatabase();
		return db.delete(table, whereClause, whereParams);
	}
}
 

synchronized private void setList(String column, List<String> strList){
    StringBuilder strBuilder = new StringBuilder();
    
    for(String hxid:strList){
        strBuilder.append(hxid).append("$");
    }
    
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    if (db.isOpen()) {
        ContentValues values = new ContentValues();
        values.put(column, strBuilder.toString());

        db.update(UserDao.PREF_TABLE_NAME, values, null,null);
    }
}
 

private static boolean isDbSafe(SQLiteDatabase db) {
    return db.isOpen() && !db.isReadOnly() && !db.isDbLockedByCurrentThread();
}
 

public void deleteMessage(String from){
	SQLiteDatabase db = dbHelper.getWritableDatabase();
	if(db.isOpen()){
		db.delete(TABLE_NAME, COLUMN_NAME_FROM + " = ?", new String[]{from});
	}
}
 

protected final void closeDatabase(SQLiteDatabase database, Cursor cursor) {
    if (cursor != null && !cursor.isClosed()) cursor.close();
    if (database != null && database.isOpen()) database.close();
}
 

public void closeDB() {
	SQLiteDatabase db = this.getReadableDatabase();
	if (db != null && db.isOpen())
		db.close();
}
 

private void loadForm(){
    _tableColumns = new ArrayList<>();

    SQLiteDatabase db = null;
    Cursor cursor = null;

    try{
        db = openOrCreateDatabase("/data/data/" + getPackageName() + "/sqlitesync.db", 1, null);
        cursor = db.rawQuery(String.format("PRAGMA table_info(%s);", _tableName), null);

        int nameIdx = cursor.getColumnIndexOrThrow("name");
        int typeIdx = cursor.getColumnIndexOrThrow("type");

        while (cursor.moveToNext()){
            String name = cursor.getString(nameIdx);
            if(!name.equalsIgnoreCase("RowId") && !name.equalsIgnoreCase("MergeUpdate")){
                _tableColumns.add(new TableColumn(name, cursor.getString(typeIdx)));
            }
        }

        if(_rowId != null){
            cursor.close();

            StringBuilder builder = new StringBuilder();
            builder.append("SELECT ");

            for(int i = 0; i < _tableColumns.size(); i++){
                if(i != 0){
                    builder.append(",");
                }
                builder.append(_tableColumns.get(i).getName());
            }

            builder.append(" FROM ");
            builder.append(_tableName);
            builder.append(" WHERE RowId = ?;");

            cursor = db.rawQuery(builder.toString(), new String[]{ _rowId });

            if (cursor.moveToFirst()){
                for(int i = 0; i < _tableColumns.size(); i++){
                    _tableColumns.get(i).setValue(cursor.getString(i));
                }
            }
        }

    }
    finally {
        if(cursor != null && !cursor.isClosed()){
            cursor.close();
        }
        if(db != null && db.isOpen()){
            db.close();
        }
    }

    LinearLayout form = (LinearLayout)findViewById(R.id.form_container);

    int index = -1;

    for(TableColumn column : _tableColumns){
        TextView label = new TextView(this);
        EditText editText = new EditText(this);

        label.setText(column.getName());
        editText.setText(column.getValue());

        if(column.getType().equalsIgnoreCase("INTEGER")){
            editText.setInputType(InputType.TYPE_CLASS_NUMBER);
        }

        form.addView(label, ++index);
        form.addView(editText, ++index);
    }
}