如何通过活动中的按钮向SQLite表列添加值

Question

我正在尝试向用户表添加一个整数值。我想在哪里添加值，这是由我传递给活动的用户的字符串值决定的。

这是我的DBHelper类：

package com.example.shashank.fffffffffffffffffffffffffff;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
import java.util.ArrayList;
import java.util.List;
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "Login.db";
public static final String FLIGHTS = "FLIGHTS";
public static final String COLUMN_ID = "ID";
public static final String COLUMN_DESTINATION = "DESTINATION";
public static final String COLUMN_PRICE = "PRICE";
public static final String COLUMN_DEPARTURE_TIME = "DEPARTURE_TIME";
public static final String COLUMN_ARRIVAL_TIME = "ARRIVAL_TIME";
public static final String COLUMN_DURATION = "DURATION";
public static final String COLUMN_AVAILABLE_SEATS = "AVAILABLE_SEATS";
public static final String USERS = "users";
public static final String USERNAME = "username";
public static final String PASSWORD = "password";
public static final String EMAIL = "email";
public static final String BALANCE = "balance";
public static final String BOOKING = "booking";
public DBHelper(Context context) {
super(context, "Login.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase MyDB) {
String createTable1 = ("create Table " + USERS + "(" + USERNAME + " TEXT primary key, " + PASSWORD + " TEXT, " + EMAIL + " TEXT UNIQUE, " + BALANCE + " REAL, " + BOOKING + " INTEGER)");
MyDB.execSQL(createTable1);
MyDB.execSQL("CREATE TABLE " + FLIGHTS + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_DESTINATION + " TEXT, " + COLUMN_PRICE + " REAL, " + COLUMN_DEPARTURE_TIME + " TEXT, " + COLUMN_ARRIVAL_TIME + " TEXT, " + COLUMN_DURATION + " TEXT, " + COLUMN_AVAILABLE_SEATS + " INTEGER)");

ContentValues insertValues = new ContentValues();
insertValues.put(COLUMN_DESTINATION, "Cape Town");
insertValues.put(COLUMN_PRICE, 500);
insertValues.put(COLUMN_DEPARTURE_TIME, "1200");
insertValues.put(COLUMN_ARRIVAL_TIME, "1400");
insertValues.put(COLUMN_DURATION, "2");
insertValues.put(COLUMN_AVAILABLE_SEATS, 10);
MyDB.insert(FLIGHTS, null, insertValues);
ContentValues insertValues2 = new ContentValues();
insertValues2.put(COLUMN_DESTINATION, "Johannesburg");
insertValues2.put(COLUMN_PRICE, 1000);
insertValues2.put(COLUMN_DEPARTURE_TIME, "1400");
insertValues2.put(COLUMN_ARRIVAL_TIME, "1600");
insertValues2.put(COLUMN_DURATION, "2");
insertValues2.put(COLUMN_AVAILABLE_SEATS, 22);
MyDB.insert(FLIGHTS, null, insertValues2);
ContentValues insertValues3 = new ContentValues();
insertValues3.put(COLUMN_DESTINATION, "Cape Town");
insertValues3.put(COLUMN_PRICE, 500);
insertValues3.put(COLUMN_DEPARTURE_TIME, "1200");
insertValues3.put(COLUMN_ARRIVAL_TIME, "1400");
insertValues3.put(COLUMN_DURATION, "2");
insertValues3.put(COLUMN_AVAILABLE_SEATS, 10);
MyDB.insert(FLIGHTS, null, insertValues3);

}

@Override
public void onUpgrade(SQLiteDatabase MyDB, int i, int i1) {
MyDB.execSQL("drop Table if exists " + USERS);
MyDB.execSQL("drop Table if exists " + FLIGHTS);
onCreate(MyDB);
}
public Boolean insertData(String username, String password, String email, Double balance){
SQLiteDatabase MyDB = this.getWritableDatabase();
ContentValues contentValues= new ContentValues();
contentValues.put(USERNAME, username);
contentValues.put(PASSWORD, password);
contentValues.put(EMAIL, email);
contentValues.put(BALANCE, balance);
long result = MyDB.insert(USERS, null, contentValues);
if(result==-1) return false;
else
return true;
}
public Boolean checkusername(String username) {
SQLiteDatabase MyDB = this.getWritableDatabase();
Cursor cursor = MyDB.rawQuery("Select * from " + USERS + " where " + USERNAME + " = ?", new String[]{username});
if (cursor.getCount() > 0)
return true;
else
return false;
}
public Boolean checkusernamepassword(String username, String password){
SQLiteDatabase MyDB = this.getWritableDatabase();
Cursor cursor = MyDB.rawQuery("Select * from " + USERS + " where " + USERNAME + " = ? and " + PASSWORD + " = ?", new String[] {username,password});
if(cursor.getCount()>0)
return true;
else
return false;
}

public List<FlightsModel> getEveryone(){
List<FlightsModel> returnList = new ArrayList<>();
String queryString = "SELECT * FROM " + FLIGHTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(queryString, null);
if(cursor.moveToFirst()){
do {
int id = cursor.getInt(0);
String destination = cursor.getString(1);
double price = cursor.getDouble(2);
String departure = cursor.getString(3);
String arrival = cursor.getString(4);
String duration = cursor.getString(5);
int space = cursor.getInt(6);
FlightsModel newFlight = new FlightsModel(id, destination, price, departure, arrival, duration, space);
returnList.add(newFlight);

}while (cursor.moveToNext());
}
else{
}
cursor.close();
db.close();
return returnList;
}

@SuppressLint("Range") // suppress Bug/issue with getColumnIndex
public FlightsModel getFlightById(int id) {
FlightsModel rv;
SQLiteDatabase db = this.getWritableDatabase();
// Uses the query convenience method rather than raw query
Cursor csr = db.query(FLIGHTS,null,COLUMN_ID+"=?",new String[]{String.valueOf(id)},null,null,null);
if (csr.moveToFirst()) {
rv = new FlightsModel(
csr.getInt(csr.getColumnIndex(COLUMN_ID)),
csr.getString(csr.getColumnIndex(COLUMN_DESTINATION)),
csr.getDouble(csr.getColumnIndex(COLUMN_PRICE)),
csr.getString(csr.getColumnIndex(COLUMN_DEPARTURE_TIME)),
csr.getString(csr.getColumnIndex(COLUMN_ARRIVAL_TIME)),
csr.getString(csr.getColumnIndex(COLUMN_DURATION)),
csr.getInt(csr.getColumnIndex(COLUMN_AVAILABLE_SEATS))
);
}  else {
rv = new FlightsModel();
}
csr.close();
// No need to close the database (inefficient to keep opening and closing db)
return rv;
}

@SuppressLint("Range")
public UsersModel getPasswordByName(String name){
UsersModel rv;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cr = db.query(USERS, null, USERNAME+"=?", new String[]{name}, null, null, null);
if (cr.moveToFirst()) {
rv = new UsersModel(
cr.getString(cr.getColumnIndex(USERNAME)),
cr.getString(cr.getColumnIndex(PASSWORD)),
cr.getString(cr.getColumnIndex(EMAIL)),
cr.getDouble(cr.getColumnIndex(BALANCE))
);
}  else rv = new UsersModel();
cr.close();
return rv;
}


}

