lundi 20 avril 2015

Query in Sqlite Android

I have a sqlite table for my android app. It has 4 fields , Id, Name, ParseId, Mobile. Here, the Id is an int that increments for every new user. The ParseId is however a string.

I want to make a query to my db by passing the ParseId. And I want to get the Mobile and Name fields of the User whose ParseId I supply to my query.

public class DatabaseHandlerLocal extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "parseUsers";

    // Contacts table name
    private static final String TABLE_PARSE = "parse_users";

    // Contacts Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_MOBILE = "mobile";
    private static final String KEY_PARSE_USERID = "parseUserId";
    private static final String KEY_PARSE_USERNAME = "parseUserName";

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

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_PARSE_TABLE = "CREATE TABLE " + TABLE_PARSE + "("+
        KEY_ID + " INTEGER PRIMARY KEY," + KEY_MOBILE + " TEXT," + KEY_PARSE_USERID + " TEXT,"
                + KEY_PARSE_USERNAME + " TEXT" + ")";
        db.execSQL(CREATE_PARSE_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PARSE);

        // Create tables again
        onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new contact
    public void addParseUser(Users users) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ID, users.getId());
        values.put(KEY_PARSE_USERID, users.getParseUserObjId());
        values.put(KEY_PARSE_USERNAME, users.getParseUserName());
         values.put(KEY_MOBILE, users.getMobile());

        // Inserting Row
        db.insert(TABLE_PARSE, null, values);
        db.close(); // Closing database connection
    }

    // Getting single contact
    public Users getUser(String id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_PARSE, new String[] { KEY_ID,
                        KEY_PARSE_USERNAME, KEY_PARSE_USERID,KEY_MOBILE }, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Users user = new Users(cursor.getString(0),
                cursor.getString(1), cursor.getString(2),cursor.getString(3));
        // return contact
        return user;
    }

    // Getting All Contacts
    public List<Users> getAllUsers() {
        List<Users> userList = new ArrayList<Users>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_PARSE;

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

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Users user = new Users();
                user.setId(cursor.getString(0));
                user.setParseUserName(cursor.getString(1));
                user.setParseUserObjId(cursor.getString(2));
                user.setMobile(cursor.getString(3));
                // Adding contact to list
                userList.add(user);
            } while (cursor.moveToNext());
        }

        // return contact list
        return userList;
    }

    // Updating single contact
    public int updateUser(Users user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_PARSE_USERID, user.getParseUserObjId());
        values.put(KEY_PARSE_USERNAME, user.getParseUserName());
        values.put(KEY_MOBILE, user.getMobile());

        // updating row
        return db.update(TABLE_PARSE, values, KEY_ID + " = ?",
                new String[] { user.getId() });
    }

    // Deleting single contact
    public void deleteContact(Users user) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_PARSE, KEY_ID + " = ?",
                new String[]{String.valueOf(user.getId())});
        db.close();
    }


    // Getting contacts Count
    public int getUserCount() {
        String countQuery = "SELECT  * FROM " + TABLE_PARSE;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

    public boolean hasObject(String id) {
        SQLiteDatabase db = getWritableDatabase();
        String selectString = "SELECT * FROM " + TABLE_PARSE
                + " WHERE " + KEY_PARSE_USERID + " =? ";
        Cursor cursor = db.rawQuery(selectString, new String[] {id}); //add the String your searching by here

        boolean hasObject = false;
        if(cursor.moveToFirst()){
            hasObject = true;

            //if you had multiple records to check for, use next 6 lines.
            int count = 0;
            while(cursor.moveToNext()){
                count++;
            }
            //here, count is records found
            Log.d("dbdbdbdbdbdbdb", String.format("%d records found", count));
        }

        cursor.close();          // Don't forget to close your cursor
        db.close();              //AND your Database!
        return hasObject;
    }

    public String getName(String id){

        SQLiteDatabase db = getWritableDatabase();

        String selectString = "SELECT * FROM " + TABLE_PARSE
                + " WHERE " + KEY_PARSE_USERID + " =? ";

        Cursor cursor = db.rawQuery(selectString, new String[] {id});

        String name = cursor.getString(cursor.getColumnIndexOrThrow(KEY_PARSE_USERNAME));
        cursor.close();          // Don't forget to close your cursor
        db.close();


        return name;


    }
    /*public Cursor getNameValues(int index) {

        SQLiteDatabase db = getWritableDatabase();

        String from[] = { KEY_PARSE_USERNAME, KEY_MOBILE };

        String where =  + "=" + index;
        Cursor cursor = db.query(true, WordsDB.TermTable, from, where, null, null, null, null, null);
        return cursor;
    }

    public Cursor getTermValues(int index) {

        String from[] = { "Term", "Type" };
        String where = WordsDB.ID + "=?";
        String[] whereArgs = new String[]{index+""};
        Cursor cursor = db.query(WordsDB.TermTable, from, where, whereArgs, null, null, null, null);
        return cursor;
    }*/

}

I tried the getName() method above, also some methods that are commented but didn't worked out. My table is getting the data i am giving, which I monitored in my log.

Aucun commentaire:

Enregistrer un commentaire