How Android data storage is done with SQLite database

Reprint Please specify source: Minsan Android

There are different ways to store and manage application data (Sharedperferences,file,sqlitedatabase, networked storage) under the Android platform, and the choice of methods depends on the type of data you need to store and how it is structured. SQLite database can solve the problem of storage of structured data in a safe and effective way;

This is mainly about the usage of SQLite and the encapsulation of common database operations.

Finally, as a comprehensive case, do a simple student management demo, create student.db, including the Name,grade field, to achieve the function of adding, deleting, changing and checking; Android data storage leverages sqlitedatabase for simple student management

about 1,sqlite Database

1, what is SQLite

SQLite is open source, support the standard SQL syntax of the relational database, support transactions, the runtime requires a small amount of memory (250k);
The supported data types are: Text,integer,real (like double in Java), and all other data types must be converted to the above data types to be stored in the database.

SQLite is embedded in every Android device, so using SQLite does not require you to install and manage a database

All you need to do is define SQL statements to create and manage data tables.

Accessing a SQLite database may have access to the file system, so it is recommended to perform database operations asynchronously;
After you create a database, the default is the one that exists DATA/APP_NAME/databases/FILENAME .

2, SQLite structure:

In the SDK, the Android.database package contains the operation classes required for the database, and Android.database.sqlite includes all operations on the SQLite database

Use of the 3,sqliteopenhelper class:

Creates a persisted database that can be inherited and SQLiteOpenHelper then called in the constructor method.

    supernull, VERSION);//传入定义好的数据库名和版本；

