Altri esercizi sui database

Esercizio:
1) Eliminazione di un record da una tabella
2)Modifica di un campo di un record.

---

---

public class MainActivity extends AppCompatActivity {

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

        helper=new Helper(this);
        helper.save("Mario","Rossi");
        helper.save("Mario","Verdi");
        helper.save("Giuseppe","Verdi");

        

        /

        Cursor crs=helper.query();
        do{
            System.out.println(crs.getString(crs.getColumnIndex("nome"))+" "+crs.getString(crs.getColumnIndex("cognome")));
        }while(crs.moveToNext());

    }

}

class Helper extends SQLiteOpenHelper {

    public Helper(Context context) {
        super(context, "database.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table tabella(_id integer primary key, nome text, cognome text)");
    }

    public void save(String n, String c){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put("nome",n);
        values.put("cognome",c);
        try {
            db.insertOrThrow("tabella", null, values);
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }



    public Cursor query(){
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor crs=db.rawQuery("select*from tabella",null);
        crs.moveToFirst();
        return crs;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

Inseriamo la funzione che modifica:

    public void save(String n, String c){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put("nome",n);
        values.put("cognome",c);
        try {
            db.insertOrThrow("tabella", null, values);
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    
    public void modifica(String vecchio,String nuovo){
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL("update tabella set nome='"+nuovo+"' where nome='"+vecchio+"'");
    }

Ora lo metto in opera:

        helper=new Helper(this);
        helper.save("Mario","Rossi");
        helper.save("Mario","Verdi");
        helper.save("Giuseppe","Verdi");

        helper.modifica("Giuseppe","Pippo");

        

Vediamo...

04-25 14:57:01.773 6034-6034/? I/System.out: Mario Rossi
04-25 14:57:01.773 6034-6034/? I/System.out: Mario Verdi
04-25 14:57:01.774 6034-6034/? I/System.out: Pippo Verdi

Ricordare che il comando SQL inizia con UPDATE tabella, e poi SET... WHERE non è difficile.

---

Ora scrivo una procedura per eliminare il terzo record.
Provo con questa (nella classe Helper):

    public void cancella(String n){
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL("delete from tabella where nome='"+n+"'");
    }

E la provo:

        helper=new Helper(this);
        helper.save("Mario","Rossi");
        helper.save("Mario","Verdi");
        helper.save("Giuseppe","Verdi");

        helper.cancella("Giuseppe");

Ecco:

04-25 15:03:58.850 9661-9661/? I/System.out: Mario Rossi
04-25 15:03:58.850 9661-9661/? I/System.out: Mario Verdi

Ancora con un altro nome. Riscrivo daccapo per allenarmi:

    public void cancella(String c){
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL("delete from tabella where cognome='"+c+"'");
    }

        helper=new Helper(this);
        helper.save("Mario","Rossi");
        helper.save("Mario","Verdi");
        helper.save("Giuseppe","Verdi");

        helper.cancella("Rossi");

04-25 15:07:32.736 11681-11681/? I/System.out: Mario Verdi
04-25 15:07:32.736 11681-11681/? I/System.out: Giuseppe Verdi

Operazione combinata di modifica e cancella. Modifico Giuseppe Verdi in Giuseppe Rossi e poi cancello Verdi.

    public void modifica(){
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL("update tabella set cognome='Rossi' where nome='Giuseppe'");
    }

    public void cancella(){
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL("delete from tabella where cognome='Verdi'");
    }

Provo:

        helper=new Helper(this);
        helper.save("Mario","Rossi");
        helper.save("Mario","Verdi");
        helper.save("Giuseppe","Verdi");

        helper.modifica();
        helper.cancella();

04-25 15:14:49.741 15579-15579/? I/System.out: Mario Rossi
04-25 15:14:49.741 15579-15579/? I/System.out: Giuseppe Rossi

perfetto!

---

---

Doppia modifica:

    public void modifica(){
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL("update tabella set cognome='La Minchia' and nome='Cicciobello' where nome='Giuseppe'");
    }

No, ottengo uno zero.
Provo così:

    public void modifica(){
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL("update tabella set cognome='La Minchia', nome='Cicciobello' where nome='Giuseppe'");
    }

04-25 15:19:57.107 18596-18596/? I/System.out: Mario Rossi
04-25 15:19:57.108 18596-18596/? I/System.out: Mario Verdi
04-25 15:19:57.108 18596-18596/? I/System.out: Cicciobello La Minchia

Perfetto!

---

Due clausole:

    public void modifica(){
        SQLiteDatabase db=this.getWritableDatabase();
        db.execSQL("update tabella set cognome='La Minchia' where nome='Mario' and cognome='Rossi'");
    }

        helper=new Helper(this);
        helper.save("Mario","Rossi");
        helper.save("Mario","Verdi");
        helper.save("Giuseppe","Verdi");

        helper.modifica();

04-25 15:47:10.068 519-519/? I/System.out: Mario La Minchia
04-25 15:47:10.069 519-519/? I/System.out: Mario Verdi
04-25 15:47:10.069 519-519/? I/System.out: Giuseppe Verdi

Okay.