android.database.CursorIndexOutOfBoundsException#android.database.SQLException源码实例Demo

@Override
public List<T> query(QueryBuilder queryBuilder, String... columns) {
    final StrictMode.ThreadPolicy oldPolicy = StrictMode.allowThreadDiskWrites();
    final ArrayList<T> list = new ArrayList<>();
    try {
       final SQLiteDatabase db = mDatabaseHelper.getReadableDatabase();
        final Cursor cursor = query(db, queryBuilder, columns);
        T obj = null;
        while (cursor.moveToNext()) {
            obj = DatabaseUtils.cursorToClassObject(mClazz, cursor, columns);
            list.add(obj);
        }
        cursor.close();
    } catch (Exception e) {
        throw new SQLException(mTableName, e);
    }
    StrictMode.setThreadPolicy(oldPolicy);
    return list;
}
 

@Override
public void storeFully(HashMap<String, CachedContent> content) throws IOException {
  try {
    SQLiteDatabase writableDatabase = databaseProvider.getWritableDatabase();
    writableDatabase.beginTransactionNonExclusive();
    try {
      initializeTable(writableDatabase);
      for (CachedContent cachedContent : content.values()) {
        addOrUpdateRow(writableDatabase, cachedContent);
      }
      writableDatabase.setTransactionSuccessful();
      pendingUpdates.clear();
    } finally {
      writableDatabase.endTransaction();
    }
  } catch (SQLException e) {
    throw new DatabaseIOException(e);
  }
}
 

public static void addFilterKeyword(int type, Collection<String> words) {

        DatabaseUtils.InsertHelper ih = new DatabaseUtils.InsertHelper(getWsd(), FilterTable.TABLE_NAME);
        final int nameColumn = ih.getColumnIndex(FilterTable.NAME);
        final int activeColumn = ih.getColumnIndex(FilterTable.ACTIVE);
        final int typeColumn = ih.getColumnIndex(FilterTable.TYPE);
        try {
            getWsd().beginTransaction();
            for (String word : words) {
                ih.prepareForInsert();

                ih.bind(nameColumn, word);
                ih.bind(activeColumn, true);
                ih.bind(typeColumn, type);

                ih.execute();
            }

            getWsd().setTransactionSuccessful();
        } catch (SQLException e) {
        } finally {
            getWsd().endTransaction();
            ih.close();
        }

    }
 

@Override
public int update(Collection<T> paramTs, String... columns) throws SQLException {
    final StrictMode.ThreadPolicy oldPolicy = StrictMode.allowThreadDiskWrites();
    final SQLiteDatabase db = mDatabaseHelper.getWritableDatabase();
    int result = -1;
    try {
        db.beginTransaction();
        for (final T paramT : paramTs) {
            result = result + db.update(mTableName, DatabaseUtils.getContentValues(paramT, columns), DatabaseUtils.getIdColumnName(mClazz) + "=?", new String[]{"" + DatabaseUtils.getIdValue(paramT)});
        }
        db.setTransactionSuccessful();
    } catch (Exception e) {
        throw new SQLException(mTableName, e);
    } finally {
        db.endTransaction();
    }
    StrictMode.setThreadPolicy(oldPolicy);
    return result;
}
 

@Override
public int create(Collection<T> paramTs) throws SQLException {
    final StrictMode.ThreadPolicy oldPolicy = StrictMode.allowThreadDiskWrites();
    final SQLiteDatabase db = mDatabaseHelper.getWritableDatabase();
    long result = -1;
    try {
        db.beginTransaction();
        for (final T paramT : paramTs) {
            result = result + db.insert(mTableName, null, DatabaseUtils.getContentValues(paramT));
        }
        db.setTransactionSuccessful();
    } catch (Exception e) {
        throw new SQLException(mTableName, e);
    } finally {
        db.endTransaction();
    }
    StrictMode.setThreadPolicy(oldPolicy);
    return (int) result;
}
 

/**
 * Create an expression to check this column against several values.
 * <p>
 * SQL: this NOT IN (values...)
 *
 * @param values The values to test against this column
 * @return Expression
 */
