Android Database connection and QRUD operations

Database connectivity in android

Here is your Database Constant.java file

package com.dar.common;

import java.text.ParseException;
import java.text.SimpleDateFormat;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

public class Constant 
{
    public static final class DatabaseStrings
    {
        public static final String MYDATABASE_NAME = "PROGRAM";
        public static final int MYDATABASE_VERSION = 1;
        public static final String TABLE_PROG_DETAIL = "program_detail";

        public static final String PROGRAM_ID = "program_id";
        public static final String PROGRAM_DATE = "program_date";
        public static final String PROGRAM_TYPE = "program_type";
    }
    
    /**
     * @description DBhelper class Object creation
     */
    public static DBHelper mDBHelper = null;
    
    /**
     * @description SQLiteDatabase Object creation
     */
    public static SQLiteDatabase mSQLiteDatabase = null;
    
    /**
     * @description Open Database 
     * @param context
     */
    public static void OpenDatabase(Context context)
    {
        if(mDBHelper == null)
        {
            mDBHelper = new DBHelper(context, Constant.DatabaseStrings.MYDATABASE_NAME, null, Constant.DatabaseStrings.MYDATABASE_VERSION);
            mSQLiteDatabase = mDBHelper.getWritableDatabase();
        }
    }
    
    /**
     * @description Close Database 
     * @param Context
     */    
    public static void CloseDatabase()
    {
        if(mDBHelper != null)
        {
            mSQLiteDatabase.close();
            mDBHelper.close();
            mSQLiteDatabase = null;
            mDBHelper = null;
        }
    }
}

Here is your DBHelper.java file

package com.dar.common;

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

public class DBHelper extends SQLiteOpenHelper
{
    public DBHelper(Context context, String databaseName, CursorFactory factory, int databaseVersion) {
        super(context, databaseName, factory, databaseVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase db) 
    {
        db.execSQL(CREATE_TABLE_PROGRAMM_DETAILS); // Create table Site_Details
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
    {
        Log.d("OldVersion", ""+oldVersion);
        Log.d("newVersion", ""+newVersion);
        if(oldVersion < newVersion)
        {
            db.execSQL("DROP TABLE IF EXISTS " + Constant.DatabaseStrings.TABLE_PROG_DETAIL);
            onCreate(db);
        }
    }
    
    /**
     * @description Create query for Site_Details table
     */
    private static final String CREATE_TABLE_PROGRAMM_DETAILS = "create table "
        + Constant.DatabaseStrings.TABLE_PROG_DETAIL + " ("
        + Constant.DatabaseStrings.PROGRAM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + Constant.DatabaseStrings.PROGRAM_DATE + " TEXT NOT NULL, "
        + Constant.DatabaseStrings.PROGRAM_TYPE + " TEXT NOT NULL);";

    /**
     * @description insert record to program_detail table
     * @param db
     * @param program_date
     * @param program_type
     * @return
     */
    public long insertProgramDetails(SQLiteDatabase db, String program_date, String program_type) 
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put(Constant.DatabaseStrings.PROGRAM_DATE, program_date);
        contentValues.put(Constant.DatabaseStrings.PROGRAM_TYPE, program_type);
        return db.insert(Constant.DatabaseStrings.TABLE_PROG_DETAIL, null, contentValues);
    }
    
    /**
     * @description Delete query for all record of Program_Details table
     * @param db
     * @return
     */
    public int deleteProgramDetails(SQLiteDatabase db) 
    {
        return db.delete(Constant.DatabaseStrings.TABLE_PROG_DETAIL, null, null);
    }
    
    /**
     * @description Delete query for single record of Program_Details table
     * @param db
     * @param SiteID
     * @return
     */
    public int deleteProgramDetails(SQLiteDatabase db,int program_id) 
    {
        return db.delete(Constant.DatabaseStrings.TABLE_PROG_DETAIL, Constant.DatabaseStrings.PROGRAM_ID + " = " + program_id, null);
    }

    
    /**
     * @description Select query for all record of Program_Details table
     * @param db
     * @return
     */
    public Cursor getProgramInfo(SQLiteDatabase db) 
    {
        String[] columns = new String[] {
            Constant.DatabaseStrings.PROGRAM_ID,
            Constant.DatabaseStrings.PROGRAM_DATE,
            Constant.DatabaseStrings.PROGRAM_TYPE};
        return db.query(Constant.DatabaseStrings.TABLE_PROG_DETAIL, columns, null, null, null, null, null);
    }

    /**
     * @description Select query for single record of Program_Details table
     * @param db
     * @param siteID
     * @return
     */
    public Cursor getProgramInfo(SQLiteDatabase db, String date) 
    {
        String[] columns = new String[] {
                Constant.DatabaseStrings.PROGRAM_ID,
                Constant.DatabaseStrings.PROGRAM_DATE,
                Constant.DatabaseStrings.PROGRAM_TYPE};
        Log.d("cursor", "strftime('%Y-%m', program_date) = '" + date + "'");

        return db.query(Constant.DatabaseStrings.TABLE_PROG_DETAIL, columns, "strftime('%Y-%m', program_date) = '"  + date + "' ORDER BY program_date", null, null, null, null);

    }

    /**
     * @description Select query for single record of Program_Details table
     * @param db
     * @param siteID
     * @return
     */
    public Cursor getProgramInfo(SQLiteDatabase db, int program_id) 
    {
        String[] columns = new String[] {
                Constant.DatabaseStrings.PROGRAM_ID,
                Constant.DatabaseStrings.PROGRAM_DATE,
                Constant.DatabaseStrings.PROGRAM_TYPE};
        return db.query(Constant.DatabaseStrings.TABLE_PROG_DETAIL, columns, Constant.DatabaseStrings.PROGRAM_ID + " = " + program_id, null, null, null, null);
    }

    /**
     * @description used to update programm_details
     * @param db
     * @param program_id
     * @param program_date
     * @param program_type
     * @return
     */
    public long updateProgramDetails(SQLiteDatabase db, int program_id, String program_date, String program_type)    
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put(Constant.DatabaseStrings.PROGRAM_DATE, program_date);
        contentValues.put(Constant.DatabaseStrings.PROGRAM_TYPE, program_type);
        return db.update(Constant.DatabaseStrings.TABLE_PROG_DETAIL, contentValues, Constant.DatabaseStrings.PROGRAM_ID + " = "+ program_id, null);
    }
}

