Android insert/update/delete SQLite queries

Question

I'm trying to write three different methods for inserting, updating and deleting data in SQLite Database in Android. So far I can insert data in the database, but I can't understand how to add the where clause in SQL. Here is the code I'm using :

The update methods:

public boolean updateSQL(String tableName,String key,String value){
    return updateData(tableName,key,value);
}

private static boolean updateData(String tableName,String key,String value){
    sqliteDb = instance.getWritableDatabase();
    String where = "id=?";
    ContentValues values = new ContentValues();
    values.put(key, value);
    values.put(key, value);
    sqliteDb.update(tableName, values, where, null);
    return true;
}

... and I'm invoking this method like this:

dbHelper.updateSQL("saved_codes", "code_id", "3");
//dbHelper is an instance to a custom DatabaseHelper class.

.. and the delete methods:

public boolean deleteSQL(String tableName,String key,String value){
    return deleteData(tableName,key,value);
}

private static boolean deleteData(String tableName,String key,String value) {
    sqliteDb = instance.getWritableDatabase();
    ContentValues values = new ContentValues();
    String where = null;
    String[] whereArgs = null;
    values.put(key, value);
    values.put(key, value);
    sqliteDb.delete(tableName, where, whereArgs);
    return true;
}

I know that the Strings where and whereArgs are null, but actually I can't understand how to add them. I don't expect someone to write the code for me, but some good advice, suggestions or even samples from the internet are welcome.

Answer 1

For Update

    public boolean updaterow(String name)
    {
     SQLitedatabase db=this.getwriteabledatabase();
     Contentvalue value=new Contentvalue();
     value.put("name","paresh");
     
     long result=db.update(databasename,value,"name="+name,null)
        if(result == -1)
        return false,
        else
       return true;
     }
    
    

For Insert-data

    public boolean insertrow(String name)
    {
     SQLitedatabase db=this.getWriteabledatabase();
     Contentvalue value=new Contentvalue();
     value.put("name",name);
     
     long result=db.insert(databasename,null,value)
        if(result == -1)
        return false,
        else
       return true;
     }
    

