Friday, 12 May 2017

Sqlite example in android

Using the Android SQLite Database. This tutorial describes how to use the SQLite database in Android applications. It also demonstrates how to use ContentProvider etc.
                                                             
This tutorials covers below points :

1) How to make a listview with text & checkbox.
2) How to select the multiple checkbox & retrieve checkbox value from listview.
3) How to add checkbox value to SQLite Database.
4) How to add value from edittext to SQLite Database.
5) How to fetch complete database Item & show them to listview.
6) Create/Read/Update/Delete Database record.

A special thanks to Camille Galang Lim, Chris, Shruti, Amit, Vivek, Richard Michael & many more who contacted me through email/phn in this month & encourage me to write a tutorial on SQlite by taking similar requirement(above six mentioned points).

 What is SQLite?

SQLite is an Open Source database. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. The database requires limited memory at runtime (approx. 250 KByte) which makes it a good choice from being embedded into other runtime environment.
SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before getting saved in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type.

SQLite in Android?

SQLite is embedded into every Android device. Using an SQLite database in Android does not require any setup procedure or administration of the database.
You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.
Access to an SQLite database involves accessing the file system. This can be slow. Therefore it is recommended to perform database operations asynchronously.
If your application creates a database, this database is by default saved in the directory DATA/data/APP_NAME/databases/FILENAME.
The parts of the above directory are constructed based on the following rules. DATA is the path which the Environment.getDataDirectory() method returns. APP_NAME is your application name. FILENAME is the name you specify in your application code for the database.

Creating and updating database with SQLiteOpenHelper :

To create and upgrade a database in your Android application you create a subclass of the SQLiteOpenHelper class. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.
In this class you need to override the following methods to create and update your database.
  • onCreate() - is called by the framework, if the database is accessed but not yet created.
  • onUpgrade() - called, if the database version is increased in your application code. This method allows you to update an existing database schema or to drop the existing database and recreate it via the onCreate() method.
Both methods receive an SQLiteDatabase object as parameter which is the Java representation of the database.
The SQLiteOpenHelper class provides the getReadableDatabase() and getWriteableDatabase() methods to get access to an SQLiteDatabase object; either in read or write mode.
The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.

 SQLiteDatabase :
SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.
More specifically SQLiteDatabase provides the insert(), update() and delete() methods.
In addition it provides the execSQL() method, which allows to execute an SQL statement directly.
The object ContentValues allows to define key/values. The key represents the table column identifier and the value represents the content for the table record in this column. ContentValues can be used for inserts and updates of database entries.
Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .
rawQuery() directly accepts an SQL select statement as input.
query() provides a structured interface for specifying the SQL query.
SQLiteQueryBuilder is a convenience class that helps to build SQL queries.

rawQuery() :


How to use :  rawQuery() 
Cursor cursor = getReadableDatabase(). rawQuery("select * from todo where _id = ?", new String[] { id });


query() :










How to use :  query() 



















return database.query(DATABASE_TABLE,
    new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION },
    null, null, null, null, null);











Cursor :







A
query
returns a
Cursor
object. A Cursor represents the result of a query and basically
points
to one row of the query result. This way Android can buffer the
query results efficiently; as it does not have to load all data into
memory.
To get the number of
elements of the resulting query use the
getCount()
method.
To move between individual data
rows, you can use the
moveToFirst()
and
moveToNext()
methods. isAfterLast()
method allows to check if the end of the query result has been
reached.
Cursor
provides typed
get*()
methods, e.g.
getLong(columnIndex),
getString(columnIndex)
to access the column data for the current position of the result.
The
"columnIndex" is the number of the column you are
accessing.
Cursor
also provides the
getColumnIndexOrThrow(String)
method which allows to get the column index for a column name of the
table.
A
Cursor
needs to be closed with the
close()
method call.









Writing CreateDataBase Class
















Before you go further you need to write your CreateDataBase class where you create you database,table & all table fields.











Now extend your CreateDataBase.java class from SQLiteOpenHelper.






























