1.SQLite
Android built-in SQLite database, which is a lightweight embedded database, created by default for each app
Database is private, its name is unique, and the individual apps cannot access the other's database. Generated by each application
The database is stored under the "/data/data/< Package name >/database/" directory.
2.DAO Design Applications
Separates the underlying database storage logic from the upper-level business logic . When the underlying stored data object changes, only the code that implements the DAO interface is modified, and the upper layer code does not have to make any changes.
Part:
1. Data storage Objects
2.DAO interface (to implement the operation of adding additions and deletions)
3. Connecting the Database interface
4. Specific classes that implement DAO interfaces
The factory class of 5.DAO.
2.1 Sqliteopenhelper (abstract class: Used to connect to a database)
Used to create or open a database, which provides a way to get the appropriate database.
Construction Method:
Sqliteopenhelper (context context, String name, Sqlitedatabase.cursorfactory factory, int version);
Just create a Sqliteopenhelper object, and you have not created or opened the appropriate database.
Member Methods:
Synchronized Sqlitedatabase getreadabledatabase ();//Open or create a database object, only read operations.
Synchronized Sqlitedatabase getwritabledatabase ();//Open or create a database object, only write operations.
void OnOpen (Sqlitedatabase db);//callback function when the database is opened.
Inherit the methods that the class must implement:
void OnCreate (Sqlitedatabase db);//called at database creation time
void Onupgrade (sqlitedatabase db, int oldversion, int newversion)//called during database upgrade
2.1A Creating triggers
Android provides a trigger (Trigger) mechanism for the SQLite database, which is a special data manipulation process that executes the specified modification statement on the specified data table.
Specific data operations will be performed. For example, when you delete an author record, you can use trigger to delete the author's corresponding book record. Triggers are generally in the Sqliteopenhelper class
Defined in the OnCreate method.
Public voidonCreate (Sqlitedatabase db) {//CREATE TRIGGER, delete records in author, cascade delete records associated in book//CREATE trigger Book_delete Delete on author (table)//begin delete from book (table) where author_id=old._id end;Db.execsql ("CREATE TRIGGER book_delete Delete" + publisher.author_table + "+" BEGIN "+" Delete from " + publisher.book_table + "WHERE" +Publisher.BOOK.AUTHOR_ID+ "=OLD._ID;" + "END;"); //CREATE INDEX, "CREATE index Authorname_index on author (table) (field of author_name,author_sex, etc.)"//you can add a descending or ascending order of "DESC" after the column name, "ASC" such as: (Author_name desc). Db.execsql ("CREATE INDEX authorname_index on" +publisher.author_table+ "(" + Publisher.AUTHOR.NAME + ");");
2.1b Creating an index
An index is a structure that sorts the values of one or more columns in a datasheet, using an index to find data faster, and
You do not have to read the entire table. However, updating a table with an index is more time than updating a table with no indexes. So the index
is used only on columns that are often retrieved.
Public void onCreate (Sqlitedatabase db) { // CREATE INDEX, "CREATE index Authorname_index on author (table) ( Author_name,author_sex, etc.) " // You can add a descending or ascending order of" desc "," ASC ", such as: (Author_name desc), after the column name. db.execsql ("CREATE INDEX authorname_index on" + publisher.author_table + "(" + Publisher.AUTHOR.NAME + "); );}
2.1c Creating a View
A view is used to represent records in one or more tables, and a virtual table can be generated in the database. SQLite is a relational type
Database. In the OnCreate method.
//create views to facilitate book and author table linking//"CREATE view if not exists book_author (table) as Select book.* (table), author_name (column name)//From Book (table) left OUTER join author (table) on author._id (column name under table) =book.author_id; (Column name under table) "//"book.*" all columns under the table, "left outer join" operation, representing the final generated virtual table row//the rows that include the left table (book_table) and the right table (author_table) meet certain criteria. The condition is the statement after "on",//the value of the author._id must be equal to the value of book.author_id, that is, the author_id under book points to the _id of author. Db.execsql ("CREATE VIEW IF not EXISTS" +publisher.book_author_table+ "as" + "select" + publisher.book_table + ". *" + "," + Publisher.AUTHOR.NAME + "from" +publisher.book_table+ "Left OUTER joins" + publisher.author_table + "on" + Publisher.author_table + "." + publisher.author._id + "=" + publisher.book_table + "." + Publisher.BOOK.AUTHOR_ID);
under "adb shell" enter the/data/data/< package name >/databases/Enter the database with "Sqlite3 database name".
Use "PRAGMA table_info (Book_author)" To view the Book_author table.
2.2 Data storage Object (VO: Value object)
You need to create a new class yourself that contains the corresponding table fields.
//defining VO (storing Data Objects) Public Final classPublisher {//There are two kinds of tables, one author, one book.//Table name Public Static FinalString book_table= "book"; Public Static FinalString author_table= "AUTHOR"; //virtual tables (views) represent the contents of multiple tables with a single table Public Static FinalString book_author_table = "Book_author"; //author table structure, there are two fields in the Basecolumns interface _id= "_id", _count= "_count " Public Static classAUTHORImplementsBasecolumns { Public Static FinalString name= "Author_name"; Public Static FinalString age= "Age"; Public Static FinalString sex= "SEX"; Public Static FinalString order_by= "Author_name desc"; } Public Static classBookImplementsBasecolumns { Public Static FinalString name= "Book_name"; Public Static FinalString year= "Year"; Public Static FinalString order_by= "Book_name desc"; Public Static FinalString author_id= "author_id"; }}
2.3 DAO Interface
Provides data manipulation methods used by upper-level applications
Public InterfacePubliserdao {Cursor getauthors (); Cursor Getauthorbyid (LongID); Cursor GetBooksByAuthor (Longauthor_id); Cursor Getbookbyid (LongID); voidInsertauthor (string name, string address, string phone); voidUpdateauthor (LongID, string name, string address, string phone); voidInsertbook (Longauthor_id, string name, string year); voidUpdatebook (LongID, string name, string year); voidDeleteauthor (Longauthor_id); voiddeleteauthors (String where);}
android-Data Persistence Store 2