Anko SQLite android Kotlin example

If you have ever programmed any application that requires database, and you came up with solution to use SQLite then you may already be familiar as how much pain it is to use and properly configure SQLite in Android. Anko provides a better way to do this complex task in a very simpler way.

Import Gradle

First of all add this gradle implementation into your app level gradle file 

dependencies {
    implement "org.jetbrains.anko:anko-sqlite:$anko_version"
}Code language: JavaScript (javascript)

For further reading and using this sqlite wrapper can check its official documentation. Which provides very easy and complete way of using this library. 

Mark My Quote Application

In this post we are going to implement an Application which could be used to save favourite quotes. For this we are using SQLite database that will be helpful for us to save quotes and latter on retrieve it. 

First of all let’s discuss some of core Functional requirements of the applicaton

• This application will allow users to save its quotes
• User will be able to star any quote he/she wants to
• User will be able to find its stared quotes as a list
• In home screen all of quotes will be displayed 
• User will be able to delete any quote from the save list
• User could also save the source of quote or author name

According to our above mentioned requirements there could be number of ways to implement this thing. But we will make this as simple as possible. Because here, our core requirement is to understand Anko-SQLite wrapper library and to use SQLite in efficient way in kotlin. So let’s get started.

First thing First

So First thing first. Let’s open Android Studio and create a new project with Kotlin Support. Goto File->New->New Project

Click next, after that select minimum SDK version and on next screen Select an Empty Activity 

On Next screen name your activity, in our case we just left default MainActivity and click finish 

After that your project will start compiling all the default resources. Once everything is ready then goto your app level gradle file and import above mention anko-sqlite gradle implementation and rebuild project again. Also add anko common gradle import. 

// Anko Commons
    implementation "org.jetbrains.anko:anko-commons:0.10.5"
    // Anko SQLite
    implementation "org.jetbrains.anko:anko-sqlite:0.10.5"Code language: JavaScript (javascript)

DB Schema

Next thing that we need to be very sure of, is the database schema. according to the above mentioned functional requirements, schema could be as per following image

Here are some points that is notable, first is that we created auto incremental id filed which could not be null. Second quote text filed will be used to add text of the actual quote which is text formate and author, source and creationdate fields are also of type text. finally isfavourite filed is Integer type which is going to save data in 0 or 1 type, indicating not favourite and favourite respectively.  

SQLite Helper Class

Anko provides a very efficient and managed Helper class called ManagedSQLiteOpenHelper You just need to extend this class and create your own helper class in kotlin. So let’s just do it. 

First of all create a separate package of name databases so that we could separate our database logic from application level logic. Goto your project java folder open up your right click on your java folder then New->package, name your package and click OK. After that you will see a new package in your project structure. Right click on that and then select a new Kotlin Class. After that from appeared dialog box select Kotlin and name your helper class. In my case I had selected the name as QuoteDBHelper.

package com.quote.my.mark.mmq.database

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import org.jetbrains.anko.db.*

class QuoteDBHelper(ctx: Context) : ManagedSQLiteOpenHelper(ctx, "MMQDatabase", null, 1) {
    companion object {
        private var instance: QuoteDBHelper? = null
@Synchronized
        fun getInstance(ctx: Context): QuoteDBHelper {
            if (instance == null) {
                instance = QuoteDBHelper(ctx.getApplicationContext())
            }
            return instance!!
        }
    }

    override fun onCreate(db: SQLiteDatabase) {
        // Here you create tables
db.createTable("Quotes", true,
                "id" to INTEGER + PRIMARY_KEY + UNIQUE + AUTOINCREMENT,
                "quote_text" to TEXT,
                "author" to TEXT,
                "source" to TEXT,
                "creation_date" to TEXT,
                "is_favourite" to INTEGER
)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Here you can upgrade tables, as usual
db.dropTable("Quotes", true)
    }
}

// Access property for Context
val Context.database: QuoteDBHelper
    get() = QuoteDBHelper.getInstance(applicationContext)

Creating data class

