android.database.SQLException Java Examples

@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();
}
 

@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 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;
}
 

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

@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);
  }
}
 

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

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();
        }

    }
 

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

/**
 * 删除全部的表
 */
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();
    }

}
 

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();
    }

}
 

@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;
}
 

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

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

@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;
}
 

@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);
  }
}
 

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

/**
 * delele by the condition
 * 
 * @param table table the table to delete from
 * @param whereClause whereClause the optional WHERE clause to apply when deleting. Passing null will delete all rows.
 * @param whereArgs whereArgs You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.
 * @return the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.
 */
public int delete(String table, String whereClause, String[] whereArgs) {
	try {
		openDB();
		return mSQLiteDatabase.delete(table, whereClause, whereArgs);
	} catch (SQLException ex) {
		return -1;
	}
}
 

void appendSql(@NonNull StringBuilder sb, @NonNull SimpleArrayMap<String, LinkedList<String>> systemRenamedTables) {
  final Table<?> table = this.table;
  final LinkedList<String> aliases;
  if (table.hasAlias || (aliases = systemRenamedTables.get(table.name)) == null) {
    sb.append(nameInQuery);
  } else {
    if (aliases.size() > 1) {
      throw new SQLException("Ambiguous column " + nameInQuery + "; aliases=" + aliases);
    }
    sb.append(aliases.getFirst())
        .append('.')
        .append(name);
  }
}
 

@Thunk boolean ensureRankColumn(SQLiteDatabase db) {
    try {
        // Make sure rank exists
        Cursor c = db.rawQuery("SELECT rank FROM favorites;", null);
        if (c != null) {
            c.close();
        }
    } catch (SQLException ex) {
        // Old version remains, which means we wipe old data
        Log.e(TAG, ex.getMessage(), ex);
        return addIntegerColumn(db, Favorites.RANK, 0);
    }
    return true;
}
 

/**
 * Saves a song's equalizer/audio effect settings to the database.
 */

public void addEQValues(int fiftyHertz,
                        int oneThirtyHertz,
                        int threeTwentyHertz,
                        int eightHundredHertz,
                        int twoKilohertz,
                        int fiveKilohertz,
                        int twelvePointFiveKilohertz,
                        int virtualizer,
                        int bassBoost,
                        int reverb,
                        int volume) {

    ContentValues values = new ContentValues();
    values.put(EQ_50_HZ, fiftyHertz);
    values.put(EQ_130_HZ, oneThirtyHertz);
    values.put(EQ_320_HZ, threeTwentyHertz);
    values.put(EQ_800_HZ, eightHundredHertz);
    values.put(EQ_2000_HZ, twoKilohertz);
    values.put(EQ_5000_HZ, fiveKilohertz);
    values.put(EQ_12500_HZ, twelvePointFiveKilohertz);
    values.put(VIRTUALIZER, virtualizer);
    values.put(BASS_BOOST, bassBoost);
    values.put(REVERB, reverb);
    values.put(VOLUME, volume);


    try {
        getDatabase().insertOrThrow(EQUALIZER_TABLE, null, values);
    } catch (SQLException e) {
        e.printStackTrace();
    }

}
 

private AuthorInfo getAuthorInfoByBookId(int book_id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor authorInformationCursor;
    try {

        authorInformationCursor = db.rawQuery(
                SQL.SELECT +
                        BooksInformationDBContract.AuthorEntry.COLUMN_NAME_ID + "," +
                        BooksInformationDBContract.AuthorEntry.COLUMN_NAME_DEATH_HIJRI_YEAR + "," +
                        BooksInformationDBContract.AuthorEntry.COLUMN_NAME_INFORMATION + "," +
                        BooksInformationDBContract.AuthorEntry.COLUMN_NAME_NAME +

                        SQL.FROM + BooksInformationDBContract.AuthorEntry.TABLE_NAME +
                        SQL.WHERE + BooksInformationDBContract.AuthorEntry.COLUMN_NAME_ID + " = " +
                        "(" + SQL.SELECT + BooksInformationDBContract.BooksAuthors.COLUMN_NAME_AUTHOR_ID +
                        SQL.FROM + BooksInformationDBContract.BooksAuthors.TABLE_NAME +
                        SQL.WHERE + BooksInformationDBContract.BooksAuthors.COLUMN_NAME_BOOK_ID + "=?" +
                        ")"
                , new String[]{String.valueOf(book_id)}
        );
        if (authorInformationCursor.moveToFirst()) {
            AuthorInfo authorInfo = new AuthorInfo(
                    authorInformationCursor.getInt(authorInformationCursor.getColumnIndex(BooksInformationDBContract.AuthorEntry.COLUMN_NAME_ID)),
                    authorInformationCursor.getString(authorInformationCursor.getColumnIndex(BooksInformationDBContract.AuthorEntry.COLUMN_NAME_NAME)),
                    authorInformationCursor.getString(authorInformationCursor.getColumnIndex(BooksInformationDBContract.AuthorEntry.COLUMN_NAME_INFORMATION)),
                    authorInformationCursor.getInt(authorInformationCursor.getColumnIndex(BooksInformationDBContract.AuthorEntry.COLUMN_NAME_DEATH_HIJRI_YEAR))
            );
            authorInformationCursor.close();
            return authorInfo;
        }
        authorInformationCursor.close();

    } catch (SQLException e) {
        Timber.e("Catch a SQLiteException when getAuthorInfoByBookId: ", e);
    }
    return null;

}
 

