Cursor cursor = getReadableDatabase(). rawQuery("select * from todo where _id = ?", new String[] { id });
query() :
How to use : query()
return database.query(DATABASE_TABLE,
new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION },
null, null, null, null, null);
Cursor :
A
query
returns a
Cursor
object. A Cursor represents the result of a query and basically
points
to one row of the query result. This way Android can buffer the
query results efficiently; as it does not have to load all data into
memory.
To get the number of
elements of the resulting query use the
getCount()
method.
To move between individual data
rows, you can use the
moveToFirst()
and
moveToNext()
methods. isAfterLast()
method allows to check if the end of the query result has been
reached.
Cursor
provides typed
get*()
methods, e.g.
getLong(columnIndex),
getString(columnIndex)
to access the column data for the current position of the result.
The
"columnIndex" is the number of the column you are
accessing.
Cursor
also provides the
getColumnIndexOrThrow(String)
method which allows to get the column index for a column name of the
table.
A
Cursor
needs to be closed with the
close()
method call.
Writing CreateDataBase Class
Before you go further you need to write your CreateDataBase class where you create you database,table & all table fields.
Now extend your CreateDataBase.java class from SQLiteOpenHelper.
public class CreateDataBase extends SQLiteOpenHelper {
After extending your class from SQLiteOpenHelper you need to override two methods onCreate() and onUpgrage()
onCreate() – These is where we need to write create table statements. This is called when database is created.
onUpgrade() – This method is called when database is upgraded like modifying the table structure, adding constraints to database etc.
CreateDataBase.java
public class CreateDataBase extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "ROBI_DB";
public static final String TABLE_NAME = "ROBI_TABLE";
public static final int VERSION = 1;
public static final String KEY_ID = "_id";
public static final String FNAME = "F_NAME";
public static final String SCRIPT = "create table " + TABLE_NAME + " ("
+ KEY_ID + " integer primary key autoincrement, " + FNAME
+ " text not null);";
public CreateDataBase(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(SCRIPT);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("drop table " + TABLE_NAME);
onCreate(db);
}}
All CRUD Operations (Create, Read, Update and Delete)
Now we need to write methods for handling all database read and write operations.
Here we are implementing following methods inside the DataBaseAdapter class for our
table.
DataBaseAdapter.java
public class DataBaseAdapter {
SQLiteDatabase database_ob;
CreateDataBase openHelper_ob;
Context context;
public DataBaseAdapter(Context c) {
context = c;
}
public DataBaseAdapter opnToRead() {
openHelper_ob = new CreateDataBase(context,
openHelper_ob.DATABASE_NAME, null, openHelper_ob.VERSION);
database_ob = openHelper_ob.getReadableDatabase();
return this;
}
public DataBaseAdapter opnToWrite() {
openHelper_ob = new CreateDataBase(context,
openHelper_ob.DATABASE_NAME, null, openHelper_ob.VERSION);
database_ob = openHelper_ob.getWritableDatabase();
return this;
}
public void Close() {
database_ob.close();
}
public long insertDetails(String fname) {
ContentValues contentValues = new ContentValues();
contentValues.put(openHelper_ob.FNAME, fname);
Log.d("hello",fname);
opnToWrite();
long val = database_ob.insert(openHelper_ob.TABLE_NAME, null,
contentValues);
Close();
return val;
}
public Cursor queryName() {
String[] cols = { openHelper_ob.KEY_ID, openHelper_ob.FNAME};
opnToWrite();
Cursor c = database_ob.query(openHelper_ob.TABLE_NAME, cols, null,
null, null, null, null);
return c;
}
public Cursor queryAll(int nameId) {
String[] cols = { openHelper_ob.KEY_ID, openHelper_ob.FNAME};
opnToWrite();
Cursor c = database_ob.query(openHelper_ob.TABLE_NAME, cols,
openHelper_ob.KEY_ID + "=" + nameId, null, null, null, null);
return c;
}
public long updateldetail(int rowId, String fname) {
ContentValues contentValues = new ContentValues();
contentValues.put(openHelper_ob.FNAME, fname);
opnToWrite();
long val = database_ob.update(openHelper_ob.TABLE_NAME, contentValues,
openHelper_ob.KEY_ID + "=" + rowId, null);
Intent send=new Intent(context,DataBase_Item_ListActivity.class);
context.startActivity(send);
Close();
return val;
}
public int deletOneRecord(int rowId) {
// TODO Auto-generated method stub
opnToWrite();
int val = database_ob.delete(openHelper_ob.TABLE_NAME,
openHelper_ob.KEY_ID + "=" + rowId, null);
Close();
Intent send=new Intent(context,DataBase_Item_ListActivity.class);
context.startActivity(send);
return val;
}}
SplashActivity.java
public class SplashActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.splash);
if (getIntent().getBooleanExtra("EXIT", false)) {
finish();
return;
}
new Handler().postDelayed(new Runnable() {
@Override
public void run() {
Intent send = new Intent(getApplicationContext(),
MainActivity.class);
startActivity(send);
}
}, 4000);}}
DataBase_Item_ListActivity.java
public class DataBase_Item_ListActivity extends AppCompatActivity {
DataBaseAdapter adapter_ob;
CreateDataBase helper_ob;
SQLiteDatabase db_ob;
ListView nameList;
Button registerBtn;
Cursor cursor;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
nameList = (ListView) findViewById(R.id.lv_name);
registerBtn = (Button) findViewById(R.id.btn_register);
adapter_ob = new DataBaseAdapter(this);
String[] from = { helper_ob.FNAME};
int[] to = { R.id.tv_fname};
cursor = adapter_ob.queryName();
SimpleCursorAdapter cursorAdapter = new SimpleCursorAdapter(this,
R.layout.row, cursor, from, to,0);
nameList.setAdapter(cursorAdapter);
if (cursorAdapter== null || cursorAdapter.isEmpty()) {
Toast.makeText(getApplicationContext(),"DataBase is empty, Please add some value to DataBase",Toast.LENGTH_LONG).show();
}
nameList.setOnItemClickListener(new OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
long arg3) {
// TODO Auto-generated method stub
Bundle passdata = new Bundle();
Cursor listCursor = (Cursor) arg0.getItemAtPosition(arg2);
int nameId = listCursor.getInt(listCursor
.getColumnIndex(helper_ob.KEY_ID));
passdata.putInt("keyid", nameId);
Intent passIntent = new Intent(DataBase_Item_ListActivity.this,
UpdateRecordActivity.class);
passIntent.putExtras(passdata);
startActivity(passIntent);
}
});
registerBtn.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
Intent registerIntent = new Intent(DataBase_Item_ListActivity.this,
InsertByEditTextActivity.class);
startActivity(registerIntent);
}});}
@Override
public boolean onKeyDown(int keyCode, KeyEvent event) {
if (keyCode == KeyEvent.KEYCODE_BACK) {
Intent intent = new Intent(DataBase_Item_ListActivity.this,
MainActivity.class);
intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
intent.putExtra("EXIT", true);
startActivity(intent);
}
return super.onKeyDown(keyCode, event);
}}
InsertByEditTextActivity.java
public class InsertByEditTextActivity extends AppCompatActivity {
DataBaseAdapter adapter;
CreateDataBase helper;
EditText fnameEdit;
Button submitBtn, resetBtn;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.register);
fnameEdit = (EditText) findViewById(R.id.et_fname);
submitBtn = (Button) findViewById(R.id.btn_submit);
resetBtn = (Button) findViewById(R.id.btn_reset);
adapter = new DataBaseAdapter(this);
submitBtn.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View arg0) {
String fnameValue = fnameEdit.getText().toString();
if(fnameValue.matches(""))
{
Toast.makeText(
getApplicationContext(),
"Please enter some value to enter in database",
Toast.LENGTH_LONG).show();
}
else
{
adapter.insertDetails(fnameValue);
Toast.makeText(
getApplicationContext(),
"Item saved to DataBase",
Toast.LENGTH_LONG).show();
Intent intent = new Intent(InsertByEditTextActivity.this,
DataBase_Item_ListActivity.class);
startActivity(intent);
}}});
resetBtn.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
fnameEdit.setText("");
}});}
@Override
public boolean onKeyDown(int keyCode, KeyEvent event) {
if (keyCode == KeyEvent.KEYCODE_BACK) {
Intent intent = new Intent(InsertByEditTextActivity.this,
MainActivity.class);
intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
intent.putExtra("EXIT", true);
startActivity(intent);
}
return super.onKeyDown(keyCode, event);
}}
MainActivity.java
public class MainActivity extends AppCompatActivity implements AdapterView.OnItemClickListener {
ListView rkt_ListView;
Button btn_save_in_database,btn_show_database;
private ArrayList<String> kaminey_dost_array_list = new ArrayList<String>();
private void kaminey_dost() {
kaminey_dost_array_list.add("Robi");
kaminey_dost_array_list.add("Anubhan");
kaminey_dost_array_list.add("Shikari");
kaminey_dost_array_list.add("Chittu");
kaminey_dost_array_list.add("Chuha");
kaminey_dost_array_list.add("Baba");
kaminey_dost_array_list.add("Gareeb");
kaminey_dost_array_list.add("Vinay");
kaminey_dost_array_list.add("Vakeel");
kaminey_dost_array_list.add("Gajju");
kaminey_dost_array_list.add("Ghoda");
kaminey_dost_array_list.add("Chhota Ghoda");
kaminey_dost_array_list.add("Peelu");
kaminey_dost_array_list.add("Anil");
}
RktArrayAdapter rktArrayAdapter;
DataBaseAdapter register;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
kaminey_dost();
rkt_ListView = (ListView) findViewById(R.id.rkt_listview);
register = new DataBaseAdapter(this);
rktArrayAdapter = new RktArrayAdapter(
this,
R.layout.list_row,
android.R.id.text1,
kaminey_dost_array_list
);
rkt_ListView.setAdapter(rktArrayAdapter);
rkt_ListView.setOnItemClickListener(this);
btn_save_in_database = (Button) findViewById(R.id.btn_save_in_database);
btn_show_database= (Button) findViewById(R.id.btn_show_database);
btn_save_in_database.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String result = "";
List<String> resultList = rktArrayAdapter.getCheckedItems();
for (int i = 0; i < resultList.size(); i++) {
result += String.valueOf(resultList.get(i)) + "\n";
}
rktArrayAdapter.getCheckedItemPositions().toString();
if(result.matches(""))
{
Toast.makeText(
getApplicationContext(),
"You have not selected any value to save in database",
Toast.LENGTH_LONG).show(); }
else
{
Toast.makeText(
getApplicationContext(),
result+" "+"saved to DataBase",
Toast.LENGTH_LONG).show();
long val = register.insertDetails(result);
Intent send=new Intent(getApplicationContext(),DataBase_Item_ListActivity.class);
startActivity(send);
}}});
btn_show_database.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Intent send=new Intent(getApplicationContext(),DataBase_Item_ListActivity.class);
startActivity(send);
}
}); }
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
rktArrayAdapter.rkt_toggleChecked(i);
}
public class RktArrayAdapter extends ArrayAdapter<String> {
private HashMap<Integer, Boolean> myChecked = new HashMap<Integer, Boolean>();
public RktArrayAdapter(Context context, int resource,
int textViewResourceId, List<String> objects) {
super(context, resource, textViewResourceId, objects);
for(int i = 0; i < objects.size(); i++){
myChecked.put(i, false);
}
}
public void rkt_toggleChecked(int position){
if(myChecked.get(position)){
myChecked.put(position, false);
}else{
myChecked.put(position, true);
}
notifyDataSetChanged();
}
public List<Integer> getCheckedItemPositions(){
List<Integer> checkedItemPositions = new ArrayList<Integer>();
for(int i = 0; i < myChecked.size(); i++){
if (myChecked.get(i)){
(checkedItemPositions).add(i);
} }
return checkedItemPositions;
}
public List<String> getCheckedItems(){
List<String> checkedItems = new ArrayList<String>();
for(int i = 0; i < myChecked.size(); i++){
if (myChecked.get(i)){
(checkedItems).add(kaminey_dost_array_list.get(i));
}
} return checkedItems;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
View row = convertView;
if(row==null){
LayoutInflater inflater=getLayoutInflater();
row=inflater.inflate(R.layout.list_row, parent, false);
}
CheckedTextView checked_TextView = (CheckedTextView)row.findViewById(R.id.checked_textview);
checked_TextView.setText(kaminey_dost_array_list.get(position));
Boolean checked = myChecked.get(position);
if (checked != null) {
checked_TextView.setChecked(checked);
}
return row;
} }
@Override
public boolean onKeyDown(int keyCode, KeyEvent event) {
if (keyCode == KeyEvent.KEYCODE_BACK) {
Intent intent = new Intent(MainActivity.this,
SplashActivity.class);
intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
intent.putExtra("EXIT", true);
startActivity(intent);
}
return super.onKeyDown(keyCode, event);
}}
UpdateRecordActivity.java
public class UpdateRecordActivity extends AppCompatActivity {
DataBaseAdapter regadapter;
int rowId;
Cursor c;
EditText fname;
Button editSubmit, btnDelete;
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.editregister);
fname = (EditText) findViewById(R.id.et_editfname);
editSubmit = (Button) findViewById(R.id.btn_update);
btnDelete = (Button) findViewById(R.id.btn_delete);
Bundle showData = getIntent().getExtras();
rowId = showData.getInt("keyid");
// Toast.makeText(getApplicationContext(), Integer.toString(rowId),
// 500).show();
regadapter = new DataBaseAdapter(this);
c = regadapter.queryAll(rowId);
if (c.moveToFirst()) {
do {
fname.setText(c.getString(1));
} while (c.moveToNext());
}
editSubmit.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
regadapter.updateldetail(rowId, fname.getText().toString());
Toast.makeText(
getApplicationContext(),
"Item successfully updated to DataBase",
Toast.LENGTH_LONG).show();
finish();
}
});
btnDelete.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
regadapter.deletOneRecord(rowId);
Toast.makeText(
getApplicationContext(),
"Item successfully removed from DataBase",
Toast.LENGTH_LONG).show();
finish();
}});}
@Override
public boolean onKeyDown(int keyCode, KeyEvent event) {
if (keyCode == KeyEvent.KEYCODE_BACK) {
Intent intent = new Intent(UpdateRecordActivity.this,
DataBase_Item_ListActivity.class);
intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
intent.putExtra("EXIT", true);
startActivity(intent);
}
return super.onKeyDown(keyCode, event);
}
}
Hope this tutorial will help you. Download complete project HERE.
Here is the ouput screen :
