working with sqlite database in android programming

I want a sample working demo or link to understand the database functions like insert, delete, modify etc.

Than开发者_如何学Cks

Android's Notepad tutorial (or Notepad Sample) would be the ideal place to start.

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;



   public class DatabaseHelper extends SQLiteOpenHelper{

       //The Android's default system path of your application database.
       private static String DB_PATH = "/data/data/com.logicnext.SmartClock/databases/";

       private static final String DATABASE_NAME = "AlarmClock_database";


       public static final String COLOR_SEEKR = "seekR";
       public static final String COLOR_SEEKG = "seekG";
       public static final String COLOR_SEEKB = "seekB";

       //private static final int DATABASE_VERSION = 1;

       private final Context context;
       private SQLiteDatabase db; int Registration=0;

       /**
        * Constructor
        * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
        * @param context
        */
       public DatabaseHelper(Context context) {

           super(context, DATABASE_NAME, null, 1);
           this.context = context;
       }  

       /**
        * Creates a empty database on the system and rewrites it with your own database.
        * */
       public void createDataBase() throws IOException{

           boolean dbExist = checkDataBase();

           if(dbExist){
                   //do nothing - database already exist
           }else{

                   //By calling this method and empty database will be created into the default system path
                  //of your application so we are gonna be able to overwrite that database with our database.
                   this.getWritableDatabase();

                   try {

                           copyDataBase();

                   } catch (IOException e) {

                           throw new Error("Error copying database");

                   }
           }

       }

       /**
        * Check if the database already exist to avoid re-copying the file each time you open the application.
        * @return true if it exists, false if it doesn't
        */
       private boolean checkDataBase(){

           SQLiteDatabase checkDB = null;

           try{
                   String myPath = DB_PATH + DATABASE_NAME;
                   checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

           }catch(SQLiteException e){

                   //database does't exist yet.

           }

           if(checkDB != null){

                   checkDB.close();

           }

           return checkDB != null ? true : false;
        }

       /**
        * Copies your database from your local assets-folder to the just created empty database in the
        * system folder, from where it can be accessed and handled.
        * This is done by transfering bytestream.
        * */
         private void copyDataBase() throws IOException{

           //Open your local db as the input stream
           InputStream myInput = context.getAssets().open(DATABASE_NAME);

           // Path to the just created empty db
           String outFileName = DB_PATH + DATABASE_NAME;

           //Open the empty db as the output stream
           OutputStream myOutput = new FileOutputStream(outFileName);

           //transfer bytes from the inputfile to the outputfile
           byte[] buffer = new byte[9216];
           int length;
           while ((length = myInput.read(buffer))>0){
                   myOutput.write(buffer, 0, length);
           }

           //Close the streams
           myOutput.flush();
           myOutput.close();
           myInput.close();

       }

       public void openDataBase() throws SQLException{

           //Open the database
           String myPath = DB_PATH + DATABASE_NAME;
           db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

       }

       @Override
           public synchronized void close() {

               if(db != null)
                       db.close();

               super.close();

           }

           @Override
           public void onCreate(SQLiteDatabase db) {
                   //db.execSQL(DATABASE_CREATE_1);
                   //db.execSQL(DATABASE_CREATE_2);
                   //db.execSQL(DATABASE_CREATE_3);

           }

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

           }

           public long setSetting_Application(int date_format, int time_font, int setting_mode, int setting_autolock, int setting_one_two_hr, int setting_two_four_hr, String setting_background, String country_name, String city_name)
           {
              ContentValues initialValues = new ContentValues();
               initialValues.put(SETTING_MODE, setting_mode);
               initialValues.put(SETTING_AUTOLOCK, setting_autolock);
               initialValues.put(SETTING_ONE_TWO_HR, setting_one_two_hr);
               initialValues.put(SETTING_TWO_FOUR_HR, setting_two_four_hr);
               initialValues.put(SETTING_BACKGROUND, setting_background);
               initialValues.put(DATE_FORMAT, date_format);
               initialValues.put(TIME_FONT, time_font);
               initialValues.put(COUNTRY_NAME, country_name);
               initialValues.put(CITY_NAME, city_name);
               long l=db.update(DATABASE_TABLE_2, initialValues, null, null);
               return l;//db.insert(DATABASE_TABLE_2, null, initialValues);

           }

           public long setNew_Alarm(int enable_alarm, String alarm_title, String set_alarm_time, String repeat_alarm, String set_alarm_sound, String setalarm_snooze, String alarm_background, int u_id, String set_repeat_days, String alarm_note)
           {
               //ByteArrayOutputStream out1 = new ByteArrayOutputStream();
               //alarm_background.compress(Bitmap.CompressFormat.JPEG, 100, out1);
               ContentValues initialValues = new ContentValues();
               initialValues.put(ENABLE_ALARM, enable_alarm);
               initialValues.put(ALARM_TITLE, alarm_title);
               initialValues.put(SET_ALARM_TIME, set_alarm_time);
               initialValues.put(REPEAT_ALARM, repeat_alarm);
               initialValues.put(SET_ALARM_SOUND, set_alarm_sound);
               initialValues.put(SETALARM_SNOOZE, setalarm_snooze);
               initialValues.put(ALARM_BACKGROUND, alarm_background);
               initialValues.put(U_ID, u_id);
               initialValues.put(SET_REPEAT_DAYS, set_repeat_days);
               initialValues.put(ALARM_NOTE, alarm_note);
               long l=db.insert(DATABASE_TABLE_1, null, initialValues);
               return l;//db.insert(DATABASE_TABLE_2, null, initialValues);

           }

           public long edit_Alarm(int enable_alarm, String alarm_title, String set_alarm_time, String repeat_alarm, String set_alarm_sound, String setalarm_snooze, String alarm_background, int u_id, String set_repeat_days, String alarm_note)
           {
               //ByteArrayOutputStream out1 = new ByteArrayOutputStream();
               //alarm_background.compress(Bitmap.CompressFormat.JPEG, 100, out1);
               ContentValues initialValues = new ContentValues();
               initialValues.put(ENABLE_ALARM, enable_alarm);
               initialValues.put(ALARM_TITLE, alarm_title);
               initialValues.put(SET_ALARM_TIME, set_alarm_time);
               initialValues.put(REPEAT_ALARM, repeat_alarm);
               initialValues.put(SET_ALARM_SOUND, set_alarm_sound);
               initialValues.put(SETALARM_SNOOZE, setalarm_snooze);
               initialValues.put(ALARM_BACKGROUND, alarm_background);
               initialValues.put(U_ID, u_id);
               initialValues.put(SET_REPEAT_DAYS, set_repeat_days);
               initialValues.put(ALARM_NOTE, alarm_note);
               long l=db.update(DATABASE_TABLE_1, initialValues, "U_ID="+"'"+u_id+"'", null);
               return l;//db.insert(DATABASE_TABLE_2, null, initialValues);

           }

           public ArrayList<NewAlarmSetting_getset> ReturnNewAlarm_setting() throws SQLException
           {
              ArrayList<NewAlarmSetting_getset> new_alarm_setting = new ArrayList<NewAlarmSetting_getset>();

                 Bitmap bmp;   
            Cursor c = db.rawQuery("select * from new_alarm_setting", null);

                        int numRows = c.getCount();
                        c.moveToFirst();
                        for (int i =0; i < numRows; i++)
                        {
                            NewAlarmSetting_getset QA=new NewAlarmSetting_getset();

                             //byte[] blob = c.getBlob(1); 
                             //bmp = BitmapFactory.decodeByteArray(blob,0,blob.length);
                             QA.setAlarm_background(c.getString(3));
                             QA.setAlarm_title(c.getString(4));
                             QA.setEnable_alarm(Integer.parseInt(c.getString(5)));
                             QA.setSet_alarm_sound(c.getString(8));
                             QA.setSet_alarm_time(c.getString(9));
                             QA.setSetalarm_snooze(c.getString(7));
                             QA.setRepeat_alarm(c.getString(6));
                             QA.setU_id(Integer.parseInt(c.getString(2)));
                             QA.setSet_repeat_days(c.getString(1));
                             QA.setAlarm_note(c.getString(0));

                             new_alarm_setting.add(QA);
                         c.moveToNext();
                        }
                c.close();
                return new_alarm_setting;
        }

           public ArrayList<ApplicationSetting_Get_Set> ReturnApplication_setting() throws SQLException
           {
              ArrayList<ApplicationSetting_Get_Set> Application_setting = new ArrayList<ApplicationSetting_Get_Set>();
              Cursor c = db.rawQuery("select * from Settings", null);

              int numRows = c.getCount();
                        c.moveToFirst();
                        for (int i =0; i < numRows; i++)
                        {
                            ApplicationSetting_Get_Set QA=new ApplicationSetting_Get_Set();
                             QA.setSetting_autolock(Integer.parseInt(c.getString(4)));
                             QA.setSetting_background(c.getString(5));
                             QA.setSetting_mode(Integer.parseInt(c.getString(6)));
                             QA.setSetting_one_two_hr(Integer.parseInt(c.getString(7)));
                             QA.setSetting_two_four_hr(Integer.parseInt(c.getString(8)));
                             QA.setDate_format(Integer.parseInt(c.getString(2)));
                             QA.setTime_font(Integer.parseInt(c.getString(3)));
                             QA.setCoutry_name(c.getString(0));
                             QA.setCity_name(c.getString(1));

                             Application_setting.add(QA);
                         c.moveToNext();
                        }
                c.close();
                return Application_setting;
        }

           public long setColor_Application(int color_seekr, int color_seekg, int color_seekb)
           {
              ContentValues initialValues = new ContentValues();
               initialValues.put(COLOR_SEEKR, color_seekr);
               initialValues.put(COLOR_SEEKG, color_seekg);
               initialValues.put(COLOR_SEEKB, color_seekb);

               long l=db.update(DATABASE_TABLE_3, initialValues, null, null);
               return l;//db.insert(DATABASE_TABLE_2, null, initialValues);

           }
           public ArrayList<ColorSettingGetSet> ReturnColor_setting() throws SQLException
           {
              ArrayList<ColorSettingGetSet> Application_setting = new ArrayList<ColorSettingGetSet>();


            Cursor c = db.rawQuery("select * from color_setting", null);

                        int numRows = c.getCount();
                        c.moveToFirst();
                        for (int i =0; i < numRows; i++)
                        {
                            ColorSettingGetSet QA=new ColorSettingGetSet();
                             QA.setSeekb(Integer.parseInt(c.getString(0)));
                             QA.setSeekg(Integer.parseInt(c.getString(1)));                                 
                             QA.setSeekr(Integer.parseInt(c.getString(2)));

                             Application_setting.add(QA);
                         c.moveToNext();
                        }
                c.close();
                return Application_setting;
        }
           /**
           * update the background image bitmap
           * @param bitmap b
           * @return
           */
          public int update_background(Bitmap b)
          {
              ByteArrayOutputStream out1 = new ByteArrayOutputStream();
              b.compress(Bitmap.CompressFormat.JPEG, 100, out1);
              ContentValues initialValues = new ContentValues();
              initialValues.put("image_bitmap",out1.toByteArray());
              int a=db.update(DATABASE_TABLE_1, initialValues, null,null);
              return a;
          }
          /**
           * get the application background
           * @param res
           * @return bitmap of the background
           */
          public Bitmap application_background(Resources res)
          {

            Bitmap bmp = null;
            Cursor c = db.query(DATABASE_TABLE_1, new String[] {"image_bitmap"},null, null, null, null, null);
              int numRows = c.getCount();
              if(numRows!=0)
              {
              c.moveToFirst();  
            byte[] blob = c.getBlob(c.getColumnIndex("image_bitmap")); 
            bmp = BitmapFactory.decodeByteArray(blob,0,blob.length);
              }
              c.close();
              return bmp;
          }
          /**
           * Insert application id
           * @param id integer value for id
           * @return
           */
          public long insertappID(int id) 
            {
                ContentValues initialValues = new ContentValues();
                initialValues.put("id", id);          

                long result1=db.insert(DATABASE_TABLE_4, null, initialValues);
                return result1;
            }
          /**
           * get application id
           * @return id
           */
          public int application_id()
          {

            int id = 0 ;
            Cursor c = db.query(DATABASE_TABLE_4, new String[] {"id"},null, null, null, null, null);
              int numRows = c.getCount();
              if(numRows!=0)
              {
              c.moveToFirst();  
                id =c.getInt(0); 
              }
              c.close();
              return id;
          }
          /**
           * update application id into database
           * @param newId - new id to be update
           * @param oldId - old id to be updated
           * @return true if update is success
           */
          public boolean updateId(int newId,int oldId)
          {
              ContentValues initialValues = new ContentValues();
              initialValues.put("id", newId);
              return db.update(DATABASE_TABLE_4, initialValues, "id="+ oldId, null)>0;
          }

          public boolean delete_alarm(int u_id)
          {

             return db.delete(DATABASE_TABLE_1, "U_ID="+"'"+u_id+"'", null) > 0;

          }
         /**
           * Insert tone
           * @param tone String value for tone
           * @return
           */
          public long insert_tone(String tone) 
            {
                ContentValues initialValues = new ContentValues();
                initialValues.put("name", tone);          

                long result1=db.insert(DATABASE_TABLE_5, null, initialValues);
                return result1;
            }
          /**
           * get tones
           * @return String tone
           */
          public String gettone()
          {

            String tone = null;
            Cursor c = db.query(DATABASE_TABLE_5, new String[] {"name"},null, null, null, null, null);
              int numRows = c.getCount();
              if(numRows!=0)
              {
              c.moveToFirst();  
                tone =c.getString(0); 
              }
              c.close();
              return tone;
          }
         public boolean delete_tone()
          {

             return db.delete(DATABASE_TABLE_5, null, null) > 0;

          }
}

The following link: Creating and using SQLite Databases in Android - made it easy for me to understand basic database functions. Hope it would help you as well... :)