Your add detail activity AddProgram.java

package com.dar.program;

import java.util.Calendar;

import android.app.Activity;
import android.app.AlertDialog;
import android.app.DatePickerDialog;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.Window;
import android.widget.Button;
import android.widget.DatePicker;
import android.widget.EditText;
import android.widget.Toast;

import com.dar.common.Constant;

public class AddProgram extends Activity implements OnClickListener
{
 private EditText et_program_date = null, et_program_type = null;
 private Button btn_add = null;
 
 private String program_date = null, program_type = null;
 private String store_date = "";
 private int day, month, year;
 
 @Override
 protected void onCreate(Bundle savedInstanceState) 
 {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.add_program);
  
  et_program_date = (EditText) findViewById(R.id.et_program_date);
  et_program_type = (EditText) findViewById(R.id.et_program_type);
  btn_add = (Button) findViewById(R.id.btn_add);
  
  et_program_date.setOnClickListener(this);
  btn_add.setOnClickListener(this);
  
  final Calendar c = Calendar.getInstance();
  year = c.get(Calendar.YEAR) ;
  month = c.get(Calendar.MONTH);
  day = c.get(Calendar.DAY_OF_MONTH);
 }

 @Override
 public void onClick(View v) 
 {
  switch (v.getId()) 
  {
  case R.id.et_program_date:
   select_date();
   break;
  
  case R.id.btn_add:
   //code for Process Bar
   final ProgressDialog mProgressDialog = new ProgressDialog(AddProgram.this);
   mProgressDialog.setProgressStyle(ProgressDialog.STYLE_SPINNER);
   mProgressDialog.setMessage("Loading.... Please Wait");
   mProgressDialog.setIndeterminate(true);
   mProgressDialog.setCancelable(true);

   program_date = store_date;
   program_type = et_program_type.getText().toString().trim();
   
   if(program_date.equals(""))
   {
    Toast.makeText(AddProgram.this, "Please select program date", Toast.LENGTH_LONG).show();
   }
   else if(program_type.equals(""))
   {
    Toast.makeText(AddProgram.this, "Please select program type", Toast.LENGTH_LONG).show();
   }
   else
   {
    mProgressDialog.show();

    Constant.OpenDatabase(AddProgram.this);
    //Code to insert data in to database
    Constant.mDBHelper.insertProgramDetails(Constant.mSQLiteDatabase, program_date, program_type);
    Constant.CloseDatabase();
    
    mProgressDialog.dismiss();
    Intent mIntent = new Intent(AddProgram.this, ProgramList.class);
    startActivity(mIntent);
    finish();
   }
   break;
  }
 }
 
 private void select_date() 
 {
  final String[] MONTHS = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};

  DatePickerDialog dp = null;
  dp = new DatePickerDialog(AddProgram.this, android.R.style.Theme_Holo_Panel, new DatePickerDialog.OnDateSetListener() 
  {
   @Override
   public void onDateSet(DatePicker view, int mYear, int monthOfYear, int dayOfMonth) 
   {
    year = mYear;
    month = monthOfYear;
    day = dayOfMonth;

    String dis_date = String.valueOf(dayOfMonth) + "-";
    dis_date = dis_date +  String.valueOf(MONTHS[monthOfYear]) + "-" + mYear;
    
    store_date = String.valueOf(mYear) + "-";
    store_date = store_date + Constant.addZero(monthOfYear + 1) + "-" + Constant.addZero(dayOfMonth);
    
    et_program_date.setText(dis_date);
   }
  }, year, month, day);
  dp.requestWindowFeature(Window.FEATURE_NO_TITLE);     
  dp.show();
 }
}

