java - 在Android Studio中:How I can update the column data?

Question

我正在开发一个数据库项目，该项目有两个表(门票、客户)。

Tickets 表有 (int Ticket_id, int Is_Available = 0 )。

客户表有(Customer_id,Customer_name,,Ticket_id)。

我想创建 Activity 来添加新客户

但是当我添加客户时，我选择的工单必须更新值(Is_Available 为 1 而不是 0 )

这是数据库类

public class DatabaseHelper   extends SQLiteOpenHelper {
    SQLiteDatabase db ;
    public DatabaseHelper(Context context) {
        super(context, "Airline", null, 12);
        db= getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db .execSQL(ticket.Create_table);
        db .execSQL(customer.Create_table);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table " + ticket.Table_Name);
        db.execSQL("drop table " + customer.Table_Name);
        onCreate(db);
    }
    public  boolean insert_ticket (String name , String flying_dat , int ticket_price  , String seat_number,int is_booked){
        ContentValues data = new ContentValues();
        data.put(ticket.Col_name,name);
        data.put(ticket.Col_Date,flying_dat);
        data.put(ticket.Col_Price,ticket_price);
        data.put(ticket.Col_SeatNum,seat_number);
        data.put(ticket.Col_isBooked,is_booked);
        return db.insert(ticket.Table_Name,null ,data) >0 ;
    }
    public  boolean insert_customer (String name , String mobile , String passport,int ticket_id){

        ContentValues data = new ContentValues();
        data.put(customer.Col_Name,name);
        data.put(customer.Col_Passport,passport);
        data.put(customer.Col_Mobile,mobile);
        data.put(customer.Col_Ticket_id,ticket_id);

        return db.insert(customer.Table_Name,null ,data) >0 ;
    }


    public ArrayList<ticket> getAll_tickets(){
        ArrayList<ticket> tickets =new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from " + ticket.Table_Name  ,null);

        while(cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex(ticket.Col_Id));
            String name = cursor.getString(cursor.getColumnIndex(ticket.Col_name));
            String flying_date = cursor.getString(cursor.getColumnIndex(ticket.Col_Date));
            String seat_number = cursor.getString(cursor.getColumnIndex(ticket.Col_SeatNum));
            int ticket_price = cursor.getInt(cursor.getColumnIndex(ticket.Col_Price));
            int is_booked = cursor.getInt(cursor.getColumnIndex(ticket.Col_isBooked));
            ticket r = new ticket(id, name ,flying_date,seat_number,ticket_price,is_booked);
            tickets.add(r);


        }
        cursor.close();
        return tickets;
    }


    public ArrayList<customer> getAll_customers(){
        ArrayList<customer> customers=new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from " +customer.Table_Name ,null);

        while (cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex(customer.Col_Id));
            String name =cursor.getString(cursor.getColumnIndex(customer.Col_Name));
            String mobile =cursor.getString(cursor.getColumnIndex(customer.Col_Mobile));
            String passport =cursor.getString(cursor.getColumnIndex(customer.Col_Passport));
            int ticket_id =cursor.getInt(cursor.getColumnIndex(customer.Col_Ticket_id));
            customer c =new customer(id,name,passport,mobile,ticket_id);
            customers.add(c);

        }
        cursor.close();
        return customers;
    }
    public boolean delete_ticket(int id){
        return db.delete(ticket.Table_Name,"id = ?" ,new String[]{String.valueOf(id)}) > 0 ;
    }
    public boolean delete_customer(int id){
        return db.delete(customer.Table_Name,"id = ?" ,new String[]{String.valueOf(id)}) > 0 ;
    }
    public ArrayList<ticket> getAll_ticketswhere_Available(){
        ArrayList<ticket> tickets =new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from " + ticket.Table_Name + " where " + ticket.Col_isBooked  + " = 0"  ,null);

        while(cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex(ticket.Col_Id));
            String name = cursor.getString(cursor.getColumnIndex(ticket.Col_name));
            String flying_date = cursor.getString(cursor.getColumnIndex(ticket.Col_Date));
            String seat_number = cursor.getString(cursor.getColumnIndex(ticket.Col_SeatNum));
            int ticket_price = cursor.getInt(cursor.getColumnIndex(ticket.Col_Price));
            int is_booked = cursor.getInt(cursor.getColumnIndex(ticket.Col_isBooked));
            ticket r = new ticket(id, name ,flying_date,seat_number,ticket_price,is_booked);
            tickets.add(r);


        }
        cursor.close();
        return tickets;
    }

    public boolean update_isBookedIF1(int OldId ) {
        ContentValues data = new ContentValues();
        data.put(ticket.Col_isBooked, 0);

        return db.update(ticket.Table_Name, data, "id = ?", new String[]{String.valueOf(OldId)}) > 0;
    }
    public boolean update_isBookedIF0(int OldId , int isBooked ) {
        ContentValues data = new ContentValues();
        data.put(ticket.Col_isBooked, isBooked);

        return db.update(ticket.Table_Name, data, "id = ?", new String[]{String.valueOf(OldId)}) > 0;
    }
    }

