Thursday, 2 May 2013

Database in android

In Android Sqlite database is used for storing data. There is two way to use database in Application.
1. Create database.
2. Used existing database.

here is below code for Used existing database in Application.
Put your database file in "assets" folder. so we can create copy of that db file into storage card of device.

Call Methods:
Below code is for create database.
DBhelperdb = new DBhelper(getApplicationContext());
       try
       {
              db.createDataBase();
       } catch (IOException e)
       {  
              e.printStackTrace();
       }

This is helper class which contain all Method of database and in this class i have put some dummy method for How its used in Application.
Database Helper Class

             public class DBhelper extends SQLiteOpenHelper
             {
              private static String DATABASE_NAME = "HB.sqlitedb";
              private SQLiteDatabase myDataBase;
              private Context myContext;

              private String path = "/data/data/com.hb/databases/";

              public DBhelper(Context context)
              {

                     super(context, DATABASE_NAMEnull, 1);
                     this.myContext = context;
              }

              // ---Create the database---
              public void createDataBase() throws IOException
              {

                     // ---Check whether database is already created or not---
                     boolean dbExist = checkDataBase();

                     if (!dbExist)
                     {
                           this.getReadableDatabase();
                           try
                           {
                                  // ---If not created then copy the database---
                                  copyDataBase();
                           } catch (IOException e)
                           {
                                  throw new Error("Error copying database");
                           }
                           this.close();
                     }

              }

              // --- Check whether database already created or not---
              private boolean checkDataBase()
              {
                     try
                     {
                           String myPath = path + DATABASE_NAME;
                           File f = new File(myPath);
                           if (f.exists())
                                  return true;
                           else
                                  return false;
                     } catch (SQLiteException e)
                     {
                           e.printStackTrace();
                           return false;
                     }

              }

              // --- Copy the database to the output stream---
              private void copyDataBase() throws IOException
              {

                     InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

                     String outFileName = path + DATABASE_NAME;

                     OutputStream myOutput = new FileOutputStream(outFileName);

                     byte[] buffer = new byte[1024];
                     int length;
                     while ((length = myInput.read(buffer)) > 0)
                     {
                           myOutput.write(buffer, 0, length);
                     }

                     myOutput.flush();
                     myOutput.close();
                     myInput.close();

              }

              public void openDataBase() throws SQLException
              {

                     // --- Open the database---
                     String myPath = path + DATABASE_NAME;

                     myDataBase = SQLiteDatabase.openDatabase(myPath, null,SQLiteDatabase.OPEN_READWRITE);
                     myDataBase.setLockingEnabled(false);
              }

              @Override
              public synchronized void close()
              {

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

                     super.close();

              }

              @Override
              public void onCreate(SQLiteDatabase arg0)
              {

              }

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

              }

              public void update_delete_insertquery(String s)
              {
                     myDataBase.execSQL(s);
              }

              public Cursor selectquery(String s)
              {
                     return myDataBase.rawQuery(s, null);
              }
  

              public int getTotalQue()
              {
                     int count = 0;
                     openDataBase();
                     Cursor c = myDataBase.rawQuery("SELECT Count(*) FROM Questions"null);
                     if (c != null)
                     {
                           if (c.moveToFirst())
                           {
                                  count = c.getInt(0);
                           }
                     }
                     c.close();
                     myDataBase.close();
                     SQLiteDatabase.releaseMemory();
                     return count;
              }



              public int getusermasterID()
              {
                     int temp = 0;
                     openDataBase();
                     Cursor c = myDataBase.rawQuery("select MAX(question_id) from Questions"null);
                     if (c != null)
                     {
                           if (c.moveToFirst())
                           {
                                  temp = c.getInt(0);
                           }
                     }
                     c.close();
                     myDataBase.close();
                     SQLiteDatabase.releaseMemory();
                     return temp;
              }


              public void Insert_Data(Question Que)
              {
                     int id = getusermasterID();
                     openDataBase();
                     ContentValues initialValues = new ContentValues(); 
                     initialValues.put("question_id", id + 1);
                     initialValues.put("songtext", Que.songtext);
                     initialValues.put("singertext", Que.singertext);
                     initialValues.put("questiontext", Que.questiontext);
                     initialValues.put("answertext", Que.answertext); 
                     initialValues.put("category", Que.category);
                     initialValues.put("isAsked", 0); 
                     myDataBase.insert("Questions"null, initialValues);
                     myDataBase.close();
                     Log.i("QUESTIONID========""" + id);

              }

              public Question GET_QUESTIONS(int catid)
              {
                     Question que= new Question();
                     openDataBase();
                     //Cursor c = myDataBase.rawQuery("SELECT * FROM Questions where category =" + catid +" LIMIT 1", null);
                     Cursor c = myDataBase.rawQuery("SELECT * FROM Questions WHERE isAsked = 0 AND category ="+catid +" ORDER BY RANDOM() LIMIT 1"null);
                     if (c != null)
                     {
                           if (c.moveToFirst())
                           {  
                                  que.questionid = c.getInt(0);
                                  que.songtext = c.getString(1);
                                  que.singertext = c.getString(2);
                                  que.questiontext = c.getString(3);
                                  que.answertext = c.getString(4);
                                  que.category = c.getInt(5);
                                  updatequestion(que.questionid);   
                           }
                     }
                     c.close();  
                     c = myDataBase.rawQuery("SELECT Count(*) FROM Questions WHERE isAsked = 0 AND category ="+ catid +" "null);
                     if (c != null)
                     {
                           if (c.moveToFirst())
                           {
                                  int count=c.getInt(0);
                                  if(count<=1)
                                  {
                                         updatequestionasked(catid);
                                  }   
                           }
                     }

                     c.close();
                     myDataBase.close();
                     SQLiteDatabase.releaseMemory();
                     return que;
              }
              public synchronized void updatequestionasked(int cat)
              {

                     ContentValues initialValues = new ContentValues();
                     initialValues.put("isAsked", 0);
                     myDataBase.update("Questions", initialValues, "category ="+cat,null);

              }

              public synchronized void updatequestion(int id)
              {


                     ContentValues initialValues = new ContentValues();
                     initialValues.put("isAsked", 1);
                     myDataBase.update("Questions", initialValues, "question_id=" + id,null);


              }

              public void deleteQuestion()
              {
                     openDataBase();
                     myDataBase.delete("Questions"nullnull);
                     myDataBase.close();
                     SQLiteDatabase.releaseMemory();
              }

       }

