Friday, 28 June 2013

Basic Databse Operations in SqLite using in Android.




This article explains Sqlite database connectivity in Android.We will see basic operations in a database.
Step 1 : How to create a database
---------------------------------------------


first import the Sqlite databse class
import android.database.sqlite.SQLiteDatabase;
SQLiteDatabase db;
                try
        {
            db = openOrCreateDatabase("Data", SQLiteDatabase.CREATE_IF_NECESSARY,null);
            db.execSQL("CREATE TABLE Data(Name VARCHAR(20),PhoneNo VARCHAR(20),Address VARCHAR(50),State                                                VARCHAR(20))");
        }
        catch (Exception  ex)
        {
            ex.printStackTrace();
        }
    }

next i am adding Data java class that hold the data for insertion.
Data.java
-------------
public class Data {
    private String _name;
    private String _phoneNo;
    private String _address;
    private String _state;
    public String get_name() {
        return _name;
    }
    public void set_name(String _name) {
        this._name = _name;
    }
    public String get_phoneNo() {
        return _phoneNo;
    }
    public void set_phoneNo(String _phoneNo) {
        this._phoneNo = _phoneNo;
    }
    public String get_address() {
        return _address;
    }
    public void set_address(String _address) {
        this._address = _address;
    }
    public String get_state() {
        return _state;
    }
    public void set_state(String _state) {
        this._state = _state;
    }
}


Step 2: Insert records into database
-------------------------------------------


I am getting data’s from text view and assigning that value to data class entity.
               
                TextView name;
                TextView phoneNo;
                TextView address;
                TextView state;
                name=(TextView)(findViewById(R.id.NameEditText));
                phoneNo=(TextView)(findViewById(R.id.PhoneNoEditText));
                address=(TextView)(findViewById(R.id.AddressEditText));
                state=(TextView)(findViewById(R.id.StateEditText));

             Data dataEntity=new Data();
            dataEntity.set_name(name.getText().toString().trim());
            dataEntity.set_phoneNo(phoneNo.getText().toString().trim());
            dataEntity.set_address(address.getText().toString().trim());
            dataEntity.set_state(state.getText().toString().trim());
            ContentValues values=new ContentValues();
            values.put("Name",dataEntity.get_name());
            values.put("PhoneNo",dataEntity.get_phoneNo());
            values.put("Address",dataEntity.get_address());
            values.put("State",dataEntity.get_state());
            if(db.insert("Data",null,values)!=-1)
            {
                Toast.makeText(getApplicationContext(), "Inserted Data", Toast.LENGTH_SHORT).show();
                     }
            else
            {
                Toast.makeText(getApplicationContext(),"Not Inserted Data",Toast.LENGTH_LONG).show();
            }
   




Step 3: select the data from database
----------------------------------------------
ArrayList<Data> mDataArrayList=new ArrayList<Data>();
                 try
        {
            db = openOrCreateDatabase("Data", SQLiteDatabase.CREATE_IF_NECESSARY,null);
            Cursor c=db.rawQuery("SELECT * FROM Data",null);
            c.moveToFirst();
            while (!c.isAfterLast())
            {
                Data dataEntity=new Data();
                dataEntity.set_name(c.getString(1));
                dataEntity.set_phoneNo(c.getString(2));
                dataEntity.set_address(c.getString(3));
                dataEntity.set_state(c.getString(4));
                mDataArrayList.add(dataEntity);
                c.moveToNext();
            }
            c.close();
            db.close();
                }
                catch (Exception ex)
        {
            ex.printStackTrace();
        }


 here mDataArrayList will return the all datas  in  table.
Posted by at
Labels: ,

12 comments:

  1. SATHEESH KUMAR M.V28 June 2013 at 22:02

    thanks

    ReplyDelete
  2. Unknown30 June 2013 at 13:53

    hey if i use where in the query my cursor.getcount returns 0 if i compare it to a text datatype field

    any ideas ?

    ReplyDelete
    Replies
    1. Rano30 June 2013 at 20:50

      show your code plz @soham

      Delete
    2. Unknown1 July 2013 at 01:41

      public String checkUserName(SQLiteDatabase db, String enteredName)

      {

      String selectionArgs[] = new String[1];
      selectionArgs[0] = enteredName;
      Cursor cursor = db.rawQuery("select * from table1 where user=?", selectionArgs);
      cursor.moveToFirst();
      return Integer.toString(cursor.getCount());
      }


      the code i use to call this function is :

      Intent intent = getIntent();
      TextView tv1 = new TextView(this);
      String message = intent.getStringExtra(MainActivity.EXTRA_MESSAGE)
      tv1.setText(db.checkUserName(db.getWritableDatabase(), message));
      relativeLayout.addView(tv1);


      my database looks like this : http://i.stack.imgur.com/LnTbl.jpg

      Delete
    3. Rano1 July 2013 at 02:06

      This comment has been removed by the author.

      Delete
    4. Rano1 July 2013 at 02:07

      try to use
      WHERE user LIKE 'enteredName%'

      Delete
    5. Unknown1 July 2013 at 08:25

      awesome man you're a genius..wasted half a day over this, now its done :D cheers and thanks a lot !!

      Delete
    6. Rano1 July 2013 at 10:06

      Thank you @Soham.

      Delete
  3. Unknown3 July 2013 at 12:06

    hey buddy i'm stuck again..this is my code:

    public String returnQuestion(SQLiteDatabase db, int difficultyLevel) {

    String abc = new String();
    abc = Integer.toString(difficultyLevel);
    Cursor cursor = db.rawQuery("SELECT question FROM table1 WHERE difficulty_level LIKE '"+abc+"%'", null);

    cursor.moveToFirst();
    if(cursor.getCount() !=0)
    {
    int i = cursor.getColumnIndex("question");
    return cursor.getString(i);
    }

    else return "Not found !";
    }

    keeps going to not found even though i pass values which are in the db. if i pass 1 it returns only the 1st record which contains 1, doesnt work for 0 or 2..the calling code is :

    for(int i=0; i<9 ;i++)

    {

    textViewArray[i].setText(db.returnQuestion(db.getWritableDatabase(), 2));
    textViewArray[i].setId(i+1);
    }

    ReplyDelete
    Replies
    1. Rano3 July 2013 at 20:50

      Hi ,
      here difficultyLevel is integer,so you can write like below

      db.rawQuery("SELECT question FROM table1 WHERE difficulty_level=difficultyLevel", null);

      try this query and let me know the result.

      Delete
  4. Anonymous11 May 2017 at 08:16

    Sir to keep simple is difficult But you do this
    Here.Thank You So Much

    ReplyDelete
  5. Unknown26 June 2018 at 23:45

    nice post , i found some basic form this site
    https://tutorial.eyehunt.in/android/android-activity-with-example-kotlin/

    ReplyDelete

Subscribe to: Post Comments (Atom)