Android learning notes -- Sqlite database, android -- sqlite

Android learning notes -- Sqlite database, android -- sqlite

I learned about data storage in Android a few days ago, including file storage, SharedPreferences storage, and the feature of Acndroid: SQLite database storage. I was surprised that a lightweight SQLite database was embedded in Android, which made a qualitative leap in local data persistence.

I want to summarize my understanding of the SQLite database on the blog through my teacher's explanation in the previous two days, and my own reading and summarizing in the class, which is also convenient for later review. Android provides the SQLiteOpenHelper help class to facilitate database management. This class is an abstract class. In this class, there are two Abstract METHODS: onCreate (), one is onUpdate (). We must inherit SQLiteOpenHelper from our help class and implement these two abstract methods, create tables and update and maintain databases in these two abstract methods respectively. SQLiteOpenHelper also has two very important instance methods: getReadableDatabase () and getWriteableDatabase (). Both methods can open or create an existing database (if the database has been opened for a long time, otherwise create a new database) and return an object that can operate on the database. The difference is that when the database cannot be written (such as the disk space is full), The getReadableDatabase () method returns an object to open the database in read-only mode, while getWriteableDatabase () an exception occurs in the method (for example, the read/write function is often applied to all objects obtained by both ). SQLiteOpenHelper has two constructor methods that can be rewritten. Here we select the one with fewer parameters. In this constructor, four parameters are received. The first parameter is Context. This parameter represents the Context and must be used to operate the database. The second parameter is the database name, it indicates the name of the database to be created. The third parameter allows us to return a custom Curs   o   r when creating the database. Generally, it is passed into the nu ll; the fourth parameter represents the version number of the current database and can be used for Database Upgrade operations.

After creating a SQLiteOpenHelper instance, call its getReadableDatabase () or getWriteableDatabase () method to create a database, database files are stored in the/data/<package name>/databases directory. At this time, the override onCreate method will also be executed, and some table creation operations are usually completed in it.

Unlike other databases, SQLite databases have a large number of complex data types. Their common data types are simple:Integer, real represents the floating point type, text represents the text type, blob represents the binary type.

Although the created database can be seen in File Explorer, it cannot be seen in its internal tables and data in the table, so we can use adb shell to view it here, to use the adb shell, you need to configure the environment variables of the adb, which is not described here. There is a simple tutorial on the Internet. After environment variables are configured, Enter cmd in Win + R to enter the command prompt mode, enter adb shell to enter the adb environment, and then enter the command: cd/data/<package name>/databases enter this directory, enter ls to view files in the directory, and then enter sqlite3 + database name to enter the SQL editing mode. Type. table to View tables in the database, and enter the. schema command to view the table creation statements. (In sqlite editing mode, each statement must be followed ). The following uses a Demo to explain the CRUD operations in the SQLi te database and some other precautions for the database.

