SQLite class to manage recipes for an Android application

Question

I have a class file that would be used to connect and execute queries into the database. The only thing I am confused about is this: do you really need to drop tables and re-create them everytime you run the application? Or does this depend on your coding or class file? I followed a tutorial on the Internet and kind of recycled the class file it provided to cope with my needs.

Do give me suggestions to better my class file if needed. I am a bit new to Java/Android and do not know the best way to do these, though I would love to learn how.

package com.thesis.menubook;

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;
import android.util.Log;

public class DBConnect {

        int id = 0;
        public static final String KEY_ROWID = "_id";
        public static final String KEY_IP = "saved_ip_address";
        private static final String TAG = "DBConnect";

        private static final String DATABASE_NAME = "MenuBook";
        private static final String DATABASE_TABLE_1 = "ipaddress";
        private static final String DATABASE_TABLE_2 = "menudb";
        private static final String DATABASE_TABLE_3 = "recipelist";
        private static final int DATABASE_VERSION = 1;

        private static final String DATABASE_CREATE =
            "CREATE TABLE ipaddress (_id integer primary key autoincrement, " +
                                 "saved_ip_address text not null " +
                                 "); "+
            "CREATE TABLE menudb (menu_ID varchar(255) NOT NULL, " +
                                 "menu_name longtext, " +
                                 "menu_price double DEFAULT NULL, " +
                                 "menu_description longtext, " +
                                 "menu_category text, " +
                                 "menu_status text, " +
                                 "PRIMARY KEY (menu_ID) " +
                                 "); "+
            "CREATE TABLE recipelist (recipe_ID int(11) NOT NULL AUTOINCREMENT, " +
                                 "menu_ID varchar(255) DEFAULT NULL, " +
                                 "stock_ID varchar(255) DEFAULT NULL, " +
                                 "recipe_quantity double DEFAULT NULL, " +
                                 "PRIMARY KEY (recipe_ID) " +
                                 ");" ;

        private final Context context;

        private static DatabaseHelper DBHelper;
        private static SQLiteDatabase db;

        public DBConnect(Context ctx)
        {
            this.context = ctx;
            DBHelper = new DatabaseHelper(context);
        }

        public static class DatabaseHelper extends SQLiteOpenHelper
        {
            DatabaseHelper(Context context)
            {
                super(context, DATABASE_NAME, null, DATABASE_VERSION);
            }

            @Override
            public void onCreate(SQLiteDatabase db)
            {
                db.execSQL(DATABASE_CREATE);
            }

            @Override
            public void onUpgrade(SQLiteDatabase db, int oldVersion,
                                  int newVersion)
            {
                Log.w(TAG, "Upgrading database from version " + oldVersion
                      + " to "
                      + newVersion + ", which will destroy all old data");
                db.execSQL("DROP TABLE IF EXISTS ipaddress");
                db.execSQL("DROP TABLE IF EXISTS menudb");
                db.execSQL("DROP TABLE IF EXISTS recipelist");
                onCreate(db);
            }

        }

        //---opens the database---
        public DBConnect open() throws SQLException
        {
            db = DBHelper.getWritableDatabase();
            return this;
        }

        //---closes the database---
        public void close()
        {
            DBHelper.close();
        }

        //---insert a title into the database---
        public long insertIPAddress(String ipaddress)
        {
            ContentValues initialValues = new ContentValues();
            initialValues.put(KEY_IP, ipaddress);
            return db.insert(DATABASE_TABLE_1, null, initialValues);
        }


}

I haven't finished this class file yet since I am still looking for more codes that I could use thus there are only 3 methods available here as of now. I am using it on an onClick event on one activity like this.

ipaddress = (EditText) findViewById(R.id.ipAddress);
ip = ipaddress.getText().toString();
db.open();
db.insertIPAddress(ip);
db.close();

Answer 1

If you're using one instance of SQLiteOpenHelper across your application (which you should be doing), you should never need to call close on it or the SQLiteDatabase object.

Why do you need the DBConnect class? See this answer for another question for a proper thread-safe SQLiteOpenHelper subclass. If you follow that example, this would be its usage:

DatabaseHelper dbHelper = DatabaseHelper.getInstance(getActivity());
dbHelper.insertIPAddress(ipAddr);