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

public List<Message> getAttachmentMessages(String contactId, Integer groupId, boolean downloadedOnly) {

        if (contactId == null && (groupId == null || groupId == 0)) {
            return new ArrayList<>();
        }

        String query = "SELECT * FROM " + MobiComDatabaseHelper.SMS_TABLE_NAME + " WHERE ";
        String params = "";

        if (groupId != null && groupId != 0) {
            params = MobiComDatabaseHelper.CHANNEL_KEY + " = " + groupId + " AND";
        } else if (contactId != null) {
            params = "contactNumbers = '" + contactId + "' AND";
        }

        String selectionArgs = (downloadedOnly ? " filePaths" : " blobKeyString") + " IS NOT NULL ORDER BY createdAt DESC";

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery(query + params + selectionArgs, null);

        return getMessageList(cursor);

    }
 

/**
 * 取得应该设置的tag
 * @return 应该设置的tags
 */
private List<String> getTagsList() {
	//先添加车次聊天室需要的tag
	List<String> newTags = new  ArrayList<String>();
	MyDatabase myDB = new MyDatabase(MyApp.getInstance().getApplicationContext());
	SQLiteDatabase db = myDB.getWritableDB();
	int userId = MyApp.getInstance().getUserInfoSPUtil().getUId();
	Cursor c= db.rawQuery("select T_id from UserTrainB where ReceiveMsg = 1 and  U_id = "+userId+" group by T_id order by _id DESC", null);
	while(c.moveToNext()){
		newTags.add(c.getString(c.getColumnIndex("T_id")));
	}
	//添加其他tag --如果有的话
	//添加公共聊天室
	if(MyApp.getInstance().getSettingSPUtil().isReceivePublicChatroom()){
		newTags.add("all");
	}
	c.close();
	myDB.closeDB();
	return newTags;
}
 

private Set<String> getHashTagList(long statusID) {
    Cursor hashsetCursor = null;
    try {
        SQLiteDatabase database = databaseHelper.getReadableDatabase();
        StringBuilder query = new StringBuilder(
                "SELECT h." + HashtagDatabase.HASHTAG
                        + " FROM " + HashtagDatabase.TABLE_NAME + " h"
                        + " JOIN " + StatusTagDatabase.TABLE_NAME + " st"
                        + " ON st." + StatusTagDatabase.HDBID + " = h." + HashtagDatabase.ID
                        + " WHERE st." + StatusTagDatabase.HDBID + " = ?");
        hashsetCursor = database.rawQuery(query.toString(), new String[]{Long.toString(statusID)});
        Set<String> hashtagSet = new HashSet<String>();
        if (hashsetCursor != null) {
            for (hashsetCursor.moveToFirst(); !hashsetCursor.isAfterLast(); hashsetCursor.moveToNext()) {
                hashtagSet.add(hashsetCursor.getString(hashsetCursor.getColumnIndexOrThrow(HashtagDatabase.HASHTAG)));
            }
        }
        return hashtagSet;
    } finally {
        if(hashsetCursor != null)
            hashsetCursor.close();
    }
}
 