活动如下：

package com.example.shashank.fffffffffffffffffffffffffff;
import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.os.Bundle;
import android.widget.TextView;
import android.widget.Toast;
public class BookingActivity extends AppCompatActivity {

TextView textView;
TextView departure, arrival, duration, price, seats, textView13;
DBHelper dbHelper; //<<<<< ADDED
FlightsModel flightsModel; //<<<<< ADDED
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_booking);
dbHelper = new DBHelper(this); //<<<<< ADDED
textView = findViewById(R.id.textView);
departure = findViewById(R.id.departure);
arrival = findViewById(R.id.arrival);
duration = findViewById(R.id.duration);
price = findViewById(R.id.price);
seats = findViewById(R.id.seats);
textView13 = findViewById(R.id.textView13);

Intent mIntent = getIntent();
int intValue = mIntent.getIntExtra("intVariableName", 0);
Intent nameIntent = getIntent();
String name = nameIntent.getStringExtra("userName");
flightsModel = dbHelper.getFlightById(intValue + 1);

intValue = intValue + 1;
textView.setText(flightsModel.getDestination());
departure.setText(flightsModel.getDeparture_time());
arrival.setText(flightsModel.getArrival_time());
duration.setText(flightsModel.getDuration());
price.setText("R" +  Double.toString(flightsModel.getPrice()));
seats.setText(Integer.toString(flightsModel.getAvailable_space()));

textView13.setText(name);
Toast.makeText(BookingActivity.this, flightsModel.getDestination(), Toast.LENGTH_SHORT).show();
}

}

我试图完成的是向booking列中的users表添加一个整数值(活动中的值为intValue(。该值需要添加到用户行中，其中的名称与我的活动中的字符串名称相匹配。我还是Android工作室和SQLite的新手，还没能弄清楚这一点。任何帮助都将不胜感激，谢谢。

Answer 1

当行存在时，UPDATE是行，SET是值。

使用update或updateWithOnConflict执行此操作，或通过和execSQL进行更复杂的更新。

因此，如果您只想将booking列的值设置为intValue的值，那么您可以在DBHelper中使用一个方法，例如：-

public int setBookingByUserName(int bookingAmount, String userName) {
ContentValues cv = new ContentValues();
cv.put(USERNAME,bookingAmount);
return this.getWritableDatabase().update(USERS,cv,USERNAME+"=?",new String[]{userName});
}

• 返回的值是更新的行数，应该是1，因为用户名是主键
• 便利方法构建底层SQL，类似于UPDATE users SET booking=? WHERE username=?，再次绑定两个值来替换？'s

然后您所需要做的就是将Button添加到活动(类似于文本视图(，然后添加它的onClickListener，它将调用传递适当值的setBookingBuUserName方法。