[Android 10] -- Data Storage 2: SQLite database operations

 Disclaimer: The book "Secrets of Android Application Development", which records the logs of the book, references the relevant code and summary, and has no commercial use, it is completely a record of self-learning, and many problems will inevitably occur in learning just now. If there are any mistakes, please criticize them a lot.

I. Data Storage-SQLite database operations

Three data operations have been learned before, and there is also one of the most common database operations, because the previous three data methods generally only store some simple data with a small amount of data. If the data volume is large and needs to be managed, maintained, and upgraded, it cannot meet the needs. The SQLite database provides a solution to this problem. For more information about why SQLite data is used, there are also many advantages on the Internet:

1. lightweight, It is a database engine in the process, and there is no database client or server.

2. independence. The SQLite core engine does not rely on third-party software and does not need to be installed.

3. Isolation. All information in the SQLite database is stored in a single file for convenient management and maintenance.

4. cross-platform support for most operating systems and many mobile phone operating systems.

5. multi-language interfaces, supporting many programming interfaces.

6. Security: SQLite databases implement independent transaction processing through database-level exclusivity and shared locks. That is to say, it can satisfy the needs of multiple threads to read data from the database at the same time, but only one is writable.

 

Database Operations are nothing more than adding, deleting, modifying, and querying data and creating and deleting tables. The following example contains the above operations. By default, this instance creates a data1 database and table Table1. Then, it sets the button listening. When you click the arrow key on the left, a piece of data is added, and a row of data is deleted from the right, the number key 1 is to modify the last data, the number key 2 is to delete the table, and the number key 3 is to delete the database.

Key source code:

Public class databasedataactivity extends activity {Private Static int COUNT = 0; private sqlitedatabase = NULL; // database object private final static string database_name = "data1 "; // database name private final static string table_name = "Table1"; // table name private final static string table_id = "_ id"; private final static string table_num = "num "; private Final Static string table_data = "data"; private final stat IC string create_table = "create table" + table_name + "(" + table_id + "integer primary key," + table_num + "integer," + table_data + "text )"; linearlayout = NULL; listview = NULL; // used to display data in the database/** called when the activity is first created. * // @ override public void oncreate (bundle savedinstancestate) {super. oncreate (savedinstancestate); linearlayout = new linearlayout (Th Is); linearlayout. setorientation (linearlayout. vertical); linearlayout. setbackgroundcolor (Android. graphics. color. black); listview = new listview (this); linearlayout. layoutparams Params = new linearlayout. layoutparams (linearlayout. layoutparams. fill_parent, linearlayout. layoutparams. wrap_content); listview. setbackgroundcolor (color. black); linearlayout. addview (listview, Params); setcontentview (linearl Ayout); // set to display the linearlayout layout sqlitedatabase = This. openorcreatedatabase (database_name, mode_private, null); // open or create a database try again sqlitedatabase.exe csql (create_table);} catch (exception e) {updataadapter ();}} // update and display the view private void updataadapter () {// obtain the database phones cursorcursor cur = sqlitedatabase. query (table_name, new string [] {table_id, table_num, table_data}, null, null); Count = cur. Getcount (); If (cur! = NULL & cur. getcount ()> = 0) {// listadapter is the bridge between listview and background data listadapter adapter = new simplecursoradapter (this, android. r. layout. simple_list_item_2, // defines the display template of each row in the list, indicating that each row contains two data items cur, // the database's cursor object New String [] {table_num, table_data }, // retrieve data from the table_num and table_data columns of the database new int [] {android. r. id. text1, android. r. id. text2}); // viewslistview corresponding to name and number. setadapter (adapter);/* Add the adapter to m_listview */} public Boolean onkeydown (INT keycode, keyevent event) {// todo auto-generated method stubif (keycode = keyevent. keycode_back) {sqlitedatabase. close (); this. finish (); Return true;} return Super. onkeydown (keycode, event);} public Boolean onkeyup (INT keycode, keyevent event) {Switch (keycode) {Case keyevent. keycode_dpad_left: adddata (); break; Case keyevent. keycode_dpad_right: deletedata (); break; Case keyevent. keycode_1: updata (); break; Case keyevent. keycode_2: deletetable (); break; Case keyevent. keycode_3: deletedatabase (); break;} return true;} // Delete the private void deletedatabase () {This. deletedatabase (database_name); this. finish () ;}// Delete the table private void deletetable () {sqlitedatabase.exe csql ("Drop table" + table_name); this. finish () ;}// update data private void updata () {contentvalues values = new contentvalues (); values. put (table_num, count); values. put (table_data, "modified data" + count); // update sqlitedatabase. update (table_name, values, table_num + "=" + integer. tostring (count-1), null); updataadapter () ;}// delete data private void deletedata () {// todo auto-generated method stubsqlitedatabase.exe csql ("delete from" + table_name + "where _ id =" + integer. tostring (count); count --; If (count <0) {COUNT = 0;} updataadapter ();} // Add private void adddata () {// todo auto-generated method stubcontentvalues CV = new contentvalues (); cv. put (table_num, count); cv. put (table_data, "Test Database Data" + count);/* insert data */sqlitedatabase. insert (table_name, null, CV); count ++; updataadapter ();}}

Running effect: the new operation is displayed on the left, and the modification operation is displayed on the right.

Generally, you can export the database files in the corresponding directory before usingSqlitespy.exeTool to view, the database file storage address is consistent with the storage directory of the previous data operations, the database file is placed under the databases file. This tool can be downloaded from my resources.

[Problem]

At the beginning of table creation, I set the primary key as ID, but during the actual running of the example, I always reported that _ id could not be found. I was wondering if I didn't create this column. Later, we found that _ id is required for creating SQLite tables, because the underlying classes are found to be _ id, which can be run after modification.

 

[Extended learning] use of cursor

About cursor, when you understand and use Android cursor, you must first know several things about cursor:

Cursor is a set of rows.
Use movetofirst () to locate the first line.
You must know the name of each column.
You must know the Data Type of each column.
Cursor is a random data source.
All data is obtained by subscript.
An important method for cursor:

Close () -- close the cursor to release resources
Copystringtobuffer (INT columnindex, chararraybuffer buffer) -- retrieves the text of the requested column in the buffer and stores it
Getcolumncount () -- returns the total number of all columns
Getcolumnindex (string columnname) -- returns the name of the specified column. If no Column exists,-1 is returned.
Getcolumnindexorthrow (string columnname) -- returns the name of the specified column from scratch. If it does not exist, an illegalargumentexception exception is thrown.
Getcolumnname (INT columnindex) -- returns the column name from the given Index
Getcolumnnames () -- returns the column name of a string array.
Getcount () -- returns the number of rows in cursor.
Movetofirst () -- move the cursor to the first line
Movetolast () -- move the cursor to the last row
Movetonext () -- move the cursor to the next row
Movetoposition (INT position) -- move the cursor to an absolute position
Movetoprevious () -- move the cursor to the previous row

Let's take a look at a short piece of code:

If (cur. movetofirst () = false ){
// Empty cursor
Return;
}

Access the subscript of cursor to obtain the data.

Int namecolumnindex = cur. getcolumnindex (people. Name );
String name = cur. getstring (namecolumnindex );
Now let's take a look at how to cyclically extract the data we need from cursor.

While (cur. movetonext ()){
// The cursor is successfully moved.
// Extract data
}

When cur. movetonext () is false, the loop will jump out, that is, the cursor data loop is complete.