ShopHandler.java

package com.seba.sqltutorial.Handlers;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.seba.sqltutorial.Models.Shop;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by seba on 04-06-16.
 */
public class ShopHandler extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = "shopsInfo";
    // Contacts table name
    private static final String TABLE_SHOPS = "shops";
    // Shops Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_SH_ADDR = "shop_address";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_SHOPS + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_SH_ADDR + " TEXT" + ")";
        db.execSQL(CREATE_CONTACTS_TABLE);
    }

    // Index shops
    public List<Shop> getShops() {
        List<Shop> shopList = new ArrayList<Shop>();
        String selectQuery = "select * from " + TABLE_SHOPS;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Shop shop = new Shop();
                shop.setId(Integer.parseInt(cursor.getString(0)));
                shop.setName(cursor.getString(1));
                shop.setAddress((cursor.getString(2)));

                shopList.add(shop);
            } while (cursor.moveToNext());
        }
        return shopList;
    }

    // Create shop
    public void addShop(Shop shop) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(KEY_NAME, shop.getName());
        values.put(KEY_SH_ADDR, shop.getAddress());

        // Insert row
        db.insert(TABLE_SHOPS, null, values);
        db.close();
    }

    // Show shop
    public Shop getShop(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] projection = {
                KEY_ID, KEY_NAME, KEY_SH_ADDR
        };
        String selection = KEY_ID + "=?";
        String[] selectionArgs = { String.valueOf(id)};
        Cursor cursor = db.query(TABLE_SHOPS, projection, selection, selectionArgs, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        Shop shop = new Shop(
                Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), cursor.getString(2));
        return shop;
    }

    // Count
    public int count() {
        String selectQuery = "select * from " + TABLE_SHOPS;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        cursor.close();
        return cursor.getCount();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SHOPS);
        // Create tables again
        onCreate(db);
    }
}