@NonNull
@CheckResult
public final Expr notIn(@NonNull Iterable<Long> values) {
  final Iterator<Long> iterator = values.iterator();
  if (!iterator.hasNext()) {
    throw new SQLException("Empty IN clause values");
  }
  final ArrayList<String> args = new ArrayList<>();
  final StringBuilder sb = new StringBuilder();
  sb.append(" NOT IN (");

  boolean first = true;
  while (iterator.hasNext()) {
    if (first) {
      first = false;
    } else {
      sb.append(',');
    }
    sb.append('?');
    args.add(iterator.next().toString());
  }
  sb.append(')');
  return new ExprN(this, sb.toString(), args.toArray(new String[args.size()]));
}
 

@Override
public void storeIncremental(HashMap<String, CachedContent> content) throws IOException {
  if (pendingUpdates.size() == 0) {
    return;
  }
  try {
    SQLiteDatabase writableDatabase = databaseProvider.getWritableDatabase();
    writableDatabase.beginTransaction();
    try {
      for (int i = 0; i < pendingUpdates.size(); i++) {
        CachedContent cachedContent = pendingUpdates.valueAt(i);
        if (cachedContent == null) {
          deleteRow(writableDatabase, pendingUpdates.keyAt(i));
        } else {
          addOrUpdateRow(writableDatabase, cachedContent);
        }
      }
      writableDatabase.setTransactionSuccessful();
      pendingUpdates.clear();
    } finally {
      writableDatabase.endTransaction();
    }
  } catch (SQLException e) {
    throw new DatabaseIOException(e);
  }
}
 