First, create our own SQLOpenHelper class to inherit from SQLiteOpenHelper and override the methods and constructor:

 1 package com.example.databasetest; 2  3 import android.content.Context; 4 import android.database.sqlite.SQLiteDatabase; 5 import android.database.sqlite.SQLiteDatabase.CursorFactory; 6 import android.database.sqlite.SQLiteOpenHelper; 7  8 public class SqlOpenHelper extends SQLiteOpenHelper { 9 10     public static final String CREATE_BOOK = "create table book("11             + "id integer primary key autoincrement, " 12             + "name text," 13             + "price real)";14     public SqlOpenHelper(Context context, String name, CursorFactory factory,15             int version) {16         super(context, name, factory, version);17         // TODO Auto-generated constructor stub18     }19 20     @Override21     public void onCreate(SQLiteDatabase db) {22         // TODO Auto-generated method stub23         db.execSQL(CREATE_BOOK);24     }25 26     @Override27     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {28         // TODO Auto-generated method stub29         }30     }31 32 }

We can see that a table book is created by calling the execSQL () method of SQLiteDatbas In the override onreate method.

Then, four buttons are added to the main interface to create a database disease and complete the CRUD operation:

1 <LinearLayout xmlns: android = "http://schemas.android.com/apk/res/android" 2 xmlns: tools = "http://schemas.android.com/tools" 3 android: layout_width = "match_parent" 4 android: layout_height = "match_parent" 5 android: orientation = "vertical" 6> 7 8 <Button 9 android: id = "@ + id/btn1" 10 android: layout_width = "match_parent" 11 android: layout_height = "wrap_content" 12 android: text = "CREATE_BOOK"/> 13 <Button 14 android: id = "@ + id/btn2" 15 android: layout_width = "match_parent" 16 android: layout_height = "wrap_content" 17 android: text = "update"/> 18 <Button 19 android: id = "@ + id/btn3" 20 android: layout_width = "match_parent" 21 android: layout_height = "wrap_content" 22 android: text = "delete"/> 23 <Button 24 android: id = "@ + id/btn4" 25 android: layout_width = "match_parent" 26 android: layout_height = "wrap_content" 27 android: text = "select"/> 28 </LinearLayout>Activity_main

We have registered and instantiated four buttons in MainActivity, and registered the button listening event:

1 1 package com. example. databasetest; 2 2 3 3 import android. app. activity; 4 4 import android. content. contentValues; 5 5 import android. database. cursor; 6 6 import android. database. sqlite. SQLiteDatabase; 7 7 import android. OS. bundle; 8 8 import android. util. log; 9 9 import android. view. view; 10 10 import android. view. view. onClickListener; 11 11 import android. widget. button; 12 12 import android. widget. Toast; 13 13 14 14 public class MainActivity extends Activity implements OnClickListener {15 16 16 private Button btn1, btn2, btn3, btn4; 17 17 private SqlOpenHelper helper; 18 18 private SQLiteDatabase db; 19 19 @ Override20 20 protected void onCreate (Bundle savedInstanceState) {21 21 super. onCreate (savedInstanceState); 22 22 setContentView (R. layout. activity_main); 23 23 helper = new SqlOpenHelper (this, "Book_store", null, 1); 24 24 btn1 = (Button) findViewById (R. id. btn1); 25 25 btn2 = (Button) findViewById (R. id. btn2); 26 26 btn3 = (Button) findViewById (R. id. btn3); 27 27 btn4 = (Button) findViewById (R. id. btn4); 28 28 btn1.setOnClickListener (this); 29 29 btn2.setOnClickListener (this); 30 30 btn3.setOnClickListener (this); 31 31 btn4.setOnClickListener (this ); 32 32} 33 34 34 35 35 @ Override36 36 public void onCli Ck (View v) {37 37 // TODO Auto-generated method stub38 38 switch (v. getId () {39 39 case R. id. btn1: 40 40 db = helper. getWritableDatabase (); 41 41 db. beginTransaction (); // start transaction 42 42 try {43 ContentValues values = new ContentValues (); 44 44 values. put ("name", "Android"); 45 45 values. put ("price", 16.5); 46 46 values. put ("category_id", "1"); 47 47 db. insert ("book", null, values); 48 48 db. setTransactionSu Ccessful (); // The transaction has been successfully executed 49 Toast. makeText (this, "Create Success", Toast. LENGTH_SHORT ). show (); 50 50} catch (Exception e) {51 51 // TODO Auto-generated catch block52 52 e. printStackTrace (); 53 53} finally {54 54 db. endTransaction (); // end transaction 55 55 db. close (); 56 56} 57 57 break; 58 58 case R. id. btn2: 59 59 db = helper. getWritableDatabase (); 60 60 ContentValues values = new ContentValues (); 61 61 values. put ("na Me "," zhangsan "); 62 62 values. put (" price ", 20); 63 63 db. update (" book ", values," id =? ", New String [] {5 +" "}); 64 64 break; 65 65 case R. id. btn3: 66 66 db = helper. getWritableDatabase (); 67 db. delete ("book", "id =? ", New String [] {1 +" "}); 68 68 break; 69 69 case R. id. btn4: 70 70 db = helper. getWritableDatabase (); 71 71 Cursor cursor = db. query ("book", null, "id DESC"); 72 72 while (cursor. moveToNext () {73 73 String name = cursor. getString (cursor. getColumnIndex ("name"); 74 74 String price = String. valueOf (cursor. getFloat (2); 75 75 Log. d ("MainActivity", name); 76 76 Log. d ("MainActivity", price); 77 77} 78 78 break; 79 79 default: 80 80 break; 81 81} 82 82} 83 84 84}

Btn1 indicates creating a database and adding data to the database. The btn2 Click Event updates data to the database, while btn3 is the delete operation, and btn4 is the most complex query operation.

When inserting data into a table, the transaction is used, and the operations in the transaction are either done or not done, so that the data is correct. Before each database operation, use getWriteableDatabase () of our custom help class to open (create) The database and return an operable SQLiteDatabase object.

Add data:

Call the insert method of SQLiteDatabase. We can use one of the three parameters in the method. The first parameter indicates the name of the database table to be operated, and the second parameter indicates the column value is null, generally, null is entered. The third parameter indicates the data to be inserted. It is of the ContentValues type. A variable of the ContentValues type has a put method that stores the data as a key-value pair.

Update Data:

SQLiteDatabase. update method. We use four parameter methods. The first parameter represents the database to be operated, and the second parameter is the value to be updated, which is of the ContentValues type, or store the value to be updated in the form of a key-value pair through his put method. The third parameter is equivalent to the where condition, it and the fourth parameter form a where condition using placeholders to control which rows meet the conditions are updated. For example:

Db. update ("book", values, "id =? ", New String [] {5 +" "}); id =? The value in the fourth parameter is '? 'Partial content

If the two parameters are null, all rows are updated. This method returns an integer, which is the number of affected rows.

Delete data:

SQLiteDatabase. delete method. This method has three parameters. The first parameter represents the database we want to operate on. The second and third parameters are basically the same as those in update and are not described.

Query data:

It is also the most complex operation in the database. Here, we simply describe its usage. The database query method returns a Cursor type variable. We use the moveToNext method of Cursor to retrieve the query result. You can use multiple query methods, such as SQLiteDatabase in this Demo. the query method receives 7 parameters, but we don't have to worry about them. We don't care about these parameters first, but only use the first parameter: The table to be queried. And the last parameter: sort by id in descending order. Then, the value is obtained through Cursor. getString () in another while loop. The seven parameters of this method are described as follows:

 

Using the above method to operate the database benefits from the convenient API provided by Android, so that even if we do not understand the SQL language, we can perform operations on the database, however, we can also use SQL to operate databases:

How to add data:

Db.exe cSQL ("insert into book (name, price) values (" haha ", 23)"); // SQL statement Method

Db.exe cSQL ("insert into book (name, price) values (?,?) ", New String [] {" haha ", 34}); // placeholder Method

Update Data:

Db.exe cSQL ("update book set name =? Where id =? ", New String [] {" pig ", 2 + ""});

Delete data:

Db.exe cSQL ("delete from book where id>? ", New String [] {" 3 "});

Query data:

Db. rawQuery ("select * from book", null); // The second parameter is the query condition.