I am trying to add an integer value to my users table. Where I want to add the value is determined by a string value of a user that I am passing to the activity.
Here is my DBHelper class:
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;
}
}
And here is the activity:
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();
}
}
What I am trying to accomplish is adding an integer value(the value being intValue in activity) to the users table in the booking column. The value needs to be added to the user row where the name matches the String name in my activity. I am still very new to Android studios and SQLite and have not been able to figure this out. Any help will be appreciated thank you.
CodePudding user response：
As the row exists you UPDATE the row and SET the value(s).
You do this using the update or updateWithOnConflict or for more complex updates via and execSQL.
So if you just want to set the value of the booking column to the value of intValue then you could have a method in the DBHelper such as :-
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});
}
- The value returned would be the number of rows updated, which should be 1 as the username is the primary key.
- The convenience method builds the underlying SQL which would be something like
UPDATE users SET booking=? WHERE username=?again binding the two values replacing the ?'s respectively.
All you then need to do is add the Button to the activity (similar to the textviews), then add it's onClickListener that will call the setBookingBuUserName method passing the appropriate values.