How do i delete or update data from database

[Guest]

 

package com.example.test1;

import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.CalendarView;
import android.widget.EditText;

import java.io.File;

public class MainActivity extends AppCompatActivity {

    private mySQLiteDBHandler dbHandler;
    private EditText editText;
    private CalendarView calendarView;
    private String selectedDate;
    private SQLiteDatabase sqLiteDatabase;

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

        editText = findViewById(R.id.editText);
        calendarView = findViewById(R.id.calendarView);

        calendarView.setOnDateChangeListener(new CalendarView.OnDateChangeListener() {
            @Override
            public void onSelectedDayChange(@NonNull CalendarView view, int year, int month, int dayOfMonth) {
                selectedDate = Integer.toString(year) + Integer.toString(month) + Integer.toString(dayOfMonth);
                ReadDatabase(view);
            }
        });
        try {
            dbHandler = new mySQLiteDBHandler(this, "CalendarDatabase", null, 1);
            sqLiteDatabase = dbHandler.getWritableDatabase();
            sqLiteDatabase.execSQL("CREATE TABLE EventCalendar(Date TEXT, Event TEXT)");
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
    public void InsertDatabase (View view){
        ContentValues contentValues = new ContentValues();
        contentValues.put("Date",selectedDate);
        contentValues.put("Event",editText.getText().toString());
        sqLiteDatabase.insert("EventCalendar",null,contentValues);
    }
    public void ReadDatabase(View view){
        String query = "Select Event from EventCalendar where Date =" +selectedDate;
        try {
            Cursor cursor = sqLiteDatabase.rawQuery(query,null);
            cursor.moveToFirst();
            editText.setText(cursor.getString(0));
        }
        catch (Exception e){
            e.printStackTrace();
            editText.setText("");
        }
    }
}

im making a calendar with small reminder note. So far i can only save the note i write on edit text i wonder how do i make it deleteable or updateable

[shadow_ray]

UPDATE EventCalendar
SET Event = " something new  "
WHERE your_condition; 
-- f. e. id = 42;

 

DELETE FROM EventCalendar
WHERE your_condition; 
-- f. e.: Event LIKE '%drunk%'

 

I would recommend you to use parameterized queries to avoid sql injection.

...
String query = "Select Event from EventCalendar where Date = ?";
Cursor cursor = sqLiteDatabase.rawQuery(query,new String[] {selectedDate});
...

 

[Eigenvektor]

Since you're on Android I'd recommend to look into something like Room, rather than using plain SQL. If you want something a little lower level you could e.g. look into greenDAO instead. That will make your life easier in the long run.

[straight_stewie]

On 12/5/2020 at 3:08 AM, shadow_ray said:

I would recommend you to use parameterized queries to avoid sql injection.

Better yet, establish views and stored procedures to do the vast majority of the work, restricting ad hoc queries pretty much solely to the domain of debugging, testing, and analysis.

Not only do these features guard against injection, they allow you to define who is and isn't allowed access to what, as well as increase maintainability and flexibility by helping to encapsulate the underlying structure.

OP can make use of these features without having to switch DBMS, as SQLite already provides them.

[Eigenvektor]

On 12/12/2020 at 10:15 AM, straight_stewie said:

Better yet, establish views and stored procedures to do the vast majority of the work, restricting ad hoc queries pretty much solely to the domain of debugging, testing, and analysis.

Based on package names, OP is programming on Android, which means they are using SQLite. SQLite does not support stored procedures.

 

Which is why I recommended to use Room. Room is a DAO that sits on top of SQLite and was developed with Android in mind. In fact it is a first party library developed by Google and is part of the AndroidX namespace. This allows you to use POJOs and abstract methods to query data from tables. And you get syntax checking of your queries at compile time.

@Query("SELECT * FROM user WHERE user_name LIKE :name AND last_name LIKE :last")
public abstract List<User> findUsersByNameAndLastName(String name, String last);