public class CreateDataBase extends SQLiteOpenHelper { 







After extending your class from SQLiteOpenHelper you need to override two methods onCreate() and onUpgrage()












 onCreate() – These is where we need to write create table statements. This is called when database is created.







onUpgrade() – This method is called when database is upgraded like modifying the table structure, adding constraints to database etc.



CreateDataBase.java




































public class CreateDataBase extends SQLiteOpenHelper {







 public static final String DATABASE_NAME = "ROBI_DB";







 public static final String TABLE_NAME = "ROBI_TABLE";







 public static final int VERSION = 1;







 public static final String KEY_ID = "_id";





 public static final String FNAME = "F_NAME";






 public static final String SCRIPT = "create table " + TABLE_NAME + " ("



   + KEY_ID + " integer primary key autoincrement, " + FNAME



   + " text not null);";





 public CreateDataBase(Context context, String name,



                          CursorFactory factory, int version) {



  super(context, name, factory, version);



  // TODO Auto-generated constructor stub



 }





 @Override



 public void onCreate(SQLiteDatabase db) {



  // TODO Auto-generated method stub



  db.execSQL(SCRIPT);



 }





 @Override



 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {



  // TODO Auto-generated method stub



  db.execSQL("drop table " + TABLE_NAME);



  onCreate(db);



 }}






























All CRUD Operations (Create, Read, Update and Delete)



Now we need to write methods for handling all database read and write operations. 
Here we are implementing following methods inside the DataBaseAdapter class for our 

table.

DataBaseAdapter.java







public class DataBaseAdapter {
 SQLiteDatabase database_ob;
 CreateDataBase openHelper_ob;
 Context context;

 public DataBaseAdapter(Context c) {
  context = c;
 }

 public DataBaseAdapter opnToRead() {
  openHelper_ob = new CreateDataBase(context,
    openHelper_ob.DATABASE_NAME, null, openHelper_ob.VERSION);
  database_ob = openHelper_ob.getReadableDatabase();
  return this;

 }

 public DataBaseAdapter opnToWrite() {
  openHelper_ob = new CreateDataBase(context,
    openHelper_ob.DATABASE_NAME, null, openHelper_ob.VERSION);
  database_ob = openHelper_ob.getWritableDatabase();
  return this;

 }

 public void Close() {
  database_ob.close();
 }

 public long insertDetails(String fname) {
  ContentValues contentValues = new ContentValues();
  contentValues.put(openHelper_ob.FNAME, fname);
  Log.d("hello",fname);

  opnToWrite();
  long val = database_ob.insert(openHelper_ob.TABLE_NAME, null,
    contentValues);
  Close();
  return val;

 }

 public Cursor queryName() {
  String[] cols = { openHelper_ob.KEY_ID, openHelper_ob.FNAME};
  opnToWrite();
  Cursor c = database_ob.query(openHelper_ob.TABLE_NAME, cols, null,
    null, null, null, null);

  return c;

 }

 public Cursor queryAll(int nameId) {
  String[] cols = { openHelper_ob.KEY_ID, openHelper_ob.FNAME};
  opnToWrite();
  Cursor c = database_ob.query(openHelper_ob.TABLE_NAME, cols,
    openHelper_ob.KEY_ID + "=" + nameId, null, null, null, null);

  return c;

 }

 public long updateldetail(int rowId, String fname) {
  ContentValues contentValues = new ContentValues();
  contentValues.put(openHelper_ob.FNAME, fname);

  opnToWrite();
  long val = database_ob.update(openHelper_ob.TABLE_NAME, contentValues,
    openHelper_ob.KEY_ID + "=" + rowId, null);

  Intent send=new Intent(context,DataBase_Item_ListActivity.class);

  context.startActivity(send);

  Close();
  return val;
 }

