SQLite database not inserting data or finding item(called bear)

Question

so I've been trying to make a sqlite database in Android Studio with pre-existing data. I'm also trying to display that data to the user, although every time I launch the application it crashes when I search for the items in the database, so I am not sure if I am creating the database correctly. Anything helps, and many Thanks.

here is my database helper

public class MyDBHandler {
myDbHelper myhelper;
public MyDBHandler(Context context)
{
    myhelper = new myDbHelper(context);
}

public void addBear(Bears bear)
{
    SQLiteDatabase dbb = myhelper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(myhelper.COLUMN_ID, bear.getID());
    values.put(myhelper.COLUMN_BEARNAME, bear.getbearname());
    values.put(myhelper.COLUMN_STUFFING, bear.getstuffing());
    values.put(myhelper.COLUMN_BEARHEALTH, bear.getbearhealth());
    values.put(myhelper.COLUMN_HEALTHCOST, bear.gethpcost());
    values.put(myhelper.COLUMN_HEALTHCOUNT, bear.gethpcount());
    values.put(myhelper.COLUMN_BEARATTACK, bear.getbearattack());

    dbb.insert(myDbHelper.TABLE_BEARS, null , values);
}

public Bears findBear(int bearID)
{
    SQLiteDatabase db = myhelper.getWritableDatabase();
    Bears bear = new Bears();
    String[] columns = 
{myDbHelper.COLUMN_ID,myDbHelper.COLUMN_BEARNAME,myDbHelper.COLUMN_STUFFING,
            myDbHelper.COLUMN_BEARHEALTH,myDbHelper.COLUMN_HEALTHCOST, 
myDbHelper.COLUMN_HEALTHCOUNT, myDbHelper.COLUMN_BEARATTACK,};

    String query = myDbHelper.COLUMN_ID + " = ?";
    String[] selections = {String.valueOf(bearID)};

    Cursor cursor = 
    db.query(myDbHelper.TABLE_BEARS,columns,query,
    selections,null,null,null,null);

    if(null != cursor) {
            bear.setID(Integer.parseInt(cursor.getString(0)));
            bear.setbearname(cursor.getString(1));
            bear.setstuffing(Integer.parseInt(cursor.getString(2)));
            bear.setbearhealth(Integer.parseInt(cursor.getString(3)));
            bear.sethpcost(Integer.parseInt(cursor.getString(4)));
            bear.sethpcount(Integer.parseInt(cursor.getString(5)));
            bear.setbearattack(Integer.parseInt(cursor.getString(6)));
        }
    db.close();
    return bear;
}


static class myDbHelper extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME = "bearDB.db";    // Database 
    private static final String TABLE_BEARS = "bears";   // Table Name
    private static final int DATABASE_Version = 1;    // Database Version
    private static final String COLUMN_ID="_id";     // Column I (Primary Key)
    public static final String COLUMN_BEARNAME = "bearname";
    public static final String COLUMN_STUFFING = "stuffing";
    public static final String COLUMN_BEARHEALTH = "bearhealth";
    public static final String COLUMN_HEALTHCOST = "healthcost";
    public static final String COLUMN_HEALTHCOUNT = "healthcount";
    public static final String COLUMN_BEARATTACK = "bearattack";    // Column III
    String CREATE_BEARS_TABLE = "CREATE TABLE " +
            TABLE_BEARS + "("
            + COLUMN_ID + " INTEGER PRIMARY KEY," +
            COLUMN_BEARNAME + " TEXT," +
            COLUMN_STUFFING + " INTEGER," +
            COLUMN_BEARHEALTH + " INTEGER," +
            COLUMN_HEALTHCOST + " INTEGER, " +
            COLUMN_HEALTHCOUNT + " INTEGER, "+
            COLUMN_BEARATTACK + " INTEGER" +
            ")";
    private static final String DROP_TABLE ="DROP TABLE IF EXISTS "+TABLE_BEARS;
    private Context context;