/**
 * 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;
}
 

private static void reduce(SQLiteDatabase db, String accountId) {
    String searchCount = "select count(" + AtUsersTable.ID + ") as total" + " from " + AtUsersTable.TABLE_NAME
            + " where " + AtUsersTable.ACCOUNTID + " = "
            + accountId;
    int total = 0;
    Cursor c = db.rawQuery(searchCount, null);
    if (c.moveToNext()) {
        total = c.getInt(c.getColumnIndex("total"));
    }

    c.close();

    int needDeletedNumber = total - 15;

    if (needDeletedNumber > 0) {
        String sql = " delete from " + AtUsersTable.TABLE_NAME + " where " + AtUsersTable.ID + " in " + "( select "
                + AtUsersTable.ID + " from "
                + AtUsersTable.TABLE_NAME + " where " + AtUsersTable.ACCOUNTID + " in " + "(" + accountId
                + ") order by " + AtUsersTable.ID + " asc limit "
                + needDeletedNumber + " ) ";

        db.execSQL(sql);
    }
}
 

@Override
public String getEnterpriseEncryptMnemonicSeed(int hdSeedId) {
    String encryptSeedMnemonicSeed = null;
    Cursor c = null;
    try {
        SQLiteDatabase db = this.mDb.getReadableDatabase();
        String sql = "select encrypt_mnemonic_seed from enterprise_hd_account where hd_account_id=?";
        c = db.rawQuery(sql, new String[]{Integer.toString(hdSeedId)});
        if (c.moveToNext()) {
            encryptSeedMnemonicSeed = c.getString(0);
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        if (c != null)
            c.close();
    }
    return encryptSeedMnemonicSeed;
}
 

protected final void loadBookmarks(final BookSettings book, final SQLiteDatabase db, final String query) {
    book.bookmarks.clear();
    final Cursor c = db.rawQuery(query, new String[] { LengthUtils.safeString(book.fileName) });
    if (c != null) {
        try {
            for (boolean next = c.moveToFirst(); next; next = c.moveToNext()) {
                final Bookmark bm = createBookmark(c);
                book.bookmarks.add(bm);
            }
            // if (LCTX.isDebugEnabled()) {
            // LCTX.d("Bookmarks loaded for " + book.fileName + ": " + book.bookmarks.size());
            // }
        } finally {
            close(c);
        }
    }
}
 

public long getOldestMessages() {
    Cursor cursor = null;
    try {
        SQLiteDatabase db = this.getReadableDatabase();
        db.beginTransaction();
        cursor = db.rawQuery("select timeSent from " + Message.TABLENAME + " ORDER BY " + Message.TIME_SENT + " ASC limit 1", null);
        db.setTransactionSuccessful();
        db.endTransaction();
        if (cursor.getCount() == 0) {
            return 0;
        } else {
            cursor.moveToFirst();
            return cursor.getLong(0);
        }
    } catch (Exception e) {
        return 0;
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}
 

public ArrayList<Week> getWeek(String fragment){
    SQLiteDatabase db = this.getWritableDatabase();

    ArrayList<Week> weeklist = new ArrayList<>();
    Week week;
    Cursor cursor = db.rawQuery("SELECT * FROM ( SELECT * FROM "+TIMETABLE+" ORDER BY " + WEEK_FROM_TIME + " ) WHERE "+ WEEK_FRAGMENT +" LIKE '"+fragment+"%'",null);
    while (cursor.moveToNext()){
        week = new Week();
        week.setId(cursor.getInt(cursor.getColumnIndex(WEEK_ID)));
        week.setSubject(cursor.getString(cursor.getColumnIndex(WEEK_SUBJECT)));
        week.setTeacher(cursor.getString(cursor.getColumnIndex(WEEK_TEACHER)));
        week.setRoom(cursor.getString(cursor.getColumnIndex(WEEK_ROOM)));
        week.setFromTime(cursor.getString(cursor.getColumnIndex(WEEK_FROM_TIME)));
        week.setToTime(cursor.getString(cursor.getColumnIndex(WEEK_TO_TIME)));
        week.setColor(cursor.getInt(cursor.getColumnIndex(WEEK_COLOR)));
        weeklist.add(week);
    }
    return  weeklist;
}
 

public int getItemCount() {
    int result = 0;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery("SELECT KEY from ".concat(slotitem_table_name), null);
    result = c.getCount();
    c.close();
    return result;
}
 

/**
 * @return the total weight for this machine for this day
 */
public float getTotalWeightMachine(Date pDate, String pMachine, Profile pProfile) {
    if (pProfile==null) return 0;
    float lReturn = 0;

    //Test is Machine exists. If not create it.
    DAOMachine lDAOMachine = new DAOMachine(mContext);
    long machine_key = lDAOMachine.getMachine(pMachine).getId();

    SimpleDateFormat dateFormat = new SimpleDateFormat(DAOUtils.DATE_FORMAT);
    dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
    String lDate = dateFormat.format(pDate);

    SQLiteDatabase db = this.getReadableDatabase();
    mCursor = null;
    // Select All Machines
    String selectQuery = "SELECT " + SETS + ", " + WEIGHT + ", " + REPS + " FROM " + TABLE_NAME
        + " WHERE " + DATE + "=\"" + lDate + "\" AND " + EXERCISE_KEY + "=" + machine_key
        + " AND " + PROFILE_KEY + "=" + pProfile.getId()
        + " AND " + TEMPLATE_RECORD_STATUS + "!=" + ProgramRecordStatus.PENDING.ordinal();
    mCursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (mCursor.moveToFirst()) {
        int i = 0;
        do {
            float value = mCursor.getInt(0) * mCursor.getFloat(1) * mCursor.getInt(2);
            lReturn += value;
            i++;
        } while (mCursor.moveToNext());
    }
    close();

    // return value
    return lReturn;
}
 

