I'm making an app, which needs a sqlite database. I can add data and view it, but on each restart of the app, closing, reopening the data get deleted. Why is that?
DB Handler:
package com.spxc.wakeuptext.sql;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHandler extends SQLiteOpenHelper {
// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "contactsManager";
// Contacts table name
public static final String TABLE_CONTACTS = "contacts";
// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PH_NO = "phone_number";
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
+ KEY_PH_NO + " TEXT" + ")";
db.execSQL(CREATE_CONTACTS_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
// Create tables again
onCreate(db);
}
/**
* All CRUD(Create, Read, Update, Delete) Operations
*/
public // Adding new contact
void addContact(WhiteList contact) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName()); // Contact Name
values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone
// Inserting Row
db.insert(TABLE_CONTACTS, null, values);
db.close(); // Closing database connection
}
// Getting single contact
WhiteList getContact(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
WhiteList contact = new WhiteList(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return contact
return contact;
}
// Getting All Contacts
public List<WhiteList> getAllContacts() {
List<WhiteList> contactList = new ArrayList<WhiteList>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
WhiteList contact = new WhiteList();
contact.setID(Integer.parseInt(cursor.getString(0)));
contact.setName(cursor.getString(1));
contact.setPhoneNumber(cursor.getString(2));
// Adding contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}
// return contact list
return contactList;
}
// Updating single contact
public int updateContact(WhiteList contact) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName());
values.put(KEY_PH_NO, contact.getPhoneNumber());
// updating row
return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getID()) });
}
// Deleting single contact
public void deleteContact(WhiteList contact) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getID()) });
db.close();
}
// Getting contacts Count
public int getContactsCount() {
String countQuery = "SELECT * FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
// return count
return cursor.getCount();
}
}
And I have my activity which is adding 1 record on startup just to have something there. My activity / fragment:
package com.spxc.wakeuptext.frag;
import java.util.ArrayList;
import java.util.List;
import android.app.AlertDialog;
import android.app.Dialog;
import android.app.AlertDialog.Builder;
import android.content.Context;
import android.content.DialogInterface;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.view.inputmethod.InputMethodManager;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.TextView;
import android.widget.Toast;
import com.actionbarsherlock.app.SherlockListFragment;
import com.actionbarsherlock.view.Menu;
import com.actionbarsherlock.view.MenuInflater;
import com.actionbarsherlock.view.MenuItem;
import com.spxc.wakeuptext.R;
import com.spxc.wakeuptext.sql.DatabaseHandler;
import com.spxc.wakeuptext.sql.WhiteList;
public class Fragment_2 extends SherlockListFragment{
private ArrayList<String> results = new ArrayList<String>();
private String tableName = DatabaseHandler.TABLE_CONTACTS;
private SQLiteDatabase newDB;
private static final int ADD = 1;
@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
Bundle savedInstanceState) {
setHasOptionsMenu(true);
View v = inflater.inflate(R.layout.fragment_2, null);
return v;
}
public void onActivityCreated(Bundle savedInstanceState) {
super.onActivityCreated(savedInstanceState);
DatabaseHandler db = new DatabaseHandler(getActivity());
db.addContact(new WhiteList("Ravi", "9100000000"));
openAndQueryDatabase();
displayResultList();
}
private void openAndQueryDatabase() {
try {
DatabaseHandler dbHelper = new DatabaseHandler(getActivity().getApplicationContext());
newDB = dbHelper.getWritableDatabase();
Cursor c = newDB.rawQuery("SELECT name, phone_number FROM " +
tableName, null);
if (c != null ) {
if (c.moveToFirst()) {
do {
String firstName = c.getString(c.getColumnIndex("name"));
int age = c.getInt(c.getColumnIndex("phone_number"));
results.add("Name: " + firstName + ",Pne: " + age);
}while (c.moveToNext());
}
}
} catch (SQLiteException se ) {
Log.e(getClass().getSimpleName(), "Could not create or Open the database");
} finally {
if (newDB != null)
newDB.execSQL("DELETE FROM " + tableName);
newDB.close();
}
}
private void displayResultList() {
TextView tView = new TextView(getActivity());
tView.setText("This data is retrieved from the database and only 4 " +
"of the results are displayed");
getListView().addHeaderView(tView);
setListAdapter(new ArrayAdapter<String>(getActivity(),
android.R.layout.simple_list_item_1, results));
getListView().setTextFilterEnabled(true);
}
@Override
public void onCreateOptionsMenu(Menu menu, MenuInflater inflater) {
super.onCreateOptionsMenu(menu, inflater);
MenuItem search = menu.add(0, ADD, 0, "Refresh");
search.setIcon(android.R.drawable.ic_menu_add);
search.setShowAsAction(MenuItem.SHOW_AS_ACTION_IF_ROOM);
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
switch (item.getItemId()) {
case ADD:
addNumber();
return true;
default:
return super.onOptionsItemSelected(item);
}
}
public void addNumber(){
LayoutInflater factory = LayoutInflater.from(getActivity());
final View textEntryView = factory.inflate(R.layout.text_entry, null);
final EditText name = (EditText) textEntryView.findViewById(R.id.editText1);
final EditText phone = (EditText) textEntryView.findViewById(R.id.editText2);
name.setHint("Name");
phone.setHint("Phone Number");
final AlertDialog.Builder alert = new AlertDialog.Builder(getActivity());
alert.setTitle(
"Contact information").setView(
textEntryView).setPositiveButton("Save",
new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog,
int whichButton) {
DatabaseHandler db = new DatabaseHandler(getActivity());
db.addContact(new WhiteList(name.getText().toString(), phone.getText().toString()));
openAndQueryDatabase();
displayResultList();
}
}).setNegativeButton("Cancel",
new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog,
int whichButton) {
/*
* User clicked cancel so do some stuff
*/
}
});
alert.show();
}
}
Why isn't my database data getting saved? I'm running the app on a real device. All the data get deleted on reopening the app. Any help is much appreciated!
This is because you are deleting all records from the table in every openAndQueryDatabase()
finally {
if (newDB != null)
newDB.execSQL("DELETE FROM " + tableName);
newDB.close();
}