Seegatesite.com

Seegatesite.com - Programming Tutorial , Sharing , How and Learn Together

🏠 » Android » Android Tutorial – Step By Step Learning Android Sqlite Database for Beginner

Android Tutorial – Step By Step Learning Android Sqlite Database for Beginner

By ∙ ∙ Android ∙ 13 Comments

Share : TwitterFacebookTelegramWhatsapp

Seegatesite – Today i will share an article about  Android Tutorial – Step By Step Learning Android Sqlite Database for Beginner . To learn android sqlite database, it is good we know the various data storage media which are owned by the android. Most applications require a medium for storing information inside. There are a lot of data storage options of android.

Here are the locations that can be used to store data permanent and most commonly used :

  • Shared Preferences : Permanent data storage in the form of key pair.
  • Internal Storage : locations where we can store files in the internal storage of the device. By default, files will be stored in the internal storage that is part of the application. When uninstalling the application, then the internal storage will also be deleted.
  • Local cache : if we want to cache data and open the store, we need a method getCacheDir () on the object activity or context.
  • External Storage : such as SD Cards and other external media.
  • Database Sqlite : A reliable database which I will discuss in this article.

Introduction Sqlite Database

Sqlite database is open source embedded database on android. To use sqlite in android application, available package by name android.database.sqlite. There are three main components that are used to handle sqlite in android

1.SQLiteOpenHelper

SQLiteOpenHelper used to determine the name and version of the database that will be used, in this class there is a onCreate() and onUpgrade() method. onCreate() invoked if the database doesn’t exist. Moreover this method is used to update the database schema the most recent.

SQLiteOpenHelper have getReadableDatabase() and getWritableDatabase() method  in order to access to the object SQLiteDatabase, either in read or write mode

2.SQLiteDatabase

SQLiteDatabase is the base class for sqlite database in android. This method is used to execute multiple SQL syntax such as query, update, insert and delete. SQLiteDatabase have many methods such as insert(), update(), delete() and also execSQL() used for the execution of the database directly.

In order the insert() and update() can work well, we need to define the object ContentValues key / value to the table. Key presented the identity column in the table. Value present the records in the table in the column.

A query can be made using the rawQuery() and query() method through SQLiteQueryBuilder class. Method rawQuery() accepts a SQL statement as input. While the method query() has a structured interface to specify the SQL query.  SQLiteQueryBuilder is a class that allows you to create SQL queries.

rawQuery()

Cursor cursor = getReadableDatabase();
rawQuery("select * from table where address = ?",new String[]{id});

Query()

return database.query(database_table,new String[]{key_1,key_3,key_2},null,null,null,null,null);

3.Cursor

Query function to return the cursor object, so that the cursor is the result of a query, one row (record) of the query results. With android cursor can perform buffer query results with efficient because it doesn’t need to load data into memory.

Several methods are commonly used

  • getCount() : To get the number of elements of the query results.
  • moveToFirst() : to move to the beginning of the line
  • moveToNext() : to move to the next line.
  • isAfterLast() : to check whether the last record of a query

Immediately, we learn through examples

Step By Step Learning Android Sqlite Database with Simple Example

1. Create a new android project with the name SqlDatabase. Read the introduction of android studio for beginners if you don’t understand how to create a new project on android studio.

2. Create a new java file named DatabaseHandler.java and copy the following script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
package com.example.seegate.sqldatabase; // change with your package name
 
import java.util.ArrayList;
import java.util.List;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
/**
* Created by seegate on 1/17/2016.
*/
public class DatabaseHandler extends SQLiteOpenHelper {
 
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "ProfileDb";
    private static final String TABLE_PERSON = "t_person";
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_COUNTRY = "country";
 