/**
 * 查询 table 表中所有的列属性名称
 *
 * @param db
 * @param table
 * @return
 */
static List<String> listColumns(SQLiteDatabase db, String table) {
    Log.d(TAG, "listColumns(" + table + ")...");
    //cursor 返回的是指向第一个行及列属性那行的数据
    Cursor cursor = db.rawQuery(String.format(SQL_SELECT_TABLE_ALL_COLUMNS, table), null);
    if (cursor == null) {
        Log.d(TAG, "listColumns(" + table + "): no columns in table " + table);
        return null;
    }
    List<String> columns = Arrays.asList(cursor.getColumnNames());
    cursor.close();
    return columns;
}
 

/**
 * @return the total weight for this day
 */
public float getTotalWeightSession(Date pDate, Profile pProfile) {

    SQLiteDatabase db = this.getReadableDatabase();
    mCursor = null;
    float lReturn = 0;

    SimpleDateFormat dateFormat = new SimpleDateFormat(DAOUtils.DATE_FORMAT);
    dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
    String lDate = dateFormat.format(pDate);

    // Select All Machines
    String selectQuery = "SELECT " + SETS + ", " + WEIGHT  + " FROM " + TABLE_NAME
        + " WHERE " + DATE + "=\"" + lDate + "\""
        + " AND " + PROFILE_KEY + "=" + pProfile.getId()
        + " AND " + TEMPLATE_RECORD_STATUS + "!=" + ProgramRecordStatus.PENDING.ordinal();
    mCursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (mCursor.moveToFirst()) {
        int i = 0;
        do {
            float value = mCursor.getInt(0) * mCursor.getFloat(1) ;
            lReturn += value;
            i++;
        } while (mCursor.moveToNext());
    }
    close();

    // return value
    return lReturn;
}
 

public synchronized List<CurrentWeatherData> getAllCurrentWeathers() {
    List<CurrentWeatherData> currentWeatherList = new ArrayList<CurrentWeatherData>();

    String selectQuery = "SELECT * FROM " + TABLE_CURRENT_WEATHER;

    SQLiteDatabase database = this.getWritableDatabase();
    Cursor cursor = database.rawQuery(selectQuery, null);

    CurrentWeatherData currentWeather;

    if (cursor.moveToFirst()) {
        do {
            currentWeather = new CurrentWeatherData();
            currentWeather.setId(Integer.parseInt(cursor.getString(0)));
            currentWeather.setCity_id(Integer.parseInt(cursor.getString(1)));
            currentWeather.setTimestamp(Long.parseLong(cursor.getString(2)));
            currentWeather.setWeatherID(Integer.parseInt(cursor.getString(3)));
            currentWeather.setTemperatureCurrent(Float.parseFloat(cursor.getString(4)));
            currentWeather.setTemperatureMin(Float.parseFloat(cursor.getString(5)));
            currentWeather.setTemperatureMax(Float.parseFloat(cursor.getString(6)));
            currentWeather.setHumidity(Float.parseFloat(cursor.getString(7)));
            currentWeather.setPressure(Float.parseFloat(cursor.getString(8)));
            currentWeather.setWindSpeed(Float.parseFloat(cursor.getString(9)));
            currentWeather.setWindDirection(Float.parseFloat(cursor.getString(10)));
            currentWeather.setCloudiness(Float.parseFloat(cursor.getString(11)));
            currentWeather.setTimeSunrise(Long.parseLong(cursor.getString(12)));
            currentWeather.setTimeSunset(Long.parseLong(cursor.getString(13)));
            currentWeather.setTimeZoneSeconds(Integer.parseInt(cursor.getString(14)));

            currentWeatherList.add(currentWeather);
        } while (cursor.moveToNext());
    }

    cursor.close();
    return currentWeatherList;
}
 

