Android sqlite

In this tutorial we will discuss about the android sqlite. Android comes with sqlitedatabase to store and retrieve data. Android comes with SqliteDatase class that is used to manage all the database operations.

I will show you with simple example how to interact with database in android application.

First of all create a new activity. In the Main.java file , Add the following code
package com.tutorial.sqlite;
import android.app.Activity;

public class Main extends Activity

{

@Override
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);

setContentView(R.layout.main);
// Code to insert data in the database
SQLiteDatabase mydb=openOrCreateDatabase(“newdb”,MODE_PRIVATE,null);

mydb.execSQL(“Create table if not exists testtbl(first_name varchar,age int);”);

mydb.execSQL(“INSERT INTO testtbl values(‘john’,21)”);
mydb.close();
//Code to retrieve data from the database

Cursor c=mydb.rawQuery(“select * from testtbl”,null);

c.moveToFirst();

Log.d(“lee”,c.getString(“first_name”));

mydb.close();

}

}
Let us understand the code one by one.

1)

SQLiteDatabase mydb=openOrCreateDatabase(“newdb”,MODE_PRIVATE,null);

The above line creates a database. The function “openOrCreateDatabase is basically used to, create a database if it does not exists. If the datbase exists , then it opens the database.

MODE_PRIVATE : It defines the access of the database. Since the access is private , so it can only be read by the current application.

Once this statement is executed, we will get the reference of the database in the db object.

2)

mydb.execSQL(“Create table if not exists testtbl(first_name varchar,age int);”);

The above statement creates a new table with name “testtbl”, with two columns first_name and age.

mydb.execSQL(“INSERT INTO testtbl(‘john’.21);”);

it is basically used to insert the values in the android sqlite database. This android sqlite tutorial does not cover full sql commands.

Once the insertion of record is done , it is very important to close the database connection.

mydb.close();

3) Retrieve data from datbase

In order to retrieve data from datbase we use Cursor. In this anroid sqlite tutorial, you will learn the cursor class.

Cursor c=mydb.rawQuery(” select * from testtbl”);

Now traverse the records from starting. So we have to move the cursor to start

c.moveToFirst();

Log.d(“lee”,c.getString(“first_name”));
The c.getString() , logs the value of “first_name” one by one from android sqlite. The android sqlite is fast and easy was to store and retrieve data .

You can show this data in textview, for testing purpose , in this android tutorial , i have displayed in log.
The final step in every sqlite interaction is to close the database.

mydb.close();

 

android-permissions