这是添加客户类

 final DatabaseHelper db=new DatabaseHelper(this);
        final Spinner tickett = findViewById(R.id.ticket_chose);
        final EditText name = findViewById(R.id.customer_name_add);
        final EditText mobile = findViewById(R.id.mobile_num_add);
        final EditText passport = findViewById(R.id.passport_num_add);
        final Button add =findViewById(R.id.addCustomer);

      final ArrayList<ticket> data=db.getAll_ticketswhere_Available();
     final ticket_adapter adapter = new ticket_adapter(this,data);
      tickett.setAdapter(adapter);

        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String Name = name.getText().toString();
                String Mobile = mobile.getText().toString();
                String Passport = passport.getText().toString();
                int Ticket_=(int) tickett.getSelectedItemId();
                if (db.insert_customer(Name,Mobile,Passport,Ticket_)) {
                    name.setText(" ");
                    mobile.setText(" ");
                    passport.setText(" ");
                    ticket t = new ticket();

                    if ( db.update_isBookedIF0(t.getId(),1)){
                        Toast.makeText(Add_customer.this, "yes", Toast.LENGTH_SHORT).show();
                    }
                    else {
                        Toast.makeText(Add_customer.this, "no", Toast.LENGTH_SHORT).show();
                    }

                }
                else {}
            }
        });

当我运行项目并添加新客户时，总是没有这意味着尚未完成任何更新。

如何更新该值。

Answer 1

您可以使用 TRIGGER 添加客户时将自动应用更新。

基于:-

CREATE TRIGGER IF NOT EXISTS setTicketAvailability 
    AFTER INSERT ON Customer 
    BEGIN 
        UPDATE Tickets SET Is_Available = 1 WHERE Tickets.Ticket_id = new.Ticket_id; 
    END;

因此，在 DatabaseHelper.java 中，您的 onCreate 方法可以替换为:-

@Override
public void onCreate(SQLiteDatabase db) {
    String crt_setTicketAvailability_trigger = "CREATE TRIGGER IF NOT EXISTS setTicketAvailability " + 
        " AFTER INSERT ON " + customer.Table_Name + 
        " BEGIN UPDATE " + ticket.Table_Name + 
        " SET " + ticket.Col_isBooked + "=1 " + 
        " WHERE " + ticket.Table_Name + "." + ticket.Col_id + 
        " = new." + customer.Col_Ticket_id + ";" + 
        " END";
    db.execSQL(ticket.Create_table);
    db.execSQL(customer.Create_table);
    db.execSQL(crt_setTicketAvailability_trigger);

}

您还应该修改您的 onUpgrade 方法，可以将其替换为:-

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("drop table IF EXISTS " + customer.Table_Name);
    db.execSQL("drop table IF EXISTS " + ticket.Table_Name);
    db.execSQL("DROP TRIGGER IF EXISTS setTicketAvailability_trigger");
    onCreate(db);
}

然后您就可以通过增加版本号来引入修改后的架构。

• 请注意，以上是原则性代码，尚未经过测试或运行，因此可能包含一些错误。

附加

但是，在SQLite管理工具(Navicat)中使用以下方法来测试原理:-

DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Tickets;
CREATE TABLE IF NOT EXISTS Tickets (Ticket_id INTEGER PRIMARY KEY, Is_Available INTEGER DEFAULT 0);
CREATE TABLE IF NOT EXISTS Customer (Customer_id INTEGER PRIMARY KEY, Customer_name TEXT, Ticket_id INTEGER REFERENCES Tickets(Ticket_id) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TRIGGER IF NOT EXISTS setTicketAvailability AFTER INSERT ON Customer BEGIN UPDATE Tickets SET Is_Available = 1 WHERE Tickets.Ticket_id = new.Ticket_id; END;

INSERT INTO Tickets (Ticket_id) VALUES(null),(null),(null);
SELECT * FROM Tickets;
INSERT INTO Customer (Customer_name, Ticket_id) VALUES 
    ('Fred',3), -- Add Customer Fred booking ticket with id = 3
  ('Mary',1) -- Add Csutomer mary booking ticket with id 1
;
SELECT * FROM Tickets;
INSERT INTO Customer (Customer_name, Ticket_id) VALUES 
   ('Jane',2)
;
SELECT * FROM Tickets;

3 个查询的结果是:-

结果 1 - 插入后的票证(全部可用):-

结果 2 - 添加 2 个客户后的门票:-

结果 3 - 添加第 3 个客户后的门票:-