/**
 * Returns all available activity samples from between the two timestamps (inclusive), of the given
 * provided and type(s).
 *
 * @param timestamp_from : time in millis from date
 * @param timestamp_to   : time in millis to date
 * @param activityTypes  combination of #TYPE_DEEP_SLEEP, #TYPE_LIGHT_SLEEP, #TYPE_ACTIVITY
 * @return
 */
private ArrayList<ActivityData> getActivitiesSample(long timestamp_from, long timestamp_to, int activityTypes) {
    if (timestamp_to == -1) {
        timestamp_to = Integer.MAX_VALUE;
    }

    MasterSQLiteHelper helperDB = new MasterSQLiteHelper(context);
    SQLiteDatabase db = helperDB.getReadableDatabase();

    ArrayList<ActivityData> allActivities = new ArrayList<ActivityData>();

    //Log.i(TAG, "data from " + DateUtils.convertString(timestamp_from) + " to " + DateUtils.convertString(timestamp_to));

    String query = "SELECT  * FROM " + TABLE_NAME + " WHERE (timestamp>=" + timestamp_from
            + " AND timestamp<=" + timestamp_to
            + getWhereClauseFor(activityTypes)
            + ") ORDER BY timestamp";

    Cursor cursor = db.rawQuery(query, null);

    cursor.moveToFirst();

    while (!cursor.isAfterLast()) {
        allActivities.add(cursorToActivity(cursor));
        cursor.moveToNext();
    }

    cursor.close();
    db.close();

    return allActivities;
}
 

/**
 * 按时间序获取最近30条数据
 * @return
 */
public List<String> queryDataTime(){
    List<String> mDataTime = new ArrayList<>();
    String query_sql = "select  " + LocationDataBase.FILED_TIME_DAY + " from " + LocationDataBase.TABLE_LOCATION
            + " order by " + LocationDataBase.FILED_TIME_DAY + " desc ";
    SQLiteDatabase db = mLocationDataBase.getReadableDatabase();
    Cursor cursor = db.rawQuery(query_sql, null);
    try {
        while (cursor.moveToNext()){
            int index= cursor.getColumnIndex(LocationDataBase.FILED_TIME_DAY);
            String date = cursor.getString(index);
            if(mDataTime.size() > RMConfiguration.MAX_SUPPORT_ITEMS){
                break;
            }
            if(!mDataTime.contains(date)) {
                mDataTime.add(date);
            }
            CFLog.e("TAG",date);
        }
    }
    finally {
        if(cursor!=null){
            cursor.close();
        }
    }
    return mDataTime;

}
 

@Thunk boolean updateFolderItemsRank(SQLiteDatabase db, boolean addRankColumn) {
    db.beginTransaction();
    try {
        if (addRankColumn) {
            // Insert new column for holding rank
            db.execSQL("ALTER TABLE favorites ADD COLUMN rank INTEGER NOT NULL DEFAULT 0;");
        }

        // Get a map for folder ID to folder width
        Cursor c = db.rawQuery("SELECT container, MAX(cellX) FROM favorites"
                + " WHERE container IN (SELECT _id FROM favorites WHERE itemType = ?)"
                + " GROUP BY container;",
                new String[]{Integer.toString(LauncherSettings.Favorites.ITEM_TYPE_FOLDER)});

        while (c.moveToNext()) {
            db.execSQL("UPDATE favorites SET rank=cellX+(cellY*?) WHERE "
                            + "container=? AND cellX IS NOT NULL AND cellY IS NOT NULL;",
                    new Object[]{c.getLong(1) + 1, c.getLong(0)});
        }

        c.close();
        db.setTransactionSuccessful();
    } catch (SQLException ex) {
        // Old version remains, which means we wipe old data
        Log.e(TAG, ex.getMessage(), ex);
        return false;
    } finally {
        db.endTransaction();
    }
    return true;
}
 

/**
 * 根据类型查询消息数量
 * 
 * @param type
 * @return
 */
