CODEWITHSUNDEEP
javaandroidsqlite
hrmck
hrmck

Reputation: 93

Android SQLite array is empty

I got a question:
Values from ShopName if LocationCode is equal to a string called SKM will be inserted into an array called ShopName.

However, the array is empty, why?

DatabaseAccess.java

public class DatabaseAccess {
private SQLiteOpenHelper openHelper;
private SQLiteDatabase database;
private static DatabaseAccess instance;

/**
 * Private constructor to aboid object creation from outside classes.
 *
 * @param context
 */
public DatabaseAccess(Context context) {
    this.openHelper = new DatabaseOpenHelper(context);
}

/**
 * Return a singleton instance of DatabaseAccess.
 *
 * @param context the Context
 * @return the instance of DabaseAccess
 */
public static DatabaseAccess getInstance(DatabaseGrabber context) {
    if (instance == null) {
        instance = new DatabaseAccess(context);
    }
    return instance;
}

/**
 * Open the database connection.
 */
public void open() {
    this.database = openHelper.getWritableDatabase();
}

/**
 * Close the database connection.
 */
public void close() {
    if (database != null) {
        this.database.close();
    }
}

/**
 * Read all quotes from the database.
 *
 * @return a List of quotes
 */
public List<String> getQuotes() {
    String WhiteFarm = "SKM";
    List<String> list = new ArrayList<>();
    Cursor cursor = database.rawQuery("SELECT * FROM WhereToEat WHERE 'LocationCode' = " + "'" + WhiteFarm + "'", null);
    cursor.moveToFirst();
    if (cursor.moveToFirst()) {
        do {
            // do what you need with the cursor here
            list.add(cursor.getString(0));
        } while (cursor.moveToNext());
    }
    cursor.close();
    return list;
}
}

DatabaseGrabber.java

public class DatabaseGrabber extends ActionBarActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.selection_main);

    final TextView Shop_name = (TextView)findViewById(R.id.ShopName);

    DatabaseAccess databaseAccess = DatabaseAccess.getInstance(this);
    databaseAccess.open();
    final List<String> ShopName = databaseAccess.getQuotes();
    databaseAccess.close();

    Button StartDraw = (Button)findViewById(R.id.draw_start);
    StartDraw.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            if (ShopName.size()>0) {
                Collections.shuffle(ShopName);
                String random = ShopName.get(0);
                Shop_name.setText(random);
            }
            else{
                Shop_name.setText("Nothing here!");
            }
        }
    });
}
}

DatabaseOpenHelper.java

public class DatabaseOpenHelper extends SQLiteAssetHelper {
private static final String DATABASE_NAME = "WhereToEat";
private static final int DATABASE_VERSION = 1;

public DatabaseOpenHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
}

And this is the sql file

BEGIN TRANSACTION;
CREATE TABLE "WhereToEat" (
`ShopName`  TEXT,
`Location`  TEXT,
`LocationCode`  TEXT,
`Photo` BLOB
);
INSERT INTO `WhereToEat` VALUES ('KFC','Shek Kip Mei','WhiteFarm',NULL);
INSERT INTO `WhereToEat` VALUES ('McDonalds','Lai Chi Kok','SKM',NULL);
INSERT INTO `WhereToEat` VALUES ('SomethingBig','Nam Cheong','SKM',NULL);
INSERT INTO `WhereToEat` VALUES ('Subway','Tsuen Wan','SKM',NULL);
COMMIT;

Thank you.

Upvotes: 0

Views: 276

Answers (1)

Snild Dolkow
Snild Dolkow

Reputation: 6876

You have quotes around the column name in your WHERE clause. For each potential result row, you're checking whether 'LocationCode' = 'SKM', which is obviously always false.

Remove the quotes around the column name, and I expect you'll be fine.

While you're at it, clean up your code by using a placeholder (?) for the string value:

Cursor cursor = database.rawQuery(
        "SELECT * FROM WhereToEat WHERE LocationCode = ?",
        new String[]{WhiteFarm});

Upvotes: 1

Related Questions