Populating Sqlite Database

I am not very much familiar with android sqlite database. I only have rough idea of populating sqlite database. I have followed some tutorials but they tells different things. I ha

Solution 1:

From my development experience , I always prefer to add a prepared sqlite database file in the /res/raw folder.You create/manage sqlite database using Sqlite Manager addon of Firefox , it's a great tool. This method is really great because

• firstly I don't need to write a bunch of codes for creating/managing database.
• Most importantly , some applications needs to read from a pre-populated database. I don't need to care about what the app requires and whether database is empty or filled already. It serves all purpose. I just need to write some methods that runs the required simple sqls.

Here is my own customised DatabaseHelper class. To use this class you'll need to follow some instructions.

1. If sqlite database size is more than 1MB then split the file into chunks , I prefer 512KB chunks and place them into /res/raw directory.

2. Edit the package name and your db file names in the following class.

   package your.packagee.name;
   
   import java.io.FileOutputStream;
   import java.io.IOException;
   import java.io.InputStream;
   import java.io.OutputStream;
   
   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.util.Log;
   import android.widget.Toast;
   
   publicclassDataBaseHelperextendsSQLiteOpenHelper {
   
   privatestaticfinalStringpkg="your package name";
   privatestaticStringDB_PATH="/data/data/" + pkg + "/databases/";
   
   privatestaticStringDB_NAME="yourDBFile.sqlite";
   int[] dbfiles = { R.raw.chunk1 , R.raw.chunk2 ..... };
   
    private SQLiteDatabase myDataBase;
   privatefinal Context myContext;
   
   publicDataBaseHelper(Context context) {
       super(context, DB_NAME, null, 1);
       this.myContext = context;
   }
   
   publicvoidcreateDataBase() {
   
       booleandbExist= checkDataBase();
   
       if (dbExist) {
           // do nothing - database already exist
       } else {
           this.getReadableDatabase();
           try {
               CopyDataBase();
           } catch (IOException e) {
               Toast.makeText(myContext, e.getMessage(), Toast.LENGTH_SHORT)
                       .show();
               Log.d("Create DB", e.getMessage());
           }
       }
   
   }
   
   privatebooleancheckDataBase() {
       SQLiteDatabasecheckDB=null;
       try {
           StringmyPath= DB_PATH + DB_NAME;
           checkDB = SQLiteDatabase.openDatabase(myPath, null,
                   SQLiteDatabase.NO_LOCALIZED_COLLATORS);
       } catch (SQLiteException e) {
           Toast.makeText(myContext, e.getMessage(), Toast.LENGTH_SHORT)
                   .show();
           Log.d("Check DB", e.getMessage());
       }
   
       if (checkDB != null) {
           checkDB.close();
       }
       return checkDB != null ? true : false;
   }
   
   privatevoidCopyDataBase()throws IOException {
       InputStreamdatabaseInput=null;
       Resourcesresources= myContext.getResources();
       StringoutFileName= DB_PATH + DB_NAME;
   
       OutputStreamdatabaseOutput=newFileOutputStream(outFileName);
   
       byte[] buffer = newbyte[512];
       int length;
   
       for (inti=0; i < dbfiles.length; i++) {
           databaseInput = resources.openRawResource(dbfiles[i]);
           while ((length = databaseInput.read(buffer)) > 0) {
               databaseOutput.write(buffer, 0, length);
               databaseOutput.flush();
           }
           databaseInput.close();
       }
   
       databaseOutput.flush();
       databaseOutput.close();
   }
   
   publicvoidopenDataBase()throws SQLException {
       StringmyPath= DB_PATH + DB_NAME;
       myDataBase = SQLiteDatabase.openDatabase(myPath, null,
               SQLiteDatabase.NO_LOCALIZED_COLLATORS);
   }
   
   @Overridepublicsynchronizedvoidclose() {
       if (myDataBase != null)
           myDataBase.close();
       super.close();
   }
   
   @OverridepublicvoidonCreate(SQLiteDatabase db) {
   
   }
   
   @OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   
   }
   
   publicbooleandeleteItem(String ID){
   
       Stringquery="delete from item where id='" + ID + "'"  ;
       Log.d("Query : ", query);
       try{
           myDataBase.execSQL(query);
           returntrue ;
       } catch (Exception e){
           Log.d("Exception", e.toString());
           returnfalse ;
       }
   }
   
   public Cursor getSearchFromID(String id) {
       return myDataBase.rawQuery("select * from item where id = \"" + id + "\"", null);
   }
   
   publicbooleanaddSave(String type, String data , String time) {
   
       Stringquery="insert into item (type, data , timestamp) values ('" + type
       + "', '" + data + "', '" + time + "')"; 
   try {
      myDataBase.execSQL(query);
      returntrue ;
   } catch (Exception e) {
      returnfalse ;
   }
   }
   }
   

Here's some methods written as a sample , how to use it.

Usage is simple. When your application starts , that means in your Launcher activity use this code to initialize your database

DataBaseHelperhelper=newDataBaseHelper(this);
helper.createDataBase();
helper.openDataBase();
helper.close();

Then just use the methods written in DatabaseHelper class. A sample will be like this

Stringid="1";
    DataBaseHelperhelper=newDataBaseHelper(this);
    helper.openDataBase();
    Cursorc= helper.getSearchFromID(id);
    if(c.getCount() > 0){
        c.moveToFirst();

      while(!c.isAfterLast()){
        // extract your data from cursor
        c.MoveToNext();
      }
    }

Hope it will solve your all problems about sqlite database in Android. At least it solved for me. Thank you.

Solution 2:

There are various way in populating a database. What I do is I create an insert(ObjectType objectName) in the DBAdapter Class. That being said, I create an object class and for this example, I'm going to use Authorized Personnel

publicclassAuthorizedPersonnelClass{

  privateString _id;
  privateString Last_Name;
  privateString Middle_Name;
  privateString First_Name;
  privateString Store_ID;
  privateString Status;
  privateString New_Personnel;

      //of course insert your 2 constructors and getter setter methods here
}

In my DBAdapter, I'll create the insert(AuthorizedPersonnelClass authorizedPersonnel) method to handle the data insertions:

publiclongaddPersonnel(AuthorizedPersonnelClass authorizedPersonnel){
    ContentValuesvalues=newContentValues();

    values.put(AUTHORIZEDPERSONNEL_ID, authorizedPersonnel.get_id());
    values.put(L_NAME_AUTHORIZED_PERSONNEL, authorizedPersonnel.getLast_Name());
    values.put(M_NAME_AUTHORIZED_PERSONNEL, authorizedPersonnel.getMiddle_Name());
    values.put(F_NAME_AUTHORIZED_PERSONNEL, authorizedPersonnel.getFirst_Name());
    values.put(STATUS, authorizedPersonnel.getStatus());
    values.put(STORE_ID, authorizedPersonnel.getStore_ID());
    values.put(NEW, authorizedPersonnel.getNew_Personnel());

    returnthis.mDB.insert(TABLE_AUTHORIZED_PERSONNEL, null, values);
}

And from there, let's say I want to populate entries in my onCreate() function or in a button call, I'll just do as such:

//instantiate a global variable for the DBAdapterDBAdapterdb=newDBAdapter(this);

//then if you want to insert 
db.insert(newAuthorizedPersonnelClass( /*insert variables here*/ ));

Of course these values may be hard coded or user input (just use EditTexts and extract the Strings and use them there).

Here, I used the ContentValues example because it's easier for beginners to use as opposed to doing a rawQuery Insert statement which may get confusing.