Android uses sqlitedatabase to operate SQLite databases (2)

Reprinting is also helpful. After reading this article, I understand, update

In addition to the execsql () and rawquery () methods described earlier, sqlitedatabase also provides operations corresponding to adding, deleting, updating, and querying:
Insert (), delete (), update (), and query (). These methods are actually used by cainiao who are not familiar with SQL syntax. For programmers who are familiar with SQL syntax, execsql () and rawquery () are used directly () to add, delete, update, and query data.


The insert () method is used to add data. The data of each field is stored using contentvalues. Contentvalues is similar to map. Compared with map, it provides the put (string key, xxx value) and getasxxx (string key) methods for data access. Key is the field name and value is the field value, xxx refers to various common data types, such as string and integer.

Java code:

Copy to clipboardJava code

1. Sqlitedatabase DB = databasehelper. getwritabledatabase ();
   
2. Contentvalues values = new contentvalues ();
   
3. Values. Put ("name ",
   "Security table ");
4. Values. Put ("Age", 4 );
   
5. Long rowid = dB. insert ("person ",
   Null, values); // returns the row number of the newly added record, regardless of the primary key ID.
   

No matter whether the third parameter contains data, the insert () method will inevitably add a record. If the third parameter is null, a record with null values other than the primary key will be added. The insert () method actually adds data by constructing an insert statement. The second parameter of the insert () method is used to specify the name of the null field. I believe you will be confused about this parameter, what is the role of this parameter? If the value of the third parameter is null or the number of elements is 0, the insert () method must add a record with null values for other fields except the primary key, to meet the syntax of this insert statement, the insert statement must be given a field name, such as insert.
Into person (name) values (null), if the field name is not given, the insert statement becomes like this: insert into person () values (), obviously this does not meet the standard SQL syntax. For field names, we recommend that you use a field other than the primary key. If an integer type primary key field is used, after executing an insert statement similar to insert into person (personid) values (null, the value of this primary key field is not null. If the value of the third parameter is not null and the number of elements is greater than 0, you can set the second parameter to null.



Use of the delete () method:


Java code:

Copy to clipboardJava code

1. Sqlitedatabase DB = databasehelper. getwritabledatabase ();
   
2. DB. Delete ("person", "personid <? ",
   New String [] {"2 "});
   
3. DB. Close ();

The above code deletes a record whose personid is less than 2 from the person table.



Use of the update () method:

Java code:

Copy to clipboardJava code

1. Sqlitedatabase DB = databasehelper. getwritabledatabase ();
   
2. Contentvalues values = new contentvalues ();
   
3. Values. Put ("name", "Security Table"); // The key is the field name, and the value is the value
   
4. DB. Update ("person", values, "personid =? ",
   New String [] {"1 "});
   
5. DB. Close ();

The code above is used to change the value of the Name field of the record whose personid is equal to 1 in the person table to "Security table ".

The query () method splits the SELECT statement into several components and serves as the input parameter of the method:

Java code:

Copy to clipboardJava code

1. Sqlitedatabase DB = databasehelper. getwritabledatabase ();
   
2. Cursor cursor = dB. Query ("person ",
   New String [] {"personid, name, age "},
   "Name like? ", New string [] {" % Chuanzhi % "},
   Null, null,
   "Personid DESC", "1, 2 ");
   
3. While (cursor. movetonext ()){
   
4. Int personid = cursor. getint (0 );
   // Obtain the value of the first column. The index of the first column starts from 0.
5. String name = cursor. getstring (1); // obtain the value of the second column
   
6. Int age = cursor. getint (2); // get the value of the third column
   
7. }
8. Cursor. Close ();
9. DB. Close ();

The above code is used to find records whose name field contains "intelligence" from the person table. matched records are sorted in descending order by personid. The first record is skipped for the sorted results and only two records are obtained.

Meanings of parameters in the query (table, columns, selection, selectionargs, groupby, having, orderby, limit) method:

Table: Table name. It is equivalent to the part after the SELECT statement from keyword. For multi-table join queries, you can use commas to separate the two table names.

Columns: name of the column to be queried. It is equivalent to the part after the select keyword of the SELECT statement.

Selection: the query Condition Clause, which is equivalent to the part after the where keyword of the SELECT statement. The placeholder "?" can be used in the Condition Clause.

Selectionargs: it corresponds to the placeholder value in the selection statement. The position of the value in the array must be the same as that of the placeholder in the statement. Otherwise, an exception occurs.

Groupby: equivalent to the part after the SELECT statement group by keyword

Having: equivalent to the part after the having keyword of the SELECT statement

Orderby: equivalent to the part after the SELECT statement order by keyword, such as: personid DESC, age ASC;

Limit: Specifies the offset and the number of records obtained, which is equivalent to the part following the limit keyword of the SELECT statement.

Java code:

Copy to clipboardJava code

1. Public class databasehelper
   Extends sqliteopenhelper {
2. Private Static
   Final string name = "android ";
   // Database Name
3. Private Static
   Final int version =
   1; // database version
   
5. }
6. Public class helloactivity
   Extends activity {
7. @ Override
8. Public void oncreate (bundle savedinstancestate ){
   
10. Button button = (button) This. findviewbyid (R. Id. Button );
    
11. Button. setonclicklistener (New View. onclicklistener (){
    
12. Public void onclick (view v ){
    
13. Databasehelper = new databasehelper (helloactivity. This );
    
14. Sqlitedatabase DB = databasehelper. getwritabledatabase ();
15. Db.exe csql ("insert into person (name, age) values (?,?) ",
    New object [] {"Security table ",
    4 });
16. DB. Close ();
17. }});
18. }
19. }

After the getwritabledatabase () or getreadabledatabase () method is called for the first time, sqliteopenhelper caches the current sqlitedatabase instance, and the sqlitedatabase instance normally keeps the database open, therefore, when you no longer need a sqlitedatabase instance, call the close () method to release the resource in time. Once the sqlitedatabase instance is cached, the same instance is obtained by calling the getwritabledatabase () or getreadabledatabase () method multiple times.