public int statusCountByType(final int type) {
    int result = -1;
    String sql = "SELECT COUNT(" + BasicColumns.ID + ") FROM "
            + StatusInfo.TABLE_NAME;
    if (type == Constants.TYPE_NONE) {
        sql += " ;";
    } else {
        sql += " WHERE " + BasicColumns.TYPE + "=" + type + ";";
    }
    final SQLiteDatabase db = this.mSQLiteHelper.getReadableDatabase();
    try {
        // String[] columns=new String[]{StatusInfo.ID};
        // String where=null;
        // String[] whereArgs=null;
        // if(type!=Status.TYPE_NONE){
        // where=StatusInfo.TYPE + "=?";
        // whereArgs=new String[]{String.valueOf(type)};
        // }
        // Cursor c=db.query(StatusInfo.TABLE_NAME, columns, where,
        // whereArgs, null, null, null);
        log("statusCountByType() sql=" + sql);
        final Cursor c = db.rawQuery(sql, null);
        if (c != null) {
            c.moveToFirst();
            result = c.getInt(0);
        }
        // 方法二
        // String where=StatusInfo.TYPE+"=?";
        // String[] whereArgs=new String[]{String.valueOf(type)};
        // Cursor c2= db.query(StatusInfo.TABLE_NAME, new
        // String[]{StatusInfo.ID}, where, whereArgs, null, null, null);
        // if(c2!=null){
        // result=c.getCount();
        // }
    } catch (final Exception e) {
        e.printStackTrace();
        result = 0;
    } finally {
        db.close();
    }
    log("statusCountByType() type=" + type + " result=" + result);
    return result;
}
 

public static boolean hasCategory(SQLiteDatabase db, String categoryName) {
	Cursor cursor = db.rawQuery("SELECT * FROM categories WHERE name = '" + categoryName +"'", null);
	boolean categoryExists = ((cursor.getCount() != 0) || !CategoryManager.isCustom(categoryName));
	cursor.close();
	return categoryExists;
}
 

public int checkGroupMemberExist(int gUid, ContactBean cb, String table) {
    try {
        String exist = "SELECT COUNT(*) as m FROM " + table + " Where " + MEMBER_UID + " = " + gUid;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(exist, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                //Log.e("ddd "," Exist "+cursor.getInt(0));
                if (cursor.getInt(0) != 0) {
                    //String updateTable= "UPDATE "+TABLE_NAME+" SET "+T_TAGNAME+"= '"+gb.getmGroupTag().trim()+"', "+T_DESCRITION+"= '"+gb.getmGroupTag().trim()+"', "+T_GROUPPHOTO+"= '"+gb.getmPhoto().trim()+"', "+T_MEMBERCOUNT+"= '"+String.valueOf(gb.getmGroupSize())+"', "+T_ADMINFLAG+"= '"+gb.getmGroupAdmin()+" Where "+T_GROUPID+" = "+gUid+";";
                    ContentValues cv = new ContentValues();
                    cv.put(MEMBER_NAME, cb.getName().trim());
                    cv.put(MEMBER_PHONE, cb.getNumber().trim());
                    cv.put(MEMBER_SEARCHKEY, cb.getSearchKey().trim());
                    cv.put(MEMBER_UID, cb.getUID().trim());
                    cv.put(MEMBER_FLAG, cb.getRequestFlag().trim());
                    cv.put(MEMBER_ORG_NAME, cb.getOrignalName().trim());
                    cv.put(MEMBER_PH_BOK_NAME, cb.getMyPhoneBookName().trim());
                    cv.put(MEMBER_ISMY_CONTACT, cb.getIsMyContact());
                    cv.put(MEMBER_BLOOD_GROUP, cb.getmBloodGroup().trim());
                    cv.put(MEMBER_CITY, cb.getCity().trim());
                    cv.put(MEMBER_PROFESSION, cb.getProfession().trim());
                    cv.put(MEMBER_ADMIN_FLAG, cb.getAdminFlag());
                    cv.put(MEMBER_CREATED_DATE, cb.getmCreatedDate());
                    db.update(table, cv, MEMBER_UID + " = ?", new String[]{String.valueOf(cb.getUID())});
                    return 0;
                } else {
                    addMember(cb, "MG" + Vars.gUID);
                }
            } while (cursor.moveToNext());
        }
        if (cursor != null)
            cursor.close();
        db.close();
    } catch (Exception e) {
        // TODO: handle exception
        //Log.e("GroupDBErro", "FetchAllDB " + e.getMessage());
        e.printStackTrace();
    }
    return 1;
}