Android Learning--basic operation of database SQLite

If you want to use the database in Android, using SQLite is a very good choice because it is an Android built-in database that provides a lot of support.

The use of the database is nothing more than a crud, that is, "Create,read,update,delete" the four basic operations.

One. Create

Create is creating a table, and to create a table, you first have to create or open a database.

There are two ways to do this:

1. Create or open a database manually

null);

Call the Openorcreatedatabase () method, if there is a database, open it without creating one. The specific information of this method still depends on the source code, but generally we use the time, as long as the name of the specified database and specify that the database is private on the line.

2. Using Sqliteopenhelper

 Public classSQLHelperextendsSqliteopenhelper { PublicSQLHelper (Context context, String name, Cursorfactory factory,intversion) {        Super(context, name, Factory, version); } @Override Public voidonCreate (Sqlitedatabase db) {} @Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {}}

Then we use it again in the activity:

New SQLHelper (thisnull, 1);

Version number (cannot be negative) is to facilitate later upgrade of the database, because it is the first edition, the version number is 1.
Sqliteopenhelper is an abstract database operation class, first performed by OnCreate, where we can perform actions such as creating a table, but the method does not actually create the database, the database is created in the following situations:

Sqlitedatabase database = Helper.getwritabledatabase ();

When you call Getwritabledatabase () or getreadabledatabase (), the database is actually created.

After creating or opening a database, we can build a table.

Using a database is not a difficult task, but it's hard to model it. Here we'll just build three simple tables:

Teacher (Teacherid, Name, classId), Student (Studentid,name,classid), Class (Classid,classname,studentid,teacherid), Where Teacherid,studentid,classid are the primary keys of the Teacher,student and class respectively.

SQLite can execute SQL statements directly, so we can build tables like this:

String create_class = "CREATE Table If not Exists CLASS (classId integer primary key,"                + "className varchar),"                + "StudentID integer References Student (StudentID),"                + "Teacherid integer References Teacher (Teacherid))"  = helper.getwritabledatabase (); Db.execsql (create_class);

Remember to close the database in a timely manner every time you finish using the database:

Db.close ();

According to the above method, we can build three tables very quickly.

Two. Updata

Update This section includes: Insert, modify these two actions.

First, the most basic action: insert.

Now we want to insert students into the class, like this:

