fulldatabaseactivity.java

Android从入门到精通

		LogCursorInfo(c);
		c.close();
		
		// Drop our tables
		Log.i(DEBUG_TAG, "Dropping tables, ditch the data");
		mDatabase.execSQL(DROP_BOOK_TABLE);
		mDatabase.execSQL(DROP_AUTHOR_TABLE);

		// Drop our triggers
		Log.i(DEBUG_TAG, "Drop triggers");
		mDatabase.execSQL("DROP TRIGGER IF EXISTS fk_insert_book;");
		mDatabase.execSQL("DROP TRIGGER IF EXISTS fk_update_book;");
		mDatabase.execSQL("DROP TRIGGER IF EXISTS fk_delete_author;");

		// Close the database
		Log.i(DEBUG_TAG, "Close the database");
		mDatabase.close();
		Log.i(DEBUG_TAG, "Database Open?  " + mDatabase.isOpen());

		// Delete the database file itself
		Log.i(DEBUG_TAG, "Delete the database");
		deleteDatabase(DATABASE_NAME);

		Log.i(DEBUG_TAG, "End Database Example");
	}

	// This function iterates a Cursor and prints its contents
	// Note: Sqlite databases are not strongly typed, so you can pull everything out as a string, or you can use the
	// appropriate get call to enforce type safety. 
	// In this case, we are just logging so we treat all columns as strings using getString() method
	public void LogCursorInfo(Cursor c) {
		Log.i(DEBUG_TAG, "*** Cursor Begin *** " + " Results:" + c.getCount() + " Columns: " + c.getColumnCount());

		// Print column names
		String rowHeaders = "|| ";
		for (int i = 0; i < c.getColumnCount(); i++) {

			rowHeaders = rowHeaders.concat(c.getColumnName(i) + " || ");
		}
		Log.i(DEBUG_TAG, "COLUMNS " + rowHeaders);

		// Print records
		c.moveToFirst();
		while (c.isAfterLast() == false) {
			String rowResults = "|| ";
			for (int i = 0; i < c.getColumnCount(); i++) {
				rowResults = rowResults.concat(c.getString(i) + " || ");
			}
			Log.i(DEBUG_TAG, "Row " + c.getPosition() + ": " + rowResults);

			c.moveToNext();
		}
		Log.i(DEBUG_TAG, "*** Cursor End ***");
	}

	// This function adds some data to our database
	// We use a transaction here. The transaction is not required, but it can be helpful 
	// if you want to be able to rollback changes if something goes wrong in the middle of a bunch
	// of database changes. 
	// For example, there's no point in adding Books if their Author record failed to insert properly
	public void addSomeBooks() {
		Log.i(DEBUG_TAG, "Database Transaction Start");
		mDatabase.beginTransaction();
		try {
			// Log some transaction diagnostics
			Log.i(DEBUG_TAG, "Database Transaction?  "
					+ mDatabase.inTransaction());
			Log.i(DEBUG_TAG, "Database Locked by current thread?  "
					+ mDatabase.isDbLockedByCurrentThread());

			// ADD SOME VALUES
			Date today = new Date(java.lang.System.currentTimeMillis());

			Author author = new Author("J.K.", "Rowling");
			addAuthor(author); // FYI - this sets the ID appropriately, which is then used by the addBook calls

			addBook(new Book("Harry Potter and the Sorcerer's Stone", today,
					author));
			addBook(new Book("Harry Potter and the Chamber of Secrets", today,
					author));
			addBook(new Book("Harry Potter and the Prisoner of Azkaban", today,
					author));
			addBook(new Book("Harry Potter and the Goblet of Fire", today,
					author));
			addBook(new Book("Harry Potter and the Order of the Phoenix",
					today, author));
			addBook(new Book("Harry Potter and the Half-Blood Prince", today,
					author));
			addBook(new Book("Harry Potter and the Deathly Hallows", today,
					author));
			
			Author author2 = new Author("Stephen", "Colbert");
			addAuthor(author2); 

			addBook(new Book("I Am America (And So Can You!)", today,
					author2));
				
			Author author3 = new Author("Antoine", "de Saint-Exupery");
			addAuthor(author3); 

			addBook(new Book("Le Petit Prince", today,
					author3));

			mDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			// Transaction failed. Failed! Do something here.
			// For example, if you got an exception for a readonly db, 
			// you could open the database for write and retry the transaction. 
			// It's up to you. We just log that we had a problem. We do not 
			// commit our changes, but roll them back, by NOT calling setTransactionSuccessful().
			Log.i(DEBUG_TAG,"Transaction failed. Exception: " + e.getMessage());
		} finally {
			mDatabase.endTransaction();
		}
		Log.i(DEBUG_TAG, "Database Transaction End");
	}

	// Add a book to the book table
	public void addBook(Book newBook) {
		ContentValues values = new ContentValues();
		values.put("title", newBook.mTitle);
		values.put("dateadded", newBook.mDateAdded.toLocaleString());
		values.put("authorid", newBook.mAuthor.mAuthorId);
		newBook.mBookId = mDatabase.insertOrThrow(TABLE_BOOK, null, values);
		Log.i(DEBUG_TAG, "Added Book:  " + newBook.mTitle + "(ID=" + newBook.mBookId + ")");
	}

	// Add an author to the author table
	public void addAuthor(Author newAuthor) {
		ContentValues values = new ContentValues();
		values.put("firstname", newAuthor.mFirstName);
		values.put("lastname", newAuthor.mLastName);
		newAuthor.mAuthorId = mDatabase.insertOrThrow(TABLE_AUTHOR, null,
				values);
		Log.i(DEBUG_TAG, "Added Author:  " + newAuthor.mFirstName + " " + newAuthor.mLastName + "(ID=" + newAuthor.mAuthorId + ")");
	}

	// Update a record in the book table with a new title
	public void updateBookTitle(String newtitle, Integer bookId) {
		ContentValues values = new ContentValues();
		values.put("title", newtitle);
		mDatabase.update(TABLE_BOOK, values, "id=?", new String[] { bookId.toString() });
	}

	// Delete a single book by its id
	public void deleteBook(Integer bookId) {
		mDatabase.delete(TABLE_BOOK, "id=?", new String[] { bookId.toString() });
		Log.i(DEBUG_TAG, "Deleted Book Id:  " + bookId.toString());
	}

	// Delete all books by an author by authorid
	public void deleteBooksByAuthor(Integer authorID) {
		int numBooksDeleted = mDatabase.delete(TABLE_BOOK, "authorid=?", new String[] { authorID.toString() });
		Log.i(DEBUG_TAG, "Deleted " + numBooksDeleted + " books with Author Id:  " + authorID.toString());
	}
	
	// Delete a single author by its id
	public void deleteAuthor(Integer authorId) {
		mDatabase.delete(TABLE_AUTHOR, "id=?", new String[] { authorId.toString() });
		Log.i(DEBUG_TAG, "Deleted Author Id:  " + authorId.toString());
	}

	// Helper class to encapsulate Author information programmatically
	class Author {
		String mFirstName;
		String mLastName;
		long mAuthorId;

		public Author(String firstName, String lastName) {
			mFirstName = firstName;
			mLastName = lastName;
			mAuthorId = -1;

		}
	}

	// Helper class to encapsulate Book information programmatically
	class Book {
		String mTitle;
		Date mDateAdded;
		long mBookId;
		Author mAuthor;

		public Book(String title, Date dateAdded, Author author) {
			mTitle = title;
			mDateAdded = dateAdded;
			mAuthor = author;
			mBookId = -1;

		}
	}

}