public Map<String, String> findAllAppName() {
    Map<String, String> map = new HashMap<String, String>();

    SQLiteDatabase db = null;
    Cursor c = null;
    try {
        db = mAppNameDbHelper.openDatabase();
        // System.out.println("---->canonicalPath:" + canonicalPath);
        c = db.rawQuery(SQL_FIND_ALL_APP_NAME, null);
        while (c.moveToNext()) {
            String dir = c.getString(0);
            String appName = c.getString(1);
            map.put(dir, appName);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (c != null) {
            c.close();
        }
    }

    return map;
}
 

private static void add(String accountId, List<String> list) {
    if (list == null || list.size() == 0) {
        return;
    }

    DatabaseUtils.InsertHelper ih = new DatabaseUtils.InsertHelper(getWsd(), TopicTable.TABLE_NAME);
    final int accountidColumn = ih.getColumnIndex(TopicTable.ACCOUNTID);
    final int nameColumn = ih.getColumnIndex(TopicTable.TOPIC_NAME);
    try {
        getWsd().beginTransaction();
        for (int i = 0; i < list.size(); i++) {
            String name = list.get(i);
            ih.prepareForInsert();
            ih.bind(accountidColumn, accountId);
            ih.bind(nameColumn, name);
            ih.execute();
        }
        getWsd().setTransactionSuccessful();
    } catch (SQLException e) {
    } finally {
        getWsd().endTransaction();
        ih.close();
    }

}
 

/**
 * 删除全部的表
 */
public void dropAllTable() {

    Cursor cursor = db.rawQuery(
            "SELECT name FROM sqlite_master WHERE type ='table'", null);
    if (cursor != null) {
        cursor.moveToFirst();
        while (cursor.moveToNext()) {
            try {
                dropTable(cursor.getString(0));
            } catch (SQLException ignored) {
            }
        }
    }
    if (cursor != null) {
        cursor.close();
    }

}
 

public int save(SqlStorage<FormDefRecordV12> formDefRecordStorage) {
    // if we don't have a path to the file, the rest are irrelevant.
    // it should fail anyway because you can't have a null file path.
    if (StringUtils.isEmpty(mFormFilePath)) {
        Logger.log(LogTypes.SOFT_ASSERT, "Empty value for mFormFilePath while saving FormDefRecord");
    }

    // Make sure that the necessary fields are all set
    File form = new File(mFormFilePath);
    if (StringUtils.isEmpty(mDisplayName)) {
        mDisplayName = form.getName();
    }

    if (StringUtils.isEmpty(mFormMediaPath)) {
        mFormMediaPath = getMediaPath(mFormFilePath);
    }

    formDefRecordStorage.write(this);

    if (recordId == -1) {
        throw new SQLException("Failed to save the FormDefRecord " + toString());
    }
    return recordId;
}
 

@Override
public Uri insert(Uri uri, ContentValues values) {
    synchronized (DBLock) {
        String table = matchTable(uri);
        SQLiteDatabase db = getDBHelper().getWritableDatabase();
        long rowId = 0;
        db.beginTransaction();
        try {
            rowId = db.insert(table, null, values);
            db.setTransactionSuccessful();
        } catch (Exception e) {
            Log.e(TAG, e.getMessage());
        } finally {
            db.endTransaction();
        }
        if (rowId > 0) {
            Uri returnUri = ContentUris.withAppendedId(uri, rowId);
            getContext().getContentResolver().notifyChange(uri, null);
            return returnUri;
        }
        throw new SQLException("Failed to insert row into " + uri);
    }
}
 

/**
 * insert mutil records at one time
 * @param table
 * @param listVal
 * @return success return true
 */
public boolean batchInsert(final String table, final List<ContentValues> listVal){
	try {
		openDB();
		DBTransaction.transact(this , new DBTransaction.DBTransactionInterface(){
			@Override
			public void onTransact() {
				for (ContentValues contentValues : listVal) {
					mSQLiteDatabase.insertWithOnConflict(table, null, contentValues,SQLiteDatabase.CONFLICT_REPLACE);
				}
			}
		});
		return true;
	} catch (SQLException ex) {
		ex.printStackTrace();
		throw ex;
	}
}
 

/**
 * Get a single observation
 * 
 * @param rowId
 * @return
 * @throws SQLException
 * 
 */
public Cursor fetchObservation(long rowId) throws SQLException {
	Cursor mCursor =

	mDB.query(true, OBSERVATIONS_TABLE, new String[] { KEY_ROW_ID,
			KEY_LATITUDE, KEY_LONGITUDE, KEY_ALTITUDE, KEY_ACCURACY,
			KEY_PROVIDER, KEY_OBSERVATION_TYPE, KEY_OBSERVATION_UNIT,
			KEY_OBSERVATION_VALUE, KEY_SHARING, KEY_TIME, KEY_TIMEZONE,
			KEY_USERID, KEY_SENSOR_NAME, KEY_SENSOR_TYPE,
			KEY_SENSOR_VENDOR, KEY_SENSOR_RESOLUTION, KEY_SENSOR_VERSION,
			KEY_OBSERVATION_TREND }, KEY_ROW_ID + "=" + rowId, null, null,
			null, null, null);
	if (mCursor != null) {
		mCursor.moveToFirst();
	}
	return mCursor;
}
 

@Override
public Uri insert(@NonNull Uri uri, ContentValues values) {
    final Uri returnUri;
    final int match = sUriMatcher.match(uri);

    switch (match) {
        case VIDEO: {
            long _id = mOpenHelper.getWritableDatabase().insert(
                    VideoContract.VideoEntry.TABLE_NAME, null, values);
            if (_id > 0) {
                returnUri = VideoContract.VideoEntry.buildVideoUri(_id);
            } else {
                throw new SQLException("Failed to insert row into " + uri);
            }
            break;
        }
        default: {
            throw new UnsupportedOperationException("Unknown uri: " + uri);
        }
    }

    mContentResolver.notifyChange(uri, null);
    return returnUri;
}
 

@TargetApi(Build.VERSION_CODES.HONEYCOMB)
@SdkSuppress(minSdkVersion = Build.VERSION_CODES.HONEYCOMB)
@Test public void executeUpdateDeleteThrowsAndDoesNotTrigger() {
  SupportSQLiteStatement statement = real.compileStatement(
      "UPDATE " + TABLE_EMPLOYEE + " SET " + USERNAME + " = 'alice'");

  db.createQuery(TABLE_EMPLOYEE, SELECT_EMPLOYEES)
      .skip(1) // Skip initial
      .subscribe(o);

  try {
    db.executeUpdateDelete(TABLE_EMPLOYEE, statement);
    fail();
  } catch (SQLException ignored) {
  }
  o.assertNoMoreEvents();
}
 

/**
 * Get information on friend by passing in their RepayID
 * @param repayID
 * @return Friend object representation of person
 * @throws IndexOutOfBoundsException
 * @throws android.database.SQLException
 */
public Friend getFriendByRepayID(String repayID) throws IndexOutOfBoundsException, SQLException, NullPointerException{
	Friend friend = null;
	Cursor c = null;
	SQLiteDatabase db = this.getReadableDatabase();
	c = db.query(Names.F_TABLENAME, new String[]{Names.F_REPAYID, Names.F_LOOKUPURI, Names.F_NAME, Names.F_DEBT},
			Names.F_REPAYID+"=?", new String[]{repayID}, null, null, null);
	c.moveToFirst();
	try {
		friend = new Friend(repayID, c.getString(1), c.getString(2), new BigDecimal(c.getString(3)));
	} catch (NullPointerException e) {
		Log.i(TAG, "No ContactURI present, passing null");
		friend = new Friend(repayID, null, c.getString(2), new BigDecimal(c.getString(3)));
	}
	db.close();
	return friend;
}
 

private static void generateTempTables(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
    for (int i = 0; i < daoClasses.length; i++) {
        String tempTableName = null;

        DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
        String tableName = daoConfig.tablename;
        if (!isTableExists(db, false, tableName)) {
            LogManager.getLogger().d(TAG, "[New Table]" + tableName);
            continue;
        }
        try {
            tempTableName = daoConfig.tablename.concat("_TEMP");
            StringBuilder dropTableStringBuilder = new StringBuilder();
            dropTableStringBuilder.append("DROP TABLE IF EXISTS ").append(tempTableName).append(";");
            db.execSQL(dropTableStringBuilder.toString());

            StringBuilder insertTableStringBuilder = new StringBuilder();
            insertTableStringBuilder.append("CREATE TEMPORARY TABLE ").append(tempTableName);
            insertTableStringBuilder.append(" AS SELECT * FROM ").append(tableName).append(";");
            db.execSQL(insertTableStringBuilder.toString());
            LogManager.getLogger().d(TAG,"[Table]" + tableName +"\n ---Columns-->"+getColumnsStr(daoConfig));
            LogManager.getLogger().d(TAG,"[Generate temp table]" + tempTableName);
        } catch (SQLException e) {
            Log.e(TAG, "[Failed to generate temp table]" + tempTableName, e);
        }
    }
}
 

/**
     * Insert into db
     * @param uri
     * @param values
     * @return
     */
    @Override
    public Uri insert(Uri uri, ContentValues values) {
        /**
         * Add a new record
         */
        long rowID;
        Uri _uri = null;
        switch (uriMatcher.match(uri)) {
            case PADLIST:
                rowID = db.insert(PAD_TABLE_NAME, "", values);
                ContentUris.withAppendedId(PADLIST_CONTENT_URI, rowID);
                break;
            case PADGROUP_LIST:
                rowID = db.insert(PADGROUP_TABLE_NAME, "", values);
                ContentUris.withAppendedId(PADGROUPS_CONTENT_URI, rowID);
                break;
            default:
                throw new IllegalArgumentException( "Unknown URI " + uri );
        }
        /**
         * If record is added successfully
         */
        if (rowID > 0)
        {
//            getContext().getContentResolver().notifyChange(_uri, null);
            return _uri;
        }
        throw new SQLException("Failed to add a record into " + uri);

    }
 

@Override
public void setDownloadingStatesToQueued() throws DatabaseIOException {
  ensureInitialized();
  try {
    ContentValues values = new ContentValues();
    values.put(COLUMN_STATE, Download.STATE_QUEUED);
    SQLiteDatabase writableDatabase = databaseProvider.getWritableDatabase();
    writableDatabase.update(tableName, values, WHERE_STATE_IS_DOWNLOADING, /* whereArgs= */ null);
  } catch (SQLException e) {
    throw new DatabaseIOException(e);
  }
}
 

@SuppressWarnings("unchecked")
@NonNull
static <T, N> SelectionColumn<T, T, T, ?, N> from(@NonNull SelectBuilder<?> selectBuilder,
                                                  @NonNull String alias) {
  final Select.SingleColumn<?, ?> columnNode = selectBuilder.columnNode;
  if (selectBuilder.columnsNode != null || columnNode == null) {
    throw new SQLException("Only a single result allowed for a SELECT that is part of a column");
  }
  final Table table = selectBuilder.from.table;
  final Column<?, ?, ?, ?, ?> column = columnNode.column;
  return new SelectionColumn<>(table, alias, false, column.valueParser, column.nullable, alias, alias, selectBuilder);
}
 

/**
 * Add a debt into the database, linked to a RepayID
 * @param repayID
 * @param amount
 * @param description
 * @throws android.database.SQLException
 * @throws NullPointerException
 */
public void addDebt(final String repayID, final BigDecimal amount, String description)
		throws SQLException, NullPointerException
{
	SQLiteDatabase db = this.getWritableDatabase();
	ContentValues values = new ContentValues();
	values.put(Names.D_REPAYID, repayID);
	values.put(Names.D_DATE, new Date().toString());
	values.put(Names.D_AMOUNT, amount.toString());
	values.put(Names.D_DESCRIPTION, description.replaceAll("[-+.^:,']",""));
	db.insert(Names.D_TABLENAME, null, values);
	db.close();
}
 

@Override
public Uri insert(Uri uri, ContentValues values) {
    TABLE_NAME = values.getAsString("table_name");
    values.remove("table_name");

    long rowID = db.insert(TABLE_NAME, "", values);
    if (rowID > 0) {
        Uri _uri = ContentUris.withAppendedId(CONTENT_URI, rowID);
        getContext().getContentResolver().notifyChange(_uri, null);
        return _uri;
    }
    throw new SQLException("Failed to add a record into " + uri);
}
 

/**
 * Given topic name, get it's database _id
 *
 * @param db database
 * @param topic topic name
 * @return _id of the topic
 */
public static long getId(SQLiteDatabase db, String topic) {
    try {
        return db.compileStatement("SELECT " + _ID + " FROM " + TABLE_NAME +
                " WHERE " +
                COLUMN_NAME_ACCOUNT_ID + "=" + BaseDb.getInstance().getAccountId() + " AND " +
                COLUMN_NAME_TOPIC + "='" + topic + "'").simpleQueryForLong();
    } catch (SQLException ignored) {
        // topic not found
        return -1;
    }
}
 

public void updateDebt(Debt debt) throws SQLException, NullPointerException {
	ContentValues values = new ContentValues();
	values.put(Names.D_AMOUNT, debt.getAmount().toString());
	values.put(Names.D_REPAYID, debt.getRepayID());
	values.put(Names.D_DESCRIPTION, debt.getDescription().replaceAll("[-+.^:,']",""));
	values.put(Names.D_DATE, debt.getDate().toString());
	SQLiteDatabase db = this.getWritableDatabase();
	db.update(Names.D_TABLENAME, values, Names.D_DEBTID+"=?",
			new String[]{Integer.toString(debt.getDebtID())});
	db.close();
}
 

private static void executeBatchSQL(String batch) throws SQLException {
    final String[] statements = batch.split("\n");
    final SQLiteDatabase db = Cache.openDatabase();
    db.beginTransaction();
    try {
        for (String query : statements) {
            db.execSQL(query);
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}
 

@Override
public T next() {
    T entity = null;
    if (cursor == null || cursor.isAfterLast()) {
        throw new NoSuchElementException();
    }

    try {
        if (cursor.isBeforeFirst()) {
            cursor.moveToFirst();
        }

        try {

            entity = ModelInflater.inflate(cursor, tableDetails);
        } finally {
            cursor.moveToNext();
        }
    }
    catch (SQLException sqle) {

        cursor.close();
        throw sqle;
    }

    return entity;
}
 

@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
        ex.printStackTrace();
        return false;
    } finally {
        db.endTransaction();
    }
    return true;
}
 

/**
 * 查询所有收藏内容
 * 
 * @return
 */
public List<Favorite> findAllFavorite() {
    List<Favorite> favorites = new ArrayList<Favorite>();
    favorites.clear();
    SQLiteDatabase db = null;
    Cursor c = null;
    try {
        db = mAppNameDbHelper.openDatabase();
        c = db.rawQuery(SQL_FIND_ALL_FAVORITE, null);

        while (c.moveToNext()) {
            String path = c.getString(c.getColumnIndex("path"));
            String name = c.getString(c.getColumnIndex("name"));
            String appName = c.getString(c.getColumnIndex("app_name"));
            int fileType = c.getInt(c.getColumnIndex("file_type"));
            long date = c.getLong(c.getColumnIndex("favorite_time"));
            long size = c.getLong(c.getColumnIndex("size"));
            String extra = c.getString(c.getColumnIndex("extra"));
            Favorite favorite = new Favorite(path, name, appName, fileType, date, size, extra);
            favorites.add(favorite);
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (c != null) {
            c.close();
        }
    }
    return favorites;
}
 

public String getSingleItem(String showId, String column) {
    String singleItem = "";
    Cursor c = mDatabase.query(DATABASE_TABLE, new String[]{column}, KEY_SHOW_ID + " = ?", new String[]{showId}, null, null, null);
    if (c != null) {
        try {
            if (c.moveToFirst())
                singleItem = c.getString(c.getColumnIndex(column));
        } catch (SQLException e) {} finally {
            c.close();
        }
    }
    return singleItem;
}