The best way to implement model class for database and for many other uses is to create a data class. Here is official data class documentation in case if you want to understand it further.

Let’s create a separate package for our Model classes and lets just call it Models. You can do this same like before you created package for database related tasks. Once you created separate package for models, then just create a new Data class under that package and name it as Quote.

so far our data class may look like this which is pretty fine for now

package com.quote.my.mark.mmq.Models
data class Quote(var id:Int?=null,
                 var quote_text: String = "",
                 var author: String = "",
                 var source: String = "",
                 var creation_date: String = "",
                 var is_favourite: Int = 0)

If you had already noticed that we created only id field nullable. Leave this class for now as it is, and we will come it to later if we need. 

Building Main Listview Layout

Now we are going to built our GUI layout for the main activity. So goto the res folder and under this folder expand layout folder. You will see one xml file here with the name of activity_main.xml, Double click on that file and this fill will open up. If you find some constraint layout change everything according to this 

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity"
android:orientation="vertical"
>
    <ListView
      android:id="@+id/listview1"
      android:layout_width="match_parent"
      android:layout_height="match_parent"
      />
    
</LinearLayout>

You can read more about ListView from android documentation. Although Listviews are deprecated but we will focus on this just to keep simplicity and assuming that our list is not gonna be so large. Here is one more example of Listview usage. 

Prepare ArrayList of Quotes

The next step that we are going to do is to create a simple ArrayList of Strings so that we could check if our list view is working properly or not. So we choose few of quotes from KeepInspiring.com and created list as per following. We also set adapter with simple list item layout in onCreate method of MainActivity Class. 

class MainActivity : AppCompatActivity() {
    private lateinit var listView: ListView
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        listView = findViewById(R.id.listview1)
        val defaultQuotes:ArrayList<String> = ArrayList<String>()
        defaultQuotes.add("Get busy living or get busy dying.")
        defaultQuotes.add("Great minds discuss ideas; average minds discuss events; small minds discuss people.")
        defaultQuotes.add("Those who dare to fail miserably can achieve greatly")
        defaultQuotes.add("It is hard to fail, but it is worse never to have tried to succeed")
        defaultQuotes.add("Let us always meet each other with smile, for the smile is the beginning of love")
        listView.adapter = ArrayAdapter(this, android.R.layout.simple_list_item_1, defaultQuotes)
    }
}

Lets try to run application on simulator or actual device to ensure that everything is working so far. 

Extend Adapter Class

Because everything is working as expected now let’s try to extend Adapter class so that we will be able to display more information onto ListView instead of just displaying static strings. Hope you remember so far that we need to use our model Quote class to build the ListView. 

The data adapter class is used to get data from any source and build view for that data accordingly. 

package com.quote.my.mark.mmq.adapters

import android.content.Context
import android.view.LayoutInflater
import android.view.View
import android.view.ViewGroup
import android.widget.BaseAdapter
import android.widget.ImageView
import android.widget.TextView
import com.quote.my.mark.mmq.Models.Quote
import com.quote.my.mark.mmq.R

class QuoteListAdapter(private val context: Context,
                       private val dataSource: ArrayList<Quote>): BaseAdapter() {

    private val inflater: LayoutInflater
            = context.getSystemService(Context.LAYOUT_INFLATER_SERVICE) as LayoutInflater

    override fun getView(position: Int, convertView: View?, parent: ViewGroup): View {
        val curQuote = getItem(position) as Quote
        val rowView = inflater.inflate(R.layout.list_item_quote, parent, false)
        val quoteTextView = rowView.findViewById(R.id.quote_text) as TextView
        val authorTextView = rowView.findViewById(R.id.quote_author) as TextView
        val sourceTextView = rowView.findViewById(R.id.quote_source) as TextView
        val thumbnailImageView = rowView.findViewById(R.id.imview1) as ImageView
        quoteTextView.text= curQuote.quote_text
authorTextView.text= curQuote.author
sourceTextView.text= curQuote.source
return rowView
    }

    override fun getItem(position: Int): Quote{
        return dataSource[position]
    }

    override fun getItemId(position: Int): Long {
        return position.toLong()
    }

    override fun getCount(): Int {
        return dataSource.size
}
}

