下面列出了android.database.sqlite.SQLiteDatabase#rawQuery ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
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);
}
@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;
}
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);
}
}
/**
* 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 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();
}
}
/**
* 取得应该设置的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;
}
/**
* 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;
}
@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;
}
/**
* 按时间序获取最近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;
}
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;
}
/**
* @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;
}
/**
* @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;
}
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;
}
/**
* 查询 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;
}
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;
}
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;
}
/**
* 根据类型查询消息数量
*
* @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;
}