Lesson 16 – Store (save) data in SQLite database

Hello viewer, You can now have our Tutorial Lessons in your android mobile device and read it offline.
Download kotlin Programming APP on PlayStore
Download Website SEO Lessons APP on PlayStore

So far we’ve seen two ways of permanently storing data (text files and the SharedPreferences class), now we’ll see another native Android tool for storing data in a database called SQLite.

SQLite is an Open Source database, it is very popular on many small devices like Android.

The advantages of using SQLite is that it does not require configuration, it does not have a database server running in a separate process and its use is relatively simple.

Problem:

Make a program that allows storing the data of articles. Create the article table and define the code fields, item description, and price.
The program should allow:

1 - Load of article.
2 - Query by code.
3 - Query by description.
4 - Deleting an article by entering its code.
4 - Modification of description and price.

Create a project in Android Studio and define it as name: Project019

The first thing we will do is create a class that inherits from SQLiteOpenHelper. This class allows us to create the database and update the structure of tables and initial data.

To create a new class from Android Studio we proceed to right click on the name of the project package containing all the Kotlin files and select New -> Kotlin Class:

Lesson 16 - Store (save) data in SQLite database - Lesson 16 - Store (save) data in SQLite database - Lesson 16 - Store (save) data in SQLite database -

In this dialog we enter the name of our class, in our example, we will call it AdminSQLiteOpenHelper and the creation of a class:

Lesson 16 - Store (save) data in SQLite database - Lesson 16 - Store (save) data in SQLite database - Lesson 16 - Store (save) data in SQLite database -

We already have a new file in our project called AdminSQLiteOpenHelper.

 

Now we have to encode this class which aims to manage the database we will create. First, we make our class inherit from the SQLiteOpenHelper class and we will implement its two onCreate and onUpgrade abstract methods:

package com.coding180.project019

/**
 * Created by Robort gabriel on 9/1/2017.
 */


import android.content.Context
import android.database.sqlite.SQLiteOpenHelper
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteDatabase.CursorFactory

class AdminSQLiteOpenHelper(context: Context, name: String, factory: CursorFactory?, version: Int) : SQLiteOpenHelper(context, name, factory, version) {
// coding180.com
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL("create table items (code int primary key, description text, real price)")
    }
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {

    }
}

We codify in the onCreate method the creation of the article table with the fields code (which is an integer and primary key), a description that is of type text and price is a real value. The onCreate method will execute only once (Eventually if one wants to modify the structure of the table we must do it in the onUpgrade method).

Now let’s implement the visual interface to solve our problem. We need to create the following interface in our activity_main.xml file:

Lesson 16 - Store (save) data in SQLite database - Lesson 16 - Store (save) data in SQLite database - Lesson 16 - Store (save) data in SQLite database -

 

As we see we have three EditText and five Button:

  EditText of type "Number" (ID = "et1", hint = "Enter code")
  EditText of type "Plain Text" (ID = "et2", hint = "Enter description")
  EditText of type "Number Decimal" (ID = "et3", hint = "Enter price")
  Button (ID = "button1", text = "High", onClick = "high")
  Button (ID = "button2", text = "Query by code")
  Button (ID = "button3", text = "Query by description")
  Button (ID = "button4", text = "Down by code")
  Button (ID = "button5", text = "Modification")

The source code for our Activity in Kotlin is as follows:

package com.coding180.project019


import android.content.ContentValues
import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.widget.Button
import android.widget.EditText
import android.widget.TextView
import android.widget.Toast

class MainActivity: AppCompatActivity () {
// coding180.com
    override fun onCreate (savedInstanceState: Bundle?) {
        super.onCreate (savedInstanceState)
        setContentView (R.layout.activity_main)
        val et1 = findViewById (R.id.et1) as EditText
        val et2 = findViewById (R.id.et2)  as EditText
        val et3 = findViewById (R.id.et3) as EditText

        val button1 = findViewById (R.id.button1) as Button
        button1.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "administration", null, 1)
            val  bd = admin.writableDatabase
            val register = ContentValues()

            register.put("code", et1.getText().toString())

