Good day, I was trying to retrieved my data's and display them in a simple way. Here's my sample code: SQLiteLocalDatabase .java
public class SQLiteLocalDatabase extends SQLiteOpenHelper{
private SQLiteDatabase sqLiteDatabase;
private static final String DB_NAME = "project.db";
private static final int VERSION = 1;
public static final String DB_TABLE = " user ";
public static final String ID = " _id ";
public static final String FULL_NAME = " fullname ";
public static final String LOCATION = " location ";
public static final String EMAIL_ADD = " email ";
public static final String PASSWORD = " password ";
public SQLiteLocalDatabase(Context context) {
super(context, DB_NAME, null, VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String queryTable = " CREATE TABLE " + DB_TABLE + "( " + ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "+ FULL_NAME + " TEXT , " + LOCATION + " TEXT NOT NULL, " + EMAIL_ADD + " TEXT , " + PASSWORD + " TEXT " + " ) ";
//setUpDb();
db.execSQL(queryTable);
}
public void setUpDb(){
//TO OPEN DATABASE - RE-WRITABLE
sqLiteDatabase = getWritableDatabase();
}
public void closeTransactionDb(){
//CLOSE DB IF OPEN
if(sqLiteDatabase != null && sqLiteDatabase.isOpen()){
sqLiteDatabase.close();
}
}
//INSERT DATA
public long insert(int id,String fullname, String location,String email,String password){
//CONTENT VALUE contains name-value-pairs
ContentValues values = new ContentValues();
if(id != -1) {
values.put(ID,id);
values.put(FULL_NAME, fullname);
values.put(LOCATION, location);
values.put(EMAIL_ADD, email);
values.put(PASSWORD, password);
}
//Object Table, column, values
return sqLiteDatabase.insert(DB_TABLE, null, values);
}
//UPDATE
public long update(int id, String fullname,String location,String email, String password){
//CONTENT VALUE contains name-value-pairs
ContentValues values = new ContentValues();
values.put(FULL_NAME,fullname);
values.put(LOCATION,location);
values.put(EMAIL_ADD,email);
values.put(PASSWORD,password);
//WHERE
String where = ID + " = " +id;
//Object Table, values, destination-id
return sqLiteDatabase.update(DB_TABLE, values, where, null);
}
//DELETE
//
public long delete(int id){
//WHERE
String where = ID + " = " +id;
//Object Table, values, destination-id
return sqLiteDatabase.delete(DB_TABLE, where, null);
}
public Cursor getAllRecords(){
String queryDB = "SELECT * FROM " + DB_TABLE;
return sqLiteDatabase.rawQuery(queryDB, null);
}
public Cursor getSingleRecord(){
String querySingleRecord = "SELECT * FROM " + DB_TABLE + "WHERE _id = " +ID;
return sqLiteDatabase.rawQuery(querySingleRecord,null);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
For my main: AddItemsActivity .java
public class AddItemsActivity extends AppCompatActivity implements View.OnClickListener{
SQLiteLocalDatabase sqLiteLocalDatabase;
TextView textViewDbData;
EditText editTextFullName;
EditText editTextAddress;
EditText editTextEmailAdd;
EditText editTextPassword;
EditText editTextIDNO;
Button btnSave;
Button btnUpdate;
Button btnSearch;
Button btnDelete;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_add_items);
sqLiteLocalDatabase = new SQLiteLocalDatabase(AddItemsActivity.this);
//CALL FUNCTION
setUpWidgets();
}
public void setUpWidgets(){
textViewDbData = (TextView) findViewById(R.id.textViewDbData);
editTextFullName = (EditText) findViewById(R.id.editTextViewAddFullName);
editTextAddress = (EditText) findViewById(R.id.editTextAddLocation);
editTextEmailAdd = (EditText) findViewById(R.id.editTextAddEmailAddress);
editTextPassword = (EditText) findViewById(R.id.editTextRegPassword);
editTextIDNO = (EditText) findViewById(R.id.editTextAddID);
btnSave = (Button) findViewById(R.id.buttonAddContacts);
btnSave.setOnClickListener(this);
btnUpdate = (Button) findViewById(R.id.buttonUpdate);
btnUpdate.setOnClickListener(this);
btnSearch = (Button) findViewById(R.id.buttonDeleteRecord);
btnSearch.setOnClickListener(this);
btnDelete = (Button) findViewById(R.id.buttonShowRecords);
btnDelete.setOnClickListener(this);
}
public void clearTextFields(){
editTextFullName.setText("");
editTextAddress.setText("");
editTextEmailAdd.setText("");
editTextPassword.setText("");
editTextIDNO.setText("");
}
@Override
public void onClick(View v) {
String fullName = editTextFullName.getText().toString().trim();
String location = editTextAddress.getText().toString().trim();
String emailAdd = editTextEmailAdd.getText().toString().trim();
String password = editTextPassword.getText().toString().trim();
switch (v.getId()){
case R.id.buttonAddContacts:
//IF result == -1
long result = sqLiteLocalDatabase.insert(Integer.parseInt(getValue(editTextIDNO)), fullName, location, emailAdd, password);
if(result == -1){
Toast.makeText(AddItemsActivity.this, "Error: Someone own that Id",Toast.LENGTH_LONG).show();
}else
{
Toast.makeText(AddItemsActivity.this, "Success Id: " +result,Toast.LENGTH_LONG).show();
clearTextFields();
}
break;
case R.id.buttonUpdate:
if(editTextIDNO.getText().equals("")){
Toast.makeText(AddItemsActivity.this, "Please Enter User ID ",Toast.LENGTH_LONG).show();
}
else {
long update = sqLiteLocalDatabase.update(Integer.parseInt(getValue(editTextIDNO)),
getValue(editTextFullName),
getValue(editTextAddress),
getValue(editTextEmailAdd),
getValue(editTextPassword)
);
if (update == 0) {
Toast.makeText(AddItemsActivity.this, "Error Updating ", Toast.LENGTH_LONG).show();
} else if (update == -1) {
Toast.makeText(AddItemsActivity.this, "Successfully Modified", Toast.LENGTH_LONG).show();
clearTextFields();
} else {
Toast.makeText(AddItemsActivity.this, "Error All data updated Id: " + update, Toast.LENGTH_LONG).show();
}
}
break;
case R.id.buttonDeleteRecord:
long delete = sqLiteLocalDatabase.delete(Integer.parseInt(getValue(editTextIDNO)));
if(delete == 0) {
Toast.makeText(AddItemsActivity.this, "Error Delete", Toast.LENGTH_LONG).show();
} else
{
Toast.makeText(AddItemsActivity.this, "Success Delete", Toast.LENGTH_LONG).show();
clearTextFields();
}
break;
case R.id.buttonShowRecords:
StringBuffer finalData = new StringBuffer();
Cursor cursor = sqLiteLocalDatabase.getAllRecords();
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext() ){
finalData.append(cursor.getLong(cursor.getColumnIndex(sqLiteLocalDatabase.ID)));
finalData.append(" - ");
finalData.append(cursor.getString(cursor.getColumnIndex(sqLiteLocalDatabase.FULL_NAME)));
finalData.append(" - ");
finalData.append(cursor.getString(cursor.getColumnIndex(sqLiteLocalDatabase.LOCATION)));
finalData.append(" - ");
finalData.append(cursor.getInt(cursor.getColumnIndex(sqLiteLocalDatabase.EMAIL_ADD)));
finalData.append(" - ");
finalData.append(cursor.getInt(cursor.getColumnIndex(sqLiteLocalDatabase.PASSWORD)));
finalData.append("\n");
}
textViewDbData.setText(finalData);
break;
}
}
public String getValue(EditText editText){
return editText.getText().toString().trim();
}
@Override
protected void onStart() {
super.onStart();
sqLiteLocalDatabase.setUpDb();
}
@Override
protected void onStop() {
super.onStop();
sqLiteLocalDatabase.closeTransactionDb();
}
My problem was with the getAllRecords in the append part. Logcat:
