Android data Storage (3): SQLite database Storage

About SQLite

Google for Andriod's larger data processing provides SQLite, he is in the storage, management, maintenance and other aspects of the excellent, the function is very strong. SQLite has the following features:

1. Lightweight: with SQLite you only need to bring a dynamic library, you can enjoy its full functionality, and the size of the dynamic library want to be small.

2. Independence: The core engine of the SQLite database does not need to rely on third-party software, nor does it require the so-called "install".

3. Isolation: All information in the SQLite database (such as tables, views, triggers, etc.) is contained within a folder for easy administration and maintenance.

4. Cross-platform: SQLite currently supports most of the operating systems, not the computer operating system more in the many mobile phone systems can also be run, such as: Android.

5. Multi-lingual Interface: The SQLite database supports multiple language programming interfaces.

6. Security: The SQLite database implements independent transaction processing through exclusive and shared locks at the database level. This means that multiple processes can read data from the same database at the same time, but only one can write data.

Android in order to allow us to better manage the database, specifically provides a Sqliteopenhelper helper class, which is an abstract class, so to use it needs to create its own helper class to inherit it. There are two abstract methods in the Sqliteopenhelper class, namely OnCreate (), and Onupgrade (), which must be overridden in the Help class, and then implemented in both methods to create and upgrade the database logic. There are two important example methods in Sqliteopenhelper, Getwritabledatabase () and Getreadabledatabase (). Both methods can create or open an existing database (if the database already exists, open it directly, Otherwise, a new database is created) and a read-write operation object is returned for the database. The difference is that when the database is not inhaled (such as the disk is full) the object returned by Getreadabledatabase () will open the database in a read-only manner, and Getwritabledatabase () throws an exception.

There are two construction methods in Sqliteopenhelper that can be overridden, generally using the one with fewer parameters, that is, the public databasehelper (context context, String name, Cursorfactory factory , int version), where the first parameter is the context, must have it to operate on the database, the second parameter is the name of the database, the third parameter allows us to query the data when we return a cursor, generally passed to null, the fourth parameter is the database version number, Can be used to upgrade a database. The code is implemented as follows:

New Project Mysqlitetest: Create a new Databasehelper.java class, Inherit Sqliteopenhelper, and modify the Activity_main.xml file, adding the following code:

<Button Android:id="@+id/create_database"Android:layout_width="fill_parent"Android:layout_height="wrap_content"Android:text="Create a database"/> <Button Android:id="@+id/add_data"Android:layout_width="fill_parent"Android:layout_height="wrap_content"Android:layout_below="@id/create_database"Android:text="Add Data"/> <Button Android:id="@+id/up_data"Android:layout_width="fill_parent"Android:layout_height="wrap_content"Android:layout_below="@id/add_data"Android:text="Update Data"/> <Button Android:id="@+id/delete_data"Android:layout_width="fill_parent"Android:layout_height="wrap_content"Android:layout_below="@id/up_data"Android:text="Delete Data"/> <Button Android:id="@+id/query_data"Android:layout_width="fill_parent"Android:layout_height="wrap_content"Android:layout_below="@id/delete_data"Android:text="Querying Data"/> <Button Android:id="@+id/replace_data"Android:layout_width="fill_parent"Android:layout_height="wrap_content"Android:layout_below="@id/query_data"Android:text="replacing data with transactions"/>

Activity_main.xml

