Search Tutorials

Sunday 20 October 2013

Android SQLite Database Tutorial and Project

In this application, we will learn how to use SQLite database in android to save values and retrieve back from it. SQLite database does not support complex operators or function, so it does not support join, group by, no data type integrity etc. other wise it is same as other databases. There are three data types in SQLite:

1) TEXT: to store any value as a text
2) Integer: to store integer type value
3) Real: to store floating type value

But as we said it does not support data type integrity so we can use any data type and we can store any type of value in it. So let's start our project, create new project and here we are using two buttons, first button is used to take value from edit text boxes and save them to SQLite database, The second button is used to retrieve all data from database and display them on text view which is scrollable. The code of android XML file is given below:

Android SQLite Database Tutorial and ProjectAndroid SQLite Database Tutorial and Project


<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
   android:layout_width="fill_parent"
   android:layout_height="fill_parent"
   android:background="#abc" >
<EditText
   android:id="@+id/editText1"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:layout_alignParentTop="true"
   android:layout_centerHorizontal="true"
   android:maxLines="1"
   android:hint="Name"
   android:layout_marginTop="28dp"
   android:ems="10" >
  <requestFocus />
</EditText>
<EditText
   android:id="@+id/editText2"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:layout_alignLeft="@+id/editText1"
   android:layout_below="@+id/editText1"
   android:hint="Sur Name"
   android:maxLines="1"
   android:ems="10" />
<Button
   android:id="@+id/button1"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:layout_alignLeft="@+id/editText2"
   android:layout_alignRight="@+id/editText2"
   android:layout_below="@+id/editText2"
   android:text="Insert Values"
   android:onClick="insert"/>
 <Button
   android:id="@+id/button2"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:layout_alignLeft="@+id/button1"
   android:layout_alignRight="@+id/button1"
   android:layout_below="@+id/button1"
   android:onClick="display"
   android:text="Display all Values" />
<ScrollView
   android:id="@+id/scrollView1"
   android:layout_width="wrap_content"
   android:layout_height="wrap_content"
   android:layout_alignParentBottom="true"
   android:layout_alignParentLeft="true"
   android:layout_alignParentRight="true"
   android:layout_below="@+id/button2" >
  <LinearLayout
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >
  <TextView android:id="@+id/textView1"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:textSize="20sp"/>
  </LinearLayout>
 </ScrollView>
</RelativeLayout>

Now open your Java file and initialize all objects.
-> To create new database or open existed database use: openOrCreataDatabase() and pass name of the database and open it in private mode.
-> To run any query except select query use: execSQL()
-> To select values from database use: rawQuery()
The code of android Java file is given below with explanation:
package com.example.checkblogapp; //your package name
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class MainActivity extends Activity {
  SQLiteDatabase db;
  TextView tv;
  EditText et1,et2;
  @Override
  protected void onCreate(Bundle savedInstanceState) {
   super.onCreate(savedInstanceState);
   setContentView(R.layout.activity_main);
   //initialize all view objects
   tv=(TextView)findViewById(R.id.textView1);
   et1=(EditText)findViewById(R.id.editText1);
   et2=(EditText)findViewById(R.id.editText2);
   //create database if not already exist
   db= openOrCreateDatabase("Mydb", MODE_PRIVATE, null);
   //create new table if not already exist
   db.execSQL("create table if not exists mytable(name varchar, sur_name varchar)");
   }
   //This method will call on when we click on insert button
   public void insert(View v)
   {
    String name=et1.getText().toString();
    String sur_name=et2.getText().toString();
    et1.setText("");
    et2.setText("");
    //insert data into able
    db.execSQL("insert into mytable values('"+name+"','"+sur_name+"')");
   //display Toast
   Toast.makeText(this, "values inserted successfully.", Toast.LENGTH_LONG).show();
    }
   //This method will call when we click on display button
   public void display(View v)
   {
   //use cursor to keep all data
   //cursor can keep data of any data type
   Cursor c=db.rawQuery("select * from mytable", null);
   tv.setText("");
   //move cursor to first position
   c.moveToFirst();
   //fetch all data one by one
   do
   {
    //we can use c.getString(0) here
    //or we can get data using column index
    String name=c.getString(c.getColumnIndex("name"));
    String surname=c.getString(1);
    //display on text view
    tv.append("Name:"+name+" and SurName:"+surname+"\n");
    //move next position until end of the data
   }while(c.moveToNext());
  }
}

Now run your project and test this application. Complete project on SQLite are also available here. If you have doubts please comment. Thanks... :)

