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

The following examples show how to use android.database.sqlite.SQLiteDatabase#rawQuery() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may want to check out the right sidebar which shows the related API usage.
Example 1
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);

    }
 
Example 2
@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;
}
 
Example 3
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);
        }
    }
}
 
Example 4
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();
        }
    }
}
 
Example 5
Source Project: TimeTable   File: DbHelper.java    License: GNU General Public License v3.0 6 votes vote down vote up
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;
}
 
Example 6
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);
    }
}
 
Example 7
Source Project: osmdroid   File: SqlTileWriter.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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;
}
 
Example 8
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();
    }
}
 
Example 9
/**
 * 取得应该设置的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;
}
 
Example 10
/**
 * 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;
}
 
Example 11
@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;
}
 
Example 12
Source Project: RunMap   File: DataManager.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 按时间序获取最近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;

}
 
Example 13
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;
}
 
Example 14
/**
 * @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;
}
 
Example 15
/**
 * @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;
}
 
Example 16
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;
}
 
Example 17
Source Project: base-module   File: AbstractDbTable.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 查询 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;
}
 
Example 18
Source Project: XERUNG   File: GroupDb.java    License: Apache License 2.0 4 votes vote down vote up
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;
}
 
Example 19
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;
}
 
Example 20
Source Project: fanfouapp-opensource   File: Database.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 根据类型查询消息数量
 * 
 * @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;
}