You also need to overwrite two methods:

     @Override    publicvoidonCreate(SQLiteDatabase sqLiteDatabase) {        //创建        sqLiteDatabase.execSQL(CREATE_TABLE);    }    @Override    publicvoidonUpgradeintint i1) {        //更新        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);        onCreate(sqLiteDatabase);    }

All two methods accept a Sqlitedatabase db object, Sqliteopenhelper also provides two methods Getwritabledatabase (), Getreadabledatabase () Used to get Sqlitedatabase objects,

4,sqlitedatabase

Sqlitedatabase is a basic database class for providing basic operations such as additions and deletions in Android. Insert (), update (), delete (), and it also provides the Execsql () method to execute the SQL statement directly;

When we need to insert or update a data table, we need to use the Contentvalues,contentvalues object to allow you to define a key-value pair, key to a field in the datasheet, and value to the contents of the table record.

Queries can be through the rawquery () or the query () method or the Sqlitequerybuilder () method

Rawquery () directly accepts an SQL SELECT statement as input

Cursor cursor= getReadableDatabase().rawQuery("Select * from TABLE where id=?",new String[]{id});

Query () provides a structured, specified interface for SQL queries

    Cursor cursor=getReadableDatabase().query(DATABASE_TABE,newString[]{KEY_ROAB,KEY_ID,KEY_NAME,KEY_HH},null,null,null,null);

Sqlitequerybuilder () is used to build complex SQL queries, such as when there are multiple data tables and their relationships are more complex.

5, Cursor:

A query returns a cursor object that mainly points to a row of query results so that Android can buffer query results without loading all of the data into memory;

In order to obtain the number of query results, you can use GetCount ();

To move through the results of the query, you can use Movetofirst (), MoveToNext ();

Isafterlast () returns whether the end of the query result has been reached.

Cursor provides get* (), methods such as Getlong (ColumnIndex), getString (columnindex) Gets the data of the query result column, ColumnIndex represents the number of columns, The cursor also provides getcolumnindexorthrow (String name) to get the number of a column in a table with a field named name.

2,android Database Operations Common steps

Knowing the relevant concepts, we have a summary of the common database operations for Android by following these steps:

1. Create a model class for each table, which is the corresponding data class:
   
   1. tips: Creating a data class for each table is a good idea, and adding an ID field to a table is necessary because many database operations methods depend on him.

2. Implementing the Databasehandler Class

   1. Inherit from Sqliteopenhelper class, implement constructor (create table) and overwrite OnCreate (), Onupgrade () method
   2. Encapsulating crud Methods According to our needs (add and revise)
   3. Implementing a specific CRUD approach

3. Using the Databasehandler class, the data is bound to the interface through a custom adapter display in the Adapterview (ListView. GridView, etc.) on.

For example, we need to create a database about contact;

1, create model class for contact:

    publicclass Contact{          privateint id;          private String name;          private String number;        //设置get\set方法          ...          get()          set()        //提供构造函数          Contact(int id,String name, String number){}     }

2, create the Databasehandler class:

Private Static FinalString database_name="Contact";Private Static FinalString table_name="Contact";Private Static Final intversion=1;Private Static FinalString key_id="id";Private Static FinalString key_name="Name";Private Static FinalString key_number="Number";Private Static FinalString create_table="CREATE TABLE"+table_name+"("+key_id+"Integer primary key AutoIncrement,"+key_name+"text NOT NULL,"+ key_number+"text not NULL);"; Public Databasehandler(Context context) {Super(Context, database_name,NULL, VERSION); }@Override     Public void onCreate(Sqlitedatabase sqlitedatabase)    {Sqlitedatabase.execsql (create_table); }@Override     Public void Onupgrade(Sqlitedatabase sqlitedatabase,intIintI1) {Sqlitedatabase.execsql ("DROP TABLE IF EXISTS"+ table_name);    OnCreate (sqlitedatabase); }

3, in the Databasehandler class package additions and deletions to change the method:

 Public void addcontact(Contact Contact) {Sqlitedatabase db= This. Getwritabledatabase ();//Use Contentvalues to add dataContentvalues values=NewContentvalues ();        Values.put (Key_name,student.getname ());        Values.put (Key_grade,student.getgrade ()); Db.insert (TABLE_NAME,NULL, values);    Db.close (); } PublicContactgetcontact(String name) {Sqlitedatabase db= This. Getwritabledatabase ();//cursor Object returns query resultsCursor Cursor=db.query (TABLE_NAME,NewString[]{key_id,key_name,key_number}, key_name+"=?",NewString[]{name},NULL,NULL,NULL,NULL); Contact contact=NULL;//Note that the return result may be empty        if(Cursor.movetofirst ()) {student=NewContact (Cursor.getint (0), Cursor.getstring (1), Cursor.getstring (2)); }returnContact } Public int getcontactcounts() {String selectquery="SELECT * from"+table_name; Sqlitedatabase db= This. Getreadabledatabase (); Cursor Cursor=db.rawquery (SelectQuery,NULL); Cursor.close ();returnCursor.getcount (); } PublicList<contact>getalllcontact() {list<contact> contactlist=NewArraylist<contact> (); String selectquery="SELECT * from"+table_name; Sqlitedatabase db= This. Getreadabledatabase (); Cursor Cursor=db.rawquery (SelectQuery,NULL);if(Cursor.movetofirst ()) { Do{Contact contact=NewContact (); Contact.setid (Integer.parseint (cursor.getstring (0))); Contact.setname (Cursor.getstring (1)); Contact.setnumber (Cursor.getstring (2));            Contactlist.add (contact); } while(Cursor.movetonext ()); }returnContactList; } Public int updatecontact(Contact Contact) {Sqlitedatabase db= This. Getwritabledatabase (); Contentvalues values=NewContentvalues ();        Values.put (Key_name,contact.getname ()); Values.put (Key_grade,contact.getnumber ());returnDb.update (table_name,values,key_id+"=?",NewString[]{string.valueof (Contact.getid ())}); } Public void deletestudent(Contact Contact) {Sqlitedatabase db= This. Getwritabledatabase (); Db.delete (table_name,key_id+"=?",NewString[]{string.valueof (Contact.getid ())});    Db.close (); }

4, in the activity in the relevant event method to call the corresponding Databasehandler methods can be

Now has mastered the basic database operations, as a practice, to achieve a simple student management, confined to the next article on the Android data storage using Sqlitedatabase for simple student management

Summarize:

There is not much to explain the SQL syntax, only related to the basic sqlitedatabase of Android common operation, if you are not familiar with, you can refer to the following article.

Reference: Android SQLite Database Tutorial

• Weibo: @ Ming sang Android Black history
• E-mail: <[email protected]>
• Personal homepage: Minsan wins the black history of Android Wang

• Public Number: Itbird

How does the Android data store work with SQLite database