Layout Resource XML File

It is very important to create a resource layout file as well so that our adapter class could able to use it. We creating xml file as follows 

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="horizontal" android:layout_width="match_parent"
android:layout_height="wrap_content"
>

    <ImageView
android:id="@+id/imview1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:src="@mipmap/ic_launcher_round"
/>
    <LinearLayout
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:gravity="center"
android:padding="16dp"
>
        <TextView
android:id="@+id/quote_text"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:lines="2"
android:ellipsize="middle"
android:textSize="25sp"
/>
        <TextView
android:id="@+id/quote_author"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textSize="20sp"
android:layout_marginTop="4dp"
/>
        <TextView
android:id="@+id/quote_source"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textSize="15sp"
/>
    </LinearLayout>

</LinearLayout>

Create ArrayList of Quotes and Update Adapter of ListView

Now we will create a dummy list of quotes and will replace the previous default data adapter of ListView as follows.

package com.quote.my.mark.mmq

import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.widget.ListView
import com.quote.my.mark.mmq.Models.Quote
import com.quote.my.mark.mmq.adapters.QuoteListAdapter

class MainActivity : AppCompatActivity() {
    private lateinit var listView: ListView
    private  val quoteList:ArrayList<Quote> = ArrayList<Quote>()
    val quoteSource:String = "http://www.keepinspiring.me/famous-quotes/"
override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        listView = findViewById(R.id.listview1)
        val defaultQuotes:ArrayList<String> = ArrayList<String>()
        defaultQuotes.add("Get busy living or get busy dying.")
        defaultQuotes.add("Great minds discuss ideas; average minds discuss events; small minds discuss people.")
        defaultQuotes.add("Those who dare to fail miserably can achieve greatly")
        defaultQuotes.add("It is hard to fail, but it is worse never to have tried to succeed")
        defaultQuotes.add("Let us always meet each other with smile, for the smile is the beginning of love")


        quoteList.add(Quote(1,defaultQuotes[0],"test1",quoteSource,"",1))
        quoteList.add(Quote(2,defaultQuotes[1],"auth2",quoteSource,"",0))
        quoteList.add(Quote(3,defaultQuotes[2],"auth3",quoteSource,"",0))
        quoteList.add(Quote(4,defaultQuotes[3],"auth32",quoteSource,"",1))
        quoteList.add(Quote(5,defaultQuotes[4],"null",quoteSource,"",0))


       // listView.adapter = ArrayAdapter(this, android.R.layout.simple_list_item_1, defaultQuotes)
listView.adapter = QuoteListAdapter(this,quoteList)
    }
}

Now run the project and view the output as follows

Reading Data from Database

Now in OnCreate you need to read all values from database and append that to your listview. Here is Async Task using Anko Commons 

val parser2 = classParser<Quote>()

doAsync {
database.use {
select(Quote.TBL_NAME).exec {
val mVar = this
            val quoteList1: List<Quote> = parseList(parser2)
            Log.d("TAG","" + quoteList1.size);

            runOnUiThread {
quoteList.addAll(quoteList1)
            }
/* for (i in 0 until mVar.count) {
                mVar.moveToNext()
                quoteList.add(
                        Quote(
                                mVar.getInt(mVar.getColumnIndex(Quote.ID)),
                                mVar.getString(mVar.getColumnIndex(Quote.TEXT)),
                                mVar.getString(mVar.getColumnIndex(Quote.AUTHOR))
                        ))
            }*/
}
    }
}

Insert Data into Databse

If you want to insert new data into database you may need to do something as following 

doAsync {
database.use {
insert(Quote.TBL_NAME,

              "quote_text" to quoteList[pos].quote_text,
              "author" to quoteList[pos].author,
              "source" to quoteList[pos].source,
              "creation_date" to quoteList[pos].creation_date,
              "is_favourite" to quoteList[pos].is_favourite
)
        runOnUiThread {
toast("inserted")
        }
    }
}