 public int deletOneRecord(int rowId) {
  // TODO Auto-generated method stub
  opnToWrite();
  int val = database_ob.delete(openHelper_ob.TABLE_NAME,
    openHelper_ob.KEY_ID + "=" + rowId, null);
  Close();


  Intent send=new Intent(context,DataBase_Item_ListActivity.class);

  context.startActivity(send);
  return val;
 }}

SplashActivity.java






public class SplashActivity extends AppCompatActivity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.splash);
        if (getIntent().getBooleanExtra("EXIT", false)) {
            finish();
            return;
        }
        new Handler().postDelayed(new Runnable() {
            @Override
            public void run() {
                Intent send = new Intent(getApplicationContext(),
                        MainActivity.class);
                startActivity(send);
            }
        }, 4000);}}


















DataBase_Item_ListActivity.java




public class DataBase_Item_ListActivity extends AppCompatActivity {



 DataBaseAdapter adapter_ob;
 CreateDataBase helper_ob;
 SQLiteDatabase db_ob;
 ListView nameList;
 Button registerBtn;
 Cursor cursor;

 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);
  nameList = (ListView) findViewById(R.id.lv_name);
  registerBtn = (Button) findViewById(R.id.btn_register);
  adapter_ob = new DataBaseAdapter(this);
                String[] from = { helper_ob.FNAME};
                int[] to = { R.id.tv_fname};
  cursor = adapter_ob.queryName();
  SimpleCursorAdapter cursorAdapter = new SimpleCursorAdapter(this,
    R.layout.row, cursor, from, to,0);
  nameList.setAdapter(cursorAdapter);
  if (cursorAdapter== null || cursorAdapter.isEmpty()) {

   Toast.makeText(getApplicationContext(),"DataBase is empty, Please add some value to DataBase",Toast.LENGTH_LONG).show();
  }
        nameList.setOnItemClickListener(new OnItemClickListener() {

   @Override
   public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
     long arg3) {
    // TODO Auto-generated method stub
    Bundle passdata = new Bundle();
    Cursor listCursor = (Cursor) arg0.getItemAtPosition(arg2);
    int nameId = listCursor.getInt(listCursor
      .getColumnIndex(helper_ob.KEY_ID));
    
    passdata.putInt("keyid", nameId);
    Intent passIntent = new Intent(DataBase_Item_ListActivity.this,
      UpdateRecordActivity.class);
    passIntent.putExtras(passdata);
    startActivity(passIntent);
   }
  });
  registerBtn.setOnClickListener(new OnClickListener() {

   @Override
   public void onClick(View arg0) {
    // TODO Auto-generated method stub
    Intent registerIntent = new Intent(DataBase_Item_ListActivity.this,
      InsertByEditTextActivity.class);
    startActivity(registerIntent);
   }});}
 @Override
 public boolean onKeyDown(int keyCode, KeyEvent event) {
  if (keyCode == KeyEvent.KEYCODE_BACK) {
   Intent intent = new Intent(DataBase_Item_ListActivity.this,
     MainActivity.class);
   intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
   intent.putExtra("EXIT", true);
   startActivity(intent);
  }
  return super.onKeyDown(keyCode, event);
 }}





InsertByEditTextActivity.java



public class InsertByEditTextActivity extends AppCompatActivity {



 DataBaseAdapter adapter;
 CreateDataBase helper;
 EditText fnameEdit;
 Button submitBtn, resetBtn;

 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.register);
  fnameEdit = (EditText) findViewById(R.id.et_fname);
                 submitBtn = (Button) findViewById(R.id.btn_submit);
  resetBtn = (Button) findViewById(R.id.btn_reset);
  adapter = new DataBaseAdapter(this);

  submitBtn.setOnClickListener(new OnClickListener() {

   @Override
   public void onClick(View arg0) {
    String fnameValue = fnameEdit.getText().toString();

    if(fnameValue.matches(""))
    {
     Toast.makeText(
       getApplicationContext(),
       "Please enter some value to enter in database",
       Toast.LENGTH_LONG).show();
    }

    else
    {
     adapter.insertDetails(fnameValue);
                                        Toast.makeText(
       getApplicationContext(),
       "Item saved to DataBase",
       Toast.LENGTH_LONG).show();
     Intent intent = new Intent(InsertByEditTextActivity.this,
       DataBase_Item_ListActivity.class);

     startActivity(intent);
    }}});
  resetBtn.setOnClickListener(new OnClickListener() {

   @Override
   public void onClick(View v) {
    // TODO Auto-generated method stub
    fnameEdit.setText("");
   }});}

 
