Simple SQLite Example in Android

See more basics on Android along with interview questions

DBAdapter.java (DABASEHELPER CLASS)

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

public class DBAdapter
  {
 public static final String KEY_ROWID = "id";
 public static final String KEY_NAME = "title";
 public static final String KEY_NICKNAME = "duedate";
 private static final String DATABASE_NAME = "assinDB.db";
 private static final String DATABASE_TABLE = "assignments";
 private static final int DATABASE_VERSION = 2; 

 private final Context context;
 private DatabaseHelper ObjectDBHelper;
 private SQLiteDatabase db;

   public DBAdapter(Context ctx) {
      context = ctx;
    }

   private static class DatabaseHelper extends SQLiteOpenHelper  {
       public DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null,DATABASE_VERSION);
            // TODO Auto-generated constructor stub
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub         

       db.execSQL("CREATE TABLE IF not exists " + DATABASE_TABLE  + " ("
                   + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                   + KEY_NAME + " TEXT NOT NULL, " 
                   +  KEY_NICKNAME+ " TEXT NOT NULL);");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion)
        {
            // TODO Auto-generated method stub
            db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE);
        }  
      }

// =====================open database================
    public DBAdapter open() throws SQLException {
        ObjectDBHelper = new DatabaseHelper(context);
        db = ObjectDBHelper.getWritableDatabase();
        return this;
    }

// =========close database==================
    public void close() {
        ObjectDBHelper.close();
    }

//==enter fields in from SQLiteExample database====
  public void entryfield(String namestr, String nickstr) {
    ContentValues cv = new ContentValues(); // Bundle for writting the database fields
    cv.put(KEY_NAME, namestr); // put the values passed ContentValues
    cv.put(KEY_NICKNAME, nickstr); // put the values passed to  ContentValues
    db.insert(DATABASE_TABLE, null, cv); //insert into databse with vlues as "content vales"
  }

//===========getting all data==============
  public String getdata() {
        // TODO Auto-generated method stub
      String[] coloumns = new String[] { KEY_ROWID, KEY_NAME,KEY_NICKNAME };
      Cursor c = db.query(DATABASE_TABLE, coloumns, null, null, null, null, null);
                   // basically reading a database need cursor
      String result = " "; // since string is to be returned
      System.out.println("count of cursor=====>>"+c.getCount());
     int iRowid = c.getColumnIndex(KEY_ROWID);  // calling each row values                
     int iRowName = c.getColumnIndex(KEY_NAME);
     int iRowNickName = c.getColumnIndex(KEY_NICKNAME);
       if (c.moveToFirst()) {
            c.moveToFirst();
            for (int i = 0; i < c.getCount(); i++) {
                result = result + c.getString(iRowid) + " "
                                + c.getString(iRowName) + " "
                                + c.getString(iRowNickName) + "\n";
        System.out.println(result + " <> " + iRowid + " <> " +
                     iRowName);
        c.moveToNext();
            }
        }
      c.close();
      db.close();
      return result;
  }

// ==================== get info ===================
   public String returnName(long l) {
        // TODO Auto-generated method stub
     String[] coloumns = new String[] { KEY_ROWID, KEY_NAME,KEY_NICKNAME };
                // calling elements in an array
     Cursor c = db.query(DATABASE_TABLE, coloumns, KEY_ROWID +"=" + l,null, null, null, null);
        if (c != null) {
            c.moveToFirst();
            String name = c.getString(1);
                 // since name is in position 1 ie second coloumn
            return name;
           }
        return null;
    }

    public String returnickname(long l) {
        // TODO Auto-generated method stub
        String[] coloumns = new String[] { KEY_ROWID, KEY_NAME, KEY_NICKNAME };//calling elements in an array
        Cursor c = db.query(DATABASE_TABLE, coloumns,KEY_ROWID + "=" + l, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
            String nickname = c.getString(2); // since name is  in position 2 ie 3rd coloumn   
            return nickname;
        }
        return null;
    }
// ==================== edit ===================
   public void updateentry(long smodify, String namestr, String nickstr) {
        // TODO Auto-generated method stub
        ContentValues cvupdate = new ContentValues();   // Bundle for writting the database fields   
        cvupdate.put(KEY_NAME, namestr); // put the values passed to ContentValues
        cvupdate.put(KEY_NICKNAME, nickstr);  // put the values passed to ContentValues     
        db.update(DATABASE_TABLE, cvupdate, KEY_ROWID + "=" + smodify, null);// specify where to be changed
    }