    public DatabaseHandler(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_PERSON_TABLE = "CREATE TABLE " + TABLE_PERSON + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_COUNTRY + " TEXT" + ")";
        db.execSQL(CREATE_PERSON_TABLE);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PERSON);
        onCreate(db);
    }
 
    public void save(Person person){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(KEY_NAME, person.getName());
        values.put(KEY_COUNTRY, person.getCountry());
 
        db.insert(TABLE_PERSON, null, values);
        db.close();
    }
 
    public Person findOne(int id){
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.query(TABLE_PERSON, new String[]{KEY_ID,KEY_NAME,KEY_COUNTRY},
                KEY_ID+"=?", new String[]{String.valueOf(id)}, null, null, null);
        if(cursor!=null){
            cursor.moveToFirst();
        }
        return new Person(Integer.parseInt(cursor.getString(0)),cursor.getString(1),cursor.getString(2));
    }
 
    public List<Person> findAll(){
        List<Person> listperson=new ArrayList<Person>();
        String query="SELECT * FROM "+TABLE_PERSON;
 
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery(query, null);
 
        if(cursor.moveToFirst()){
            do{
                Person person=new Person();
                person.setId(Integer.valueOf(cursor.getString(0)));
                person.setName(cursor.getString(1));
                person.setCountry(cursor.getString(2));
                listperson.add(person);
            }while(cursor.moveToNext());
        }
 
        return listperson;
    }
 
    public void update(Person person){
        SQLiteDatabase db=this.getWritableDatabase();
 
        ContentValues values=new ContentValues();
        values.put(KEY_NAME , person.getName());
        values.put(KEY_COUNTRY, person.getCountry());
 
        db.update(TABLE_PERSON, values, KEY_ID+"=?", new String[]{String.valueOf(person.getId())});
        db.close();
    }
 
    public void delete(Person person){
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(TABLE_PERSON, KEY_ID+"=?", new String[]{String.valueOf(person.getId())});
        db.close();
    }
}

Explanation :

Learning Sqlite Android How To Create Save Method For Beginner

Method save() is used to store the new data into the database. This method has a parameter in the form of an object from the Person class. ContentValues used to assign values to the table column. Furthermore, the process of storing data only call methods insert() owned by SQLiteOpenHelper.

The inserts() has the following parameters

public long insert (String table, String nullColumnHack, ContentValues values).

For details please visit developer.android.com

Learning Sqlite Android How To Create Update Method For Beginner

Method update() is used to update old data in the database. The update process has the following parameters

public int update (String table, ContentValues values, String whereClause, String[] whereArgs)

For details please visit developer.android.com

Learning Sqlite Android How To Create Delete Method For Beginner

Delete method used to remove data from the database. Sqlite delete process has the following parameters

public int delete (String table, String whereClause, String[] whereArgs)

For details please visit developer.android.com

Android Tutorial Learning Sqlite Android How To Show Table From Query Method Sqlite

The following method is tasked takes one row of data in the database with the parameters held in the form id. This method returns an object that has a class book that corresponds to the parameter id.

Android Tutorial Learning Sqlite Android How To Show Cursor From Arraylist Sqlite Android

Taking all the available data in the database need to iterate on Cursor and then turn them into objects of persons and put on a list.

3. Creating an entity Person.java. As usual when it will perform the operation associated with the data we modeled the data into a Java class along with its property that adapts to a column in the table that we have made above.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.example.seegate.sqldatabase; // change with your package name
 
/**
* Created by sigit on 18/01/16.
*/
public class Person {
    private int id;
    private String name;
    private String country;
 
    public Person(){}
 
    public Person(String name,String country){
        this.name=name;
        this.country=country;
    }
 
    public Person(int id,String name,String country){
        this.id=id;
        this.name=name;
        this.country=country;
    }
 
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getCountry() {
        return country;
    }
    public void setCountry(String country) {
        this.country = country;
    }
}

4. Class to manipulate the data in the database is finished we make. Then we will finish the entire code and create a class above the main class / MainActivity.java. In this example we will see if the results DatabaseHandler class that we make are correct or not?. We checked through the Debug Log on android studio. Please copy the following code on MainActivity.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.example.seegate.sqldatabase; // change with your package name
 
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
 