Create a new book table in the Databasehelper.java class

 Public classDatabasehelperextendsSqliteopenhelper { Public Static FinalString create_book= "CREATE table book (" + "ID integer primary key autoincrement," + "author text," + "Price real," + "page integer," + "Name text)"; PrivateContext Mycontext;  PublicDatabasehelper (Context context, String name, Cursorfactory factory,intversion) {        Super(context, name, Factory, version); //TODO auto-generated Constructor stubmycontext=context; } @Override Public voidonCreate (Sqlitedatabase db) {//TODO auto-generated Method StubDb.execsql (Create_book); Toast.maketext (Mycontext,"Create Success", Toast.length_short). Show (); } @Override Public voidOnupgrade (Sqlitedatabase arg0,intArg1,intarg2) {        //TODO auto-generated Method Stub    }}

Databasehelper

This defines the statement as a string constant, and then calls the Sqlitedatabase Execsql () method in the OnCreate () method to execute the statement and pops up "Create Success", This ensures that the Book table is created while the database creation is complete.

Then click the Create Database button in the Mainactivity.java code:

Define some variables:

 private  button creatbsesbtn; //     CREATE DATABASE  private  Button adddatabtn; //     add data  private  Button uddatabtn; //     update data  private  Button deletedatabtn; //     delete data  private  Button querydatabtn; //     socialize data  private  Button replacedatabtn; //     replace data   private  Databasehelper MyDB; 

Button

mydb=New databasehelper (thisnull, 1);                   // Add a database         Creatbsesbtn.setonclicklistener (new  Onclicklistener () {                        @Override            public  void  OnClick (View arg0) {                //  TODO auto-generated method Stub                 mydb.getwritabledatabase ();            }        });

Add a database

This is done by constructing a databasehelper and specifying the name of the database as bookstore.db by the parameters of the constructor, the version number 1, and then calling the Getwritabledatabase () method in the Click event. This is not a specific description, you can use the ADB shell to check the database and table creation, the specific configuration is not described.

The following is not specified, see the code:

Add Data:

//Add DataAdddatabtn.setonclicklistener (NewOnclicklistener () {@Override Public voidOnClick (View arg0) {//TODO auto-generated Method StubSqlitedatabase db=mydb.getwritabledatabase (); Contentvalues CV=Newcontentvalues (); //Add DataCv.put ("name", "Android"); Cv.put ("Author", "Jesson"); Cv.put ("Page", 120); Cv.put ("Price", 75.0); Db.insert ("Book",NULL, CV);                Cv.clear (); Db.insert ("Book",NULL, CV);                  }        }); 

AddData

In this case, get the Sqlitedatabase object first, then use Contentvalues to assemble the data to be added, because the ID column is set to autogrow, so it's just four columns. Finally, the Insert () method is called for data insertion.

Update data:

//Update DataUddatabtn.setonclicklistener (NewOnclicklistener () {@Override Public voidOnClick (View arg0) {//TODO auto-generated Method StubSqlitedatabase db=mydb.getwritabledatabase (); Contentvalues CV=Newcontentvalues (); Cv.put ("Prices", 18.32); Db.update ("Book", CV, "Name=?",Newstring[]{"Android"}); }        });

Update Data

Here we use the update () method to perform the specific update operation, using the 34th parameter to specify which line of the specific update, that is: The book title for Android books Price changed to 18.32 (originally 75.0).

Delete data:

// Delete Data         Deletedatabtn.setonclicklistener (new  Onclicklistener () {                        @Override            public  void  OnClick (View arg0) {                //  TODO auto-generated method stub                Sqlitedatabase db=mydb.getwritabledatabase ();                Db.delete (new string[]{")})        ;

Delete data:

Here, the Delete () method is used to perform the deletion, and the 23rd parameter is used to specify which row to delete, and delete the book with more than 50 pages.

 //Query OperationsQuerydatabtn.setonclicklistener (NewOnclicklistener () {@Override Public voidOnClick (View arg0) {//TODO auto-generated Method StubSqlitedatabase db=mydb.getwritabledatabase (); //querying all data in a tableCursor cursor=db.query ("book",NULL,NULL,NULL,NULL,NULL,NULL); if(Cursor.movetofirst ()) { Do{String name=cursor.getstring (Cursor.getcolumnindex ("name")); String author=cursor.getstring (Cursor.getcolumnindex ("Author")); intPage=cursor.getint (Cursor.getcolumnindex ("page")); DoublePrice=cursor.getdouble (Cursor.getcolumnindex ("Price")); } while(Cursor.movetonext ());            } cursor.close (); }        });

Query Operations

Sqlitedatabase provides a query () method for querying data, which is more complex and has a minimum of seven parameters, as shown in the following table:

Query () method parameters	Describe
Table	Specifies the table name of the query (can be null)
Columns	Specifies the column name of the query (can be null)
Selection	Specify the WHERE constraint (nullable)
Selectionargs	Provide a specific value for the placeholder in the Where (nullable)
GroupBy	Specify the column that requires group by (nullable)
Having	Further constraint on the result of group by (can be null)
By	To specify how the query results are sorted (nullable)

When the query () method is called, a cursor object is returned, and all data that is queried is removed from the object. In this query, after we get the cursor object, we call its Movetofirst () method to move the data pointer to the first row, and then into a loop to iterate through each row of data that is queried. In this loop, the Getcolumnindex () method can be used to obtain the corresponding position index for each column in the table. Finally, don't forget to close the cursor.

The use of the transaction is described in the next article.

Android data Storage (3): SQLite database Storage