Working with SQLite Database

Create class name TaskDatabaseHelper for creating database and table

import org.groundme.general.General;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TaskDatabaseHelper extends SQLiteOpenHelper {

    public static final String TAG = "TaskDatabaseHelper";
    public static final String DATABASE_NAME = "MyTask.db";
    public static final int DATABASE_VERSION = 9;

    public static final String TABLE_NAME = "tasklist";
    public static String ID="id";
    public static final String TASK_TITLE = "task_title";
    public static final String TASK_DESC = "task_desc";
    public static final String TASK_DATE = "task_date";
    public static final String TASK_TIME = "task_time";
    public static final String TASK_LOCATION = "task_location";
    public static final String TASK_LAT = "task_lat";
    public static final String TASK_LNG = "task_lng";
    public static final String TASK_STATUS = "status";
    public static final String TASK_TLBASE = "timeloc_base";
    public static final String TASK_DURATION = "task_duration";
   
    private static final String DATABASE_CREATE = "create table " + TABLE_NAME + "(" +
            ID + " integer primary key autoincrement ," +
            TASK_TITLE + " text," +
            TASK_DESC + " text," +
            TASK_DATE + " text," +
            TASK_TIME + " text," +
            TASK_LOCATION + " text ," +
            TASK_LAT + " text ," +
            TASK_LNG + " text ," +
            TASK_STATUS + " text ," +
            TASK_TLBASE + " INTEGER default 0," +
            TASK_DURATION + " text)";

    public TaskDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
        Log.i(General.TAG, TAG + "constructer");
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        Log.i(General.TAG, TAG + "oncreate");
        db.execSQL(DATABASE_CREATE);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
}
 