Your get detail activity ProgramList.java

package com.dar.program;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Calendar;

import android.app.Activity;
import android.app.AlertDialog;
import android.app.DatePickerDialog;
import android.app.Dialog;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.view.Window;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.BaseAdapter;
import android.widget.DatePicker;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.TextView;

import com.dar.common.Constant;

public class ProgramList extends Activity implements OnClickListener
{
    private ImageView iv_select_date = null, iv_add_program = null;
    private ListView lv_program_list = null;
    
    private int day, month, year;
    private ArrayList<String> mArrProgramID = null, mArrProgramDate = null, mArrProgramType = null;
    
    private ProgrammListAdapter mListAdapter = null; 
    
    @Override
    protected void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.program_list);
        
        iv_select_date = (ImageView) findViewById(R.id.iv_select_date);
        iv_add_program = (ImageView) findViewById(R.id.iv_add_program);
        lv_program_list = (ListView) findViewById(R.id.lv_program_list);
        
        mArrProgramID = new ArrayList<String>();
        mArrProgramDate = new ArrayList<String>();
        mArrProgramType = new ArrayList<String>();
        
        mListAdapter = new ProgrammListAdapter();
        
        iv_select_date.setOnClickListener(this);
        iv_add_program.setOnClickListener(this);
        
        Calendar c = Calendar.getInstance();
        year = c.get(Calendar.YEAR) ;
        month = c.get(Calendar.MONTH) + 1;
        day = c.get(Calender.DATE) ;
        
        lv_program_list.setOnItemClickListener(new OnItemClickListener() 
        {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) 
            {
                Intent mEditIntent = new Intent(ProgramList.this, EditProgram.class);
                mEditIntent.putExtra(Constant.DatabaseStrings.PROGRAM_ID, mArrProgramID.get(position));
                startActivity(mEditIntent);
            }
        });
    }
    
    @Override
    protected void onResume() 
    {
        super.onResume();
        getData(year, day, month);
    }

    private void getData(int day, int month, int year)
    {
        clearArray();

        //code for Process Bar
        final ProgressDialog mProgressDialog = new ProgressDialog(ProgramList.this);
        mProgressDialog.setProgressStyle(ProgressDialog.STYLE_SPINNER);
        mProgressDialog.setMessage("Loading.... Please Wait");
        mProgressDialog.setIndeterminate(true);
        mProgressDialog.setCancelable(true);        
        mProgressDialog.show();

        Constant.OpenDatabase(ProgramList.this);
        Cursor cursor = Constant.mDBHelper.getProgramInfo(Constant.mSQLiteDatabase, year + "-" + Constant.addZero(month) + "-" + Constant.addZero(day));
        if (cursor != null)
        {
            if (cursor.getCount() > 0)
            {
                int iProg_id = cursor.getColumnIndex(Constant.DatabaseStrings.PROGRAM_ID);
                int iProg_date = cursor.getColumnIndex(Constant.DatabaseStrings.PROGRAM_DATE);
                int iProg_type = cursor.getColumnIndex(Constant.DatabaseStrings.PROGRAM_TYPE);
                
                for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) 
                {
                    mArrProgramID.add(cursor.getString(iProg_id));
                    mArrProgramDate.add(cursor.getString(iProg_date));
                    mArrProgramType.add(cursor.getString(iProg_type));
                }
            }
            if (!cursor.isClosed()) 
            {
                cursor.close();
            }
        }
        Constant.CloseDatabase();
        
        lv_program_list.setAdapter(mListAdapter);
        mProgressDialog.dismiss();
    }
    
    @Override
    public void onClick(View v) 
    {
        switch (v.getId()) 
        {
        case R.id.iv_select_date:
            select_date();
            break;
        
        case R.id.iv_add_program:
            Intent mAddProgrammIntent = new Intent(ProgramList.this, AddProgram.class);
            startActivity(mAddProgrammIntent);
            break;
        }
    }
    
    private void select_date() 
    {
        final Calendar c = Calendar.getInstance();

        int d = c.get(Calendar.DAY_OF_MONTH);

        DatePickerDialog dp = null;
        dp = new DatePickerDialog(ProgramList.this, android.R.style.Theme_Holo_Panel, new DatePickerDialog.OnDateSetListener() 
        {
            @Override
            public void onDateSet(DatePicker view, int mYear, int monthOfYear, int dayOfMonth) 
            {
                month = monthOfYear + 1;
                year = mYear;
                getData(month, year);
            }
        }, year, month - 1, d);
        dp.requestWindowFeature(Window.FEATURE_NO_TITLE);     
        dp.show();
        
        try 
        {
            Field[] datePickerDialogFields = dp.getClass().getDeclaredFields();
            for (Field datePickerDialogField : datePickerDialogFields) 
            {
                if (datePickerDialogField.getName().equals("mDatePicker")) 
                {
                    datePickerDialogField.setAccessible(true);
                    DatePicker datePicker = (DatePicker) datePickerDialogField.get(dp);
                    Field datePickerFields[] = datePickerDialogField.getType().getDeclaredFields();
                    for (Field datePickerField : datePickerFields) 
                    {
                        if ("mDayPicker".equals(datePickerField.getName()) || "mDaySpinner".equals(datePickerField.getName()))
                        {
                            datePickerField.setAccessible(true);
                            Object dayPicker = new Object();
                            dayPicker = datePickerField.get(datePicker);
                            ((View) dayPicker).setVisibility(View.GONE);
                        }
                    }
                }
            }
        } 
        catch (Exception ex) 
        {
            ex.printStackTrace();
        }
    }
    
    private void clearArray()
    {
        mArrProgramID.clear();
        mArrProgramDate.clear();
        mArrProgramType.clear();
    }
    
    private class ProgrammListAdapter extends BaseAdapter
    {
        LayoutInflater mLayoutInflater = null;
        public ProgrammListAdapter() 
        {
            mLayoutInflater = LayoutInflater.from(ProgramList.this);
        }
        
        @Override
        public int getCount() 
        {
            return mArrProgramID.size();
        }

        @Override
        public Object getItem(int position) 
        {
            return null;
        }

        @Override
        public long getItemId(int position) 
        {
            return 0;
        }

        @Override
        public View getView(final int position, View convertView, ViewGroup parent) 
        {
            if(convertView == null)
            {
                convertView = mLayoutInflater.inflate(R.layout.row_program, null);
            }
            
            ImageView iv_delete = (ImageView) convertView.findViewById(R.id.iv_delete);
            TextView tv_prog_date = (TextView) convertView.findViewById(R.id.tv_prog_date);
            TextView tv_prog_type = (TextView) convertView.findViewById(R.id.tv_prog_type);

            tv_prog_date.setText(Constant.formateDate(mArrProgramDate.get(position)));
            tv_prog_type.setText(mArrProgramType.get(position));
            
            iv_delete.setOnClickListener(new OnClickListener() 
            {
                @Override
                public void onClick(View v) 
                {
                    final AlertDialog.Builder mDialogDel = new AlertDialog.Builder(ProgramList.this);
                    mDialogDel.setTitle("Alert !");
                    mDialogDel.setMessage("Are you sure want to delete?");
                    mDialogDel.setPositiveButton("OK", new Dialog.OnClickListener() 
                    {
                        @Override
                        public void onClick(DialogInterface dialog, int which) 
                        {
                            Constant.OpenDatabase(ProgramList.this);
                            Constant.mDBHelper.deleteProgramDetails(Constant.mSQLiteDatabase, Integer.parseInt(mArrProgramID.get(position)));
                            Constant.CloseDatabase();
                            dialog.dismiss();
                            mListAdapter.notifyDataSetChanged();
                        }
                    });
                    mDialogDel.setNegativeButton("Cancel", new Dialog.OnClickListener() 
                    {
                        @Override
                        public void onClick(DialogInterface dialog, int which) 
                        {
                            dialog.dismiss();
                        }
                    });
                    mDialogDel.create();    
                    mDialogDel.show();

                }
            });
            return convertView;
        }
    }
}