Android’s built in persistence layer : SQLite

Late to the party — I just upgraded my Android Studio IDE today and enjoying the built in SQLite Inspector. So yeah, no need to use third party clients.

DBHandler

package com.example.sqliteapp;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.HashMap;

public class DbHandler extends SQLiteOpenHelper {

    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "itemsdb";
    private static final String TABLE_Items = "itemdetails";
    private static final String KEY_ID = "id";
    private static final String KEY_ITEM = "item";
    private static final String KEY_QUANTITY = "quantity";
    private static final String KEY_PRICE = "price";

    public DbHandler(Context context){
        super(context,DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db){
        String CREATE_TABLE = "CREATE TABLE " + TABLE_Items + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_ITEM + " TEXT,"
                + KEY_QUANTITY + " TEXT,"
                + KEY_PRICE + " TEXT"+ ")";
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_Items);
        onCreate(db);
    }

    void insertItemDetails(String item, String quantity, String price){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cValues = new ContentValues();
        cValues.put(KEY_ITEM, item);
        cValues.put(KEY_QUANTITY, quantity);
        cValues.put(KEY_PRICE, price);
        long newRowId = db.insert(TABLE_Items,null, cValues);
        db.close();
    }

    public ArrayList<HashMap<String, String>> GetItems(){
        SQLiteDatabase db = this.getWritableDatabase();
        ArrayList<HashMap<String, String>> itemList = new ArrayList<>();
        String query = "SELECT item, quantity, price FROM "+ TABLE_Items;
        Cursor cursor = db.rawQuery(query,null);
        while (cursor.moveToNext()){
            HashMap<String,String> item = new HashMap<>();
            item.put("item",cursor.getString(cursor.getColumnIndex(KEY_ITEM)));
            item.put("quantity",cursor.getString(cursor.getColumnIndex(KEY_PRICE)));
            item.put("price",cursor.getString(cursor.getColumnIndex(KEY_QUANTITY)));
            itemList.add(item);
        }
        return  itemList;
    }

    public ArrayList<HashMap<String, String>> GetItemrByItemId(int itemId){
        SQLiteDatabase db = this.getWritableDatabase();
        ArrayList<HashMap<String, String>> itemList = new ArrayList<>();
        String query = "SELECT item, quantity, price FROM "+ TABLE_Items;
        Cursor cursor = db.query(TABLE_Items, new String[]{KEY_ITEM, KEY_QUANTITY, KEY_PRICE}, KEY_ID+ "=?",new String[]{String.valueOf(itemId)},null, null, null, null);
        if (cursor.moveToNext()){
            HashMap<String,String> item = new HashMap<>();
            item.put("item",cursor.getString(cursor.getColumnIndex(KEY_ITEM)));
            item.put("quantity",cursor.getString(cursor.getColumnIndex(KEY_PRICE)));
            item.put("price",cursor.getString(cursor.getColumnIndex(KEY_QUANTITY)));
            itemList.add(item);
        }
        return  itemList;
    }

    public void DeleteItem(int itemId){
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_Items, KEY_ID+" = ?",new String[]{String.valueOf(itemId)});
        db.close();
    }

    public int UpdateItemDetails(String quantity, String price, int id){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cVals = new ContentValues();
        cVals.put(KEY_QUANTITY, quantity);
        cVals.put(KEY_PRICE, price);
        int count = db.update(TABLE_Items, cVals, KEY_ID+" = ?",new String[]{String.valueOf(id)});
        return  count;
    }

}

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical" android:layout_width="match_parent"
    android:layout_height="match_parent">
    <TextView
        android:id="@+id/fstTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:layout_marginTop="150dp"
        android:text="Item" />
    <EditText
        android:id="@+id/txtItem"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:ems="10"/>
    <TextView
        android:id="@+id/secTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Quantity"
        android:layout_marginLeft="100dp" />
    <EditText
        android:id="@+id/txtQuantity"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:ems="10" />
    <TextView
        android:id="@+id/thirdTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Price"
        android:layout_marginLeft="100dp" />
    <EditText
        android:id="@+id/txtPrice"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:ems="10" />
    <Button
        android:id="@+id/btnSave"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:text="Save" />
</LinearLayout>

details.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >
    <ListView
        android:id="@+id/itemList"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:dividerHeight="1dp" />
    <Button
        android:id="@+id/btnBack"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:layout_marginTop="20dp"
        android:text="Back" />
</LinearLayout>

list_row.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal"
    android:padding="5dip" >
    <TextView
        android:id="@+id/item"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textStyle="bold"
        android:textSize="17dp" />
    <TextView
        android:id="@+id/quantity"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@id/item"
        android:layout_marginTop="7dp"
        android:textColor="#343434"
        android:textSize="14dp" />
    <TextView
        android:id="@+id/price"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/quantity"
        android:layout_alignBottom="@+id/quantity"
        android:layout_alignParentRight="true"
        android:textColor="#343434"
        android:textSize="14dp" />
</RelativeLayout>

MainActivity

package com.example.sqliteapp;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.widget.EditText;
import android.widget.Button;
import android.view.View;
import android.widget.Toast;

import android.os.Bundle;

public class MainActivity extends AppCompatActivity {

    EditText name, loc, price;
    Button saveBtn;
    Intent intent;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        name = (EditText)findViewById(R.id.txtItem);
        loc = (EditText)findViewById(R.id.txtQuantity);
        price = (EditText)findViewById(R.id.txtPrice);
        saveBtn = (Button)findViewById(R.id.btnSave);
        saveBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String item = name.getText().toString()+"\n";
                String quantity = loc.getText().toString();
                String price = MainActivity.this.price.getText().toString();
                DbHandler dbHandler = new DbHandler(MainActivity.this);
                dbHandler.insertItemDetails(item,quantity,price);
                intent = new Intent(MainActivity.this,DetailsActivity.class);
                startActivity(intent);
                Toast.makeText(getApplicationContext(), "Item successfully stored",Toast.LENGTH_SHORT).show();
            }
        });
    }
}

DetailsActivity

package com.example.sqliteapp;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.view.View;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import java.util.ArrayList;
import java.util.HashMap;
import android.os.Bundle;

public class DetailsActivity extends AppCompatActivity {

    Intent intent;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.details);
        DbHandler db = new DbHandler(this);
        ArrayList<HashMap<String, String>> userList = db.GetItems();
        ListView lv = (ListView) findViewById(R.id.itemList);
        ListAdapter adapter = new SimpleAdapter(DetailsActivity.this, userList, R.layout.list_row,new String[]{"item","quantity","price"}, new int[]{R.id.item, R.id.quantity, R.id.price});
        lv.setAdapter(adapter);
        Button back = (Button)findViewById(R.id.btnBack);
        back.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                intent = new Intent(DetailsActivity.this,MainActivity.class);
                startActivity(intent);
            }
        });
    }
}

AndroidManifast

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.sqliteapp">

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="My Grocery App"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity android:name=".DetailsActivity" android:label="My Grocery List"></activity>
    </application>

</manifest>