@Override
public Uri insert(@NonNull Uri uri, ContentValues values) {
    // COMPLETED (1) Get access to the task database (to write new data to)
    final SQLiteDatabase db = mTaskDbHelper.getWritableDatabase();

    // COMPLETED (2) Write URI matching code to identify the match for the tasks directory
    int match = sUriMatcher.match(uri);
    Uri returnUri;

    switch (match) {
        case TASKS:
            // COMPLETED (3) Insert new values into the database
            long id = db.insert(TaskEntry.TABLE_NAME, null, values);
            if (id > 0) {
                returnUri = ContentUris.withAppendedId(TaskEntry.CONTENT_URI, id);
            } else {
                throw new SQLException("Failed to insert row " + uri);
            }
            break;
        // COMPLETED (4) Set the value for the returnedUri and write the default case for unknown URI's
        default:
            throw new UnsupportedOperationException("Unknown uri: " + uri);
    }

    // COMPLETED (5) Notify the resolver if the uri has been changed, and return the newly inserted URI
    getContext().getContentResolver().notifyChange(uri, null);

    return returnUri;
}
 

public void open() {
	try {
		sqLiteDB = dbOpenHelper.getWritableDatabase();
	} catch (SQLException s) {
		new Exception("Error with DB Open");
	}
}
 

public DataService(Context context) {
    try {
        DatabaseHelper dbHelper = DatabaseHelper
                .createDataBaseHelper(context);
        dao = dbHelper.getDao(Data.class);
        dao.showSql(true);
    } catch (SQLException e) {
        e.printStackTrace();
        android.util.Log.e(TAG, "DataService init fail!");
    }
}
 

public void updateRecordUUID(int recordId, String uuid) {
    SQLiteDatabase database = storage.getWritableDatabase();
    try {
        try {
            database.execSQL("UPDATE " + Storage.Record.TABLE_NAME + " SET " +
                    Storage.Record.COLUMN_UPLOAD_ID + " = ? WHERE " +
                    Storage.Record.COLUMN_ID + " = ?", new Object[]{uuid, recordId});
        } catch (SQLException sqlException) {
            LOGGER.error(sqlException.getLocalizedMessage(), sqlException);
        }
    } finally {
        database.close();
    }
}
 

@Override
public void setStopReason(int stopReason) throws DatabaseIOException {
  ensureInitialized();
  try {
    ContentValues values = new ContentValues();
    values.put(COLUMN_STOP_REASON, stopReason);
    SQLiteDatabase writableDatabase = databaseProvider.getWritableDatabase();
    writableDatabase.update(tableName, values, WHERE_STATE_IS_TERMINAL, /* whereArgs= */ null);
  } catch (SQLException e) {
    throw new DatabaseIOException(e);
  }
}
 

private boolean addIntegerColumn(SQLiteDatabase db, String columnName, long defaultValue) {
    db.beginTransaction();
    try {
        db.execSQL("ALTER TABLE favorites ADD COLUMN "
                + columnName + " INTEGER NOT NULL DEFAULT " + defaultValue + ";");
        db.setTransactionSuccessful();
    } catch (SQLException ex) {
        ex.printStackTrace();
        return false;
    } finally {
        db.endTransaction();
    }
    return true;
}
 

public void testInsertTwice()
{
    Object obj = createEntity(nextPk());
    dao.insert(obj);
    try
    {
        dao.insert(obj);
        fail("Inserting twice should not work");
        return;
    }
    catch (SQLException sqlexception)
    {
        return;
    }
}
 

/**
 * How many observations are there?
 * 
 * @param rowId
 * @return
 * @throws SQLException
 * 
 */
public long fetchObservationMaxID() throws SQLException {
	open();
	Cursor mCount = mDB.rawQuery("SELECT COUNT(*) FROM "
			+ OBSERVATIONS_TABLE, null);
	mCount.moveToFirst();
	long rowId = mCount.getInt(0);
	mCount.close();
	return rowId;
}