Android Development using SQLite for data storage

Android Development using SQLite for data storage

• Android Development using SQLite for data storage
  • A simple introduction to SQLite database
  • How to use SQLite in Android
    • 1 Creating a Sqliteopenhelper object and creating a table
    • 2 run additions and deletions by Sqlitedatabase objects
    • 3 Sqlitedatabase's Affairs transaction

1.SQLite Database Brief Introduction

Sqlite. is a lightweight database that adheres to the acid-based relational database management system, which is included in a relatively small C library. It is the public domain project established by D.richardhipp. Its design goal is embedded, and it is now used in very many embedded products, it occupies a very low resource, in the embedded device. Maybe just hundreds of K of memory is enough. It can support Windows/linux/unix and so on mainstream operating system, at the same time can be combined with very many programming languages, such as TCL, C #, PHP, Java, and ODBC interface, the same compared to M
Ysql, PostgreSQL the two open-source world-renowned database management system, it processing speed faster than they.

The first alpha version number of SQLite was born in May 2000.

To 2015 has 15 years, SQLite also ushered in a version number SQLite 3 has been announced.

Summary : SQLite is very suitable as a database for mobile terminals. Small Footprint , lightweight , faster processing ...

How to use SQLite2.1 to create Sqliteopenhelper objects in 2.Android. and create a table

Create a new class, named Mysqliteopenhelper, and inherit it from Sqliteopenhelper:

