Lesson 35. SQLite. Update and delete methods provided with conditions
Android Lessons

Lesson 35. SQLite. Update and delete methods provided with conditions


In this lesson:

– we use the update and delete methods to ensure the conditions

In the last lesson we figured out how insert record, consider all entries in the table and clean up table. Now let’s see how refresh and remove specific record.

We will not create a new project, we use it P0341_SimpleSQLite from the last lesson. Let’s change the screen a bit, add field for input ID and buttons for updates and removal.

rewrite main.xml:



    
        
        
        
        
        
        
    
    
        
        
        
            
            
        
    
    
        
        
        
        
    
    
        
        
        
    

When the button is pressed Update we will read the contents of the fields Name and Email, And update the entry in the table for which id = Field value ID. When the button is pressed Delete we will delete the entry from the table by id = Field value ID. The screen came out, of course, not the best in terms of ergonomics and usability, but here we are not a group of young designers, but a serious study of the database.

We will edit MainActivity.java. Add description and definition of new screen elements, assignment of handlers for buttons.

  final String LOG_TAG = "myLogs";

  Button btnAdd, btnRead, btnClear, btnUpd, btnDel;
  EditText etName, etEmail, etID;
  
  ...

  public void onCreate(Bundle savedInstanceState) {
    
    ...
    
    btnClear = (Button) findViewById(R.id.btnClear);
    btnClear.setOnClickListener(this);

    btnUpd = (Button) findViewById(R.id.btnUpd);
    btnUpd.setOnClickListener(this);

    btnDel = (Button) findViewById(R.id.btnDel);
    btnDel.setOnClickListener(this);

    etName = (EditText) findViewById(R.id.etName);
    etEmail = (EditText) findViewById(R.id.etEmail);
    etID = (EditText) findViewById(R.id.etID);

    // создаем объект для создания и управления версиями БД
    dbHelper = new DBHelper(this);
  }

(Note only the highlighted lines)

Now let’s complete the implementation onClick:

 public void onClick(View v) {

    // создаем объект для данных
    ContentValues cv = new ContentValues();

    // получаем данные из полей ввода
    String name = etName.getText().toString();
    String email = etEmail.getText().toString();
    String id = etID.getText().toString();

    // подключаемся к БД
    SQLiteDatabase db = dbHelper.getWritableDatabase();

    switch (v.getId()) {
    case R.id.btnAdd:
      ...
    case R.id.btnRead:
      ...
    case R.id.btnClear:
      ...
    case R.id.btnUpd:
      if (id.equalsIgnoreCase("")) {
        break;
      }
      Log.d(LOG_TAG, "--- Update mytable: ---");
      // подготовим значения для обновления
      cv.put("name", name);
      cv.put("email", email);
      // обновляем по id
      int updCount = db.update("mytable", cv, "id = ?",
          new String[] { id });
      Log.d(LOG_TAG, "updated rows count = " + updCount);
      break;
    case R.id.btnDel:
      if (id.equalsIgnoreCase("")) {
        break;
      }
      Log.d(LOG_TAG, "--- Delete from mytable: ---");
      // удаляем по id
      int delCount = db.delete("mytable", "id = " + id, null);
      Log.d(LOG_TAG, "deleted rows count = " + delCount);
      break;
    }
    // закрываем подключение к БД
    dbHelper.close();
  }

(Only add highlighted text)

We are adding a variable id, We write in it the value of the field etID. IN switch we add two new branches:

btnUpd renewal entries in mytable. We check that the value id not empty, fill in cv data for the update and we are updating record. The method is used for this purpose update. He is fed to the entrance name table filled ContentValues with refresh data, string conditions (Where) and array arguments for the condition string. I used the character in the condition bar ?. When querying a DB, a value from the array will be substituted instead of the character arguments, In our case this is the value of the variable id. if signs ? if there are several in the string, then they will be matched with the array in order. method update returns to us number of updated records that we display in the log.

btnDel removal records with mytable. We check that id not empty and we call the method delete. At the entrance we pass name table, row conditions and array arguments for the condition. The delete method returns the number of deleted rows that we output to the log.

Note that conditions and for update and for delete I have the same, namely id = Field value etID. But I implemented them a little differently. for update used the symbol ? in the condition line and massif arguments. And for delete inserted the value immediately into the string conditions. So, I just showed ways of forming a condition. And you already use the one you like best or better in a particular situation.

We’ll save everything and run it. In the last lesson, we added a couple of records, but then deleted them. So I’ll add again. I will add couple of records, click Read, I see in a beam:

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

Now let’s try to restore the record from ID= 3. For this we enter 3 in the field ID and new data in the fields Name and Email:

press Update, Look at the log:

– Update mytable: —
updated rows count = 1

one entry was updated, all right.

we press Read and make sure. log:

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

The recording has indeed been updated.

Now let’s delete the record from ID = 4. Enter 4 in the field ID

press Delete, Look at the log:

— Delete from mytable: —
deleted rows count = 1

one entry deleted.

press Read, Look at the log:

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

one entry left.

