Java SQLiteDatabase类代码示例

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public ArrayList<Plant> getAllPlants() {
    ArrayList<Plant> array_list = new ArrayList<Plant>();

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery( "SELECT id, name, start_date, image FROM plants", null );
    res.moveToFirst();

    while(res.isAfterLast() == false) {
        int id = res.getInt(res.getColumnIndex("id"));
        String name = res.getString(res.getColumnIndex(PLANTS_COLUMN_NAME));
        String startDate = res.getString(res.getColumnIndex(PLANTS_COLUMN_START_DATE));
        String image = res.getString(res.getColumnIndex(PLANTS_COLUMN_IMAGE));
        Plant p = new Plant(id, name, startDate, image);
        array_list.add(p);
        res.moveToNext();
    }
    db.close();
    return array_list;
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public static String ancestorsIds(SQLiteDatabase db, long bookId, long noteId) {
    Cursor cursor = db.query(
            DbNote.TABLE,
            new String[] { "group_concat(" + DbNote._ID + ", ',')" },
            DatabaseUtils.whereAncestors(bookId, String.valueOf(noteId)),
            null, null, null, null);
    try {
        if (cursor.moveToFirst()) {
            return cursor.getString(0);
        }
    } finally {
        cursor.close();
    }

    return null;
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public int getMessageCountForThread(long threadId) {
  SQLiteDatabase db = databaseHelper.getReadableDatabase();
  Cursor cursor     = null;

  try {
    cursor = db.query(TABLE_NAME, new String[] {"COUNT(*)"}, THREAD_ID + " = ?",
                      new String[] {threadId+""}, null, null, null);

    if (cursor != null && cursor.moveToFirst())
      return cursor.getInt(0);
  } finally {
    if (cursor != null)
      cursor.close();
  }

  return 0;
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public static long getOrInsert(SQLiteDatabase db, long nameId, long valueId) {
    long id = DatabaseUtils.getId(
            db,
            TABLE,
            "name_id = " + nameId + " AND value_id = " + valueId, null);

    if (id == 0) {
        ContentValues values = new ContentValues();
        values.put("name_id", nameId);
        values.put("value_id", valueId);

        id = db.insertOrThrow(TABLE, null, values);
    }

    return id;
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
@Nullable
@Override
public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection,
                    @Nullable String[] selectionArgs, @Nullable String sortOrder)
        throws UnsupportedOperationException {
    final SQLiteDatabase db = mDbHelper.getReadableDatabase();
    int match = sUriMatcher.match(uri);
    Cursor cursor;
    switch (match) {
        case FAVORITES:
            cursor = db.query(MediaEntry.TABLE_NAME, projection, MediaEntry.COLUMN_IS_FAVORED + " = 1 ",
                    null, null, null, sortOrder);
            break;
        default:
            throw new UnsupportedOperationException("Unknown uri: " + uri);
    }

    mContext.getContentResolver().notifyChange(uri, null);
    return cursor;
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
@Override
public void onCreate(SQLiteDatabase db) {
    Log.d("DB_CREATE", "Creating table " + AppChoiceTable.NAME);
    db.execSQL("create table " + AppChoiceTable.NAME + "(" +
            " _id integer primary key autoincrement, " +
            AppChoiceTable.Cols.APP_PACKAGE_NAME + ", " +
            AppChoiceTable.Cols.APP_NAME + ", " +
            AppChoiceTable.Cols.SELECTION + ", " +
            AppChoiceTable.Cols.FILTER_TEXT + ", " +
            AppChoiceTable.Cols.START_TIME_HOUR + ", " +
            AppChoiceTable.Cols.START_TIME_MINUTE + ", " +
            AppChoiceTable.Cols.STOP_TIME_HOUR + ", " +
            AppChoiceTable.Cols.STOP_TIME_MINUTE + ", " +
            AppChoiceTable.Cols.DISCARD_EMPTY_NOTIFICATIONS + ", " +
            AppChoiceTable.Cols.DISCARD_ONGOING_NOTIFICATIONS + ", " +
            AppChoiceTable.Cols.ALL_DAY_SCHEDULE + ", " +
            AppChoiceTable.Cols.DAYS_OF_WEEK +
            ")"
            // ALERT!!! Make sure you have a comma to separate all names! Had a bug because I forgot it of ALL_DAY_SCHEDULE
    );
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public int getContactCount() {
    int count = 0;
    try {
        String countQuery = "SELECT COUNT(1) FROM " + CONTACT_DB;
        SQLiteDatabase sq = this.getReadableDatabase();
        Cursor cursor = sq.rawQuery(countQuery, null);
        cursor.close();
        count = cursor.getCount();
        sq.close();
    } catch (Exception e) {
        // TODO: handle exception
        //Log.e("GroupDBErro", "GetGroup Count "+e.getMessage());
        e.printStackTrace();
    }
    return count;
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
/**
 * Function to get aya touched rectangle dimensions and info
 *
 * @param pageNumber Page number
 * @param positionX  Touch X position
 * @param positionY  Touch Y position
 * @return Aya Selection
 */
public Aya getTouchedAya(int pageNumber, float positionX, float positionY) {
    int suraID = -1;
    int ayaID = -1;
    SQLiteDatabase db = openDB(SELECTION_DATABASE);
    String sqlPosition = "select soraid , ayaid from ayarects where page = " +
            "" + pageNumber + " and minx <= " + positionX + " and maxx >= " +
            positionX + " and miny <= " + positionY + " and maxy >= " + positionY + " ;";
    Cursor cursor = db.rawQuery(sqlPosition, null);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        suraID = cursor.getInt(0);
        ayaID = cursor.getInt(1);
        cursor.moveToNext();
    }

    cursor.close();
    closeDB(db);

    return new Aya(pageNumber, suraID, ayaID, getAyaRectsMap(pageNumber, suraID, ayaID));
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public void update(Task task) {

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(Task.KEY_TITLE,task.getTitle());
        values.put(Task.KEY_BEGIN_TIME,task.getBeginDate());
        values.put(Task.KEY_DESCRIPTION,task.getContent());
        values.put(Task.KEY_IMPORTANCE,task.getImportance());
        values.put(Task.KEY_STATE,task.getState());
        values.put(Task.KEY_END_TIME,task.getEndDate());


        // It's a good practice to use parameter ?, instead of concatenate string

        db.update(Task.TABLE, values, Task.KEY_ID + " = ?", new String[] { String.valueOf(task.ID) });
        Log.e("task sta "+task.getState(),"has changed in db");
        db.close(); // Closing database connection
    }
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
@Override
public Uri insert(Uri uri, ContentValues values) {
    final SQLiteDatabase db = mHelper.getWritableDatabase();
    switch (sMatcher.match(uri)) {
        case URI_BOOKMARK:
            // Ensure that row exists, then update with changed values
            //db.insertWithOnConflict(TABLE_BOOKMARK, null, key, SQLiteDatabase.CONFLICT_IGNORE);
            db.insert(TABLE_BOOKMARK, null, values);

            return uri;
        case URI_CONNECTION:
            db.insert(TABLE_CONNECTION, null, values);

            return uri;
        default:
            throw new UnsupportedOperationException("Unsupported Uri " + uri);
    }
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
@Override
public void onCreate(SQLiteDatabase db) {
	try {
		Log.i(TAG, "Creating ALTERLOG table");
		db.execSQL("create table "+ALTERLOG+" (script text not null, datetime long not null);");
		db.execSQL("create index "+ALTERLOG+"_script_idx on "+ALTERLOG+" (script);");
		Log.i(TAG, "Running create scripts...");
		runAllScripts(db, CREATE_PATH, false);
		Log.i(TAG, "Running alter scripts...");
		runAllScripts(db, ALTER_PATH, true);
		Log.i(TAG, "Running create view scripts...");
		runAllScripts(db, VIEW_PATH, false);
	} catch (Exception ex) {
		throw new RuntimeException("Failed to create database", ex);
	}
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public void deleteForward(int protocol, int dport) {
    lock.writeLock().lock();
    try {
        SQLiteDatabase db = this.getWritableDatabase();
        db.beginTransactionNonExclusive();
        try {
            db.delete("forward", "protocol = ? AND dport = ?",
                    new String[]{Integer.toString(protocol), Integer.toString(dport)});

            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    } finally {
        lock.writeLock().unlock();
    }

    notifyForwardChanged();
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
/**
 * 创建闹钟记录数据表
 * @param db 数据库
 */
private void createAlarmRecord(SQLiteDatabase db){
    String createSQL = "CREATE TABLE [" + AlarmRecordMng.TABLE_NAME + "] (";
    createSQL += "[alarm_index] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ";
    createSQL += "[action_type] INTEGER,";
    createSQL += "[alarm_time] INTEGER,";
    createSQL += "[date_year] INTEGER,";
    createSQL += "[date_month] INTEGER,";
    createSQL += "[date_day] INTEGER,";
    createSQL += "[title] TEXT,";
    createSQL += "[content] TEXT,";
    createSQL += "[display] INTEGER,";
    createSQL += "[pause] INTEGER,";
    createSQL += "[create_time] INTEGER)";

    // 执行创建表的SQL语句
    db.execSQL(createSQL);
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public List<ItemData> getAll(){
    SQLiteDatabase db = getReadableDatabase();
    List<ItemData> res = new LinkedList<ItemData>();
    Cursor cur = db.rawQuery("SELECT  id, text FROM data", null);
    if (!cur.moveToFirst()){
        cur.close();
        return res;
    }

    do {
        ItemData data = new ItemData();
        data.id = cur.getLong(0);
        data.text = cur.getString(1);
        res.add(data);
    } while (cur.moveToNext());

    cur.close();
    db.close();

    return res;
}
 

import android.database.sqlite.SQLiteDatabase; //导入依赖的package包/类
public @NonNull List<DatabaseAttachment> getAttachmentsForMessage(long mmsId) {
  SQLiteDatabase           database = databaseHelper.getReadableDatabase();
  List<DatabaseAttachment> results  = new LinkedList<>();
  Cursor                   cursor   = null;

  try {
    cursor = database.query(TABLE_NAME, PROJECTION, MMS_ID + " = ?", new String[] {mmsId+""},
                            null, null, null);

    while (cursor != null && cursor.moveToNext()) {
      results.add(getAttachment(cursor));
    }

    return results;
  } finally {
    if (cursor != null)
      cursor.close();
  }
}