Why this query is giving me an empty cursor?_问答_开发者

im doing an android app, and i have a local DB with MyDBAdapter

i insert a lot of positions in my DB with this code (myPositions haves a lot of positions on it)

for (int i=0;i<myPositions.size();i++)
        {
            MyApplication.getDatabaseAdapter().createPosition(myPositions.get(i).getIdposition(),myPositions.get(i).getLatitude(),myPositions.get(i).getLongitude(),myPositions.get(i).getpositiontimeFormated(),myPositions.get(i).getFk_email());
        }

ok, but when i try to get the positions of a user that haves a lot of possitions i get an empty cursor..... here is the code i use to get the cursor with the positions of the user, i call retrievePositionsByEmail, and that function calls fetchPositionsByEmail, but fetchPositionsByEmail returns en EMPTY CURSOR

public Cursor fetchPositionsByEmail(String email) throws SQLException {

    Cursor mCursor = mDb.query(true, "position", new String[] {"idposition", "latitude", "longitude", "timestamp", "fk_email"}
        , "fk_email" + "='" + email+"'", null, null, null, null, null);

    //Cursor mCursor = mDb.query(true, "position", new String[] {"idposition", "latitude", "longitude", "timestamp", "fk_email"}
    //, "fk_email=?", new String[] { email } , null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}


public List<Position> retrievePositionsByEmail(String email) 
{
    List <Position> positions=new ArrayList<Position>();
    Cursor result=fetchPositionsByEmail(email);
    if( result.moveToFirst() ){
        do{
            positions.add(new Position(result.getString(result.getColumnIndexOrThrow("idposition")),
                    result.getString(result.getColumnIndexOrThrow("latitude")), 
                    result.getString(result.getColumnIndexOrThrow("longitude")),
                    result.getString(result.getColumnIndexOrThrow("timestamp")),
                    result.getString(result.getColumnIndexOrThrow("fk_email"))));
        }while( result.moveToNext() );
    }
    return positions;
}

and here is the full code of my local DB adapter:

public class MyDbAdapter {

    private static final String TAG = "NotesDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private static final String DATABASE_NAME = "gpslocdb";
    private static final String PERMISSION_TABLE_CREATE = "CREATE TABLE permission ( fk_email1 varchar, fk_email2 varchar, validated tinyint, hour1 time default '08:00:00', hour2 time default '20:00:00', date1 date, date2 date, weekend tinyint default '0', fk_type varchar, PRIMARY KEY  (fk_email1,fk_email2))";
    private static final String USER_TABLE_CREATE = "CREATE TABLE user ( email varchar, password varchar, fullName varchar, mobilePhone varchar, mobileOperatingSystem varchar, PRIMARY KEY  (email))";
    private static final String POSITION_TABLE_CREATE = "CREATE TABLE position (idposition bigint, latitude varchar, longitude 开发者_如何学Cvarchar, timestamp datetime, fk_email varchar, PRIMARY KEY (idposition))";


    private static final int DATABASE_VERSION = 2;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

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

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("DROP TABLE IF EXISTS user");
            db.execSQL("DROP TABLE IF EXISTS permission");
            db.execSQL("DROP TABLE IF EXISTS position");
            db.execSQL(PERMISSION_TABLE_CREATE);
            db.execSQL(USER_TABLE_CREATE);
            db.execSQL(POSITION_TABLE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            //db.execSQL("DROP TABLE IF EXISTS user");
            //db.execSQL("DROP TABLE IF EXISTS permission");
            //onCreate(db);
        }

        @Override
        public void onOpen(SQLiteDatabase db) {
            super.onOpen(db);
            //onCreate(db);
        }
        public void clearDb(SQLiteDatabase db){
            db.execSQL("DROP TABLE IF EXISTS user");
            db.execSQL("DROP TABLE IF EXISTS permission");
            db.execSQL("DROP TABLE IF EXISTS position");
            onCreate(db);
        }
        public void deleteUser(SQLiteDatabase db, String email)
        {
            db.execSQL("DELETE FROM user Where email='"+email+"'");
        }
        public void deletePermission(SQLiteDatabase db, String email)
        {
            db.execSQL("DELETE FROM permission Where fk_email2='"+email+"'");
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public MyDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public MyDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        //clearDB();
        mDbHelper.close();
    }

    public long createUser(String email, String password, String fullName, String mobilePhone, String mobileOperatingSystem) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("email",email);
        initialValues.put("password",password);
        initialValues.put("fullName",fullName);
        initialValues.put("mobilePhone",mobilePhone);
        initialValues.put("mobileOperatingSystem",mobileOperatingSystem);
        return mDb.insert("user", null, initialValues);
    }
    public long createPermission(String email1, String email2, String validated, String hour1, String hour2,
            String date1, String date2, String weekend, String fk_type) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("fk_email1",email1);
        initialValues.put("fk_email2",email2);
        initialValues.put("validated",validated);
        initialValues.put("hour1",hour1);
        initialValues.put("hour2",hour2);
        initialValues.put("date1",date1);
        initialValues.put("date2",date2);
        initialValues.put("weekend",weekend);
        initialValues.put("fk_type",fk_type);
        return mDb.insert("permission", null, initialValues);
    }
    public long createPosition(String idposition, String latitude, String longitude, String timestamp, String fk_email) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("idposition",idposition);
        initialValues.put("latitude",latitude);
        initialValues.put("longitude",longitude);
        initialValues.put("timestamp",timestamp);
        initialValues.put("fk_email",fk_email);
        return mDb.insert("position", null, initialValues);
    }



    public void clearDB() {
        mDbHelper.clearDb(mDb);        
    }

    public Cursor fetchAllUsers() {

        return mDb.query("user", new String[] {"email", "password", "fullName", "mobilePhone", "mobileOperatingSystem"}, null, null, null, null, null);
    }
    public Cursor fetchAllPermissions() {

        return mDb.query("permission", new String[] {"fk_email1", "fk_email2", "validated", "hour1", "hour2", "date1", "date2", "weekend", "fk_type"}, null, null, null, null, null);
    }

    public Cursor fetchUser(String email) throws SQLException {

        Cursor mCursor = mDb.query(true, "user", new String[] {"email", "password", "fullName", "mobilePhone", "mobileOperatingSystem"}
            , "email" + "=" + email, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public Cursor fetchPositionsByEmail(String email) throws SQLException {

        Cursor mCursor = mDb.query(true, "position", new String[] {"idposition", "latitude", "longitude", "timestamp", "fk_email"}
            , "fk_email" + "='" + email+"'", null, null, null, null, null);

        //Cursor mCursor = mDb.query(true, "position", new String[] {"idposition", "latitude", "longitude", "timestamp", "fk_email"}
        //, "fk_email=?", new String[] { email } , null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public List<Friend> retrieveAllFriends() 
    {
        List <Friend> friends=new ArrayList<Friend>();
        Cursor result=fetchAllUsers();
        if( result.moveToFirst() ){
            do{
                friends.add(new Friend(result.getString(result.getColumnIndexOrThrow("email")),result.getString(result.getColumnIndexOrThrow("password")), result.getString(result.getColumnIndexOrThrow("fullName")),result.getString(result.getColumnIndexOrThrow("mobilePhone")),result.getString(result.getColumnIndexOrThrow("mobileOperatingSystem"))));
            }while( result.moveToNext() );
        }
        return friends;
    }
    public List<Permission> retrieveAllPermissions() 
    {
        List <Permission> permissions=new ArrayList<Permission>();
        Cursor result=fetchAllPermissions();
        if( result.moveToFirst() ){
            do{
                permissions.add(new Permission(result.getString(result.getColumnIndexOrThrow("fk_email1")),
                        result.getString(result.getColumnIndexOrThrow("fk_email2")), 
                        Integer.parseInt(result.getString(result.getColumnIndexOrThrow("validated"))),
                        result.getString(result.getColumnIndexOrThrow("hour1")),
                        result.getString(result.getColumnIndexOrThrow("hour2")),
                        result.getString(result.getColumnIndexOrThrow("date1")),
                        result.getString(result.getColumnIndexOrThrow("date2")),
                        Integer.parseInt(result.getString(result.getColumnIndexOrThrow("weekend"))),
                        result.getString(result.getColumnIndexOrThrow("fk_type"))));
            }while( result.moveToNext() );
        }
        return permissions;
    }

    public List<Position> retrievePositionsByEmail(String email) 
    {
        List <Position> positions=new ArrayList<Position>();
        Cursor result=fetchPositionsByEmail(email);
        if( result.moveToFirst() ){
            do{
                positions.add(new Position(result.getString(result.getColumnIndexOrThrow("idposition")),
                        result.getString(result.getColumnIndexOrThrow("latitude")), 
                        result.getString(result.getColumnIndexOrThrow("longitude")),
                        result.getString(result.getColumnIndexOrThrow("timestamp")),
                        result.getString(result.getColumnIndexOrThrow("fk_email"))));
            }while( result.moveToNext() );
        }
        return positions;
    }


    public void deleteUser(String email) throws SQLException
    {
        mDbHelper.deleteUser(mDb, email);
    }
    public void deletePermission(String email) throws SQLException
    {
        mDbHelper.deletePermission(mDb, email);
    }

}

The problem, I think, is in using:

"fk_email" + "='" + email+"'"

for the selection args. The email string probably has characters that need to be escaped to be legal SQL syntax. See if this works any better:

StringBuilder sb = new StringBuilder("fk_email=");
DatabaseUtils.appendEscapedSQLString(sb, email);
Cursor mCursor = mDb.query(
    true,
    "position",
    new String[] {"idposition", "latitude", "longitude", "timestamp", "fk_email"},
    sb.toString(),
    null, null, null, null, null);