For Get-Data

    public Cursor getalldata()
    {
     SQLitedatabase db=this.getWriteabledatabase();
    Cursor res=db.rawQuery("select * from"+databasename,null)
       return true;
 
    

For Delete One-Row

    public void deleteonerow(int id)
    {
     SQLitedatabase db=this.getWriteabledatabase();
    db.exeSQL(db.rawQuery("DELETE FROM"+databasename+"where id+"="+id);)
    db.close();
 
  

Answer 2

first of all you need to create connetion api class for connect with database

public class DatabaseconectionApiDemo extends SQLiteOpenHelper {

    private final Context myContext;
    ArrayList<String> labels1;
    //  StaticValues mStaticValues;
    private static SQLiteDatabase db;

    @SuppressLint("SdCardPath")
    public final static String DB_PATH = "/data/data/com....youpackagename..../databases/";
    public final static String DB_NAME = "DataBaseName.sqlite";


    public DatabaseconectionApiDemo(Context context) {
        super(context, DB_NAME, null, 1);
        this.myContext = context;
    }
    public void createDataBase() throws IOException {
        boolean dbExist = checkDataBase();

        if (dbExist) {
            // do nothing
        } else {
            // By calling this method and empty database will be created into the default system path
            // of your application so we are gonna be able to overwrite that database with our database.
            this.getReadableDatabase();
            try {
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }

    private void copyDataBase() throws IOException{
        InputStream myInput = myContext.getAssets().open(DB_NAME);

        // Path to the just created empty db
        String outFileName = DB_PATH + DB_NAME;

        // Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);

        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[2048];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }

        // Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

    private boolean checkDataBase() {
        SQLiteDatabase checkDB = null;
        try {
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
        } catch (SQLiteException e) {
            // database does't exist yet.
        }

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

        return checkDB != null ? true : false;
    }

    public void openDataBase() throws SQLException {
        try {
            if (db != null) {
                if (db.isOpen()) {
                    db.close();
                }
            }

        } catch (Exception e) {
            System.out.println("no database connected to close");
        }
        // Open the database
        String myPath = DB_PATH + DB_NAME;
        db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    @Override
    public synchronized void close() {
        if (db != null)
            db.close();
        super.close();
    }
    public int Update(String TableName, String cols[], String values[], String whereClause, String whereArgs[]) {
        int id = 0;
        openDataBase();
        id = db.update(TableName, getContentValues(cols, values), whereClause, whereArgs);
        close();

        return id;

    }

    public int insertwithreturnid(String TableName, String cols[], String values[]) {
        int id = 0;
        openDataBase();

        id = (int) db.insert(TableName, null, getContentValues(cols, values));
        close();
        return id;
    }
    public int delete(String TableName, String where, String whereArgs[]) {
        int id = 0;
        openDataBase();
        id = db.delete(TableName, where, whereArgs);
        close();
        return id;
    }


    public DataHolder readFromTableName(String TableName, String cols[], String where[], String keyword) {

        openDataBase();
        DataHolder _holder = null;

        Cursor c = null;    

        c = db.query(TableName, cols, where[0], null, null, null, null);

        if (c != null) {
            c.moveToFirst();
            _holder = new DataHolder();
            while (!c.isAfterLast()) {
                int count = c.getColumnCount();
                _holder.CreateRow();
                for (int i = 0; i < count; i++) {
                    _holder.setmColoumn(c.getColumnName(i), c.getString(i));
                }
                _holder.AddRow();
                c.moveToNext();
            }
        }
        c.close();
        close();
        return _holder;
    }

    public void exeQuery(String sql) {
        openDataBase();
        try {
            db.execSQL(sql);
        } catch (Exception e) {
            System.out.println(e.toString());
        }
        close();
    }

    public boolean InsertByValue(String table, ContentValues values) {
        try {
            openDataBase();
            db.insertOrThrow(table, null, values);
            Log.d("InsertByValue", "Data Insert");
            return true;
        } catch (Exception e) {
            Log.d("InsertByValue", e.toString());
            e.printStackTrace();
            return false;
        }
    }

    public DataHolder read(String sql) {

        openDataBase();
        DataHolder _holder = null;

        Cursor c = db.rawQuery(sql, null);

        if (c != null) {
            c.moveToFirst();
            _holder = new DataHolder();

            while (!c.isAfterLast()) {

                int count = c.getColumnCount();

                _holder.CreateRow();

                for (int i = 0; i < count; i++) {
                    _holder.setmColoumn(c.getColumnName(i), c.getString(i));
                }
                _holder.AddRow();
                c.moveToNext();
            }
        }
        c.close();
        close();
        return _holder;
    }

    public ContentValues getContentValues(String cols[], String values[]) {

        ContentValues cv = new ContentValues();
        for (int i = 0; i < cols.length; i++) {
            cv.put(cols[i], values[i]);

        }
        return cv;
    }
    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }

}

and you need to add data holder class for read you data from

public class DataHolder {

    private ArrayList<LinkedHashMap<String, String>> mRow;
    private LinkedHashMap<String, String> mColoumn; 

    public DataHolder() {
        super();
        mRow = new ArrayList<LinkedHashMap<String, String>>();
    }   

    public void setmColoumn(String col, String value) {
        this.mColoumn.put(col, value);
    }

    public ArrayList<LinkedHashMap<String, String>> getmRow() {
        return mRow;
    }

    public void CreateRow() {
        this.mColoumn = new LinkedHashMap<String, String>();

    }

    public void AddRow() {
        this.mRow.add(this.mColoumn);
    }

    public void clear() {
        this.mRow.clear();
    }

    public void add(LinkedHashMap<String, String> linkedHashMap) {
        this.mRow.add(linkedHashMap);
    }
}

and finnaly you can read,insert,update and delete you database table data by following queries

public class YourActivity extends Activity{

private DataHolder mDataHolder;
private DatabaseconectionApi mDatabaseconectionApi;



@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.your_layout);


 //   initialize dataholder and connectionapi class like bellow
    mDataHolder=new DataHolder();
    mDatabaseconectionApi=new DatabaseconectionApi(YourActivity.this);
    try {
        mDatabaseconectionApi.createDataBase();
        mDatabaseconectionApi.openDataBase();
    }catch (IOException e) {
        e.printStackTrace();
    }


 ...........

//now you can perform insert,update,read and delete like these

mDataHolder = new DataHolder();
    mDataHolder = mDatabaseconectionApi.read("SELECT * from table_name where "+table_id
            +" = '"+TId+"'");

mDatabaseconectionApi.insertwithreturnid("table_name", 
                                        new String[]{"column_name"},
                                        new String[]{"column_value"});
                                    mDatabaseconectionApi.Update("table_name",
                                        new String[]{"column_name"}, new String[]{"column_value"},
                                        "column_id"+" = ?", new String[]{"column_id_value"});
                                    mDatabaseconectionApi.delete("table_name", null, null);
//                                    or
                                    mDatabaseconectionApi.delete("table_name", "column_id"+" = ?", new String[]{"column_id_value"});

Answer 3

public class Database {

    public static final String DATABASE_NAME="bookdb";

    public static final int DATABASE_VERSION=1;

    public static final String TABLE_NAME="tbbook";

    public static final String ISDN="isdn";

    public static final String TITLE="title";;

    public static final String AUTHOR="author";

    public static final String PRICE="price";

    public static final String TABLE_CREATE="create table tbbook(isdn INT,title TEXT,author TEXT,price FLOAT);";

    Context ctx;
    SQLiteDatabase db;
    DatabaseHelper dbhelper;

    public Database(Context ctx){
        this.ctx=ctx;
        dbhelper=new DatabaseHelper(ctx);
    }

    class DatabaseHelper extends SQLiteOpenHelper{

        public DatabaseHelper(Context ctx) {
            super(ctx,DATABASE_NAME, null, DATABASE_VERSION);
            // TODO Auto-generated constructor stub
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub

            db.execSQL(TABLE_CREATE);
            Toast.makeText(Database.this.ctx,"Database is created",Toast.LENGTH_LONG).show();
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            db.execSQL("DROP TABLE IF EXIST");
            onCreate(db);
        }

    }

    public long insertData(int bisdn,String btitle,String bauthor,float bprice){

        ContentValues intialvalues=new ContentValues();

        intialvalues.put(ISDN, bisdn);
        intialvalues.put(TITLE, btitle);
        intialvalues.put(AUTHOR, bauthor);
        intialvalues.put(PRICE,bprice);

        Log.d("isdn==",bisdn+"");
        Log.d("title==",btitle+"");
        Log.d("author==",bauthor+"");
        Log.d("price==",bprice+"");

        Toast.makeText(ctx,"values inserted",Toast.LENGTH_SHORT).show();




        return db.insert(TABLE_NAME, null,intialvalues);

    }

    public Database open(){

        db=dbhelper.getWritableDatabase();
        return this;

    }
    public void close(){
        dbhelper.close();
    }

    public Cursor getAllData(){

        String query="select * from tbbook";
        Log.d("query==",query);
        Cursor cur=db.rawQuery(query, null);

        return cur;

    }

    public void updateData(String bisdn,String btitle,String bauthor,float bprice){

        ContentValues intialvalues=new ContentValues();


        intialvalues.put(TITLE, btitle);
        intialvalues.put(AUTHOR, bauthor);
        intialvalues.put(PRICE,bprice);


        Log.d("title==",btitle+"");
        Log.d("author==",bauthor+"");
        Log.d("price==",bprice+"");



        db.update(TABLE_NAME,intialvalues,ISDN+" = ? ",new String[]{bisdn});

        Log.d("values","updated");

        Toast.makeText(ctx,"values updated",Toast.LENGTH_SHORT).show();

    }

    public void DeleteData(String bisdn)
    {

        db.delete(TABLE_NAME,ISDN+"=?",new String[]{bisdn});

        Log.d("values","deleted");
        Toast.makeText(ctx,"delete value",Toast.LENGTH_SHORT).show();
    }

    public void DeleteAllData()
    {

        db.delete(TABLE_NAME,null,null);

        Log.d("all","deleted");
        Toast.makeText(ctx,"all record deleted",Toast.LENGTH_SHORT).show();
    }

}

//////how to call every method/////

public void load_custom_view(){

    try {
        detail=new ArrayList<BookPojo>();
        database=new Database(CustomViewActivity.this);
        database.open();
        Cursor c=database.getAllData();
        if(c.moveToFirst())
        {
            do{
                detail.add(new BookPojo(c.getInt(0),c.getString(1), c.getString(2),c.getFloat(3)));

            }while(c.moveToNext());
        }
        c.close();
        database.close();
        messagelist.setAdapter(new CustomAdapter(detail,CustomViewActivity.this));




    } catch (Exception e) {
        // TODO: handle exception
    }

}


btnsubmit.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub

             bisdn=edtisdn.getText().toString();
             btitle=edttitle.getText().toString();
             bauthor=edtauthor.getText().toString();
             bprice=edtprice.getText().toString();

            /*Database databse=new Database(InsertActivity.this);
            databse.open();
            databse.insertData(bisdn,btitle,bauthor,bprice);
            databse.close();*/


        }
});


public void create_dialog() {

    builder = new AlertDialog.Builder(this);
    builder.setTitle("Warning!");
    builder.setMessage("Are you sure to delete isdn="+bisdn);
    builder.setIcon(R.drawable.ic_launcher);
    builder.setCancelable(false);

    builder.setPositiveButton("Yes", new DialogInterface.OnClickListener() {

        @Override
        public void onClick(DialogInterface dialog, int which) {
            // TODO Auto-generated method stub

            Database database = new Database(VUDActivity.this);
            database.open();
            database.DeleteData(bisdn);
            database.close();


        }
    });
    builder.setNegativeButton("No", new DialogInterface.OnClickListener() {

        @Override
        public void onClick(DialogInterface dialog, int which) {
            // TODO Auto-generated method stub

            Toast.makeText(getApplicationContext(), "Not deleted",
                    Toast.LENGTH_LONG).show();

        }
    });
    builder.show();

}

Answer 4

You need whereArgs for

String where = "id=?";

something like :

sqliteDb.update(tableName, values, where, new String[] {"42"});

where 42 would be the _id of the row to update. Also prefer BaseColumns._ID to "id".