fulldatabaseactivity.java

Android从入门到精通

package com.androidbook.fulldatabase;

import java.sql.Date;
import java.util.Arrays;
import java.util.Locale;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.database.sqlite.SQLiteStatement;
import android.os.Bundle;
import android.util.Log;

public class FullDatabaseActivity extends Activity {

	private static final String DEBUG_TAG = "FullDatabase Log";

	private static final String DATABASE_NAME = "test.db";

	// TABLE (COLUMN) NAMES
	private static final String TABLE_BOOK = "tbl_books";
	private static final String TABLE_AUTHOR = "tbl_authors";

	// SQL CREATE AND DROP TABLE STATEMENTS
	private static final String CREATE_AUTHOR_TABLE = "CREATE TABLE tbl_authors (id INTEGER PRIMARY KEY AUTOINCREMENT , firstname TEXT, lastname TEXT);";
	private static final String CREATE_BOOK_TABLE = "CREATE TABLE tbl_books (id INTEGER PRIMARY KEY AUTOINCREMENT , title TEXT, dateadded DATE,  authorid INTEGER NOT NULL CONSTRAINT authorid REFERENCES tbl_authors(id) ON DELETE CASCADE);";
	private static final String DROP_AUTHOR_TABLE = "DROP TABLE tbl_authors;";
	private static final String DROP_BOOK_TABLE = "DROP TABLE tbl_books;";

	// Using triggers to enforce foreign key constraint, because Sqlite doesnt enforce them
	private static final String CREATE_TRIGGER_ADD = "CREATE TRIGGER fk_insert_book BEFORE INSERT ON tbl_books FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table \"tbl_books\" violates foreign key constraint \"fk_authorid\"') WHERE  (SELECT id FROM tbl_authors WHERE id = NEW.authorid) IS NULL; END;";
	private static final String CREATE_TRIGGER_UPDATE = "CREATE TRIGGER fk_update_book BEFORE UPDATE ON tbl_books FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table \"tbl_books\" violates foreign key constraint \"fk_authorid\"') WHERE  (SELECT id FROM tbl_authors WHERE id = NEW.authorid) IS NULL; END;";
	private static final String CREATE_TRIGGER_DELETE = "CREATE TRIGGER fk_delete_author BEFORE DELETE ON tbl_authors FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"tbl_authors\" violates foreign key constraint \"fk_authorid\"') WHERE (SELECT authorid FROM tbl_books WHERE authorid = OLD.id) IS NOT NULL; END;";

