java - 在 Android Studio 中:如何更新列数据?
问题描述
我在一个有两个表(Tickets,Customer)的数据库项目上工作。
门票表有 (int Ticket_id, int Is_Available = 0)。
客户表有(Customer_id,Customer_name,,Ticket_id)。
我想创建活动以添加新客户
但是当我添加客户时,我选择的票必须更新值(Is_Available1
不是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 {}
}
});
当我运行项目并添加新客户时,总是no
这样 no update have done
。
我如何更新值。
解决方案
您可以使用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 - 添加第三个客户后的票证:-
推荐阅读
- r - 保存 XLS 文件格式错误:自动转换为数字
- ruby-on-rails - 控制器 I18n 编码
- c# - IIS 工作进程和工作线程
- mysql - 来自外部服务器的 Google Cloud SQL 超时
- neo4j - neo4j apoc.load.xml 获取嵌套元素值
- node.js - 在基本 Node/Express 应用程序中呈现“hello world”时出现问题
- firebase - Firestore 返回两个 newIndex 0
- angular - 无法解析服务参数
- arrays - 比较分配给 std_logic_vector 的整数值
- qt - 无法将双精度分配给 QQuickAnchorLine