Working with Android SQLite databases

Question

I'm working with a SQLite database in an android application. But I'm not very much familiar with SQLite. Also I'm confused when I'm going through some tutorials as there structure is somewhat differ from the Applications one in which now I'm working on.

In some tutorials, It's mentioned that CRUD operations are implement in the class called DatabaseHelper and the DB part contains only this class. It extends SQLiteOpenHelper class.

But in my application there are five classes and they are,

• DataAdapter
• DataBaseHelper
• DBAdapter
• LoadDBActivity
• SelectDBAdapter

In there except LoadDBActivity all the others extend SQLiteOpenHelper class. And LoadDBActivity extend Activity.

So i'm very much confused at this moment on these two approaches. Is this a some standerd structure or something else. I'm very much greatful if some one give me healping hand in this matter.

in my project the DataAdapter class is starting like this.

public class DataAdapter extends SQLiteOpenHelper {
private static String DB_PATH = "/data/data/aaa.v4.controller/databases/";
private static final String DB_NAME = "aaa.db";
private SQLiteDatabase myDataBase;
private final Context myContext;
private static DataAdapter mDBConnection;
private DataAdapter(Context context) {
 super(context, DB_NAME, null, 1);
 this.myContext = context;
 DB_PATH = "/data/data/"+ context.getApplicationContext().getPackageName()+ "/databases/";
}

This will be helpful in helping me. Thank you!!!!!

• user2058839: With the code of those classes, it will be easier to answer you as there are 3 differents adapter, and we may not know which one is for what.
• Samantha Withanage: In this case it's not possible to post the code as it's not belongs to me. Any way can you give me a rough idea about the structure in populating a SQLite database. Thank You!

Answer 1

I've used SQLite DB in different applications and my approach is:

I create a DB using a Mozilla extension SQLite Manager, saving the .sqlite in the application asset folder. Then i write the class DatabaseHelper

public class DataBaseHelper extends SQLiteOpenHelper{
private static String DB_PATH = "/data/data/your.package.name/databases/";
private static String DB_NAME = "/*name of your sqlite file, ex Contacts.sqlite*/";
private SQLiteDatabase LuckyDB;//name of your DB
private final Context myContext;
 /**
 * 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, DB_NAME, null, 1);
 this.myContext = 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{
 this.getReadableDatabase(); 
 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 + DB_NAME;
 checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
 }catch(SQLiteException e){ 
 }
 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 = myContext.getAssets().open(DB_NAME); 
 // Path to the just created empty db
 String outFileName = DB_PATH + DB_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[1024];
 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 + DB_NAME;
 LuckyDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
 }
 @Override
 public synchronized void close() {
 if(LuckyDB != null)
 LuckyDB.close();
 super.close();
 }
 @Override
 public void onCreate(SQLiteDatabase db) {
 }
 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 }
 /** Crea (se necessario) il database e restituisce l'oggetto database **/
 public SQLiteDatabase getDb()
 {
 try {
 this.createDataBase();
 } catch (IOException ioe) {
 throw new Error("Unable to create database");
 }
 try {
 this.openDataBase();
 }catch(SQLException sqle){ 
 throw sqle;
 }
 return LuckyDB;
 } 
}

When you need to use it, you can do so:

DataBaseHelper myDbHelper = new DataBaseHelper(getApplicationContext());
 SQLiteDatabase db = myDbHelper.getDb();

You should use Cursor,rawQuery and while loop to get, add or delete datas from your DB. Hope it helps ya!

• Samantha Withanage: Thanks a lot. I also using SQLite Browser for just viewing the db. also test the Queries. Anyway thanks a lot for the help given.Also is it ok to use only one SQLiteOpenHelper to perform CRUD operations.

Answer 2

In the list you gave, it seems (cause the name is free) that you have 3 adapters, one DatabaseHelper, and one activity.

• Activity is a Controller in the MVC pattern. It will deal with your view to display the view regarding your datas
• The DatabaseHelper is, as it is its name, helping you opening databases. It will manage for you the version of your database, and will create and/or updates it before opening if it is needed.
• The adapter is a class to manage your listview. It will contain methods to define how to display a view, know how much view there is in the listview, ...

In a concrete case, for example, displaying a list of books :

• The activity will set the adapter to the listview
• The database helper will open the databases, and creates and/or updates tables and datas if needed
• The adapter is the class in which you will get a book for a specific row, and set the datas of the book to each item, ...

• Samantha Withanage: OK. what's the difference between the Adapters and DatabaseHelpers since both the classes inherits from SQLiteOpenHelper.
• user2058839: An adapter must better herit from BaseAdapter rather than SQLiteOpenHelper. But without the code, I cannot tell you which class is for what.