Android Sqlite Development

           Android SQLite App. – Diet Meter.

A.      An Overview  to develop Android SQLite App. – Diet Meter.

B.      Working Model of SQLite App.

C.      Create SQL Helper Class
1.       To Create Tables.
2.       To insert data in Tables.
3.       To get the data from Tables.
4.       Drop or Update tables.

D.      Create Login Page or Splash page.
1.       Call Intent. Implement click listener.
2.       Refresh a Page.

E.       Create List Adapter.
1.       Define and Implement Checkbox in Viewholder class.
2.       Show checked box while scrolling.

F.       Create MainActivity Page.
1.       Define fields.
2.       Get Data.
3.       Define List Adapter.
4.       Associate data to list Adapter.
5.       Define Buttons to call intent. Implement click Listener.
6.       Get/Calculate Total value for checked rows.
7.       Save Calculated data in dietlog table for future reference.

G.     Create Class for Tables.
1.       exe
2.       cal
3.       dietlog

Step 1. – Create Class.public class MySQLiteHelper extends SQLiteOpenHelper {

Step 2. - Define Variables

1. DEFINE VARIABLES for Table creation and Data insertion in Table.

private static final int DATABASE_VERSION = 4;

private static final String DATABASE_NAME = "BookDB";

 Step 3. - Create Table Statements.private String ex ="CREATE TABLE ex (`activity` TEXT,`a125lbs`    INTEGER, `a155lbs`      INTEGER,`a185lbs` INTEGER, `id`     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT )";    private String cal ="CREATE TABLE cal (`food`     TEXT,`size` TEXT,`cal`  INTEGER,`id`      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT )";    private String dietlog ="CREATE TABLE dietlog (datee    TEXT,cal    INTEGER,workout      INTEGER,`id`      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT )"; 

Step4.-  Insert statement to  insert data in created tables as Master.private String[] exins=  new String[]{

"INSERT INTO `ex` VALUES('Golf ',210,260,310,1);" ,"INSERT INTO `ex` VALUES('Soccer',480,600,710,11);"}; private String[] calins= new String[]{ 
" INSERT INTO `cal` VALUES('Fruits(Avg-2nos).....','Per Day',160,1);"," INSERT INTO `cal` VALUES('Yogurt, non-fat','1 cup',150,49);"}; 

Step5.-  Statements to create Tables            db.execSQL(cal);            db.execSQL(ex);            db.execSQL(dietlog);

Step6.-  Statements to Insert Data in Tables 

for(String sql : calins){ db.execSQL(sql);}

for(String sql : exins){                db.execSQL(sql);            

}

Step7.-  Drop tables if exist.        db.execSQL("DROP TABLE IF EXISTS cal");        db.execSQL("DROP TABLE IF EXISTS ex");        db.execSQL("DROP TABLE IF EXISTS dietlog");    

Step8.-  Create Fresh Tables.

this.onCreate(db);

Step9.-  method to get  Data from table: cals..   

public List<cal> getAllcal() {        List<cal> cals = new LinkedList<cal>();         // 1. build the query        //   String query = "SELECT  * FROM " + TABLE_BOOKS+" order by author";        String query = "SELECT  id,food,size,cal FROM cal";
       // 2. get reference to writable DB        SQLiteDatabase db = this.getWritableDatabase();        Cursor cursor = db.rawQuery(query, null);         // 3. go over each row, build book and add it to list        cal cal1 = null;        if (cursor.moveToFirst()) {            do {                  cal1 = new cal();                cal1.setId(Integer.parseInt(cursor.getString(0)));           
                cal1.setfood(cursor.getString(1));                cal1.setsize(cursor.getString(2));                cal1.setCal(cursor.getInt(3));                 // Add book to books                cals.add(cal1);            } while (cursor.moveToNext());        }     
        // return books        return cals;    }

Step10.-  Method to get  Data from table: ex.

public List<exe> getAllex() {        List<exe> exe = new LinkedList<exe>();         // 1. build the query        //   String query = "SELECT  * FROM " + TABLE_BOOKS+" order by author";        String query = "SELECT  id,activity,a125lbs,a155lbs,a185lbs FROM ex";     
      // 2. get reference to writable DB        SQLiteDatabase db = this.getWritableDatabase();        Cursor cursor = db.rawQuery(query, null);         // 3. go over each row, build book and add it to list        exe exe1 = null;        if (cursor.moveToFirst()) {            do {                  exe1 = new exe();                exe1.setId(Integer.parseInt(cursor.getString(0)));           
                exe1.setactivity(cursor.getString(1));                exe1.seta125lbs(cursor.getInt(2));                exe1.seta155lbs(cursor.getInt(3));                exe1.seta185lbs(cursor.getInt(4));             
                exe.add(exe1);            } while (cursor.moveToNext());        }     
        return exe;    } 

Step11.-  Method to get  Data from table: dietlog.     public List<log> getAlllog() {        List<log> log = new LinkedList<log>();         // 1. build the query        //   String query = "SELECT  * FROM " + TABLE_BOOKS+" order by author";        String query = "SELECT  id,datee,cal,workout FROM dietlog";     
      // 2. get reference to writable DB        SQLiteDatabase db = this.getWritableDatabase();        Cursor cursor = db.rawQuery(query, null);         // 3. go over each row, build book and add it to list        log exe1 = null;        if (cursor.moveToFirst()) {            do {                  exe1 = new log();                exe1.setId(cursor.getInt(0));           
                exe1.setLdate(cursor.getString(1));                exe1.setLcal(cursor.getInt(2));                exe1.setLworkout(cursor.getInt(3));                 log.add(exe1);            } while (cursor.moveToNext());        }         return log;    }  

Step12.-  Method to save Data in table: dietlog.     public void savelog(String ldate,int lcal,int lworkout) {      String query,query2;
      SQLiteDatabase db = this.getWritableDatabase();     
      if (!checkdata(ldate))      //insert new record            query = "insert into dietlog(datee,cal,workout) values ('"    +ldate+"',"+lcal+ ","+lworkout+")";   
      else            query = "update dietlog "                        + " set cal="+lcal +","                        + " workout="+lworkout +" "                        + " where datee='"+ldate+"'";     db.execSQL(query);    //      db.close();    } 

Step13.-  Method to save Data in table: dietlog.    public void deletelog() {      String query;  
      SQLiteDatabase db = this.getWritableDatabase();      query = "delete from dietlog";   
      db.execSQL(query);      db.close();    }    public boolean checkdata(String ldate)    {      SQLiteDatabase db = this.getWritableDatabase();      boolean rtnmsg;      String query = "select datee from dietlog where datee = ?";        Cursor cursor = db.rawQuery(query, new String[] {ldate});        if (cursor.getCount()!=0)        {                    rtnmsg=true;        /* record exist */         }        else        {                rtnmsg=false;        }        return rtnmsg;    }