Code example for SQLiteDatabase

Methods: deleteinsertqueryupdate

0
		values.put(COLUMN_HISTORY_SPIELER, playerId);
		values.put(COLUMN_HISTORY_STRAFE, fineId);
		values.put(getColumnHistoryBezahlt(), paied);
		values.put(getColumnHistoryTyp(), type);
 
		Long rowid = db.insert(getTableHistory(), null, values);
		if (rowid < 0) {
			Log.e(TAG, "database insert failed: " + rowid);
		} else { 
			Log.d(TAG, "database insert success, rowid = " + rowid);
		} 
 
		return rowid;
	} 
 
	/* ************************************************************** 
	 * From here on: Public methods to delete stuff from the database 
	 * ************************************************************** 
	 */ 
 
	/** 
	 * Removes a player and all the history that was connected to him from the 
	 * database. 
	 *  
	 * @param _id 
	 *            the id of the player 
	 * @return the number of deleted entries (player + number of his history 
	 *         entries). 0 if nothing was deleted. 
	 */ 
	public int removePlayer(int _id) {
		Log.d(TAG, "removePlayer: id = " + _id);
 
		/* now delete the player himself */ 
		int deleted = db.delete(getTableSpieler(), getColumnSpielerId() + "=" + _id,
				null); 
		/* 
		 * remove history entries, associated with the player ("normal" AND 
		 * birthday) 
		 */ 
		deleted += db.delete(getTableHistory(), COLUMN_HISTORY_SPIELER + "=" + _id,
				null); 
		/* log */ 
		if (deleted == 0) {
			Log.e(TAG, "nothing deleted");
		} else { 
			Log.d(TAG, "row delete success, number of rows deleted: " + deleted);
		} 
		return deleted;
	} 
 
	/** 
	 * Removes a fine and all the history that was connected to it from the 
	 * database. 
	 *  
	 * @param _id 
	 *            the id of the fine 
	 * @return the number of deleted entries (fine + number of its history 
	 *         appearences). 0 if nothing was deleted. 
	 */ 
	public int removeFine(int _id) {
		Log.d(TAG, "removeFine: id = " + _id);
 
		/* remove fine */ 
		int deleted = db.delete(TABLE_STRAFEN, getColumnStrafenId() + "=" + _id,
				null); 
		/* remove all history that used that fine */ 
		deleted += db.delete(getTableHistory(), COLUMN_HISTORY_STRAFE + "=" + _id,
				null); 
		/* log */ 
		if (deleted == 0) {
			Log.e(TAG, "nothing deleted");
		} else { 
			Log.d(TAG, "row delete success, number of rows deleted: " + deleted);
		} 
		return deleted;
	} 
 
	/** 
	 * Removes a history entry. 
	 *  
	 * @param _id 
	 *            the id of the history entry 
	 * @return 1 if entry was deleted. 0 if nothing was deleted. 
	 */ 
	public int removeHistory(int _id) {
		Log.d(TAG, "removeHistory: id = " + _id);
 
		/* remove a single history entry */ 
		int deleted = db.delete(getTableHistory(), COLUMN_HISTORY_ID + " = '" + _id
				+ "'", null); 
		/* log */ 
		if (deleted == 0) {
			Log.e(TAG, "nothing deleted");
		} else { 
			Log.d(TAG, "row delete success, number of rows deleted: " + deleted);
		} 
		return deleted;
	} 
 
	/* ****************************************************** 
	 * From here on: Public methods to read from the database 
	 * ****************************************************** 
	 */ 
 
	public int getCount(Cursor c) {
		Log.d(TAG, "Cursor getCount()");
		try { 
			Log.d(TAG, "count = " + c.getCount());
			return c.getCount();
		} catch (Exception e) {
			Log.e(TAG, "database exception: " + e);
			return -1; 
		} 
	} 
 
	public Cursor getAllHistory(String orderBy, boolean enableBirthday) {
		String MY_QUERY = "SELECT " + getTableHistory() + "." + COLUMN_HISTORY_ID
				+ ", " + getTableHistory() + "." + getColumnHistoryDatum() + ", " 
				+ getTableSpieler() + "." + getColumnSpielerName() + ", " 
				+ TABLE_STRAFEN + "." + getColumnStrafenBezeichnung() + ", "
				+ getTableHistory() + "." + getColumnHistoryBezahlt() + ", " 
				+ getTableHistory() + "." + getColumnHistoryTyp() 
 
				+ " FROM " + getTableHistory() + ", " + TABLE_STRAFEN + ", "
				+ getTableSpieler() + " WHERE " + getTableSpieler() + "." 
				+ getColumnSpielerId() + " = " + getTableHistory() + "." 
				+ COLUMN_HISTORY_SPIELER + " AND " + TABLE_STRAFEN + "."
				+ getColumnStrafenId() + " = " + getTableHistory() + "." 
				+ COLUMN_HISTORY_STRAFE;
		// query to exclude birthdays 
		if (!enableBirthday) {
			MY_QUERY += " AND " + getTableHistory() + "." + getColumnHistoryTyp()
					+ " != " + getColumnHistoryTypGeburtstag(); 
		} 
		// query if birthdays are shown: hide future birthday entries 
		else if (enableBirthday) {
			MY_QUERY += " AND (" + getTableHistory() + "."
					+ getColumnHistoryDatum() + " <= date('now') OR " 
					+ getTableHistory() + "." + getColumnHistoryTyp() + " != '" 
					+ getColumnHistoryTypGeburtstag() + "')"; 
		} 
		MY_QUERY += " ORDER BY " + orderBy + ";";
		Log.d(TAG, MY_QUERY);
 
		return db.rawQuery(MY_QUERY, null);
	} 
 
	public Cursor getAllFines() {
		String[] columns = { getColumnStrafenId(), getColumnStrafenBezeichnung(),
				getColumnStrafenKosten() }; 
		return db.query(TABLE_STRAFEN, columns, "NOT " + getColumnStrafenId()
				+ " = " + BIRTHDAY_ID, null, null, null,
				getColumnStrafenBezeichnung()); 
	} 
 
	public Cursor getAllPlayers() {
		String[] columns = { getColumnSpielerId(), getColumnSpielerName(),
				getColumnSpielerGeburtstag(), COLUMN_SPIELER_HISTORY_ID };
		return db.query(getTableSpieler(), columns, null, null, null, null,
				getColumnSpielerName()); 
	} 
 
	public Cursor getSinglePlayer(int _id) {
		String[] columns = { getColumnSpielerId(), getColumnSpielerName(),
				getColumnSpielerGeburtstag(), COLUMN_SPIELER_HISTORY_ID };
		return db.query(getTableSpieler(), columns,
				getColumnSpielerId() + " = " + _id, null, null, null,
				getColumnSpielerName()); 
	} 
 
	/** 
	 * Checks the database if a history entry is already paied or not 
	 *  
	 * @param _id 
	 *            the id of the row in the History-Database 
	 * @return 0 for "not paied" or 1 for "paied" 
	 */ 
	public int getPaiedState(int _id) {
		String[] columns = { getColumnHistoryBezahlt() };
		Cursor c = db.query(getTableHistory(), columns, COLUMN_HISTORY_ID + " = '"
				+ _id + "'", null, null, null, null);
		if (c.getCount() != 0) {
			c.moveToFirst();
			int i = c
					.getInt(c.getColumnIndexOrThrow(getColumnHistoryBezahlt()));
			return i;
		} else 
			return -1; 
	} 
 
	/** 
	 * Checks if a player has a birthday set, or not 
	 *  
	 * @param _id 
	 *            the id of the player 
	 * @return true if birthday is set, else false 
	 */ 
	public boolean getBirthdaySetStatus(int _id) {
		String[] columns = { getColumnSpielerGeburtstag() };
		Cursor c = db.query(getTableSpieler(), columns, getColumnSpielerId() + " = '"
				+ _id + "'", null, null, null, null);
		c.moveToFirst();
		if (c.isNull(c.getColumnIndex(getColumnSpielerGeburtstag()))) {
			return false; 
		} else { 
			return true; 
		} 
	} 
 
	/** 
	 * @param i 
	 *            0 = only unpaied; 1 = only paied; 2 = all 
	 * @return -1 if error 
	 */ 
	public double getCash(int i) {
		String MY_QUERY = "SELECT TOTAL(" + TABLE_STRAFEN + "."
				+ getColumnStrafenKosten() + ") FROM " + getTableHistory() + ", " 
				+ TABLE_STRAFEN + " WHERE " + TABLE_STRAFEN + "."
				+ getColumnStrafenId() + " = " + getTableHistory() + "." 
				+ COLUMN_HISTORY_STRAFE;
		// get all combined 
		if (i == 2) {
			MY_QUERY += ";";
			// get only paied/unpaied 
		} else { 
			MY_QUERY += " AND " + getTableHistory() + "."
					+ getColumnHistoryBezahlt() + " = " + i + ";";
		} 
		Cursor c = db.rawQuery(MY_QUERY, null);
		c.moveToFirst();
 
		if (c.getCount() == 0) {
			return -1; 
		} else { 
			return c.getDouble(0);
		} 
	} 
 
	/* ****************************************************** 
	 * From here on: Public methods to edit data 
	 * ****************************************************** 
	 */ 
 
	public int togglePaiedState(int _id) {
		try { 
			ContentValues cv = new ContentValues();
			int paiedState = getPaiedState(_id);
 
			switch (paiedState) {
			case 0: // not paied -> paied 
				cv.put(getColumnHistoryBezahlt(), 1);
				return db.update(getTableHistory(), cv, COLUMN_HISTORY_ID + " = '"
						+ _id + "'", null);
			case 1: // paied -> not paied 
				cv.put(getColumnHistoryBezahlt(), 0);
				return db.update(getTableHistory(), cv, COLUMN_HISTORY_ID + " = '"
						+ _id + "'", null);
			default: 
				return -1; 
			} 
		} catch (Exception e) {
			Log.e(TAG, "database exception: " + e);
			return -1; 
		} 
	} 
 
	public int changePlayer(int _id, String newName, String newBirthday) {
		try { 
			ContentValues cv = new ContentValues();
			cv.put(getColumnSpielerName(), newName);
			cv.put(getColumnSpielerGeburtstag(), newBirthday);
			Log.d(TAG, "player updated to: " + newName + ", " + newBirthday);
 
			/* no birthday set before */ 
			if (!getBirthdaySetStatus(_id)) {
				/* new birthday will be set */ 
				if (newBirthday != null) {
					/* change year to current year for the history table */ 
					newBirthday = changeYearToCurrentYear(newBirthday);
					/* new (birthday) history */ 
					long hId = addNewHistory(newBirthday, _id, -2, 0,
							getColumnHistoryTypGeburtstag()); 
					/* add the just added history id to the player */ 
					changePlayerBirthdayHistoryId(_id, (int) hId);
				} 
				// else: do nothing extra 
			} 
 
			/* birthday was set before for this player */ 
			else { 
				/* update to a new birthday-value */ 
				if (newBirthday != null) {
					/* change year to current year for the history table */ 
					newBirthday = changeYearToCurrentYear(newBirthday);
					/* 
					 * update current history entry (set the new date) 
					 * (birthdayHistoryId stays the same) 
					 */ 
					ContentValues cv2 = new ContentValues();
					cv2.put(getColumnHistoryDatum(), newBirthday);
					db.update(getTableHistory(), cv2, COLUMN_HISTORY_SPIELER
							+ " = " + _id + " AND " + getColumnHistoryTyp()
							+ " = " + getColumnHistoryTypGeburtstag(), null); 
				} 
				/* delete birthday (set it null) */ 
				else { 
					/* remove history entry in History-table */ 
					Cursor c = getSinglePlayer(_id);
					c.moveToFirst();
					removeHistory(c.getInt(c
							.getColumnIndex(COLUMN_SPIELER_HISTORY_ID)));
					/* remove birthdayHistoryId in Spieler-table */ 
					ContentValues values = new ContentValues();
					values.putNull(COLUMN_SPIELER_HISTORY_ID);
					db.update(getTableSpieler(), values, getColumnSpielerId() + " = "
							+ _id, null);
				} 
 
			} 
 
			/* update player-name and his birthday */ 
			return db.update(getTableSpieler(), cv, getColumnSpielerId() + " = '"
					+ _id + "'", null);
		} catch (Exception e) {
			Log.e(TAG, "database exception: " + e);
			return -1; 
		} 
	} 
 
	public int changePlayerBirthdayHistoryId(int playerId, int historyId) {
		try { 
			ContentValues cv = new ContentValues();
			cv.put(getColumnSpielerId(), playerId);
			cv.put(COLUMN_SPIELER_HISTORY_ID, historyId);
			Log.d(TAG, "playerBirthdayHistoryId of player " + playerId
					+ " updated to: " + historyId);
			return db.update(getTableSpieler(), cv, getColumnSpielerId() + " = '"
					+ playerId + "'", null);
		} catch (Exception e) {
			Log.e(TAG, "database exception: " + e);
			return -1; 
		} 
Experience pair programming with AI  Get Codota for Java