jardin jardin - 1 month ago 7
Android Question

How to update existing data in a database

I need to create a table like [this]:

enter image description here

I also want to update an existing record after the table is created. Any suggestions?

Answer

You need to create a DatabaseAdapter class with the necessary CRUD functions for your SQLite database. Start by creating a new Java class file called DatabaseAdapter.java. Insert the following code:

public class DatabaseAdapter {
    private static final String databaseName  = "MealsDatabase";
    private static final int databaseVersion = 1;
    private static final String databaseTable = "Meals";
    private static final String columnId = "id";
    private static final String columnQuantity = "quantity";
    private static final String columnMeal = "meal";
    private static final String columnPrice = "price";
    private static final String columnAmount = "amount";

    DatabaseHelper helper;
    SQLiteDatabase database;

    public DatabaseAdapter(Context context) {
        this.helper = new DatabaseHelper(context, databaseName, databaseVersion);
    }

    public DatabaseAdapter open() throws SQLException {
        this.database = helper.getWritableDatabase();
        return this;
    }

    public void close() throws SQLException {
        this.helper.close();
    }

    public Cursor getAllMeals() {
        return this.database.query(databaseTable, new String[] {columnId, columnName, columnDate, columnDescription}, null, null, null, null, null);
    }

    public Cursor getMeal(long id) throws SQLException {
        Cursor cursor = this.database.query(true, databaseTable, new String[] {columnId, columnQuantity, columnMeal, columnPrice, columnAmount}, columnId + "=" + id, null, null, null, null, null);

        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    public long insertMeal(int quantity, String meal, float price, int amount) {
        ContentValues contentValues = new ContentValues();

        contentValues.put(columnQuantity, quantity);
        contentValues.put(columnMeal, meal);
        contentValues.put(columnPrice, price);
        contentValues.put(columnAmount, amount);

        return this.database.insert(databaseTable, null, contentValues);
    }

    public boolean editMeal(long id, int quantity, String meal, float price, int amount) {
        ContentValues contentValues = new ContentValues();

        contentValues.put(columnQuantity, quantity);
        contentValues.put(columnMeal, meal);
        contentValues.put(columnPrice, price);
        contentValues.put(columnAmount, amount);

        return this.database.update(databaseTable, contentValues, columnId + "=" + id, null) > 0;
    }

    public boolean deleteMeal(long id) {
        return this.database.delete(databaseTable, columnId + "=" + id, null) > 0;
    }
}

Now, create a new Java class called DatabaseHelper.java and add the following code:

public class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context, String databaseName, int databaseVersion) {
        super(context, databaseName, null, databaseVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        try {
            database.execSQL("CREATE TABLE Meals (id INTEGER PRIMARY KEY AUTOINCREMENT, quantity INTEGER NOT NULL, meal TEXT NOT NULL, price REAL NOT NULL, amount INTEGER NOT NULL)");
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        Log.w("MealsDatabase", "Upgrading database from version " + oldVersion + " to " + newVersion + ".");
        database.execSQL("DROP TABLE IF EXISTS SavedLists");
        onCreate(database);
    }
}

In the activity that creates a new meal, add the following code:

DatabaseAdapter database = new DatabaseAdapter(this);
database.open();

if (database.insertMeal(quantity, "Meal", 5.60, amount) > 0) {
    Toast.makeText(this, "A new meal has been created!", Toast.LENGTH_SHORT).show();
}

database.close();

To update an existing record, add the following code:

DatabaseAdapter database = new DatabaseAdapter(this);
database.open();

if (database.editMeal(id, quantity, "Meal", 5.60, amount)) {
    Toast.makeText(this, "A meal has been updated!", Toast.LENGTH_SHORT).show();
}

database.close();

You have now created your meals database, added a meal and updated an existing meal.