Populating SQLite Database

Question

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 have an android project and one of its' packeges is a .db This package consists of 5 different classes. They are:

1. DataAdapter.java
2. DataBaseHelper.java
3. DBAdapter.java
4. LoadDBActivity.java
5. SelectDBAdapter.java

I know SelectDBAdapter class is used to select data from the database. My database is in asset folder which is in .jpeg format.I can open it from sqliteBrowser. Actually, what I want to know is why should we use these different classes ? and what's the purpose of each and every class ?

I am really sorry, I cannot post codes since this projects belongs to another person (my friend). I would be much obliged if anyone could be so kind enough to explain the meaning of using these different classes and why should we use such a senario ?

Answer 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;
   
   public class DataBaseHelper extends SQLiteOpenHelper {
   
   private static final String pkg = "your package name";
   private static String DB_PATH = "/data/data/" + pkg + "/databases/";
   
   private static String DB_NAME = "yourDBFile.sqlite";
   int[] dbfiles = { R.raw.chunk1 , R.raw.chunk2 ..... };
   
    private SQLiteDatabase myDataBase;
   private final Context myContext;
   
   public DataBaseHelper(Context context) {
       super(context, DB_NAME, null, 1);
       this.myContext = context;
   }
   
   public void createDataBase() {
   
       boolean dbExist = 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());
           }
       }
   
   }
   
   private boolean checkDataBase() {
       SQLiteDatabase checkDB = null;
       try {
           String myPath = 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;
   }
   
   private void CopyDataBase() throws IOException {
       InputStream databaseInput = null;
       Resources resources = myContext.getResources();
       String outFileName = DB_PATH + DB_NAME;
   
       OutputStream databaseOutput = new FileOutputStream(outFileName);
   
       byte[] buffer = new byte[512];
       int length;
   
       for (int i = 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();
   }
   
   public void openDataBase() throws SQLException {
       String myPath = DB_PATH + DB_NAME;
       myDataBase = SQLiteDatabase.openDatabase(myPath, null,
               SQLiteDatabase.NO_LOCALIZED_COLLATORS);
   }
   
   @Override
   public synchronized void close() {
       if (myDataBase != null)
           myDataBase.close();
       super.close();
   }
   
   @Override
   public void onCreate(SQLiteDatabase db) {
   
   }
   
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   
   }
   
   public boolean deleteItem (String ID){
   
       String query = "delete from item where id='" + ID + "'"  ;
       Log.d("Query : ", query);
       try{
           myDataBase.execSQL(query);
           return true ;
       } catch (Exception e){
           Log.d("Exception", e.toString());
           return false ;
       }
   }
   
   public Cursor getSearchFromID(String id) {
       return myDataBase.rawQuery("select * from item where id = \"" + id + "\"", null);
   }
   
   public boolean addSave(String type, String data , String time) {
   
       String query = "insert into item (type, data , timestamp) values ('" + type
       + "', '" + data + "', '" + time + "')"; 
   try {
      myDataBase.execSQL(query);
      return true ;
   } catch (Exception e) {
      return false ;
   }
   }
   }
   

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

DataBaseHelper helper = new DataBaseHelper(this);
helper.createDataBase();
helper.openDataBase();
helper.close();

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

 String id = "1";
    DataBaseHelper helper = new DataBaseHelper(this);
    helper.openDataBase();
    Cursor c = 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.

Answer 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

public class AuthorizedPersonnelClass {

  private String _id;
  private String Last_Name;
  private String Middle_Name;
  private String First_Name;
  private String Store_ID;
  private String Status;
  private String 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:

public long addPersonnel(AuthorizedPersonnelClass authorizedPersonnel){
    ContentValues values = new ContentValues();

    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());

    return this.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 DBAdapter
DBAdapter db = new DBAdapter(this);

//then if you want to insert 
db.insert(new AuthorizedPersonnelClass( /*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.