If you try to delete an entry from empty field ID, Then nothing will happen because we have implemented the check. If you try to delete a record with a nonexistent one IDThen the method delete will return 0. That is, nothing was deleted because there were no records to delete.

Now you can read, insert, remove and change SQLite entries.

full code MainActivity.java:

package ru.startandroid.develop.p0341simplesqlite;

import ru.startandroid.develop.p0341simpelsqlite.R;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class MainActivity extends Activity implements OnClickListener {

  final String LOG_TAG = "myLogs";

  Button btnAdd, btnRead, btnClear, btnUpd, btnDel;
  EditText etName, etEmail, etID;

  DBHelper dbHelper;

  /** Called when the activity is first created. */
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    btnAdd = (Button) findViewById(R.id.btnAdd);
    btnAdd.setOnClickListener(this);

    btnRead = (Button) findViewById(R.id.btnRead);
    btnRead.setOnClickListener(this);

    btnClear = (Button) findViewById(R.id.btnClear);
    btnClear.setOnClickListener(this);

    btnUpd = (Button) findViewById(R.id.btnUpd);
    btnUpd.setOnClickListener(this);

    btnDel = (Button) findViewById(R.id.btnDel);
    btnDel.setOnClickListener(this);

    etName = (EditText) findViewById(R.id.etName);
    etEmail = (EditText) findViewById(R.id.etEmail);
    etID = (EditText) findViewById(R.id.etID);

    // создаем объект для создания и управления версиями БД
    dbHelper = new DBHelper(this);
  }

  public void onClick(View v) {

    // создаем объект для данных
    ContentValues cv = new ContentValues();

    // получаем данные из полей ввода
    String name = etName.getText().toString();
    String email = etEmail.getText().toString();
    String id = etID.getText().toString();

    // подключаемся к БД
    SQLiteDatabase db = dbHelper.getWritableDatabase();

    switch (v.getId()) {
    case R.id.btnAdd:
      Log.d(LOG_TAG, "--- Insert in mytable: ---");
      // подготовим данные для вставки в виде пар: наименование столбца -
      // значение
      cv.put("name", name);
      cv.put("email", email);
      // вставляем запись и получаем ее ID
      long rowID = db.insert("mytable", null, cv);
      Log.d(LOG_TAG, "row inserted, ID = " + rowID);
      break;
    case R.id.btnRead:
      Log.d(LOG_TAG, "--- Rows in mytable: ---");
      // делаем запрос всех данных из таблицы mytable, получаем Cursor
      Cursor c = db.query("mytable", null, null, null, null, null, null);

      // ставим позицию курсора на первую строку выборки
      // если в выборке нет строк, вернется false
      if (c.moveToFirst()) {

        // определяем номера столбцов по имени в выборке
        int idColIndex = c.getColumnIndex("id");
        int nameColIndex = c.getColumnIndex("name");
        int emailColIndex = c.getColumnIndex("email");

        do {
          // получаем значения по номерам столбцов и пишем все в лог
          Log.d(LOG_TAG,
              "ID = " + c.getInt(idColIndex) + ", name = "
                  + c.getString(nameColIndex) + ", email = "
                  + c.getString(emailColIndex));
          // переход на следующую строку
          // а если следующей нет (текущая - последняя), то false -
          // выходим из цикла
        } while (c.moveToNext());
      } else
        Log.d(LOG_TAG, "0 rows");
      c.close();
      break;
    case R.id.btnClear:
      Log.d(LOG_TAG, "--- Clear mytable: ---");
      // удаляем все записи
      int clearCount = db.delete("mytable", null, null);
      Log.d(LOG_TAG, "deleted rows count = " + clearCount);
      break;
    case R.id.btnUpd:
      if (id.equalsIgnoreCase("")) {
        break;
      }
      Log.d(LOG_TAG, "--- Update mytable: ---");
      // подготовим значения для обновления
      cv.put("name", name);
      cv.put("email", email);
      // обновляем по id
      int updCount = db.update("mytable", cv, "id = ?",
          new String[] { id });
      Log.d(LOG_TAG, "updated rows count = " + updCount);
      break;
    case R.id.btnDel:
      if (id.equalsIgnoreCase("")) {
        break;
      }
      Log.d(LOG_TAG, "--- Delete from mytable: ---");
      // удаляем по id
      int delCount = db.delete("mytable", "id = " + id, null);
      Log.d(LOG_TAG, "deleted rows count = " + delCount);
      break;
    }
    // закрываем подключение к БД
    dbHelper.close();
  }

  class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context) {
      // конструктор суперкласса
      super(context, "myDB", null, 1);
    }

    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, "--- onCreate database ---");
      // создаем таблицу с полями
      db.execSQL("create table mytable ("
          + "id integer primary key autoincrement," 
          + "name text,"
          + "email text" + ");");
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
  }

}

In the next lesson:

– we thoroughly examine the method of reading query data
– we use sorting, grouping, conditions, having




Discuss in the forum [165 replies]

Leave a Reply

Your email address will not be published. Required fields are marked *