Guide on Database in Android Studio

Ankit Mishra — Wed, 16 Apr 2025 08:14:37 +0000

MainActivity.java
package com.example.restaurantapp;

import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.*;
import androidx.appcompat.app.AppCompatActivity;

import java.util.ArrayList;
import java.util.HashMap;

public class MainActivity extends AppCompatActivity {

Spinner categorySpinner, itemSpinner;
TextView priceTextView;
Button addButton, checkoutButton;
int totalCost = 0;

DatabaseHelper dbHelper;
ArrayList<String> categoryList = new ArrayList<>();
HashMap<String, Integer> categoryMap = new HashMap<>();

ArrayList<String> itemList = new ArrayList<>();
HashMap<String, Integer> itemPriceMap = new HashMap<>();

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

    dbHelper = new DatabaseHelper(this);

    categorySpinner = findViewById(R.id.categorySpinner);
    itemSpinner = findViewById(R.id.itemSpinner);
    priceTextView = findViewById(R.id.priceTextView);
    addButton = findViewById(R.id.addButton);
    checkoutButton = findViewById(R.id.checkoutButton);

    loadCategories();

    categorySpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
        @Override public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
            if (position > 0) {
                String category = categoryList.get(position);
                loadItems(categoryMap.get(category));
            }
        }
        @Override public void onNothingSelected(AdapterView<?> parent) {}
    });

    itemSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
        @Override public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
            if (position >= 0 && position < itemList.size()) {
                String item = itemList.get(position);
                int price = itemPriceMap.get(item);
                priceTextView.setText("Price: $" + price);
            }
        }
        @Override public void onNothingSelected(AdapterView<?> parent) {}
    });

    addButton.setOnClickListener(v -> {
        String item = (String) itemSpinner.getSelectedItem();
        if (item != null) {
            totalCost += itemPriceMap.get(item);
            Toast.makeText(MainActivity.this, "Item added! Total: $" + totalCost, Toast.LENGTH_SHORT).show();
        }
    });

    checkoutButton.setOnClickListener(v -> {
        Intent intent = new Intent(MainActivity.this, SummaryActivity.class);
        intent.putExtra("total", totalCost);
        startActivity(intent);
    });
}

private void loadCategories() {
    categoryList.clear();
    categoryList.add("Select Category");

    Cursor cursor = dbHelper.getCategories();
    while (cursor.moveToNext()) {
        int id = cursor.getInt(0);
        String name = cursor.getString(1);
        categoryList.add(name);
        categoryMap.put(name, id);
    }
    cursor.close();

    ArrayAdapter<String> adapter = new ArrayAdapter<>(this, android.R.layout.simple_spinner_item, categoryList);
    categorySpinner.setAdapter(adapter);
}

private void loadItems(int categoryId) {
    itemList.clear();
    itemPriceMap.clear();

    Cursor cursor = dbHelper.getItemsByCategoryId(categoryId);
    while (cursor.moveToNext()) {
        String name = cursor.getString(2);
        int price = cursor.getInt(3);
        itemList.add(name);
        itemPriceMap.put(name, price);
    }
    cursor.close();

    ArrayAdapter<String> adapter = new ArrayAdapter<>(this, android.R.layout.simple_spinner_item, itemList);
    itemSpinner.setAdapter(adapter);
}

private static class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DB_NAME = "restaurant.db";
    public static final int DB_VERSION = 1;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY KEY, name TEXT)");
        db.execSQL("CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, category_id INTEGER, name TEXT, price INTEGER)");

        db.execSQL("INSERT INTO categories (name) VALUES ('Drinks'), ('Main Course'), ('Dessert')");
        db.execSQL("INSERT INTO items (category_id, name, price) VALUES " +
                "(1, 'Coke', 2), (1, 'Water', 1), (1, 'Juice', 3), " +
                "(2, 'Burger', 5), (2, 'Pizza', 8), (2, 'Pasta', 7), " +
                "(3, 'Ice Cream', 4), (3, 'Cake', 5), (3, 'Pudding', 3)");
    }

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

    public Cursor getCategories() {
        return getReadableDatabase().rawQuery("SELECT * FROM categories", null);
    }

    public Cursor getItemsByCategoryId(int categoryId) {
        return getReadableDatabase().rawQuery("SELECT * FROM items WHERE category_id = ?", new String[]{String.valueOf(categoryId)});
    }
}

}

activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
android:layout_width="match_parent"
android:layout_height="match_parent">

<LinearLayout
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical"
    android:padding="24dp"
    android:gravity="center_horizontal">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Select Category"
        android:textSize="18sp"
        android:layout_marginBottom="8dp" />

    <Spinner
        android:id="@+id/categorySpinner"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="24dp"/>

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Select Item"
        android:textSize="18sp"
        android:layout_marginBottom="8dp" />

    <Spinner
        android:id="@+id/itemSpinner"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="24dp"/>

    <TextView
        android:id="@+id/priceTextView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Price: $0"
        android:textSize="20sp"
        android:textStyle="bold"
        android:layout_marginBottom="32dp"/>

    <Button
        android:id="@+id/addButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Add Item"
        android:layout_marginBottom="20dp"
        android:padding="12dp"/>

    <Button
        android:id="@+id/checkoutButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Checkout"
        android:padding="12dp"/>

</LinearLayout>

SummaryActivity.java
package com.example.restaurantapp;

import android.os.Bundle;
import android.widget.TextView;
import androidx.appcompat.app.AppCompatActivity;

public class SummaryActivity extends AppCompatActivity {
@override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_summary);

    int total = getIntent().getIntExtra("total", 0);
    TextView summaryText = findViewById(R.id.summaryText);
    summaryText.setText("Total cost: $" + total);
}

}

activity_summary.xml
<?xml version="1.0" encoding="utf-8"?>
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:padding="32dp"
android:gravity="center">

<TextView
    android:id="@+id/summaryText"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Total cost: $0"
    android:textSize="28sp"
    android:textStyle="bold"
    android:textColor="#000000"
    android:layout_marginTop="20dp"/>

androidmanifest.xml
only if required
android:allowBackup="true"
android:dataExtractionRules="@xml/data_extraction_rules"
android:fullBackupContent="@xml/backup_rules"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/Theme.Restaurantapp"
tools:targetApi="31">
android:name=".MainActivity"
android:exported="true">

            <category android:name="android.intent.category.LAUNCHER" />
        </intent-filter>
    </activity>

    <activity android:name=".SummaryActivity"></activity>

</application>

DEV Community: Ankit Mishra

Guide on Database in Android Studio