CODEWITHSUNDEEP
androidsqlite
Johan
Johan

Reputation: 35194

SQLite select * query only returning values on first column

public List<User> getAllUsers() {
  List<User> userList = new ArrayList<User>();

  String selectQuery = "SELECT  * FROM " + TABLE_USERS;

  SQLiteDatabase db = this.getWritableDatabase();
  Cursor cursor = db.rawQuery(selectQuery, null);

  if (cursor.moveToFirst()) {
   do {
    User user = new User(cursor.getString(0),cursor.getString(1),cursor.getString(2),cursor.getString(3),cursor.getString(4),cursor.getString(5),cursor.getString(6));

    userList.add(user);
   } while (cursor.moveToNext());
  }

  return userList;
 }

 public void addUser(User user) {
  SQLiteDatabase db = this.getWritableDatabase();

  ContentValues values = new ContentValues();
  values.put(KEY_ID, user._id); // User id
  values.put(KEY_BLUETOOTH_NAME, user._bluetooth_name);
  values.put(KEY_COLOR, user._color);
  values.put(KEY_LAST_LAT, user._last_lat);
  values.put(KEY_LAST_LNG, user._last_lng);
  values.put(KEY_MODEL, user._model);
  values.put(KEY_LAST_SEEN, user._last_seen);

  db.insert(TABLE_USERS, null, values);
  db.close(); 
 }

User class properties

public class User {

String _id;
String _bluetooth_name;
String _color;
String _last_seen;
String _model;
String _last_lng;
String _last_lat;

 public User(String id, String bluetooth_name, String color, String last_seen, String model, String last_lng, String last_lat){
      this._id = id;
      this._bluetooth_name = bluetooth_name;
      this._color = color;
      this._last_seen = last_seen;
      this._model = model;
      this._last_lat = last_lat;
      this._last_lng = last_lng;
 }

}

DatabaseHandler dh = new DatabaseHandler(getApplicationContext());

dh.clearTable(); //clear the table

User user = new User("1","2","3","5","6","7","8"); //add dummy user

dh.addUser(user);

List<User> userlist = dh.getAllUsers(); //all users properties are null, except "id" (which is the first prop, value: "1")

Probably an easy question since this is my first attempt at using SQLite in android:

I'm trying to get all the users that I've added to my users SQLite table. But only the first property, "id", on all my users are set. Can anyone please explain why?

Update:

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

 @Override
 public void onCreate(SQLiteDatabase db) {
  String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "(" +
     KEY_ID + " INTEGER PRIMARY KEY," +
     KEY_BLUETOOTH_NAME + " TEXT," +
     KEY_COLOR + " TEXT," +
     KEY_LAST_LAT + " TEXT," +
     KEY_LAST_LNG + " TEXT," +
     KEY_LAST_SEEN + " TEXT," +
     KEY_MODEL + " TEXT" + ")";

  db.execSQL(CREATE_USERS_TABLE);
 }

Upvotes: 0

Views: 2218

Answers (2)

Moog
Moog

Reputation: 10193

You should be using Cursor.getColumnIndex(String columnName) to return the indices of the column to feed into Cursor.getString(long index).

Make sure you do this after your query but before you loop through your results otherwise it will be rather inefficient.

Also you should use SQLiteDatabase.query(...) rather than raw queries to reduce complexity

UPDATE

You should not specifiy a value for KEY_ID column if you want it to autoincrement without declaring AUTOINCREMENT in the column definition

Upvotes: 1

Vit Khudenko
Vit Khudenko

Reputation: 28418

I can imagine such a scenario: you're developing the DB scheme and at some version the table has only _id column. You run the app, so it creates the DB/table. Then you add all the rest of the columns to the table creation code, but since the table has already been created the code is not executed again, so the table remains unchanged. If this theory is true, then reinstalling the app should fix it.

UPDATE:

I hope KEY_ID is "_id"? If not, please fix. Also the SQL for KEY_ID should be:

KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +

UPDATE2:

Sorry, my UPDATE section above messed up DB id with your business logic User id. So I'd suggest to use KEY_UID ("uid") for business logic User id and KEY_DB_ID ("_id") for DB id.

Upvotes: 1

Related Questions