    public myDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_Version);
        this.context=context;
    }

    public void onCreate(SQLiteDatabase db) {

        try {
            db.execSQL(CREATE_BEARS_TABLE);

            ContentValues beary = new ContentValues();
            beary.put(COLUMN_ID, 1 );
            beary.put(COLUMN_BEARNAME, "Beary");
            beary.put(COLUMN_STUFFING, 5);
            beary.put(COLUMN_BEARHEALTH, 10);
            beary.put(COLUMN_HEALTHCOST, 1);
            beary.put(COLUMN_HEALTHCOUNT, 0);
            beary.put(COLUMN_BEARATTACK,4);
            db.insert(TABLE_BEARS, null, beary);

            ContentValues honey = new ContentValues();
            honey.put(COLUMN_ID, 2 );
            honey.put(COLUMN_BEARNAME, "Honey");
            honey.put(COLUMN_STUFFING, 5);
            honey.put(COLUMN_BEARHEALTH, 8);
            honey.put(COLUMN_HEALTHCOST, 1);
            honey.put(COLUMN_HEALTHCOUNT, 0);
            honey.put(COLUMN_BEARATTACK, 3);
            db.insert(TABLE_BEARS, null, honey);

            ContentValues baobao = new ContentValues();
            baobao.put(COLUMN_ID, 3 );
            baobao.put(COLUMN_BEARNAME, "BaoBao");
            baobao.put(COLUMN_STUFFING, 5);
            baobao.put(COLUMN_BEARHEALTH,11);
            baobao.put(COLUMN_HEALTHCOST, 1);
            baobao.put(COLUMN_HEALTHCOUNT, 0);
            baobao.put(COLUMN_BEARATTACK, 3);
            db.insert(TABLE_BEARS, null, baobao);

        } catch (Exception e) {
           // do nothing
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            db.execSQL(DROP_TABLE);
            onCreate(db);
        }catch (Exception e) {
           // do nothing
        }
    }
}

and here is my activity page,

public class BearSelectActivity extends AppCompatActivity {

TextView idBear, healthBear, hpcost, attackBear, abilityBear, stuffingBear;
public int hpcount;

EditText nameBear;

public int beartype = 1;
public String Fighter = "Fighter";
public String Healer = "Healer";
public String Tank = "Tank";
MyDBHandler helper;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_bear_select);
    helper = new MyDBHandler(this);
    Button changeBear = (Button)findViewById(R.id.bearChange);
    idBear = (TextView) findViewById(R.id.bearID);
    nameBear = (EditText) findViewById(R.id.bearName);
    stuffingBear = (TextView) findViewById(R.id.bearStuffing);

    healthBear = (TextView) findViewById(R.id.bearHealth);
    hpcost = (TextView)findViewById(R.id.HPCOST);
    Button plushp = (Button)findViewById(R.id.plusbearhp);
    Button minushp = (Button)findViewById(R.id.minusbearhp);
    attackBear = (TextView) findViewById(R.id.bearAttack);


    abilityBear = (TextView) findViewById(R.id.bearAbility);


    abilityBear.setText(Fighter);

    Bears bear = helper.findBear(beartype);

    idBear.setText(String.valueOf(bear.getID()));
    nameBear.setText(String.valueOf(bear.getbearname()));
    healthBear.setText(String.valueOf(bear.getbearhealth()));
    attackBear.setText(String.valueOf(bear.getbearattack()));
    stuffingBear.setText(String.valueOf(bear.getstuffing()));
}

public void changeBearClick (View view){
    //MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
    ImageView image = (ImageView) findViewById(R.id.bearimage);
    //Bears bear;
    beartype++;
    beartype = bearmod(beartype, 3);
    if(beartype == 2) {
        image.setImageResource(R.drawable.bear2);
        abilityBear.setText(Healer);
    }
    else if(beartype == 3){
        image.setImageResource(R.drawable.bear3);
        abilityBear.setText(Tank);
    }
    else if(beartype == 1){
        image.setImageResource(R.drawable.bear1);
        abilityBear.setText(Fighter);
    }
    lookupBear(view);
}

public int bearmod(int a, int b){
    if (a < b && a > 0){
        return a;
    }
    else if(a == b){
        return a;
    }
    else if(a == 0 || a > b){
        a = 1;
    }
    return a;
}


public void lookupBear (View view) {
    Bears bear = helper.findBear(beartype);
    if(bear == null) {
        if (beartype == 1) {
            bear = new Bears(1, "Beary", 5, 10, 1, 0, 4);
            helper.addBear(bear);
        } else if (beartype == 2) {
            bear = new Bears(2, "Honey", 5, 8, 1, 0, 3);
            helper.addBear(bear);
        } else if (beartype == 3) {
            bear = new Bears(3, "Baobao", 5, 11, 1, 0, 2);
            helper.addBear(bear);
        }
    }
    if(bear != null) {
        idBear.setText(String.valueOf(bear.getID()));
        nameBear.setText(String.valueOf(bear.getbearname()));
        stuffingBear.setText(String.valueOf(bear.getstuffing()));
        healthBear.setText(String.valueOf(bear.getbearhealth()));
        hpcost.setText(String.valueOf(bear.gethpcost()));
        hpcount = bear.gethpcount();
        attackBear.setText(String.valueOf(bear.getbearattack()));
    }
}
}