@Override
 public boolean onKeyDown(int keyCode, KeyEvent event) {
  if (keyCode == KeyEvent.KEYCODE_BACK) {
   Intent intent = new Intent(InsertByEditTextActivity.this,
     MainActivity.class);
   intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
   intent.putExtra("EXIT", true);
   startActivity(intent);
  }
  return super.onKeyDown(keyCode, event);
}}









MainActivity.java



public class MainActivity extends AppCompatActivity implements AdapterView.OnItemClickListener {

    ListView rkt_ListView;
    Button btn_save_in_database,btn_show_database;

    private ArrayList<String> kaminey_dost_array_list = new ArrayList<String>();

    private void kaminey_dost() {
        kaminey_dost_array_list.add("Robi");
        kaminey_dost_array_list.add("Anubhan");
        kaminey_dost_array_list.add("Shikari");
        kaminey_dost_array_list.add("Chittu");
        kaminey_dost_array_list.add("Chuha");
        kaminey_dost_array_list.add("Baba");
        kaminey_dost_array_list.add("Gareeb");
        kaminey_dost_array_list.add("Vinay");
        kaminey_dost_array_list.add("Vakeel");
        kaminey_dost_array_list.add("Gajju");
        kaminey_dost_array_list.add("Ghoda");
        kaminey_dost_array_list.add("Chhota Ghoda");
        kaminey_dost_array_list.add("Peelu");
        kaminey_dost_array_list.add("Anil");
    }

    RktArrayAdapter rktArrayAdapter;
    DataBaseAdapter register;

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

        setContentView(R.layout.activity_main);
        kaminey_dost();
        rkt_ListView = (ListView) findViewById(R.id.rkt_listview);
        register = new DataBaseAdapter(this);

        rktArrayAdapter = new RktArrayAdapter(
                this,
                R.layout.list_row,
                android.R.id.text1,
                kaminey_dost_array_list
        );

        rkt_ListView.setAdapter(rktArrayAdapter);
       rkt_ListView.setOnItemClickListener(this);

        btn_save_in_database = (Button) findViewById(R.id.btn_save_in_database);
        btn_show_database= (Button) findViewById(R.id.btn_show_database);
        btn_save_in_database.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                String result = "";

                List<String> resultList = rktArrayAdapter.getCheckedItems();
                for (int i = 0; i < resultList.size(); i++) {
                    result += String.valueOf(resultList.get(i)) + "\n";
                }
                rktArrayAdapter.getCheckedItemPositions().toString();
                if(result.matches(""))
                {
                    Toast.makeText(
                            getApplicationContext(),
                            "You have not selected any value to save in database",
                            Toast.LENGTH_LONG).show(); }