            register.put( "description", et2.getText().toString ())

            register.put ("price", et3.getText ().toString ())

            bd.insert ( "articles", null, register)
            bd.close ()
            et1.setText ("")
            et2.setText ("")
            et3.setText ("")
            Toast.makeText (this, "Item data loaded", Toast.LENGTH_SHORT) .show ()
        }

        val button2 = findViewById (R.id.button2) as Button
        button2.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "admin", null, 1)
            val bd = admin.writableDatabase
            val row = bd.rawQuery ("select description, price from articles where code = ${et1.text.toString ()}", null)
            if (row.moveToFirst ()) {
                et2.setText (row.getString (0))
                et3.setText (row.getString (1))
            } else
                Toast.makeText (this, "There is no article with this code", Toast.LENGTH_SHORT) .show ()
            bd.close ()
        }

        val button3 = findViewById (R.id.button3) as Button
        button3.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "admin", null, 1)
            val bd = admin.writableDatabase
            val row = bd.rawQuery ("select code, price from articles where description = '${et2.text.toString()}'", null)
            if (row.moveToFirst ()) {
                et1.setText (row.getString (0))
                et3.setText (row.getString (1))
            } else
                Toast.makeText (this, "There is no article with that description", Toast.LENGTH_SHORT) .show ()
            bd.close ()
        }

        val button4 = findViewById (R.id.button4) as Button
        button4.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "admin", null, 1)
            val bd = admin.writableDatabase
            val count = bd.delete ("articles", "code = ${et1.text.toString()}", null)
            bd.close ()
            et1.setText ("")
            et2.setText ("")
            et3.setText ("")
            if (count == 1)
                Toast.makeText (this, "The article with that code was deleted", Toast.LENGTH_SHORT) .show ()
            else
                Toast.makeText (this, "There is no article with this code", Toast.LENGTH_SHORT) .show ()
        }

        val button5 = findViewById (R.id.button5) as Button
        button5.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "admin", null, 1)
            val bd = admin.writableDatabase
          //  val register = ContentValues​​()
              val register = ContentValues()

            register.put ("description", et2.text.toString ())
            register.put ("price", et3.text.toString ())
            val count = bd.update ("articles", register, "code = ${et1.text.toString ()}", null)
            bd.close ()
            if (count == 1)
                Toast.makeText (this, "data was modified", Toast.LENGTH_SHORT) .show ()
            else
                Toast.makeText (this, "there is no article with the code entered", Toast.LENGTH_SHORT) .show ()
        }
    }
}

1 – Data entry.

When the “HIGH” button is pressed, the lambda function passed to the setOnClickListener method of button1.
The first thing we do in this method is to create an object of the class that we raised previously and we pass it to this constructor (reference of the current Activity), “administration” (it is the name of the database that we will create in the case that does not exist ) then we pass null and a one indicating that it is the first version of the database (in case we change the structure or add tables, for example, we can pass a two instead of a one to execute the onUpgrade method where we indicate the new structure of the database).

After creating an object in the AdminSqLiteOpenHelper class we proceed to create an object of the SQLiteDataBase class by accessing the writableDatabase property (the database opens in read and write mode).

We create an object of the class ContentValues and by means of the put method, we initialize all the fields to load.
Then we call the insert method of the class SQLiteDatabase passing in the first parameter the name of the table, as the second parameter a null and finally the object of the ContentValues class already initialized (this method is the one that causes a new row to be inserted in the table items in the database called administration)
We then delete the EditText and display a message to let the operator know that the data was correctly entered:

button1.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "administration", null, 1)
            val  bd = admin.writableDatabase
            val register = ContentValues()

            register.put("code", et1.getText().toString())

            register.put( "description", et2.getText().toString ())

            register.put ("price", et3.getText ().toString ())

            bd.insert ( "articles", null, register)
            bd.close ()
            et1.setText ("")
            et2.setText ("")
            et3.setText ("")
            Toast.makeText (this, "Item data loaded", Toast.LENGTH_SHORT) .show ()
        }

2 – Item inquiry by code.

When the button “SEARCH BY CODE” is pressed, it is executed:

val button2 = findViewById (R.id.button2) as Button
        button2.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "admin", null, 1)
            val bd = admin.writableDatabase
            val row = bd.rawQuery ("select description, price from articles where code = ${et1.text.toString ()}", null)
            if (row.moveToFirst ()) {
                et2.setText (row.getString (0))
                et3.setText (row.getString (1))
            } else
                Toast.makeText (this, "There is no article with this code", Toast.LENGTH_SHORT) .show ()
            bd.close ()
        }

In the lambda function, the first thing we do is create an object from the AdminSQLiteOpenHelper class and get a reference from the database by accessing the writableDatabase property.
Next, we define a variable of class Cursor called row and we initialize it with the value returned by the method called rawQuery.

The Cursor class stores in this case a row or zero rows (one in case we have entered an existing code in the article table), call the moveToFirst() method of the Cursor class and return true if there is an article with the code entered, otherwise returns zero.

To retrieve the actual data we want to query, we call the getString method and pass the position of the field to retrieve (it starts to be numbered in zero, in this example the column zero represents the field description and column 1 represents the price field).

3 – Item inquiry by description.

When the button is pressed, it is executed:

val button3 = findViewById (R.id.button3) as Button
        button3.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "admin", null, 1)
            val bd = admin.writableDatabase
            val row = bd.rawQuery ("select code, price from articles where description = '${et2.text.toString()}'", null)
            if (row.moveToFirst ()) {
                et1.setText (row.getString (0))
                et3.setText (row.getString (1))
            } else
                Toast.makeText (this, "There is no article with that description", Toast.LENGTH_SHORT) .show ()
            bd.close ()
        }

In the lambda function, the first thing we do is create an object from the AdminSQLiteOpenHelper class and get a reference from the database.
Next, we define a variable of class Cursor and initialize it with the value returned by the method called rawQuery.

It is important to note in the wherein the SQL clause we have arranged single quotes between the contents of the variable descript:

            val row = bd.rawQuery ("select code, price from articles where description = '$ {et2.text.toString ()}'", null)

This is mandatory for fields of type text (in this case description is of type text)

4 – Low or erase data.

To delete one or more records the SQLiteDatabase class has a method that we pass in the first parameter the name of the table and in the second the condition that must be fulfilled to delete the row of the table. The delete method returns an integer that indicates the number of deleted records:

val button4 = findViewById (R.id.button4) as Button
        button4.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "admin", null, 1)
            val bd = admin.writableDatabase
            val count = bd.delete ("articles", "code = ${et1.text.toString()}", null)
            bd.close ()
            et1.setText ("")
            et2.setText ("")
            et3.setText ("")
            if (count == 1)
                Toast.makeText (this, "The article with that code was deleted", Toast.LENGTH_SHORT) .show ()
            else
                Toast.makeText (this, "There is no article with this code", Toast.LENGTH_SHORT) .show ()
        }

5 – Modification of data.

In data modification, we must create an object of the ContentValues class and using the put method store the values for each field that will be modified. Then the method update of the class SQLiteDatabase is called by passing the table name, the object of the class ContentValues and the condition of where (the quantum parameter in this example is not used):

val button5 = findViewById (R.id.button5) as Button
        button5.setOnClickListener {
            val admin = AdminSQLiteOpenHelper (this, "admin", null, 1)
            val bd = admin.writableDatabase
          //  val register = ContentValues​​()
              val register = ContentValues()

            register.put ("description", et2.text.toString ())
            register.put ("price", et3.text.toString ())
            val count = bd.update ("articles", register, "code = ${et1.text.toString ()}", null)
            bd.close ()
            if (count == 1)
                Toast.makeText (this, "data was modified", Toast.LENGTH_SHORT) .show ()
            else
                Toast.makeText (this, "there is no article with the code entered", Toast.LENGTH_SHORT) .show ()
        }

When we run the program we have the following interface:

Lesson 16 - Store (save) data in SQLite database - Lesson 16 - Store (save) data in SQLite database - Lesson 16 - Store (save) data in SQLite database -

 

,…

About The Author

Related posts

Leave a Reply