4
\$\begingroup\$

I have been through a few SQLite tutorials and wrote this code on my own to reinforce the principles. The tutorials I went through varied widely in a few areas so this is what I came up with as a combination of everything.

Other than the two classes I have listed below, all I have is one activity that adds, remove, inserts, update and display data from the database.

I have a few specific questions:

  • When should I close the helper class or does garbage collection deal with it automatically?
  • Should I create a Boxer POJO (Plain Old Java Object) to pass boxer data to and from the DAO?
  • Is the DAO implementation efficient?
  • Does the code deviate from Java and Android best practices in any way?

Helper Class

public class BoxScoresHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "boxing_scores.db";
    private static final int VERSION = 1;
    private static BoxScoresHelper instance = null;

    public static BoxScoresHelper getInstance(Context context){

        if(instance == null){

            instance = new BoxScoresHelper(context);

        }

        return instance;
    }

    private BoxScoresHelper(Context context) {
        super(context, DB_NAME, null, VERSION);



    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(createBoxerSQLString());

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("Drop Table If Exists " + BoxerDAO.TABLE_NAME);
        onCreate(db);

    }

    private String createBoxerSQLString(){

        String boxerCreateString = "create table " + BoxerDAO.TABLE_NAME +
                "(" + BoxerDAO._ID + " Integer Primary Key AutoIncrement, " +
                BoxerDAO.BOXER_NAME +  " Text Not Null, " +
                BoxerDAO.WEIGHT_CLASS + " Text Not Null, " +
                BoxerDAO.WINS + " Integer Not Null, " +
                BoxerDAO.LOSSES + " Integer Not Null);";


        return boxerCreateString;
    }

}

DAO Class

public class BoxerDAO {

    public static final String TABLE_NAME = "Boxer";
    public static final String _ID  = "_id";
    public static final String BOXER_NAME = "boxer_name";
    public static final String WEIGHT_CLASS = "weight_class";
    public static final String WINS = "wins";
    public static final String LOSSES ="losses";

    private final BoxScoresHelper myScoresHelper;
    private SQLiteDatabase myBoxerDB;

    public BoxerDAO(Context context){

        myScoresHelper = BoxScoresHelper.getInstance(context);

    }

    public Cursor query(String[] projection,String selection,String[] selectionArgs, String orderBy){

        Cursor cursor;
        myBoxerDB = myScoresHelper.getReadableDatabase();

        cursor = myBoxerDB.query(TABLE_NAME, projection, selection, selectionArgs, null, null, orderBy);

        //myBoxerDB.close();

        return cursor;


    }

    public Cursor queryAll(){

        Cursor cursor;
        myBoxerDB = myScoresHelper.getReadableDatabase();

        cursor = myBoxerDB.rawQuery("Select * From " + TABLE_NAME, null);

        //myBoxerDB.close();
        return cursor;

    }

    public int delete(int id){

        int rowsDel;
        myBoxerDB = myScoresHelper.getWritableDatabase();

        rowsDel = myBoxerDB.delete(TABLE_NAME, _ID + " = " + id , null);

        //myBoxerDB.close();
        return rowsDel;

    }

    public long insert(ContentValues values){

        long insertId = -1;

        myBoxerDB = myScoresHelper.getWritableDatabase();

        insertId = myBoxerDB.insert(TABLE_NAME, null, values);

        //myBoxerDB.close();

        return insertId;

    }

    public int update(ContentValues values,String selection, String[] selectionArgs){

        int updatedRows;
        myBoxerDB = myScoresHelper.getWritableDatabase();

        updatedRows = myBoxerDB.update(TABLE_NAME, values, _ID + " = " + selection, selectionArgs);
        //myBoxerDB.close();

        return updatedRows;

    }   

}
\$\endgroup\$
1
  • \$\begingroup\$ I would suggest using a ORM system for this. Why write insert query while you can call save method on an object instead. Examples are DBflow, ActiveAndroid, .. \$\endgroup\$ Commented Jul 11, 2015 at 13:03

2 Answers 2

2
\$\begingroup\$

Convert member field to local variable when you can

In BoxerDAO, the myBoxerDB member can be converted to a local variable. Each utility method gets an SQLiteDatabase instance from the helper. The class needs only an SQLiteOpenHelper as a member field.

Refer to types by interfaces

In BoxerDAO, myScoresHelper can be declared as an instance of SQLiteOpenHelper. It doesn't use any features of BoxScoresHelper.

Class design

BoxerDAO is only aware of Context to get an instance of BoxScoresHelper. It would be better if it wasn't aware of Context at all. You could pass to the constructor an SQLiteOpenHelper instead.

Naming

Prefixing member fields with "my" is not a common convention. You could simply drop that.

Coding style

There are too many blank lines in the code. This is unnecessary, it would be good to trim the code a little.

\$\endgroup\$
0
0
\$\begingroup\$

This suggestion is a total turn from your approach, so I'm sorry if that's unhelpful or not an option for you right now.

You could use a JDBC driver that talks to SQLite. One of the more popular ones seems to be xerial (I haven't used it). Using JDBC would make it easier to transfer your program to another SQL database in the future. Plus, there is a lot more knowledge of interacting with databases using JDBC than with the SQLite-specific API, so it would be easier to get general JDBC help regarding how to architect and design your application, and there are tools for talking to JDBC databases or doing Object Relational Mapping (ORM).

Depending on the rest of your application, I would say that your data access layer would do well to return data objects.

\$\endgroup\$
1
  • \$\begingroup\$ Thanks for the input. I am working on home projects just to solidify my understanding of sqlite in android so the jdbc isn't applicable. I will make modifications to return data objects though. \$\endgroup\$
    – abujafar
    Commented Jul 6, 2015 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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