import java.util.List;
 
public class MainActivity extends AppCompatActivity {
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        DatabaseHandler databaseHandler=new DatabaseHandler(this);
 
        Log.d("insert", "inserting data");
        databaseHandler.save(new Person("Sigit", "Indonesia"));
        databaseHandler.save(new Person("Linus Torvalds","Finlandia"));
 
        Log.d("reading", "reading all data");
        List<Person> listPerson=databaseHandler.findAll();
        for(Person b:listPerson){
            Log.d("data", "ID :"+b.getId()+" | Name :"+b.getName()+" | Country :"+b.getCountry());
        }
 
        Log.d("reading","reading one data");
        Person b=databaseHandler.findOne(2);
        Log.d("data", "ID :"+b.getId()+" | Name :"+b.getName()+" | Country :"+b.getCountry());
 
        Log.d("update","updating data");
        b.setName("Sigit Prasetya Nugroho");
        databaseHandler.update(b);
        Log.d("reading","reading one data after update");
        Person bUpdate=databaseHandler.findOne(2);
        Log.d("data", "ID :"+bUpdate.getId()+" | Name :"+bUpdate.getName()+" | Country :"+bUpdate.getCountry());
 
        Log.d("delete", "deleting data");
        databaseHandler.delete(b);
        Log.d("reading", "reading all data after delete");
        List<Person> listPerson2=databaseHandler.findAll();
        for(Person b2:listPerson2){
            Log.d("data", "ID :"+b2.getId()+" | Name :"+b2.getName()+" | Country :"+b2.getCountry());
        }
    }
}

Explanation :

To see the results of the activity above, please refer to the debug log you

Android Tutorial Learning Sqlite Android Showing Log Debug On Android Studio

The results are as shown below

Android Tutorial Step By Step Learning Android Sqlite Database For Beginner

How do I display the query results table sqlite to listview?

Learning Sqlite Android How To Show Table Result To Listview Android

1. Create a new layout with the name list_item.xml and copy the following code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?xml version="1.0" encoding="utf-8"?>
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android" android:id="@+id/TableLayout1" android:layout_width="match_parent" android:layout_height="match_parent" >
 
    <TableRow android:id="@+id/tableRow1" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="4dp" >
 
        <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:textAppearance="?android:attr/textAppearanceMedium" android:text="Name : " android:id="@+id/textView3" android:layout_column="3" />
 
        <TextView android:id="@+id/outname" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="4dp" android:text="Name" android:textAppearance="?android:attr/textAppearanceMedium" android:layout_column="4" />
 
    </TableRow>
 
    <TableRow android:layout_width="match_parent" android:layout_height="match_parent">
 
        <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Country : " android:id="@+id/textView2" android:textAppearance="?android:attr/textAppearanceMedium" android:layout_column="3" />
 
        <TextView android:id="@+id/outaddress" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="4dp" android:text="Address" android:textAppearance="?android:attr/textAppearanceMedium" android:layout_column="4" />
    </TableRow>
 
    <TableRow android:layout_width="match_parent" android:layout_height="match_parent">
 
    </TableRow>
 
</TableLayout>

Table row above will be displayed as a list of items in the ListView

2. Add listview on your activity_main.xml

1
<ListView android:id="@+id/listView1" android:layout_width="match_parent" android:layout_height="305dp" app:listitem="@layout/list_item" android:layout_alignParentRight="true" android:layout_alignParentEnd="true" />

To add list_item.xml the ListView using the following code

app:listitem="@layout/list_item"

3. Add scripts on DatabaseHandler.java we have made as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public ArrayList<ArrayList<Object>> ambilSemuaBaris() {
 
        ArrayList<ArrayList<Object>> dataArray = new ArrayList<ArrayList<Object>>();
        Cursor cur;
        try {
            SQLiteDatabase db=this.getReadableDatabase();
            cur = db.query(TABLE_PERSON, new String[] { KEY_NAME, KEY_COUNTRY },
                    null, null, null, null, null);
            cur.moveToFirst();
            if (!cur.isAfterLast()) {
                do {
                    ArrayList<Object> dataList = new ArrayList<Object>();
                    dataList.add(cur.getString(0));
                    dataList.add(cur.getString(1));
                    dataArray.add(dataList);
                } while (cur.moveToNext());
 
            }
 
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            Log.e("DEBE ERROR", e.toString());
        }
        return dataArray;
    }