create class name TaskData for other operations

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.groundme.data.Task;
import org.groundme.general.General;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TaskData {

    public static final String TAG = "TaskData";
    private TaskDatabaseHelper taskdbhelper;
    private SQLiteDatabase sqlitedb;
//    Integer id;

    private String[] taskcol = {TaskDatabaseHelper.ID,TaskDatabaseHelper.TASK_TITLE,
            TaskDatabaseHelper.TASK_DESC, TaskDatabaseHelper.TASK_DATE,
            TaskDatabaseHelper.TASK_TIME, TaskDatabaseHelper.TASK_LOCATION, TaskDatabaseHelper.TASK_LAT,
            TaskDatabaseHelper.TASK_LNG,TaskDatabaseHelper.TASK_STATUS,TaskDatabaseHelper.TASK_TLBASE};

   
    String[] MyCol = { TaskDatabaseHelper.TASK_TIME };

    public TaskData(Context ctx) {
        Log.i(General.TAG, TAG + "TaskData");
        taskdbhelper = new TaskDatabaseHelper(ctx);
    }

    public void open() throws SQLException {

        Log.i(General.TAG, TAG + "open");
        if (sqlitedb == null) {          
            sqlitedb = taskdbhelper.getWritableDatabase();
        }
    }

    public void createTask(String title, String description, String date,
            String time, String location,String lat,String lng,String datetimeloc) {

        Log.i(General.TAG, TAG + "create task for insert ");
        ContentValues contentvalue = new ContentValues();

        contentvalue.put(TaskDatabaseHelper.TASK_TITLE, title);
        contentvalue.put(TaskDatabaseHelper.TASK_DESC, description);
        contentvalue.put(TaskDatabaseHelper.TASK_DATE, date);
        contentvalue.put(TaskDatabaseHelper.TASK_TIME, time);
        contentvalue.put(TaskDatabaseHelper.TASK_LOCATION, location);
        contentvalue.put(TaskDatabaseHelper.TASK_LAT, lat);
        contentvalue.put(TaskDatabaseHelper.TASK_LNG, lng);
        contentvalue.put(TaskDatabaseHelper.TASK_STATUS, "true");
        contentvalue.put(TaskDatabaseHelper.TASK_TLBASE, datetimeloc);
    //    contentvalue.put(TaskDatabaseHelper.TASK_DURATION, duration);
      
   
      
        sqlitedb.insert(TaskDatabaseHelper.TABLE_NAME, null, contentvalue);
        Log.i(General.TAG, TAG + "insert complete");

    }

    public void updateStatus(String id, String status) {
        ContentValues contentvalue = new ContentValues();
        contentvalue.put(TaskDatabaseHelper.TASK_STATUS, status);
      
        sqlitedb.update(TaskDatabaseHelper.TABLE_NAME, contentvalue, TaskDatabaseHelper.ID + "=?", new String[]{id});
        Log.i(General.TAG, TAG + "updateStatus for id="+ id + " status set to " + status);

    }


    public void update(Integer id,String utitle,String udesc,String udate,String utime,String uloc,String ulat,String ulng,String ustatus,String udatetimeloc)
    {
        Log.i(General.TAG, TAG + "update start...");

        ContentValues cv = new ContentValues();
        cv.put(TaskDatabaseHelper.TASK_TITLE, utitle);
        cv.put(TaskDatabaseHelper.TASK_DESC, udesc);
        cv.put(TaskDatabaseHelper.TASK_DATE, udate);
        cv.put(TaskDatabaseHelper.TASK_TIME, utime);
        cv.put(TaskDatabaseHelper.TASK_LOCATION, uloc);
        cv.put(TaskDatabaseHelper.TASK_LAT, ulat);
        cv.put(TaskDatabaseHelper.TASK_LNG, ulng);
        cv.put(TaskDatabaseHelper.TASK_STATUS, ustatus);
        cv.put(TaskDatabaseHelper.TASK_TLBASE, udatetimeloc);
        //cv.put(TaskDatabaseHelper.TASK_DURATION, dur);
      
        String whered= "id" + "=" + id;
        sqlitedb.update(TaskDatabaseHelper.TABLE_NAME, cv, whered , null);

        Log.i(General.TAG, TAG + "update end...");
    }


    public void deleteTask(String id)
    {
        Log.i("delete task", "Delete Task");
        long idis = Long.parseLong(id);      
        System.out.println("Sqlite database id is............."+idis);
        sqlitedb.delete(TaskDatabaseHelper.TABLE_NAME, TaskDatabaseHelper.ID + " = " + idis, null);
              
    }
   
    private Task cursorTotask(Cursor cursor) {

        Log.i(General.TAG, TAG + "cursortotask");

        Task task = new Task();
        task.setId(cursor.getInt(cursor.getColumnIndex("id")));
        //task.setId(cursor.getInt(0));
        task.setTtile(cursor.getString(1));
        task.setDescription(cursor.getString(2));
        task.setDate(cursor.getString(3));
        task.setTime(cursor.getString(4));
        task.setLocation(cursor.getString(5));
        task.setLang(cursor.getString(7));
        task.setLat(cursor.getString(6));
        task.setStatus(cursor.getString(8));
        task.setBase(cursor.getString(9));
    //    task.setDuration(cursor.getString(10));
      
      
        //        task.setLat(cursor.getString(cursor.getColumnIndex("lat")));
        return task;

    }
   
    public List<Task> getListofDate(String curdate)
    {  
        Log.i(General.TAG, TAG + "Start to Get List Of Date");
        List<Task> taskdatelist = new ArrayList<Task>();
   
    //Cursor c = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME, taskcol, TaskDatabaseHelper.TASK_DATE + " =? ",new String[]{curdate}, null, null, TaskDatabaseHelper.TASK_DATE + " ASC, " + TaskDatabaseHelper.TASK_TIME + " ASC");
    Cursor c = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME, taskcol,TaskDatabaseHelper.TASK_TLBASE + " =?" + " AND " + TaskDatabaseHelper.TASK_DATE + "=?",new String[]{"10",curdate}, null, null, TaskDatabaseHelper.TASK_TLBASE + " ASC, " + TaskDatabaseHelper.TASK_DATE + " ASC, " + TaskDatabaseHelper.TASK_TIME + " ASC");
   
        c.moveToFirst();
        while(!c.isAfterLast())
        {
            Task taskdate=cursorTotask(c);
            taskdatelist.add(taskdate);
            c.moveToNext();
        }
        c.close();
        Log.i(General.TAG, TAG + "Get List of Date End");
        return taskdatelist;
      
    }
   
   
   
    public List<Task> gettomorrowListofDate(String tomorrowdate)
    {
        Log.i(General.TAG, TAG+ "start tomorrow date list");
        List<Task> tasktomorrowdatelist = new ArrayList<Task>();
        Cursor c = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME, taskcol,TaskDatabaseHelper.TASK_TLBASE + " =?" + " AND " + TaskDatabaseHelper.TASK_DATE + " =? ",new String[]{"10",tomorrowdate},null, null, TaskDatabaseHelper.TASK_TLBASE + " ASC, " + TaskDatabaseHelper.TASK_DATE + " ASC, " + TaskDatabaseHelper.TASK_TIME + " ASC");
        c.moveToFirst();
        while(!c.isAfterLast())
        {
            Task taskdate=cursorTotask(c);
            tasktomorrowdatelist.add(taskdate);
            c.moveToNext();
        }
        c.close();
        Log.i(General.TAG, TAG + "Get List of Date End");
        return tasktomorrowdatelist;
    }
   
   
    public List<Task> getListofOtherDate(String otherdate)
    {  
        Log.i(General.TAG, TAG + "Start to Get List Of Date");
        List<Task> taskdatelist = new ArrayList<Task>();
   
        //Cursor c = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME, taskcol, TaskDatabaseHelper.TASK_DATE + " >? ",new String[]{otherdate}, null, null, TaskDatabaseHelper.TASK_DATE + ", " + TaskDatabaseHelper.TASK_TIME + " ASC");
        Cursor c = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME, taskcol,TaskDatabaseHelper.TASK_TLBASE + " =?" + " AND " + TaskDatabaseHelper.TASK_DATE + " >? ",new String[]{"10",otherdate}, null , null,TaskDatabaseHelper.TASK_TLBASE + " ASC, " + TaskDatabaseHelper.TASK_DATE + " ASC, " + TaskDatabaseHelper.TASK_TIME + " ASC");
        c.moveToFirst();
        while(!c.isAfterLast())
        {
            Task taskdate=cursorTotask(c);
            taskdatelist.add(taskdate);
            c.moveToNext();
        }
        c.close();
        Log.i(General.TAG, TAG + "Get List of Date End");
        return taskdatelist;
    }
   
    public List<Task> getAllTask() {

        Log.i(General.TAG, TAG + "getalltasklist..");
        List<Task> tasklist = new ArrayList<Task>();

        SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy");
        Cursor cursor = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME,taskcol,TaskDatabaseHelper.TASK_DATE + " >= ?",new String[]{sdf.format(new Date())},null,null, TaskDatabaseHelper.TASK_DATE + " ASC, " + TaskDatabaseHelper.TASK_TIME + " ASC");

        cursor.moveToFirst();

        while (!cursor.isAfterLast()) {
            Task task = cursorTotask(cursor);
            tasklist.add(task);

            cursor.moveToNext();
        }
        cursor.close();
        Log.i(General.TAG, TAG + "Get List Of Task...");

        return tasklist;

    }
   
   
    public List<Task> getAllTaskForLocationBased() {

        Log.i(General.TAG, TAG + "getalltasklist..");
        List<Task> tasklist = new ArrayList<Task>();
   
        //Cursor cursor = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME,taskcol,TaskDatabaseHelper.TASK_TLBASE + " =? ",new String[]{one},null,null, TaskDatabaseHelper.TASK_DATE + " ASC, " + TaskDatabaseHelper.TASK_TIME + " ASC");
        Cursor cursor = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME,taskcol,TaskDatabaseHelper.TASK_TLBASE + " = " + "11",null,null,null, TaskDatabaseHelper.ID + " DESC");
        cursor.moveToFirst();

        while (!cursor.isAfterLast()) {
            Task task = cursorTotask(cursor);
            tasklist.add(task);

            cursor.moveToNext();
        }
        cursor.close();
        Log.i(General.TAG, TAG + "Get List Of Task for the Location Based Data..."+tasklist);

        return tasklist;

    }

   

   
    public Task GetRecord(int id)
    {
        Task gettask = new Task();

        Cursor cursor = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME, taskcol, TaskDatabaseHelper.ID + "="+ id, null, null, null,null);

        if(cursor.moveToFirst()) {
            gettask = cursorTotask(cursor);
            cursor.moveToNext();
        }

        cursor.close();
        Log.i(General.TAG, TAG + "Get Single data");
        return gettask;
    }


    public List<Task> GetRecord(Integer id)
    {
        List<Task> gettask = new ArrayList<Task>();


        Cursor cursor = sqlitedb.query(TaskDatabaseHelper.TABLE_NAME, taskcol, TaskDatabaseHelper.ID + "="+ id.toString(), null, null, null,null);

        cursor.moveToFirst();
        while(!cursor.isAfterLast())
        {
            Task taskobj = cursorTotask(cursor);
            gettask.add(taskobj);
            cursor.moveToNext();

        }
        cursor.close();
        Log.i(General.TAG, TAG + "Get Single data");
        return gettask;
    }



    public void close() {

        Log.i(General.TAG, TAG + "close");
        sqlitedb.close();
    }

}

now using this operations in our main activity

Get Record from database using id

List<Task> lsttask;
Task taskgetset;(This is your getter setter class)
 
TaskData taskdataobj = new TaskData(AddTask.this);
            taskdataobj.open();
            Log.i(General.TAG, TAG + "get Single id");

            lsttask = taskdataobj.GetRecord(singleid);

taskgetset = lsttask.get(0);  

String name = taskgetset.getTitle();
            String desc = taskgetset.getDescrption();
            String date = taskgetset.getDate();
            String time = taskgetset.getTime();


for insert record

taskdataobj = new TaskData(AddTask.this);
                    taskdataobj.open();

                    taskdataobj.createTask(title, desc, date, time, loc, lat,
                            lang, datetimeloc);

                    Log.d(TAG, "Date :" + date + " Time: " + time + " Lat="

                    + lat + " Lon=" + lang);
                    taskdataobj.close();