Android database storage method (I)-Basic SQLite operations

I recently started my internship in a project and fully realized my shortcomings, including defects in ability and personality. How to quickly master the development environment andProgramCommunication between members, how to accurately know the specific functions of the modules assigned to them, and so on are all big issues. More importantly, you cannot just writeCodeIt also needs to know the application environment of your code, how others use it, and what interfaces should they provide. This is a matter of scalability. It is not something that a newbie can understand immediately, but it is the key to our ability to "Remove farmers.

Not much nonsense. This article describes the operations on the android database, a new knowledge I have used in projects. I didn't have any development experience on the android database in the past, and everything is now available, so I would like to summarize it.

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

Database usage is nothing more than crud, that is, "Create, read, update, delete.

I. Create

Create is to create a table. To create a table, you must first create or open a database.

There are two ways to do this:

1. manually create or open a database

Sqlitedatabase database = openorcreatedatabase ("Student. DB", Mode_private,Null);
 

Call the openorcreatedatabase () method. If the database exists, open it and create one if it does not exist. The specific information of this method depends on the source code, but when we use it, we only need to specify the database name and the database is private.

2. Use sqliteopenhelper

  Public   class   sqlhelper extends sqliteopenhelper {  Public   sqlhelper (context, string name, cursorfactory factory,   int   Version) {super (context, name, factory, Version) ;}@ override   Public   void   oncreate (sqlitedatabase dB) {}@ override   Public   void  onupgrade (sqlitedatabase dB,  int  oldversion,  int   newversion) {} 

Then we use the following in activity:

 
Sqlhelper helper =NewSqlhelper (This, "Student. DB ",Null, 1 );
 

The version number (which cannot be a negative number) is used to upgrade the database later. because it is the first version, the version number is 1.
Sqliteopenhelper is an abstract database operation class. The first operation is oncreate. Here we can execute table creation and other actions, but this method does not actually create a database. The following conditions are used to create a database:

 
Sqlitedatabase database = helper. getwritabledatabase ();
 

When getwritabledatabase () or getreadabledatabase () is called, the database is actually created.

After you create or open a database, you can create a table.

It is not difficult to use databases, but it is difficult to build models. Here we only create three simple tables:

Teacher (teacherid, name, classid), student (studentid, name, classid), Class (classid, classname, studentid, teacherid), where teacherid, studentid, and classid are teacher, primary keys of student and class.

SQLite can directly execute SQL statements. Therefore, we can create a table as follows:

 
String create_class = "create table if not exists class (classid integer primary key," + "classname varchar (100)," + "studentid integer references student (studentid ), "+" teacherid integer references teacher (teacherid ))"; Sqlitedatabase DB=Helper. getwritabledatabase (); db.exe csql (create_class );
 

Remember to close the database in time after each use:

 
DB. Close ();
 

Based on the above method, we can quickly create three tables.

Ii. updata

The update part includes: insert and modify the two actions.

First, let's talk about the most basic action: insert.

We want to insert students into the class now, like this:

 
Classinfoprovider provider =NewClassinfoprovider (This); Student=NewStudent (2857, "Zheng wenbiao"); Classinfo=NewClassinfo ("China Telecom Class 1", 1); Provider. addstudent (student, classinfo );
 

Here we have three classes:

Public   Class  Classinfo {  Private  String name;  Private   Int  ID;  Public  Classinfo (){}  Public Classinfo (string name, Int  ID ){  This . ID = ID; This . Name = Name ;}  Public   Int  GETID (){  Return   This  . ID ;}  Public   Void Setid ( Int  ID ){  This . ID = ID ;}  Public String getname (){  Return   This  . Name ;}  Public   Void  Setname (string name ){  This . Name = Name ;}} 

 Public   Class  Teacher {  Private   Int  Teacherid; Private  String name;  Private  String classname;  Public  Teacher (){}  Public Teacher ( Int  Teacherid, string name ){  This . Name = Name;  This . Teacherid = Teacherid ;}  Public  Int  Getteacherid (){  Return   This  . Teacherid ;}  Public   Void Setteacherid ( Int  Teacherid ){  This . Teacherid = Teacherid ;}  Public  String getname (){  Return   This . Name ;}  Public   Void  Setname (string name ){  This . Name = Name ;}  Public  String getclassname (){  Return   This  . Classname ;}  Public   Void  Setclassname (string name ){  This . Classname = Name ;}} 

 Public   Class  Student {  Private   Int  Studendtd;  Private  String name;  Private  String classname;  Public  Student (){}  Public Student ( Int Studentid, string name ){  This . Name = Name;  This . Studentid = Studentid ;}  Public   Int  Getstudentid (){  Return   This  . Studentid ;}  Public   Void Setstudendid ( Int Studentid ){  This . Studentid = Studendtd ;}  Public  String getname (){  Return   This  . Name ;}  Public   Void  Setname (string name ){  This . Name = Name ;}  Public String getclassname (){  Return   This  . Classname ;}  Public   Void  Setclassname (string name ){  This . Classname = Name ;}} 

These three classes are used to store the basic information of student, teacher, and class.
Then we started to insert the students into the class:

  Public   void   addstudent (student, classinfo) {string insert_student_into_class  = "insert into class (classname, studentid, classid) values ('" + classinfo. getname () + "'," + student. getstudentid () + "," + classinfo. GETID () + ")" ; string insert_student  = "insert into student (studentid, name, classid) values ("+ student. getstudentid () + "," + "'" + student. getname () + "'," + classinfo. GETID () + ")" ; sqlitedatabase dB  =  helper. getwritabledatabase (); db.exe csql (insert_student); db.exe csql (insert_student_class _class); dB. close () ;} 

This is the practice of directly executing SQL statements.
Sqliteopenhelper encapsulates an insert method to facilitate the execution of the insert behavior:

Sqlitedatabase DB = Helper. getwritabledatabase (); contentvalues values = New  Contentvalues (); values. Put ( "Classname" , Classinfo. getname (); values. Put ( "Studentid" , Student. getstudentid (); values. Put ( "Classid" , Classinfo. GETID (); DB. insert ( "Class ", Null  , Values); contentvalues values1 =New  Contentvalues (); 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 map. The key value is the column value in the table, and the value is the corresponding field. The first parameter of the insert method is the name of the table to be inserted, and the second parameter is the corresponding column. Here we use null to represent all columns, and then the field to be inserted.
This method can indeed simplify our operations. At least we do not need to write so long SQL statements in our code, which is easy to make mistakes and annoying, especially when the insert action is continuously executed. However, it is worth noting that, if our database provides interface methods for other modules, and the persons or persons who want to modify them in the future are not ourselves, they may have to know what the parameters of these methods are, but if they execute SQL statements directly, as long as they have basic knowledge about the database, they will understand what they are doing and how to modify them. Even worse, if the android interface method changes in the future, the Code may be faulty. Of course, we are willing to believe that they will not modify the interface, because modification to the interface is an incorrect behavior, especially when the interface has been released.

Now we have data in our table. If we want to modify the table, we can change the Student name as follows:

Public VoidUpdatestudent (IntID, string name) {sqlitedatabase DB=Helper. getwritabledatabase (); string update_student= "Update student set name =" + "'" + name + "'where id =" + ID + ""; Db.exe csql (update_student); DB. Close ();}
 

Of course, we can also simplify the process:

 Public VoidUpdatestudent (IntID, string name) {sqlitedatabase DB=Helper. getwritabledatabase (); contentvalues values = new contentvalues ();
Values. Put ("name", name );
DB. Update ("student", values, "studentid =? ", New string [] {ID + ""});DB. Close ();}
 

In the update method, it is worth noting that the last two parameters are whereclause and whereargs. Whereclause indicates the location to be modified, and whereargs indicates the modified field. A string [] is required here whether we want to modify one or more fields.

Iii. Read
Read is a series of query actions.

In the student table, we want to query the student ID "Zheng wenbiao", that is, studentid:

  Public   int   getstudentid (string name) {  int  id = 0 ; string select_studentid  =" select studentid from student where name =? "; sqlitedatabase dB  =  helper. getwritabledatabase (); 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 the cursor. Cursor points to the current data record, and then we can get the corresponding data from the cursor.
Iv. Delete

Delete includes table deletion and data record deletion.

The first is the deletion of data records. Now we want to delete the records of students named "Zheng wenbiao:

 
 Public VoidDeletestudent (Student) {sqlitedatabase DB=Helper. getwritabledatabase (); string delete_student= "Delete from class where studentid =? "; Db.exe csql (delete_student,NewString [] {student. getstudentid () + ""}); DB. Close ();}
 

Then call this method as follows:

Student =NewStudent (2857, "Zheng wenbiao"); Classinfo=NewClassinfo ("China Telecom Class 1", 1); Provider. addstudent (student, classinfo); provider. deletestudent (student1 );
 

It can also be simplified:

 
DB. Delete ("class", "studentid =? ", New string [] {student. getstudentid () + ""});
 

The next step is to delete the table, which is very simple:

 
String drop_class = "Drop table class"; Sqlitedatabase DB=Helper.getwritabledatabase(mongomongodb.exe csql (drop_class );
 

During the test, the data in the tables in the database is redundant because it needs to run frequently, especially when the key value is set to auto-increment. Therefore, we need to delete the table after each test so that the table will not be affected during the next test.
V. Conclusion

SQLite is completely a learning task, so it is not very well understood. It is generally written, but please also point out the shortcomings.