4. Create a new class with the name PersonAdapter.java to display the query results in a TextView in the list_item.xml.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package com.example.seegate.sqldatabase; // change with your package name
 
/**
* Created by sigit on 20/01/16.
*/
import java.util.ArrayList;
 
import android.content.Context;
import android.graphics.Bitmap;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
 
public class PersonAdapter extends BaseAdapter {
    private static ArrayList<Person> searchArrayList;
 
    private LayoutInflater mInflater;
 
    public PersonAdapter(Context context, ArrayList<Person> results) {
        searchArrayList = results;
        mInflater = LayoutInflater.from(context);
    }
 
    @Override
    public int getCount() {
        return searchArrayList.size();
    }
 
    @Override
    public Object getItem(int p) {
        return searchArrayList.get(p);
    }
 
    @Override
    public long getItemId(int p) {
        return p;
    }
 
    @Override
    public View getView(int p, View v, ViewGroup parent) {
        ViewHolder holder;
 
        if (v == null) {
            v = mInflater
                    .inflate(R.layout.list_item, null);
            holder = new ViewHolder();
 
            holder.name = (TextView) v.findViewById(R.id.outname);
            holder.address = (TextView) v.findViewById(R.id.outaddress);
 
            v.setTag(holder);
        } else {
            holder = (ViewHolder) v.getTag();
        }
 
        holder.name.setText(searchArrayList.get(p).getName());
        holder.address.setText(searchArrayList.get(p).getCountry());
        return v;
    }
 
    static class ViewHolder {
        TextView name, address;
 
    }
 
}

5. To display the data in a ListView, we will change the code on MainActivity.xml that we created earlier. Please copy the following script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.example.seegate.sqldatabase;
 
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.widget.ListView;
 
import java.util.ArrayList;
 
public class MainActivity extends AppCompatActivity {
 
    Person komponenperson;
    ArrayList<Person> isiperson = new ArrayList<Person>();
 
    private ListView listcontent = null;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        DatabaseHandler databaseHandler=new DatabaseHandler(this);
 
        databaseHandler.save(new Person("Sigit", "Indonesia"));
        databaseHandler.save(new Person("Linus Torvalds", "Finlandia"));
 
        listcontent=(ListView) findViewById(R.id.listView1);
        tampilPerson();
 
    }
    private void tampilPerson() {
        // TODO Auto-generated method stub
        isiperson.clear();
        DatabaseHandler databaseHandler=new DatabaseHandler(this);
        ArrayList<ArrayList<Object>> data =  databaseHandler.ambilSemuaBaris();
 
        for (int p = 0; p < data.size(); p++) {
            komponenperson = new Person();
            ArrayList<Object> baris = data.get(p);
            Log.e("baris", baris.get(0).toString());
            Log.e("baris", baris.get(1).toString());
            komponenperson.setName(baris.get(0).toString());
            komponenperson.setCountry(baris.get(1).toString());
            isiperson.add(komponenperson);
        }
        PersonAdapter datakamus = new PersonAdapter(MainActivity.this,isiperson);
        listcontent.setAdapter(datakamus);
    }
}

Thus article about Android Tutorial – Step By Step Learning Android Sqlite Database for Beginner, hope useful

Another Android Related Post :

Reader Interactions

Comments

  1. This tutorial is good for ones who only want to use SQLite directly. I think it is better to use an ORM.

    Reply

  3. Thanks Id Like your post, but, I have some problems creating my database, can you help me??
    I need to create a database that saves information from a checkbox like a yes o no, o true and false, please help me!!!

    Reply

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.