New Classinfoprovider (Thisnew Student (2857, "Zhengwenbiao"new ClassInfo ("Telecom 1 class", 1 ); Provider.addstudent (student, ClassInfo);

Here we have three classes:

 Public classClassInfo {PrivateString name; Private intID;  PublicClassInfo () {} PublicClassInfo (String name,intID) { This. ID =ID;  This. Name =name; }     Public intgetId () {return  This. ID; }     Public voidSetId (intID) { This. ID =ID; }     PublicString GetName () {return  This. Name; }     Public voidsetName (String name) { This. Name =name; }  }

 Public classTeacher {Private intTeacherid; PrivateString name; PrivateString ClassName;  PublicTeacher () {} PublicTeacher (intTeacherid, String name) {         This. Name =name;  This. Teacherid =Teacherid; }     Public intGetteacherid () {return  This. Teacherid; }     Public voidSetteacherid (intTeacherid) {         This. Teacherid =Teacherid; }     PublicString GetName () {return  This. Name; }     Public voidsetName (String name) { This. Name =name; }     PublicString GetClassName () {return  This. ClassName; }     Public voidsetclassname (String name) { This. ClassName =name; }}

 Public classStudent {Private intStudendtd; PrivateString name; PrivateString ClassName;  PublicStudent () {} PublicStudent (intStudentID, String name) {         This. Name =name;  This. StudentID =StudentID; }     Public intGetstudentid () {return  This. StudentID; }     Public voidSetstudendid (intStudentID) {         This. StudentID =Studendtd; }     PublicString GetName () {return  This. Name; }     Public voidsetName (String name) { This. Name =name; }     PublicString GetClassName () {return  This. ClassName; }     Public voidsetclassname (String name) { This. ClassName =name; }}

These three classes are basic information for storing Student,teacher and class.
Then we start inserting the students into the class:

 Public void addstudent (Student Student, ClassInfo classinfo) {        = "Insert into Class (ClassName, StudentID, classId) Valu Es (' "+ classinfo.getname () +" ', "                + student.getstudentid () +", "+ classinfo.getid () +") ";         = "Insert into Student (StudentID, Name, classId) Values (" + student.getstudentid () + ","                + "'" + student.getname () + " ', ' + classinfo.getid () + ")";         = helper.getwritabledatabase ();        Db.execsql (insert_student);        Db.execsql (insert_student_into_class);        Db.close ();    }

This is the practice of directly executing SQL statements.
The Sqliteopenhelper encapsulates an insert method to allow us to perform the insertion behavior:

Sqlitedatabase db =helper.getwritabledatabase (); Contentvalues Values=Newcontentvalues (); Values.put ("ClassName", Classinfo.getname ()); Values.put ("StudentID", Student.getstudentid ()); Values.put ("ClassId", Classinfo.getid ()); Db.insert ("Class",NULL, values); Contentvalues values1=Newcontentvalues (); Values1.put ("StudentID", Student.getstudentid ()); Values1.put ("Name", Student.getname ()); Values1.put ("ClassId", Classinfo.getid ()); Db.insert ("Student",NULL, values1); Db.close ();

Contentvalues is actually a dictionary. The Map,key value is the column value in the table, and value is the corresponding field. The first parameter of the Insert method is the name of the table to be inserted, the second parameter is the corresponding column, where we represent all the columns with NULL, and then the field we want to insert.
Such a method can really simplify our operations, at least not in our code to write so long SQL statements, easy to make mistakes, and very annoying, especially when the insertion of the action is constantly executing. But there is a place to note: If our database is to provide an interface method for other modules to use, and later to modify the person or view is not our own, they may have to know the parameters of these methods, but directly execute the SQL statement, as long as he has the basic knowledge of the database, will understand what this is doing, Also know how to modify. What's worse is that after the Android interface method changes, the code can be problematic. Of course, we would like to believe that they will not modify the interface, because modification of the interface is a wrong behavior, especially if the interface is already published.

Now that our table has data, if we want to modify it, like to change the student's name, you can do this:

 Public void updatestudent (int  ID, String name) {        = helper.getwritabledatabase ();         = "Update Student Set name =" + "'" + Name + "' Where id=" + ID + "";        Db.execsql (update_student);        Db.close ();}

Of course, we can also simplify:

 Public void updatestudent (int  ID, String name) {     = helper.getwritabledatabase ();      New contentvalues ();     Values.put ("name", name);     Db.update (new string[]{id + ""});        
Db.close ();}

In the Update method, it is worth noting that the last two parameters, Whereclause and Whereargs. Whereclause indicates where to modify, Whereargs represents the modified field, whether we want to modify a field or more than one, this requires a string[].

Three. Read
The so-called Read is a series of query actions.

       We're going to look up a student in this table called "Zhengwenbiao", which is StudentID:

 public  int   Getstudentid (String name) { int  id = 0; String Select_studentid  = "Select StudentID from Student Where name=?"         = Helper.getwritabledatabase (); Cursor cursor  = db.rawquery (Select_studentid, new   string[]{name});  if   (Cursor.movetonext ()) {ID         = Cursor.getint (0);        } cursor.close ();        Db.close ();     return   ID; }

Here we need to use cursor cursors. The cursor points to the current data record, and then we can get the corresponding data from the cursor.
Four. Delete

Delete includes the deletion of the table and the deletion of the data record.

The first is the deletion of the data record. Now we want to delete the record of the student named "Zhengwenbiao":

 Public void deletestudent (Student Student) {        = helper.getwritabledatabase ();         = "Delete from Class Where studentid=?" ;         New String[]{student.getstudentid () + ""});        Db.close ();    }

It then calls the method:

New Student (2857, "Zhengwenbiao"new ClassInfo ("Telecommunications 1 Classes", 1); Provider.addstudent (Student, ClassInfo); Provider.deletestudent (student1);

The same can be simplified:

  New String[]{student.getstudentid () + ""});

Then delete the table, which is simple:

String drop_class = "DROP Table CLASS"= helper.getwritabledatabase ();d b.execsql (drop_class);

When doing tests, the data in the tables in the database is very redundant, especially when the key value is set to self-increment, because it needs to run frequently. Therefore, we need to delete the table after each test so that the next test will not be affected.

Android Learning--basic operation of database SQLite