                else
                {
                    Toast.makeText(
                            getApplicationContext(),
                            result+" "+"saved to DataBase",
                            Toast.LENGTH_LONG).show();

                    long val = register.insertDetails(result);
                   Intent send=new Intent(getApplicationContext(),DataBase_Item_ListActivity.class);
                    startActivity(send);
                }}});
        btn_show_database.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                    Intent send=new Intent(getApplicationContext(),DataBase_Item_ListActivity.class);
                    startActivity(send);
 }
        });    }

    @Override
    public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
        rktArrayAdapter.rkt_toggleChecked(i);
    }
    public class RktArrayAdapter extends ArrayAdapter<String> {

        private HashMap<Integer, Boolean> myChecked = new HashMap<Integer, Boolean>();

        public RktArrayAdapter(Context context, int resource,
                               int textViewResourceId, List<String> objects) {
            super(context, resource, textViewResourceId, objects);

            for(int i = 0; i < objects.size(); i++){
                myChecked.put(i, false);
            }
        }
        public void rkt_toggleChecked(int position){
            if(myChecked.get(position)){
                myChecked.put(position, false);
            }else{
                myChecked.put(position, true);
            }

            notifyDataSetChanged();
        }

        public List<Integer> getCheckedItemPositions(){
            List<Integer> checkedItemPositions = new ArrayList<Integer>();
            for(int i = 0; i < myChecked.size(); i++){
                if (myChecked.get(i)){
                    (checkedItemPositions).add(i);
               }            }

            return checkedItemPositions;
        }
        public List<String> getCheckedItems(){
            List<String> checkedItems = new ArrayList<String>();
            for(int i = 0; i < myChecked.size(); i++){
                if (myChecked.get(i)){
                    (checkedItems).add(kaminey_dost_array_list.get(i));
                }
            }            return checkedItems;
        }
        @Override
        public View getView(int position, View convertView, ViewGroup parent) {
            View row = convertView;
            if(row==null){
                LayoutInflater inflater=getLayoutInflater();
                row=inflater.inflate(R.layout.list_row, parent, false);
            }
            CheckedTextView checked_TextView = (CheckedTextView)row.findViewById(R.id.checked_textview);
            checked_TextView.setText(kaminey_dost_array_list.get(position));
            Boolean checked = myChecked.get(position);
            if (checked != null) {
                checked_TextView.setChecked(checked);
            }
   return row;
        }   }
@Override
    public boolean onKeyDown(int keyCode, KeyEvent event) {
        if (keyCode == KeyEvent.KEYCODE_BACK) {
            Intent intent = new Intent(MainActivity.this,
                    SplashActivity.class);
            intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
            intent.putExtra("EXIT", true);
            startActivity(intent);
        }
        return super.onKeyDown(keyCode, event);
    }}







UpdateRecordActivity.java





public class UpdateRecordActivity extends AppCompatActivity {



 DataBaseAdapter regadapter;

 int rowId;
 Cursor c;
 EditText fname;
 Button editSubmit, btnDelete;

 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.editregister);
  fname = (EditText) findViewById(R.id.et_editfname);

  editSubmit = (Button) findViewById(R.id.btn_update);
  btnDelete = (Button) findViewById(R.id.btn_delete);

  Bundle showData = getIntent().getExtras();
  rowId = showData.getInt("keyid");
  // Toast.makeText(getApplicationContext(), Integer.toString(rowId),
  // 500).show();
  regadapter = new DataBaseAdapter(this);

  c = regadapter.queryAll(rowId);

  if (c.moveToFirst()) {
   do {
    fname.setText(c.getString(1));


   } while (c.moveToNext());
  }

  editSubmit.setOnClickListener(new OnClickListener() {

   @Override
   public void onClick(View arg0) {
    // TODO Auto-generated method stub
    regadapter.updateldetail(rowId, fname.getText().toString());
    Toast.makeText(
      getApplicationContext(),
      "Item successfully updated to DataBase",
      Toast.LENGTH_LONG).show();
    finish();
   }
  });
  btnDelete.setOnClickListener(new OnClickListener() {

   @Override
   public void onClick(View v) {
    // TODO Auto-generated method stub
    regadapter.deletOneRecord(rowId);
    Toast.makeText(
      getApplicationContext(),
      "Item successfully removed from DataBase",
      Toast.LENGTH_LONG).show();
 finish();
   }});}

 @Override
 public boolean onKeyDown(int keyCode, KeyEvent event) {
  if (keyCode == KeyEvent.KEYCODE_BACK) {
   Intent intent = new Intent(UpdateRecordActivity.this,
     DataBase_Item_ListActivity.class);
   intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
   intent.putExtra("EXIT", true);
   startActivity(intent);
  }
  return super.onKeyDown(keyCode, event);
 }
}










Hope this tutorial will help you. Download complete project HERE. 





Here is the ouput screen : 








           












































Posted by



at







































2 comments:








  1. Hari18 October 2017 at 00:16
    Hi,
    Thanks for sharing the info about Android Plz keep sharing on... 
    Thank you...
    ReplyDelete




























        

      









Subscribe to:
Post Comments (Atom)