set flag in sqlite database in android

问题:

In my application i want to set flag for favorites quote.On button click i want to set that flag for that particular row.In my database,for quotes i have taken TEXT data format and for boo lean BOOL.I have managed to retrieve text value from database but I'm not able to set Boolean value in database.As I'm new to this topic any help on this is really appreciated. Below is my code

public class DatabaseHelper extends SQLiteOpenHelper{
    //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/com.t4t.smspopup/databases/";
    private static String DB_NAME = "smspopupplus.sqlite";
    private static SQLiteDatabase myDataBase; 
    private final Context myContext;
    public static String Column_Favorite="0"; 

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, 1);
        this.myContext = context;
    }

    public void createDataBase() throws IOException
    {
        boolean dbExist = checkDataBase();      
        SQLiteDatabase db_Read = null;
        if(dbExist)
        {

        }
        else
        {
            db_Read=this.getReadableDatabase();
            db_Read.close();
            Log.i("debug++++", "databse created");
            try
            {
                copyDataBase();
            }
            catch (IOException e)
            {
                e.printStackTrace();
                throw new Error("Error copying database");
            }
        }
    }

    public boolean checkDataBase()
    {
        SQLiteDatabase checkDB = null;

        try
        {
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
        }
        catch(SQLiteException e)
        {

        }

        if(checkDB != null)
        {
            checkDB.close();
        }

        return checkDB != null ? true : false;
    }

    public void copyDataBase() throws IOException
    {
        InputStream myInput = myContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream myOutput = new FileOutputStream(outFileName);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer))>0)
        {
            myOutput.write(buffer, 0, length);
        }

        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

    public void openDataBase() throws SQLException
    {
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
        Log.i("debug++++", "databse opened");
    }

    public void close()
    {
        if(myDataBase != null)
            myDataBase.close();

        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {

    }

    public boolean updateDB(String status,String what,int value)
    {
        return false;

    }
    //retrive sms text from DB
    public  ArrayList<String> getSMSList(String categoryName)
    {
        ArrayList<String> SMSList = new ArrayList<String>();

        try{
            Cursor cursor=null;


            cursor = myDataBase.query(true,categoryName,null,null,null,null,null,null,null);
            while(cursor.moveToNext())
            {
                //ProgressData progress = new ProgressData(cursor.getString(0),cursor.getString(1));
                String SMSString=cursor.getString(1);
                SMSList.add(SMSString);
                //Log.i("debug++++", ""+progressList);
            }
            cursor.close();

        }catch (Exception e) {
            e.printStackTrace();
        }

        return SMSList;
    }


    @SuppressWarnings("null")
    public void execSql(String sql,Object[] args)
    {
        SQLiteDatabase sqLiteDatabase = null;
        if(args==null)
        {
            sqLiteDatabase.execSQL(sql);
        }
        else

        {
            Log.e("debug -----------", "command execuated");
            sqLiteDatabase.execSQL(sql,args);
        }

    } 

回答1:

SQLite Boolean Datatype:
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

You can convert boolean to int in this way:

int flag = (boolValue==true)? 1:0;
String query = "UPDATE "+ TABLE_NAME + " set "+COLUMN_NAME+" = "+flag + " where "+CONDITION;
myDataBase.exeSQL(query);

You can convert int back to boolean as follows:

 // Select COLUMN_NAME 'flag' values from db. 
 // This will be integer value, you can convert this int value back to Boolean as follows
Boolean flag2 = (intValue==1)?true:false;

回答2:

To change values in a table you need an UPDATE statement. You can check the syntax in http://www.sqlite.org/lang_update.html.

回答3:

If you want to store the boolean data in SQLite database you can store the Boolean values as integers 0 (false) and 1 (true)

回答4:

I always use a TINYINT to save flags in my DB because it needs
the most less space in the DB. Later on i simply switching the state
with the if-shorthand:

post.postFaved = post.postFaved == 1 ? 0 : 1;