"Anroid Advanced" SQLite database using fully detailed

In Android development, we can generally use files, sharedpreferences, and database to save the data generated by our program, this article mainly explains how to use the SQLite database, to complete the data deletion and modification of the general operation

about SQLite SQLite is a very popular embedded database that supports the SQL language and has good performance with very little memory. It's also open source and can be used by anyone.

SQLite is no different from the other major SQL databases. It has the advantage of being efficient, and the Android runtime environment contains the full SQLite.

The biggest difference between SQLite and other databases is support for data types, and when you create a table, you can specify the data type of a column in the CREATE TABLE statement, but you can put any data type in any column. When a value is inserted into the database, SQLite checks its type. If the type does not match the associated column, SQLite attempts to convert the value to the type of the column. If it cannot be converted, the value is stored as the type it has. For example, you can put a string into an INTEGER column. SQLite calls this a "weak type" (Manifest typing.).

In addition, SQLite does not support some standard SQL features, especially foreign KEY constraints (FOREIGN key constrains), nested transcaction and right OUTER joins and full OUTER joins, and some ALTER TABLE function.

In addition to the above functions, SQLite is a complete SQL system with complete triggers and so on.

creation of SQLite databaseIf we want to use a database on our phone to save our app information, we have to create a database before we use it. The Android API provides us with the Sqliteopenhelper class, which completes the initial creation of the database and the processing needed to update the database version as a result of the software upgrade. We must customize a class that inherits fromSqliteopenhelper, and implement its three methods, the code is as follows

/** * Database Creation class *  * @author Zhaokaiqiang * @time June 4, 2014 */public class Mydbopenhelper extends Sqliteopenhelper {//Database The name of private static final String Datebase_name = "student.db";//The version of the database private static final int datebase_version = 1;//must be defined A constructor and calls the parent class's constructor public Mydbopenhelper (context context) {Super (context, datebase_name, NULL, datebase_version);} When the database is used for the first time, the system will determine if the database has been created. If you have not yet created the database, run the following SQL statement to complete the creation of the database @overridepublic void OnCreate (Sqlitedatabase db) {db.execsql ("Create TABLE student (ID Integer PRIMARY key Autoincrement,name Vchar (), Age Integer,school Vchar (50));} When the version of the database changes, the system will automatically call this method, we can complete the database update and other operations @overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {}}

After we have completed this class that inherits from Sqliteopenhelper, we will generally create a special class for the operation of database additions and deletions. Here, we take the student table as an example, explain how to achieve the student table of additions and deletions to change the operation
First look at our student entity class

/** * Student Entity class * @author Zhaokaiqiang * @time   June 4, 2014 */public class Student implements Serializable {private static fi nal long serialversionuid = -4885878468381093121l;private int id;private string name;private int age;private String School ;p ublic int getId () {return ID;} public void setId (int id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} public int getage () {return age;} public void Setage (int.) {this.age = age;} Public String Getschool () {return school;} public void Setschool (String school) {this.school = school;} public Student (int ID, string name, int age, string school) {super (); this.id = Id;this.name = Name;this.age = Age;this.sch Ool = School;} Public Student () {super ();} @Overridepublic String toString () {return "--id =" + id + "--name =" + name + "--age =" + age + "--school =" + School ;}}

First look at our method of adding data

Add Data public void Add (string name, Int. age, String school) {Sqlitedatabase database = Dbopenhelper.getwritabledatabase (); Database.execsql ("INSERT into student (Name,age,school) VALUES (?,?,?)", new object[] {name, age, School});}

In the Add method, we use placeholders to add the data we need to add to our database. We can use the unit test to complete the test of adding a method, here is the method of unit test

Test data add public void Testadd () throws Exception {Studentdbhelper dbhelper = new Studentdbhelper (GetContext ()); Dbhelper.add ("Zhao", "Qust");d bhelper.add ("Kai", 18, "Thanh da");d bhelper.add ("Qiang", 17, "Tsinghua");d Bhelper.add ("Da", 16, "Qing Nong");d Bhelper.add ("Huai", 15, "Qing medical");d Bhelper.add ("Dan", 14, "Qingdao");}

After the unit test has finished running, there is no error message, which means that we have added the data successfully. Before we finish adding data, the system will automatically create a database named "Student.db" under the/data/data/<package name>/database/directory, as shown in

When this file exists, it means that we have completed the creation of the database and the addition of data.
So how do you see the information in the database?
We can use the SQLite developer software to complete the view of the SQLite database. First we export our database files to our desktop and then use this software to import the database files into


After the import is successful, we can see the structure of the table we created and the data we inserted.



Here is the data we have successfully added


Here, we are done with adding data.
Other methods of pruning and checking are similar.
Here's how to delete

Delete data public void delete (int id) {Sqlitedatabase database = Dbopenhelper.getwritabledatabase ();d atabase.execsql (" Delete from student where id=? ", new object[] {ID});}

For unit Testing

Delete method Test public void Testdelete () throws Exception {Studentdbhelper dbhelper = new Studentdbhelper (GetContext ()); Dbhelper.delete (3);}

Result diagram

Modify method

Modify public void Update (Student Student) {sqlitedatabase database = Dbopenhelper.getwritabledatabase (); Database.execsql ("Update student set name=?,age=?,school=?") Where id=? ", new object[] {student.getname (), Student.getage (), Student.getschool (), Student.getid ()});}

Unit Test

Modify method Test public void Testupdate () throws Exception {Studentdbhelper dbhelper = new Studentdbhelper (GetContext ()); Student Student = new Student (1, "Modified", 10, "Home Squat");d bhelper.update (Student);}

Result diagram


Query method

Query public Student find (int id) {Sqlitedatabase database = Dbopenhelper.getreadabledatabase ();//cursors that get query results cursor cursor = Database.rawquery ("SELECT * from student where id=?", new string[] {id + ""});//Use the cursor to get the corresponding value if (Cursor.movetofirst ()) { Student Student = new Student () Student.setid (Cursor.getint ("id")); Cursor.getcolumnindex ( Cursor.getint (Cursor.getcolumnindex ("Age")); Student.setname (Cursor.getstring (Cursor.getcolumnindex ("name")); Student.setschool (Cursor.getstring (Cursor.getcolumnindex ("school")); return student;} return null;}

Unit Test

Find method Test public void Testfind () throws Exception {Studentdbhelper dbhelper = new Studentdbhelper (GetContext ()); Student Student = dbhelper.find (1); LOG.D (TAG, student.tostring ());}

Results

So far, the simple addition and deletion of SQLite database is introduced here, if there is doubt, also please message exchange!