92 comments:

  1. wow nice!!! thanks sir....

    ReplyDelete
  2. Is this code enough or I should go Mysql and create database and tables? Please help me out. I'm new to connecting Android to database.

    ReplyDelete
    Replies
    1. No need to go anywhere..just use it and finish...:)

      Delete
  3. This is good one...Bhai I have a project in final year of B.Tech based on android.Tha neme of project is Android based traffic challaan monitoring application and in this project my first module is Log in and registration module,now i want that all the information added by a police personnel should be store in the cloud,please tell me the way and links where i can link how to store information in cloud database.Also tell who provide free cloud service.

    ReplyDelete
    Replies
    1. you can find login and registration form projects in android label. For free cloud database use Google database.

      Delete
    2. sir i check your code from android label for login in which you use images as a show and hide but i face a problem when open xml file login and signin it show an error the error is "@+id/rl is not sibling in the same relative layout" plz help me

      Delete
    3. plz reply me as soon as possible

      Delete
    4. This comment has been removed by the author.

      Delete
    5. error remove from login.xml not from siginin.xml

      Delete
  4. Please give me resource to deal with RSS feed. I am learning android. I want to develop quiz app

    ReplyDelete
  5. hello sir,
    can you show more example of android project connect with php and mysql.

    ReplyDelete
    Replies
    1. check all projects in Android Lablel(above) or check it: http://www.coders-hub.com/p/android.html

      Delete
  6. How can you call buttons without intializing of them?

    ReplyDelete
    Replies
    1. Check XML file. I have declared a method in onClick tag in XML file which will call in Java.

      Delete
    2. you should use Listener for this.

      Delete
  7. How to create two or more than two table in android app

    ReplyDelete
    Replies
    1. use this code same given in above post:

      db.execSQL("create table if not exists table_name(name varchar, sur_name varchar)");

      Delete
    2. db.execSQL("create table if not exists mytable(Latitude rel, Longitude real"
      + "Locality varchar, Postcode varchar, Country varchar)");

      Please I want to store and retrieve location address by following the above example, part of it works but i am getting an error when the store and retrieve button is pressed. above is the line of code i implemented.

      Please help out.
      Thanks

      Delete
  8. hi sir thank you , can u tell me how to get data from sqlite database and showing it in list view

    ReplyDelete
    Replies
    1. have u got the soulution for that...?

      Delete
    2. Example of listview and sqlite database fetching both tutorials is are given on this site..merge both code and make easily.

      Delete
  9. Can anybody Tell where is the Code for Button OnClickListener Coding,

    ReplyDelete
    Replies
    1. i used onClick in XML File bro.

      Delete
  10. This comment has been removed by the author.

    ReplyDelete
  11. bro i am a android beginner.i am developing one simple app my source code has no error but it not run.it provide activity not found exception and null pointer exception in console.why was the error accure,how can solve this?

    ReplyDelete
  12. can you help on my project mobile wallet

    ReplyDelete
  13. Sir nice coding ....I have started android since last august..now i am studying database portion..and faced many problem..sir.., So please post update,and delete coding..

    ReplyDelete
  14. sir how to clear up the text after clicking display names

    ReplyDelete
  15. how can i set image background color as white

    ReplyDelete
  16. thank you that help me about SqlitDatabase for android please post class that extent from SQL....

    ReplyDelete
  17. how to display the details in message box? please..

    ReplyDelete
  18. after i created my sql database and enter and save my data , how i can use it in my app. in another java activity...

    ReplyDelete
  19. Anonymous4:37 pm

    this is good. but any idea how to assign auto increment ID on an existing table?

    ReplyDelete
  20. Please Share Code to Add Button '' Delete " and Clear All Data in database,,,

    ReplyDelete
    Replies
    1. Check incoming and outgoing call project in android label.

      Delete
  21. I am making a contact Application where i have tp show all saved contact onto a Listview from sqlLite..help me in that

    ReplyDelete
  22. sir,i want to display the database table as such like all columns and rows,can u help me for that

    ReplyDelete
  23. Hello Sir,
    I'm used your app.It works properly but when i'm force to stop and clear it cache of this app and restart it again ,during that time direct press "Display all values" it unfortunately stopped.please give me suggestion on it sir.

    ReplyDelete
  24. good, and it is running thanks 4 code

    ReplyDelete
  25. sir ... can u just help that how can i get the location of another person ...

    ReplyDelete
  26. Sir i hve instlled unsigned apk to my smart ph by using above code snippet, after installing it showing "unfortunately, mypppication has stopped."

    ReplyDelete
  27. Plz help me.. ?..

    ReplyDelete
  28. How to pass parameter in select query, please help

    ReplyDelete
    Replies
    1. ex:- "select * from mytable2 where coloum1 =Parameter1 and coloum2 =Parameter2 "

      Delete
  29. hi how to store list view value to sqlite data base...

    ReplyDelete
  30. hi !!!
    please help for my android project, its based on keyboard using IME, i have database also and in database i have 3 tables from these 3 table i need to retrieve data for different different activity so please help me because i m new to sqlite databse

    ReplyDelete
  31. Really superb this is first time i can able to understand it clearly

    ReplyDelete
  32. sir i need to show only recently added data.it show all the data..plz give me a sln..

    ReplyDelete
  33. Thank you ! This's amazing ;)

    ReplyDelete
  34. It's working only for once, when I try to run it again, the error shows "MyApp has stopped working". Please Help !!!

    ReplyDelete
  35. please how can i update and retrive the value in table form please help me

    and we can see the table in our project ??????

    ReplyDelete
  36. http://stackoverflow.com/questions/32027513/android-fetch-data-from-database-error-application-crash

    Please answer anyone

    ReplyDelete
  37. Nice and quick tutorial, but how would you edit it if the database is online?

    ReplyDelete
  38. I am working with wamp server for accessing mysql and PHP for online app...i wanted get interact with db in it as server...so plz help me establish connection b/w android studio to server(mysql wit PHP)

    ReplyDelete
  39. how we can store the gridview text when user select ..into sqlitedatabase

    ReplyDelete
  40. How about if i try for 3 edittexts ??
    iam trying but its not running
    plz help me out

    ReplyDelete
  41. Very good thank zou a lot.

    ReplyDelete
  42. its awesome, thanks!!!

    ReplyDelete
  43. Hi, thanks a lot.
    So isn't it a good choice if I have to implement even a simple join?
    If it's not, then what is your suggestion? Remote db like mysql?

    ReplyDelete
  44. my name is marita.. i am new to android development but i am sure your code is 100% working but i did not clearly understand how to use the code. i mean should i paste this code into SQLite or android studio or eclipse? i am using android studio for developing my final year project. please help me out and let me know soon how exactly to use this :( totally new to android coding.
    Thanks!

    ReplyDelete
    Replies
    1. Hi Marita, you can use this code in android studio or eclipse.

      Delete
  45. hi,nice tutorial. i want to create an android app that provides short tutorials or lessons.How do i go about? Can SQLITE contain all the data or other options.thanks

    ReplyDelete
  46. It doesnt works app crashes!! all syntax is correct

    ReplyDelete
  47. wen i click display ,, my launcher stop working

    ReplyDelete
  48. nice sir.thank you

    ReplyDelete
  49. Hello everyone,i want to create android app that provides all agricultural information and this is my final year projects please help how to get code

    ReplyDelete
  50. wow! really useful, bro! thanks sooo much

    ReplyDelete
  51. This comment has been removed by the author.

    ReplyDelete
  52. Very simple............
    Easy for new learners......

    ReplyDelete
  53. Code for auto increment? let if i want to start from 2016 2017 2018 ......so on.

    ReplyDelete
    Replies
    1. Same as MySQL. Read more about SQLite in my android app: https://play.google.com/store/apps/details?id=coders.hub.android.master

      Delete
    2. its directing to google play store...... i have coded like this (user_id INTEGER AUTO INCREMENT NOT NULL).
      and in class :mydb.execSQL("insert into tablename values(" , )");. i kept the id place blank, but i want to assign starting no.

      Delete
  54. how to fetch all record show in new activity using sqlite database

    ReplyDelete
  55. The is very help full to me.but i need a code to display name and surname separately in two textview.
    and additional age contact number i need to display it separatly

    ReplyDelete
  56. How to create database in SQL Lite? DO I need to purchase from any company

    ReplyDelete
  57. can u plz snd me the source codes of any mini projects so that i can make any app and can impose it to my resume..my email id-atul.verma6387@gmail.com

    ReplyDelete
  58. thanks for this but i have a question. How can i show these data in another activity in a text view?

    ReplyDelete
  59. please Bro make a tutorial on save image and text in SQLite data base and retrieve it in Gridview.....Please....

    ReplyDelete
  60. How Can i Delete record ???

    ReplyDelete
  61. Anonymous9:11 am

    hii sir in i am new for android developer please support me sir, in which platform i will run the program code please give me details about the project sir please...

    ReplyDelete
  62. hi sir, i am developing a bible app where is want to read an entire chapter of the bible stored in the database on click of a button. i have created my table in the following format

    COLUMN_BOOK(string)
    COLUMN_CHAPTER(integer)
    COLUMN_VERSE(integer)
    COLUMN_WORDINGS (string)

    can you help me as to how to print/read the data from the database into a new textview onClick of a button in the previous activity.

    ReplyDelete
  63. nice.. thank you sir!!

    Btw, I am new to android programming. And i'm looking for a way to insert data such as location(lat,long), fish type, remarks,date, and fish image into my fishing app, which acts as a fish diary and load it back, by type and searching the fish type.. Can you tell me how to figure this out using sqlite3 and android studio? pls help thank you..

    ReplyDelete
  64. application crash can u plz help

    ReplyDelete
  65. Can u help me I have to print data in another activity
    for ex.my profile acticity

    ReplyDelete
  66. I got error like application stopped unfortunately...what I do..please help me

    ReplyDelete

Back to Top