	// Our database instance
	private SQLiteDatabase mDatabase;

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);

		// The bulk of our example is here
		runDatabaseExample();

	}

	public void runDatabaseExample() {

		Log.i(DEBUG_TAG, "Begin Simple Database Example");

		if (Arrays.binarySearch(databaseList(), DATABASE_NAME) >= 0) {
			// Delete the old database file, if it exists
			deleteDatabase(DATABASE_NAME);
		}

		// create a new database
		mDatabase = openOrCreateDatabase(DATABASE_NAME,
				SQLiteDatabase.CREATE_IF_NECESSARY, null);

		// SET SOME DATABASE CONFIGURATION INFO
		mDatabase.setLocale(Locale.getDefault()); // Set the locale
		mDatabase.setLockingEnabled(true); // SQLiteDatabase is made thread-safe by using locks around critical sections.
		mDatabase.setVersion(1); // Sets the database version.

		// Log some information about our database
		Log.i(DEBUG_TAG, "Created database: " + mDatabase.getPath());
		Log.i(DEBUG_TAG, "Database Version: " + mDatabase.getVersion());
		Log.i(DEBUG_TAG, "Database Page Size: " + mDatabase.getPageSize());
		Log.i(DEBUG_TAG, "Database Max Size: " + mDatabase.getMaximumSize());

		Log.i(DEBUG_TAG, "Database Open?  " + mDatabase.isOpen());
		Log.i(DEBUG_TAG, "Database readonly?  " + mDatabase.isReadOnly());
		Log.i(DEBUG_TAG, "Database Locked by current thread?  "
				+ mDatabase.isDbLockedByCurrentThread());

		// CREATE TABLES
		Log.i(DEBUG_TAG, "Create the tbl_authors table using execSQL()");
		mDatabase.execSQL(CREATE_AUTHOR_TABLE);

		Log.i(DEBUG_TAG,
				"Create the tbl_books table using SQLiteStatement.execute()");
		SQLiteStatement sqlSelect = mDatabase
				.compileStatement(CREATE_BOOK_TABLE);
		sqlSelect.execute();

		// Create some SQL triggers to enforce our foreign key constraints
		Log.i(DEBUG_TAG, "Create some triggers");
		mDatabase.execSQL(CREATE_TRIGGER_ADD);
		mDatabase.execSQL(CREATE_TRIGGER_UPDATE);
		mDatabase.execSQL(CREATE_TRIGGER_DELETE);

		// Add some records (within a transaction)
		addSomeBooks();
		Log.i(DEBUG_TAG, "Database Transaction?  " + mDatabase.inTransaction());
		Log.i(DEBUG_TAG, "Database Locked by current thread?  "
				+ mDatabase.isDbLockedByCurrentThread());

		// LET'S DO SOME QUERIES

		// SIMPLE QUERY: select * from tbl_books
		Log.i(DEBUG_TAG, "SQL QUERY EQUIVALENT: select * from tbl_books");
		Cursor c = mDatabase.query(TABLE_BOOK, null, null, null, null, null,
				null);
		LogCursorInfo(c);
		c.close();

		// SIMPLE QUERY: SELECT title, id  FROM tbl_books ORDER BY title ASC;
		Log.i(DEBUG_TAG, "SQL QUERY EQUIVALENT: SELECT title, id  FROM tbl_books ORDER BY title ASC;");					
		String asColumnsToReturn2[] = { "title", "id" };
		String strSortOrder2 = "title ASC";
		c = mDatabase.query("tbl_books", asColumnsToReturn2, null, null, null, null,strSortOrder2);
		LogCursorInfo(c);
		c.close();	

		// JOIN QUERY USING SQLiteQueryBuilder: select title, author first name, author last name, and ids (Join two tables)
		Log.i(DEBUG_TAG, "SQL QUERY EQUIVALENT: SELECT tbl_books.title, tbl_books.id, tbl_authors.firstname, tbl_authors.lastname, tbl_books.authorid FROM tbl_books INNER JOIN tbl_authors on tbl_books.authorid=tbl_authors.id ORDER BY title ASC;");
		SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
		queryBuilder.setTables(TABLE_BOOK + ", " + TABLE_AUTHOR);							// Tables to join
		queryBuilder.appendWhere(TABLE_BOOK + ".authorid" + "=" + TABLE_AUTHOR	+ ".id");	// how to join
		String asColumnsToReturn[] = {
				TABLE_BOOK + ".title",
				TABLE_BOOK + ".id",
				TABLE_AUTHOR + ".firstname",
				TABLE_AUTHOR + ".lastname",
				TABLE_BOOK + ".authorid" 
		};
		String strSortOrder = "title ASC";
		c = queryBuilder.query(mDatabase, asColumnsToReturn, null, null, null, null,strSortOrder);
		LogCursorInfo(c);
		c.close();	

		// QUERY Again as above, but this time filter results to just titles that contain the string "Prince"
		Log.i(DEBUG_TAG, "SQL QUERY EQUIVALENT: SELECT tbl_books.title, tbl_books.id, tbl_authors.firstname, tbl_authors.lastname, tbl_books.authorid FROM tbl_books INNER JOIN tbl_authors on tbl_books.authorid=tbl_authors.id  WHERE title LIKE '%Prince%' ORDER BY title ASC;");
		SQLiteQueryBuilder queryBuilder2 = new SQLiteQueryBuilder();
		queryBuilder2.setTables(TABLE_BOOK + ", " + TABLE_AUTHOR);							// Tables to join
		queryBuilder2.appendWhere("("+TABLE_BOOK + ".authorid" + "=" + TABLE_AUTHOR	+ ".id" + ")");	// how to join
		queryBuilder2.appendWhere(" AND (" + TABLE_BOOK + ".title" + " LIKE '%Prince%'" + ")");	// WHERE clauses are AND'ed together, so now we are getting all titles with 'of'
		c = queryBuilder2.query(mDatabase, asColumnsToReturn, null, null, null, null,strSortOrder);
		LogCursorInfo(c);
		c.close();
		
		// Tip: Unions with QueryBuilder are overly complicated for simple unions, just use raw queries with args.
		// Note: the Query args will all be surrounded with '' so you need to put the % inside the arg instead of in the query string 
		Log.i(DEBUG_TAG, "SQL QUERY EQUIVALENT: SELECT title AS Name, 'tbl_books' AS OriginalTable from tbl_books WHERE Name LIKE '%ow%' UNION SELECT (firstname||' '|| lastname) AS Name, 'tbl_authors' AS OriginalTable from tbl_authors WHERE Name LIKE '%ow%' ORDER BY Name ASC;");
		String sqlUnionExample = "SELECT title AS Name, 'tbl_books' AS OriginalTable from tbl_books WHERE Name LIKE ? UNION SELECT (firstname||' '|| lastname) AS Name, 'tbl_authors' AS OriginalTable from tbl_authors WHERE Name LIKE ? ORDER BY Name ASC;";
		c = mDatabase.rawQuery(sqlUnionExample, new String[]{ "%ow%", "%ow%"});
		LogCursorInfo(c);
		c.close();
		
		// SIMPLE QUERY: select * from tbl_books WHERE id=9;
		Log.i(DEBUG_TAG, "SQL QUERY EQUIVALENT: select * from tbl_books WHERE id=9");
		c = mDatabase.query(TABLE_BOOK, null, "id=?", new String[]{"9"}, null, null,null);
		LogCursorInfo(c);
		c.close();
		
		// LET's DO AN UPDATE
		Log.i(DEBUG_TAG, "Update Le Petit Prince Book to new title 'The Little Prince' (Book Record Id 9)");
		updateBookTitle("The Little Prince", 9);	// Change Book #9 to the English title from the French Version
		
		// SIMPLE QUERY: select * from tbl_books WHERE id=9
		Log.i(DEBUG_TAG, "SQL QUERY EQUIVALENT: select * from tbl_books WHERE id=9");
		c = mDatabase.query(TABLE_BOOK, null, "id=?", new String[]{ "9"}, null, null,null);
		LogCursorInfo(c);
		c.close();
		
		// LET'S DO SOME DELETES
		
		// Deletes fail if they violate the database constraints we imposed in the CREATE TABLE (triggers)
		try
		{
			Log.i(DEBUG_TAG, "Try to delete Stephen Colbert (Author Record Id 2)");
			deleteAuthor(2);	// try to delete Colbert. This will FAIL because he's got books (constraint violation)
		}
		catch(SQLiteConstraintException e)
		{
			Log.i(DEBUG_TAG, "Delete Failed, threw SQLiteConstraintException: "+  e.getMessage() + "  ***FYI: Error Code 19 is Constraint Violated. Colbert still has books!");
		}
		
		// DELETES DONE IN THE RIGHT ORDER TO AVOID CONSTRAINT VIOLATIONS WILL SUCCEED
		Log.i(DEBUG_TAG, "Try to delete Stephen Colbert's Book (Book Record Id 8)");
		deleteBook(8);	// Delete Colbert's book
		Log.i(DEBUG_TAG, "Try to delete Stephen Colbert Again (Author Record Id 2)");
		deleteAuthor(2); // Delete Colbert
		
		Log.i(DEBUG_TAG, "Deleting All books by J.K. Rowling (Author Record Id 1)");
		deleteBooksByAuthor(1);
		
		// SIMPLE QUERY: select * from tbl_books
		Log.i(DEBUG_TAG, "SQL QUERY EQUIVALENT: select * from tbl_books");
		c = mDatabase.query(TABLE_BOOK, null, null, null, null, null,null);