35. SQLite. Methods update and delete with condition

In this lesson:

- we use the query and delete methods with indication of the condition

In the last lesson we discussed how to insert a record, read all the records from the table, and clear the table. Now let's see how to update and delete a specific entry.

We will not create a new project, use P0341_SimpleSQLite from the last lesson. Let's change the screen a bit, add an ID field and buttons for updating and deleting .

Rewrite main.xml :

  <? xml version = "1.0" encoding = "utf-8"?> 
  <LinearLayout 
  xmlns: android = "http://schemas.android.com/apk/res/android" 
  android: layout_width = "fill_parent" 
  android: layout_height = "fill_parent" 
  android: orientation = "vertical"> 
  <LinearLayout 
  android: id = "@ + id / linearLayout4" 
  android: layout_width = "match_parent" 
  android: layout_height = "wrap_content" 
  android: layout_marginTop = "5dp"> 
  <Textview 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: text = "ID" 
  android: layout_marginLeft = "5dp" 
  android: layout_marginRight = "25dp"> 
  </ TextView> 
  <Edittext 
  android: id = "@ + id / etID" 
  android: layout_width = "70dp" 
  android: layout_height = "wrap_content" 
  android: layout_marginTop = "2dp"> 
  </ Edittext> 
  <Button 
  android: id = "@ + id / btnUpd" 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: text = "Update"> 
  </ Button> 
  <Button 
  android: id = "@ + id / btnDel" 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: text = "Delete"> 
  </ Button> 
  </ LinearLayout> 
  <LinearLayout 
  android: id = "@ + id / linearLayout1" 
  android: layout_width = "match_parent" 
  android: layout_height = "wrap_content"> 
  <Textview 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: text = "Name" 
  android: layout_marginLeft = "5dp" 
  android: layout_marginRight = "5dp"> 
  </ TextView> 
  <Edittext 
  android: id = "@ + id / etName" 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: layout_weight = "1"> 
  <requestFocus> 
  </ requestFocus> 
  </ Edittext> 
  </ LinearLayout> 
  <LinearLayout 
  android: id = "@ + id / linearLayout3" 
  android: layout_width = "match_parent" 
  android: layout_height = "wrap_content"> 
  <Textview 
  android: id = "@ + id / textView2" 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: text = "Email" 
  android: layout_marginLeft = "5dp" 
  android: layout_marginRight = "5dp"> 
  </ TextView> 
  <Edittext 
  android: id = "@ + id / etEmail" 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: layout_weight = "1"> 
  </ Edittext> 
  </ LinearLayout> 
  <LinearLayout 
  android: id = "@ + id / linearLayout2" 
  android: layout_width = "match_parent" 
  android: layout_height = "wrap_content"> 
  <Button 
  android: id = "@ + id / btnAdd" 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: text = "Add"> 
  </ Button> 
  <Button 
  android: id = "@ + id / btnRead" 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: text = "Read"> 
  </ Button> 
  <Button 
  android: id = "@ + id / btnClear" 
  android: layout_width = "wrap_content" 
  android: layout_height = "wrap_content" 
  android: text = "Clear"> 
  </ Button> 
  </ LinearLayout> 
  </ LinearLayout> 

By pressing the Update button, we will read the contents of the Name and Email fields, and update the record in the table for which id = value from the ID field. By pressing the Delete button we will delete the record from the table by id = value from the ID field. The screen turned out, of course, not the best from the point of view of ergonomics and usability, but we do not have a circle of young designers, but a serious study of the database.

Let's edit MainActivity.java .   Add a description and definition of new screen elements, assigning handlers for buttons.

(Only add bold italic text. If not, zoom in)

Now we add onClick implementation:

(Only add bold italic text)

We add the id variable, write the value of the etID field in it. We add two new branches to switch :

btnUpd - update record in mytable. We check that the id value is not empty, fill cv with the data for the update and update the record. To do this, use the update method. The input is the name of the table filled with ContentValues with the values ​​to update, the condition string (Where), and an array of arguments for the condition string. In the condition line, I used the sign ? . When querying the database, the value from the array of arguments will be substituted for this sign, in our case it is the value of the id variable. If signs ? there are several conditions in the string, they will be assigned the values ​​from the array in order. The update method returns us the number of updated records, which we output to the log.

btnDel - delete record from mytable. We check that id is not empty and call the delete method. The input is the name of the table, the condition string , and the argument array for the condition. The delete method returns the number of deleted lines, which we output to the log.

Please note that the conditions for both update and delete are the same for me, namely id = value from the etID field . But I implemented them a little differently. For update used the symbol ? in the string conditions and an array of arguments. And for delete, I inserted the value immediately into the condition string. Thus, I just showed ways to form the condition . And you already use the one that is more like or better in a particular situation.

All save and run. In the last lesson we added a couple of records, but then we deleted them. So I will add again. Add a couple of entries, click Read , I see in the log:

ID = 3, name = Ivan Petrov, email = ipetrov @ abc.com
ID = 4, name = Anton Sidorov, email = asidorov @ def.com

Now let's try to update the record with ID = 3. To do this, enter 3 in the ID field and new data in the Name and Email fields:

Click Update , look at the log:

- Update mytabe: ---
updated rows count = 1

one record updated, that's right.

Click Read and verify this. Log:

ID = 3, name = Petr Ivanov, email = pivanov @ abc.com
ID = 4, name = Anton Sidorov, email = asidorov @ def.com

Record really updated.

Now let's delete the record with ID = 4. Enter 4 in the ID field

Click Delete , look at the log:

--- Delete from mytabe: ---
deleted rows count = 1

one record deleted.

Click Read , look at the log:

--- Rows in mytable: ---
ID = 3, name = Petr Ivanov, email = pivanov @ abc.com

there was one entry.

If you try to delete an entry with an empty ID field, nothing will happen, because we implemented a check. If you try to delete the record with a non-existent ID , then the delete method returns 0 . Those. nothing has been deleted because There were no records to delete.

Now you can read , insert , delete and modify records in SQLite.

Full code MainActivity.java :

In the next lesson:

- parse the method of reading the data query
- we use sorting, grouping, conditions, having