The new error will be added, because the construction method is not added, join the construction method:

     PackageCom.example.sqllitetest;ImportAndroid.content.Context;ImportAndroid.database.sqlite.SQLiteDatabase;ImportAndroid.database.sqlite.SQLiteDatabase.CursorFactory;ImportAndroid.database.sqlite.SQLiteOpenHelper; Public  class mysqlopenhelper extends sqliteopenhelper {         Public Mysqlopenhelper(context context, String name, Cursorfactory factory,intVersion) {Super(context, name, Factory, version);//TODO auto-generated constructor stub}/** * This is called when the database is created, where the statement creating the table is run * /        @Override         Public void onCreate(Sqlitedatabase db) {//TODO auto-generated method stub}/** * When the database is upgraded, this method is called, where the database update operation is run * /        @Override         Public void Onupgrade(Sqlitedatabase DB,intOldversion,intNewVersion) {//TODO auto-generated method stub}/** * When the database is open, this method calls the */        @Override         Public void OnOpen(Sqlitedatabase db) {//TODO auto-generated method stub            Super. OnOpen (DB); }    }

In the upper OnCreate (). method to run a statement that creates a table named person

    db.execSQL("create table person (_id integer primary key autoincrement, name char(10), age integer(3), phone integer(20))");

Note: In fact, in SQLite in addition to the ID primary key. All fields have no clear type restrictions, for example, we can insert a char type of data into an integer type field.

So, in that case, when we created the database, what was the purpose of the specified data type?

These are for. Let us program apes understand the type limitations of a field. When actually coding, it is necessary to specify the type of the field, which is easy to maintain and understand later.

Create a new test class Sqlitetestcase.java:

     PackageCom.example.sqllitetest;ImportAndroid.content.ContentValues;ImportAndroid.database.Cursor;ImportAndroid.database.sqlite.SQLiteDatabase;ImportAndroid.test.AndroidTestCase; Public  class sqllitetestcase extends androidtestcase {        PrivateMysqlopenhelper Oh;PrivateSqlitedatabase DB; Public void Test() {System.out.println ("This is Test method!"); }/** * After the test frame initialization is complete, call this method before the test method call * /        @Override        protected void setUp()throwsException {Super. SetUp ();//Get a writable database object (if the database does not exist). Create the database first. Get a readable writable database object; If the database exists, open it directly)            //*********************** parameter Description ***********************            //Number 1: Gets the context object, Androidtestcase provides a getcontext () method for easy testing            //Number 2: Database file name            //Number 3: Cursor factory object, used to create a cursor object. The empty feeling of silence            //Parameter 4: Used to identify the version number of the database, used to compare with the previous creation, when the version number is greater than, call Onupgrade ()Oh =NewMysqlopenhelper (GetContext (),"Person.db",NULL,1);/** * ************** First method: Get Database ************** * 1. Used to create or open a writable database. * When it is first called, it will be based on the database name and version number in new Sqlopenhelper () * When the same database file already exists. And the version number is the same.             is opened directly, * Conversely, the Create or Update method is called. * * 2. Once the data has been opened successfully. will be cached, and when we need to enter data, we can invoke the returned object in any place to manipulate the database. Note: However, you do not need to use the database. You need to close the database * * 3. But without permission. Or when the disk is full, this method will be called failed, but if the problem is resolved. You can successfully invoke * 4. Call this method, if the update operation is triggered, you need to be alert: * Because the database update is a time-consuming operation. We should not call it in the main thread of the application. Includes contentprovider.oncreate () * */db = Oh.getwritabledatabase ();/** * ************** Another way: Get a database ************** * Don't be misled by names.             This method has the same access to read-write database objects. * It differs from the above method when encountering some problems: (columns such as: Disk is full.) This time the call does not fail, but will return a read-only database object) * /db = Oh.getreadabledatabase (); }/** * Test method after the run is complete, call this method * /        @Override        protected void TearDown()throwsException {//TODO auto-generated method stub            Super. TearDown ();//Close DatabaseDb.close (); }    }

Note: When we open a database with new Sqliteopenhelper (). The incoming version number should not be lower than the version number that was bound when the database was created earlier. Otherwise, the following error will occur and the version number can only be incremented

Run the test () method, which appears green, stating that there are no errors

From the Ddms–>file Explore, view the data/data/Project package name/databases folder:

Export the file. Open in SQLite expert software:

You can see that both the database and the table are created correctly.

2.2 Run additions and deletions by Sqlitedatabase objects

2.2.1 Adding data operations

There are two ways to add data:

• The first method: using handwritten SQL statements, run Execsql ();

Adding the Insert () method to the Sqlitetestcase.java

 public  void  Insert ( {Db.execsql ( "INSERT into person (name, age, phone) values (?,? 
 
 
,?) ", new  object  []{  "Zhang San" , 18 ,  " 180199678455 "}"); Db.execsql ( "INSERT into person (name, age, phone) values (?  
 
,?,?) ", new  object  []{" Zhao Si ", 16 , " 180199678455 "}) ; Db.execsql ( "INSERT into person (name, age, phone) values (?  
  
, ? , ?)", new Object[]{"Android", 15, "180199678455"}); }

To run the results, the data is successfully inserted into the table:

• Another way: Use the Android API to encapsulate the data in contentvalues.

Add the Insertbyapi () method to the Sqlitetestcase.java:

 Public void Insertapi() {//Package all data in ContentvaluesContentvalues values =NewContentvalues (); Values.put ("Name","Zhangsan"); Values.put ("Age", +); Values.put ("Phone","13812235689");//Description of the parameters:        //First parameter table: Table name        //second parameter nullcolumnhack: can be specified as NULL, if NULL, when you have no value in values. The row is not inserted        //If you specify a value of Nullcolumnhack, even if you have no value in your values, you will        //Insert a null value under your field        //Third parameter Values:contentvalues objectDb.insert ("Person",NULL, values); }

To export the db file, refresh:

2.2.1 Deleting data operations

There are two ways to delete data:

• The first method: Run the Execsql () method by using the handwritten SQL statement.

publicvoiddelete() {            // 删除id为1的行            db.execSQL("delete from person where _id = ?"new Object[]{1});        }

• Another way: through the Android API;

publicvoiddeleteApi() {            // 删除表中age = 78, id = 4的记录            // 返回值为受影响的行，删除了多少行            int i = db.delete("person""age = ? and _id = ?
new String[]{"78""4"}); }

2.2.1 Changing Data operations

There are two ways to change the data:

• The first method: through handwritten SQL statements. Run the Execsql () method:

publicvoidupdate() {            // 改动id为2的phone值为110            db.execSQL("update person set phone = ? where _id = ?"new Object[]{"110"2});        }

View results:

• Another way: through the Android API;

publicvoidupdateApi() {            // 通过ContentValues来指定改动后的值            new ContentValues();            values.put("phone""120");            // 返回值为受影响的行            int i = db.update("person""_id = ? and age = ?"new String[]{"3""15"});        }

View results:

2.2.1 Querying data operations

There are two ways to query the same data:

• The first method: the Rawquery () method:

 Public void Query() {Cursor cursor = Db.rawquery ("SELECT name, age, phone from person",NULL); while(Cursor.movetonext ()) {//It is not recommended to use such subscript method to get the value, once the position of the late field has changed, maintenance is more troublesome                //String name = cursor.getstring (0);                //int age = Cursor.getint (1);                //String phone = cursor.getstring (2);                //Recommended UseString name = cursor.getstring (Cursor.getcolumnindex ("Name"));intAge = Cursor.getint (Cursor.getcolumnindex ("Age")); String phone = cursor.getstring (Cursor.getcolumnindex ("Phone")); System. out. println ("Name:"+ name +"; Age = "+ Age +";"+"phone ="+ phone); }        }

Logcat Output Results:

• Another way: Through the Android API.

 Public void Queryapi() {Cursor cursor = Db.query ("Person",NULL,NULL,NULL,NULL,NULL,NULL); while(Cursor.movetonext ()) {String name = cursor.getstring (Cursor.getcolumnindex ("Name"));intAge = Cursor.getint (Cursor.getcolumnindex ("Age")); String phone = cursor.getstring (Cursor.getcolumnindex ("Phone")); System. out. println ("Name:"+ name +"; Age = "+ Age +";"+"phone ="+ phone); }        }

2.3 Sqlitedatabase's Affairs transaction

Scenario: When multiple statements need to be guaranteed to run successfully at the same time, otherwise. Rolling back

Over here. Let's simply simulate it. Let's say we need age plus 1 for ID 2, and at the same time make sure that age with ID 3 is minus 1 years old.

The original data is in the database:

Assume that the transaction runs successfully. The age of name Zhao four becomes 17, and the age of "Android" is 14. Conversely, all property values of the two data are unchanged.

First of all. We artificially caused the failure of the operation:

Join method:

 Public void Transaction() {Try{//Open transactionDb.begintransaction (); Contentvalues values =NewContentvalues (); Values.put ("Age", -); Db.update ("Person", Values,"_id =?",Newstring[]{"2"});                Values.clear (); Values.put ("Age", -); Db.update ("Person", Values,"_id =?",Newstring[]{"3"});inti =1/0;//Error here, will cause the transaction to run failed                //Set the transaction to run successfullyDb.settransactionsuccessful (); }Catch(Exception e) {//Todo:handle exception}finally{//End transaction, commit at the same time. Assuming that the transaction has been set successfully, the SQL statement takes effect. Conversely, the rollbackDb.endtransaction (); }        }

Export db file, refresh, see Data no matter what changes:

Then remove the error. How to run:

The results, such as the following, run successfully:

Android Development using SQLite for data storage