Lesson 34. Data Storage. SQLite

Lesson 34. Data Storage. SQLite

In this lesson:

– data storage with SQLite

In the last lesson, we looked at the easiest way to store data – Preferences. But this method is rather limited and inconvenient for storing large amounts of structured data. In this lesson, we’ll look at SQLite. This is a database with tables and queries – just like a regular database.

For starters, a little theory about the interaction between the program and the database.

In the application, when connecting to the database we specify name DB and version. The following situations may occur:

1) DB does not exist. This may be the case, for example, in the case of initial installation of the program. In this case, the program should itself create DB and all tables in it. And further it already works with the newly created database.

2) DB exist, But its version is outdated. This may be the case when the program is updated. For example, a new version of the program requires additional fields in the old tables or new tables. In this case, the application has update existing tables and create new ones if needed.

3) DB exist and its version relevant. In this case, the application is successful connects to the database and works.

As you understand, the phrase “application has” is equivalent to the phrase “developer must”, that is our job. To handle the situations described above, we need to create one classSuccessor to SQLiteOpenHelper. Let’s call it DBHelper. This class will provide us with methods for creation or updates DB in its cases absence or aging.

onCreate is the method that will be called if the database we want to connect to does not exist

onUpgrade – will be called if we try to connect to the database a newer version than the existing one

Let’s sketch a simple application – a directory of contacts to store name and email. introduce the data will be on screen applications as well for display information we use logs. Usually this is used by List – but we don’t know this topic yet. And you do not want to overload the application. The main thing – to learn the techniques of working with the database.

Let’s create a project:

Project name: P0341_SimpleSQLite
Build Target: Android 2.3.3
Application name: SimpleSQLite
Package name: ru.startandroid.develop.p0341simplesqlite
Create Activity: MainActivity

We reduce the screen for entering records and clearing the table. open main.xml and we write:


A pair of input fields and a button to add records, output existing records, and clear the table.

open MainActivity.java and we write:

package ru.startandroid.develop.p0341simplesqlite;

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;
  EditText etName, etEmail;

  DBHelper dbHelper;

  /** Called when the activity is first created. */
  public void onCreate(Bundle savedInstanceState) {

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

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

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

    etName = (EditText) findViewById(R.id.etName);
    etEmail = (EditText) findViewById(R.id.etEmail);
    // создаем объект для создания и управления версиями БД
    dbHelper = new DBHelper(this);

  public void onClick(View v) {
    // создаем объект для данных
    ContentValues cv = new ContentValues();
    // получаем данные из полей ввода
    String name = etName.getText().toString();
    String email = etEmail.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);
    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 {
          // получаем значения по номерам столбцов и пишем все в лог
              "ID = " + c.getInt(idColIndex) + 
              ", name = " + c.getString(nameColIndex) + 
              ", email = " + c.getString(emailColIndex));
          // переход на следующую строку 
          // а если следующей нет (текущая - последняя), то false - выходим из цикла
        } while (c.moveToNext());
      } else
        Log.d(LOG_TAG, "0 rows");
    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);
    // закрываем подключение к БД

  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) {



A bunch of new unfamiliar words in the code. Let’s figure it out.

In the Activity method, onCreate we define objects, assign handlers, and create a dbHelper class object DBHelper for database management. The class itself will be described below.

Next we look at the Activity method – onClickIn which we handle button clicks.

The ContentValues ​​class is used to specify fields tables and valuesWhich we will insert into these fields. We create an object cv, And later we use it. Next, we write the variable with fields introduction. Then, using the getWritableDatabase method, we connect to the database and get the SQLiteDatabase object. It will allow us to work with the database. We will use his methods insert – insert recording, query – reading, delete – deletion. They have many different input options, but we are still using the bare minimum.

Here’s a look at which button was pressed:

btnAdd – adding a record to the table mytable. We fill the object cv in pairs: name fields and value. And (when inserting a table entry) the corresponding values ​​will be inserted into the specified fields. We fill in the fields name and email. id our primary key autoincrement will be filled in automatically. Call the insert method – pass it table name and the object cv with inserted values. The second method argument is used when inserting an empty row into a table. We don’t need it now, so pass null. method insert returns ID inserted string, we store it in rowID and output to log.

btnRead – Read all the entries in the table mytable. The query method is used for reading. He is fed to the entrance table name, List of requested fields, sampling conditions, grouping, sorting. Because we need all the data in all fields without groupings and groupings – we use everywhere null. Only the name of the table is specified. The method returns a Cursor class object. It can be viewed as a data table. The moveToFirst method does the first entry in Cursor active and at the same time checks to see if there are any entries in it (that is, whether something in the method was selected query). Next we get the column numbers in Cursor by their names using the getColumnIndex method. These numbers are then used to read data in the getInt and getString methods and output the data to a log. With the moveToNext method, we sort through all the lines in Cursor until we get to the last line. If there were no entries, then we output a corresponding message – 0 rows. At the end we close the cursor (free up the resources occupied by it) by the method close, because further we do not use it anywhere.

btnClear – clearing the table. The delete method deletes records. At the entrance we pass table name and null as conditions for removal, and therefore everything will go. method returns count remote records.

Then close the connection with the DB method close.

class DBHelper is nested in MainActivity and described at the end of the code. As I wrote above, this class should inherit the class SQLiteOpenHelper.

IN designers we call the superclass constructor and pass it:
context – context
mydb – the name of the database
null – the cursor object we don’t need yet, so null
1 – version of the database

In the method onCreate in this class, we use the execSQL method of the SQLiteDatabase object to execute the SQL query that creates the table. I will remind – this method is called if the database does not exist and it should be created. Upon request, we can see that we are creating a table mytable with fields id, name and email.

method onUpgrade until we fill in, because we use one version of the database and do not plan to change it.

We will save everything and launch the application. We will work with the database and look at the logs that will be able to check these methods are implemented, and what is happening in them.

Enter something in the input field and click Add.

We look at the log:

— onCreate database —
— Insert in mytable: —
row inserted, ID = 1

We see that the method was called onCreate in the classroom DBHelperSo the script on creation tables. It happened because of what it is first running the program and database yet was not created. The database now exists and can be worked with.

Next we see that the method of inserting the record was called and returned ID = 1.

Let’s insert another entry.

We look at the log:

— Insert in mytable: —
row inserted, ID = 2

This time onCreate was not calledBecause the database already exist. Inserted record with ID = 2.

Let’s look at the contents of the table – click a button Read and look at the log:

— Rows in mytable: —
ID = 1, name = John Smith, email = This e-mail address is being protected from spambots. You need JavaScript enabled to view it.
ID = 2, name = Some body, email = This e-mail address is being protected from spambots. You need JavaScript enabled to view it.

We see the records that were pasted. Everything is right here.

Now we clear the table – click Clear. We look at the log:

— Clear mytable: —
deleted rows count = 2

Deleted two entries, all right. If we now look at the contents of the table – Read button:

— Rows in mytable: —
0 rows

No entries.

It is important to understand in this thread that we used two classes to work with the database:

DBHelper, next SQLiteOpenHelper. In it designers we call the super-class constructor and specify the name and version of the database. method getWritableDatabase connects to the database and returns the object to us SQLiteDatabase to work with her. method close closes database connection. When the database is missing or outdated, the class gives us the ability to implement the creation or update of methods onCreate and onUpgrate.

SQLiteDatabase. Contains methods for working with data – that is insert, renewal, removal and reading.

The database file can be found in File ExplorerAs in the previous lesson. The path to it data / data / ru.startandroid.develop.p0341simpelsqlite / databases / myDB.

In the next lesson, we will continue this program. Add the ability to update and delete specific records.

important note

In my examples, I perform all database operations in the main thread. I do my best not to complicate the lesson. But in real life, you should use a separate stream to work with the database so that your program does not slow visually. I write about lessons 80-91 and 135-136 on how to do this.

In the next lesson:

– we use query and delete methods to provide conditions

Discuss in the forum [762 replies]

Leave a Comment