Call Method of Helper Class:

private void GetData()
       {
              Question que=new Question();
              que=db.GET_QUESTIONS(1);
             
              songtext=que.songtext;
              singertext=que.singertext;
              quetext=que.questiontext;
              anstext=que.answertext;
        }

2. This is the second way to create database and used in our application.


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

public class DatabaseHandler extends SQLiteOpenHelper {

       private static final String DATABASE_NAME = "Registration";
       private static final int DATABASE_VERSION = 1;
       private static final String DATABASE_TABLE = "Reg";

       private static final String KEY_ID = "id";
       private static final String KEY_NAME = "name";
       private static final String KEY_PASSWORD = "password";
       private static final String KEY_GENDER = "gender";
       private static final String KEY_CONTACTNO = "contact";

       public DatabaseHandler(Context context) {
              super(context, DATABASE_NAMEnullDATABASE_VERSION);
       }

       @Override
       public void onCreate(SQLiteDatabase db) {
              String CREATE_TABLE = "CREATE TABLE " + DATABASE_TABLE + "(" + KEY_ID
                           + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_PASSWORD
                           + " TEXT," + KEY_GENDER + " TEXT," + KEY_CONTACTNO + " TEXT"
                           + ")";
              db.execSQL(CREATE_TABLE);
       }

       @Override
       public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
              db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
              onCreate(db);
       }

       public void Register(String name, String password, String gender,
                     String contact) {

              SQLiteDatabase db = this.getWritableDatabase();

              ContentValues cv = new ContentValues();
              cv.put(KEY_NAME, name);
              cv.put(KEY_PASSWORD, password);
              cv.put(KEY_GENDER, gender);
              cv.put(KEY_CONTACTNO, contact);

              db.insert(DATABASE_TABLEnull, cv);
              db.close();
       }

       public boolean isUserExists(String name, String password, String id) {

              boolean isExists = false;

              SQLiteDatabase db = this.getWritableDatabase();

              String strFilter = KEY_NAME + " ='" + name + "' and " + KEY_PASSWORD
                           + " ='" + password + "' and " + KEY_ID + " <>'" + id + "'";

              Cursor mCursor = db.query(DATABASE_TABLEnull, strFilter, nullnull,
                           nullnull);

              if (mCursor != null && mCursor.getCount() > 0) {
                     isExists = true;
              }
              if (mCursor != null) {
                     mCursor.close();
              }
              db.close();
              return isExists;
       }
}


More Ref :
  http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/
  http://matrix-examplecode.blogspot.in/2011/08/sqlite-external-database-connectivity.html