DbAdapter.java

package pawitp.apartmentmanager;

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;

public class DbAdapter {

    private static class TenantColumns {
        public final static String _TABLE = "tenants";
        
        public final static String ROWID = "_id";
        public final static String TITLE = "title";
        public final static String NAME = "name";
        public final static String ADDRESS = "address";
        public final static String CARD_NO = "card_no";
        public final static String CARD_EXPIRE_DATE = "card_expire_date";
        public final static String PHONE = "phone";
        
        public final static String _CREATE =
            "create table " + _TABLE + " ("
            + ROWID + " integer primary key autoincrement, "
            + TITLE + " text not null, "
            + NAME + " text not null, "
            + ADDRESS + " text not null, "
            + CARD_NO + " text not null,"
            + CARD_EXPIRE_DATE + " string not null,"
            + PHONE + " text not null );";
        
        public final static ContentValues toContentValues(String title, String name, String address, String cardNo, String cardExpireDate, String phone) {
            ContentValues args = new ContentValues();
            args.put(TITLE, title);
            args.put(NAME, name);
            args.put(ADDRESS, cardNo);
            args.put(CARD_NO, cardNo);
            args.put(CARD_EXPIRE_DATE, cardExpireDate);
            args.put(PHONE, phone);
            return args;
        }
    }
    
    public static final int ID_NEW = -1;

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private static final String DATABASE_NAME = "data.db";
    private static final int DATABASE_VERSION = 1;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

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

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // No upgrade yet
        }
    }

    public DbAdapter(Context ctx) {
        mCtx = ctx;
    }

    public DbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

    public long createTenant(int id, String title, String name, String address, String cardNo, String cardExpireDate, String phone) {
        ContentValues args = TenantColumns.toContentValues(title, name, address, cardNo, cardExpireDate, phone);
        return mDb.insert(TenantColumns._TABLE, null, args);
    }
    
    public boolean updateTenant(int id, String title, String name, String address, String cardNo, String cardExpireDate, String phone) {
        ContentValues args = TenantColumns.toContentValues(title, name, address, cardNo, cardExpireDate, phone);
        return mDb.update(TenantColumns._TABLE, args, TenantColumns.ROWID + "=" + id, null) > 0;
    }

    public Cursor fetchTenant(int id) {
        Cursor c = mDb.query(TenantColumns._TABLE, new String[] {TenantColumns.ROWID, TenantColumns.TITLE, TenantColumns.NAME, TenantColumns.ADDRESS, TenantColumns.CARD_NO, TenantColumns.CARD_EXPIRE_DATE, TenantColumns.PHONE},
                TenantColumns.ROWID + "=" + id, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    public Cursor fetchTenants() {
        return mDb.query(TenantColumns._TABLE, new String[] {TenantColumns.ROWID, TenantColumns.TITLE, TenantColumns.NAME, TenantColumns.ADDRESS, TenantColumns.CARD_NO, TenantColumns.CARD_EXPIRE_DATE, TenantColumns.PHONE},
                null, null, null, null, null /* TODO: Order */);
    }

}