Hi I'm making a bill splitting app in android studio 3.3. I've made a database with sqlite and it stores a product's name and cost. As I am trying to get the total cost of the input from the database and store it in a text view the app closes. I've came to the conclusion that the problem is located in the getTotal() method in MyDBHandler.java
public class MyDBHandler extends SQLiteOpenHelper{
private static final int DATABASE_VERSION = 6;
private static final String DATABASE_NAME = "productDB.db";
public static final String TABLE_PRODUCTS = "products";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_PRODUCTNAME = "productname";
public static final String COLUMN_PRODUCTCOST = "productcost";
//We need to pass database information along to superclass
public MyDBHandler(Context context, String name,
SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String query = "CREATE TABLE " + TABLE_PRODUCTS + "(" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_PRODUCTNAME + " TEXT " + "," +
COLUMN_PRODUCTCOST + " INTEGER " +
");";
db.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
onCreate(db);
}
//Add a new row to the database
public void addProduct(Products product){
ContentValues values = new ContentValues();
values.put(COLUMN_PRODUCTNAME, product.get_productname());
values.put(COLUMN_PRODUCTCOST, product.get_productcost());
SQLiteDatabase db = getWritableDatabase();
db.insert(TABLE_PRODUCTS, null, values);
db.close();
}
//Delete a product from the database
public void deleteProduct(String productName){
SQLiteDatabase db = getWritableDatabase();
db.execSQL("DELETE FROM " + TABLE_PRODUCTS + " WHERE " +
COLUMN_PRODUCTNAME + "=\"" + productName + "\";");
}
//get the total sum of teh Product_Cost column
//
// TODO: fix this method to display total
// NOTE: THIS IS YOUR PROBLEM
//
//
public int GetTotal(){
int temp;
SQLiteDatabase db = getWritableDatabase();
String query = " SELECT SUM(COLUMN_PRODUCTCOST) FROM " + TABLE_PRODUCTS + ";";
Cursor cursor = db.rawQuery(query , null);
if (cursor.moveToFirst()) {
temp = cursor.getInt(0);
}
else return 0;
cursor.close();
return temp;
}
// converts the elements in the database to a string so you can print the database out.
public String databaseToString(){
String dbString = "";
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_PRODUCTS + " WHERE 1";// why not leave out the WHERE clause?
//Cursor points to a location in your results
Cursor recordSet = db.rawQuery(query, null);
//Move to the first row in your results
recordSet.moveToFirst();
//Position after the last row means the end of the results
while (!recordSet.isAfterLast()) {
// null could happen if we used our empty constructor
if (recordSet.getString(recordSet.getColumnIndex("productname")) != null) {
dbString += recordSet.getString(recordSet.getColumnIndex("productname"));
dbString += "\t $";
dbString += recordSet.getString(recordSet.getColumnIndex("productcost"));
dbString += "\n";
}
recordSet.moveToNext();
}
db.close();
return dbString;
}
}
public class MainActivity extends AppCompatActivity {
EditText userInput;
EditText userInputC;
TextView recordsTextView;
TextView Results;
MyDBHandler dbHandler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
userInput = (EditText) findViewById(R.id.user_Input);
userInputC = (EditText) findViewById(R.id.user_InputC);
recordsTextView = (TextView) findViewById(R.id.records_TextView);
Results = (TextView) findViewById(R.id.Results);
/* Can pass nulls because of the constants in the helper.
* the 1 means version 1 so don't run update.
*/
dbHandler = new MyDBHandler(this, null, null, 1);
printDatabase();
}
//Print the database
public void printDatabase(){
String dbString = dbHandler.databaseToString();
recordsTextView.setText(dbString);
userInput.setText("");
userInputC.setText("");
}
/*
//Add a product to the database
public void addButtonClicked(View view){
// dbHandler.add needs an object parameter.
int count = Integer.parseInt(Results.getText().toString());
int num = Integer.parseInt(userInputC.getText().toString());
count = count + num;
String temp = Results.getText().toString();
float temp1 = Float.parseFloat(temp);
//Results.setText(Integer.toString(count));
Results.setText(Float.toString(count));
Products product = new Products(userInput.getText().toString(), temp1);
dbHandler.addProduct(product);
//Results = ;
printDatabase();
}*/
@SuppressLint("SetTextI18n")
public void addButtonClicked(View view){
// dbHandler.add needs an object parameter.
int usersinput = Integer.parseInt(userInputC.getText().toString());
Products product = new Products(userInput.getText().toString(), usersinput);
int temp = dbHandler.GetTotal();
dbHandler.addProduct(product);
Results.setText(Integer.toString(temp));
printDatabase();
}
//Delete items
public void deleteButtonClicked(View view){
// dbHandler delete needs string to find in the db
String inputText = userInput.getText().toString();
dbHandler.deleteProduct(inputText);
printDatabase();
}
}