Always show the text of any error messages in the body of the question. — , May 06 '18 at 22:11
You chose for SQL-statements - why not use JPA? We have a working JPA-solution with sqlite. — Flying Dutchman, May 06 '18 at 22:13
@jdv I wasn't getting errors in the code, it would only crash and stop working when I clicked on "changeBear" which would call lookupBear and from there it should call findBear from MyDBHandler. But I'm not sure what is going wrong, I feel like it's not creating the database. — Rahevin, May 06 '18 at 23:18
@FlyingDutchman I have not hear of JPA? If you have an example I would love to see it and understand how it works. -Thanks — Rahevin, May 06 '18 at 23:19
The details about the crash are sort of the whole point of this question. — , May 07 '18 at 03:00

score 0 · Accepted Answer · answered May 07 '18 at 20:19

You have a number of issues that I have spotted.

The following line, defining the columns has an extra trailing comma so :-

String[] columns = 
{myDbHelper.COLUMN_ID,myDbHelper.COLUMN_BEARNAME,myDbHelper.COLUMN_STUFFING,
            myDbHelper.COLUMN_BEARHEALTH,myDbHelper.COLUMN_HEALTHCOST, 
myDbHelper.COLUMN_HEALTHCOUNT, myDbHelper.COLUMN_BEARATTACK,};

Should be :-

String[] columns = 
{myDbHelper.COLUMN_ID,myDbHelper.COLUMN_BEARNAME,myDbHelper.COLUMN_STUFFING,
            myDbHelper.COLUMN_BEARHEALTH,myDbHelper.COLUMN_HEALTHCOST, 
myDbHelper.COLUMN_HEALTHCOUNT, myDbHelper.COLUMN_BEARATTACK};

When a Cursor is returned it is positioned at before the first row (-1). To access data from the cursor you need to move to a row within the Cursor. You are not doing this.

Additionally a returned Cursor will not be null. So checking for a null Cursor is useless.

Furthermore, using hard coded offsets may well be problematic and inflexible. A Cursor has a getColumnIndex method that will return the offset according to the column name.

A Cursor also has methods other than getString for directly extracting data as other types e.g. getInt, getLong, getBlob ...... Cursor

I'd suggest changing :-

if(null != cursor) {
        bear.setID(Integer.parseInt(cursor.getString(0)));
        bear.setbearname(cursor.getString(1));
        bear.setstuffing(Integer.parseInt(cursor.getString(2)));
        bear.setbearhealth(Integer.parseInt(cursor.getString(3)));
        bear.sethpcost(Integer.parseInt(cursor.getString(4)));
        bear.sethpcount(Integer.parseInt(cursor.getString(5)));
        bear.setbearattack(Integer.parseInt(cursor.getString(6)));
    }
db.close();
return bear;

to be :-

    if(cursor.moveToFirst) {
        bear.setID(cursor.getInt(cursor.getColumnIndex(myDbHelper.COLUMN_ID)));
        bear.setbearname(cursor.getString(cursor.getColumnIndex(myDbHelper.COLUMN_BEARNAME)));
        bear.setstuffing(cursor.getInt(cursor.getColumnIndex(myDbHelper.COLUMN_STUFFING)));
        bear.setbearhealth(cursor.getInt(cursor.getColumnIndex(myhelper.COLUMN_BEARHEALTH)));
        bear.sethpcost(cursor.getInt(cursor.getColumnIndex(myDbHelper.COLUMN_HEALTHCOST)));
        bear.sethpcount(cursor.getInt(cursor.getColumnIndex(myhelper.COLUMN_HEALTHCOUNT))));
        bear.setbearattack(cursor.getInt(cursor.getColumnIndex(myDbHelper.COLUMN_BEARATTACK))));
    }
    cursor.close(); //<<< ADDED SHOULD ALWAYS CLOSE CURSORS WHEN DONE WITH THEM
    db.close();
    return bear;

so I am not sure if I am creating the database correctly.

The class here at :- Are there any methods that assist with resolving common SQLite issues? , that you can add, includes some methods that can assist with knowing what the database contains.

All you have to do is add the class by copying and pasting the code (from the second answer) and to then use add the following after the line helper = new MyDBHandler(this); :-

    CommonSQLiteUtilities.logDatabaseInfo(helper.getWitableDatabase());

When you run the App, check the Log and it will display DatabaseInformation in the log.

Note the code above is in-principle code and has not been tested, so it may contain simple errors.

thanks! I finally got it up and running, I was also having an issue where it wouldn't find my textview that I had in place even after I did a findbyid, but I fixed that after a while. Thanks for all the help! — Rahevin, May 08 '18 at 05:34

SQLite database not inserting data or finding item(called bear)

1 Answers1