Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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 does not know the best way to do these though would love to learn how.

Here it is:

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();
share|improve this question
1  
Heck no, you don't need to be constantly creating/dropping the tables. For one, thing, that's terribly 'expensive'. Also, what happens when users come back to the app - they'd complain about missing data! There should be a way to do 'one time setup', either as part of the android install process, or simply the first time your app runs. –  Clockwork-Muse Jan 24 '13 at 22:38
    
@Clockwork-Muse: onCreate and onUpgrade are called by the Android framework only when necessary, so this code is not constantly creating/dropping tables. –  seand Nov 27 '13 at 18:08
add comment

1 Answer

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);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.