// ==================== delete ===================
    public void deleteentry(long ldelete) {
        // TODO Auto-generated method stub
        db.delete(DATABASE_TABLE, KEY_ROWID + "="  + ldelete, null);                          
    }  }

SQliteexample.java

public class SQliteExample extends Activity
   {
     Button butupdate,butview,butgetinfo,butedit,butdelete;
     EditText editName,editNickName,editinfo;
   
    @Override
    public void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_main);
       
      butupdate=(Button) findViewById(R.id.button1);
      butview=(Button) findViewById(R.id.button2);    
      butgetinfo=(Button) findViewById(R.id.button3);    
      butedit=(Button) findViewById(R.id.button4);    
      butdelete=(Button) findViewById(R.id.button5);    
      editName=(EditText) findViewById(R.id.editText1);
      editNickName=(EditText) findViewById(R.id.editText2);
      editinfo=(EditText) findViewById(R.id.editText3);
    
  butupdate.setOnClickListener(new OnClickListener() { 
        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            boolean diditwork=true;
            try {
            String Namestr = editName.getText().toString();
            String Nickstr = editNickName.getText().toString();
           
//==Create an object of adapter class to call methods==   
            DBAdapter Entryadapter = new DBAdapter(SQliteExample.this);
            Entryadapter.open();
            Entryadapter.entryfield(Namestr,Nickstr); //Calling the function in DBAdapter class                                   
            Entryadapter.close(); 
            }
              //Exception handling
            catch (Exception e) {
                diditwork=false;
                String error=e.toString();
                Dialog dialog=new Dialog(SQliteExample.this);
                dialog.setTitle("Exception !!!");
                TextView tv= new TextView(SQliteExample.this);
                tv.setText(error);
                dialog.setContentView(tv);
                dialog.show();
            }  
            finally
            {
                if(diditwork)
                {
                    Dialog dialog=new Dialog(SQliteExample.this);
                    dialog.setTitle("Created !!!");
                    TextView tv= new TextView(SQliteExample.this);
                    tv.setText("Sucess");
                    dialog.setContentView(tv);
                    dialog.show();
                }
            }
        }
    });
  butview.setOnClickListener(new OnClickListener() {
   @Override
       public void onClick(View v) {
          Intent intent =new Intent(SQliteExample.this,SqlView.class);
          startActivity(intent);
             
          }
      });
     
 butgetinfo.setOnClickListener(new OnClickListener() {
  @Override
       public void onClick(View v) {
          String s =editinfo.getText().toString();
          long l=Long.parseLong(s);
          DBAdapter adapter=new DBAdapter(SQliteExample.this);
          adapter.open();
          String returnedname=adapter.returnName(l);
          String returnednickname=adapter.returnickname(l);
          adapter.close();
          editName.setText(returnedname);
          editNickName.setText(returnednickname);
            }
        });
 butedit.setOnClickListener(new OnClickListener() {
    @Override
        public void onClick(View v) {
          String Namestr = editName.getText().toString();
          String Nickstr = editNickName.getText().toString();
          String smodify =editinfo.getText().toString();
          long lmodify =Long.parseLong(smodify);
          DBAdapter adaptmodify =new DBAdapter(SQliteExample.this);
          adaptmodify.open();
          adaptmodify.updateentry(lmodify,Namestr,Nickstr);
          adaptmodify.close();
           }
        });
 butdelete.setOnClickListener(new OnClickListener() {
     @Override
       public void onClick(View v) {
         String sdelete =editinfo.getText().toString();
         long ldelete =Long.parseLong(sdelete);
         DBAdapter adaptdelete =new DBAdapter(SQliteExample.this);
         adaptdelete.open();
         adaptdelete.deleteentry(ldelete);
         adaptdelete.close();
            }
        });
    }   }

SqliView.java

public class SqlView extends Activity {
  @Override
    public void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.sqlview);
      TextView infotext=(TextView)findViewById(R.id.tvsqlinfo);   
      DBAdapter info = new DBAdapter(this);
      info.open();
      String data =info.getdata();
      System.out.println("result is ==========>>" + data);
      info.close();
      infotext.setText(data);
      infotext.setTextColor(Color.RED);  
     } }