How to get data from sqlite in Android?

Question

I have to implement login app in my Android Application using SQLite database. First i created sign up database with inserted the values in table and i have done this point .And When i run my app first i sign up successfully and insert the data in Database and then i have login data is not getting it shows the error NullPointerException.Here is my code.

public class DataBaseHandller extends SQLiteOpenHelper 

{

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

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "LeadManagment";

    // table's name
    private static final String TABLE_REGISTER = "registration";
// Register Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static String KEY_EMAIL = "email";
    private static final String KEY_PIN = "pin";

@Override
    public void onCreate(SQLiteDatabase db) 
    {
       String CREATE_REGISTER_TABLE = "CREATE TABLE " + TABLE_REGISTER + "("
         + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
         + KEY_EMAIL + " TEXT," + KEY_PIN + " TEXT" + ")";
       db.execSQL(CREATE_REGISTER_TABLE);
}


 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_REGISTER);


    // Create tables again
    onCreate(db);
    }


 /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new contact Or Insert Field In TableS
    public void Add_Contact(Register_BeanClass register_Bean) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues insert_ContentValues = new ContentValues();
    insert_ContentValues.put(KEY_NAME, register_Bean.get_name());
    insert_ContentValues.put(KEY_EMAIL, register_Bean.get_email());
    insert_ContentValues.put(KEY_PIN, register_Bean.get_pin());
    // Inserting Row
    db.insert(TABLE_REGISTER, null, insert_ContentValues);
    db.close(); // Closing database connection
    }

    //Get Single Antry
    public String getSinlgeEntry(String userName)
    {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor=db.query(TABLE_REGISTER, null,  KEY_EMAIL +="?", new String[]{userName}, null, null, null);
        if(cursor.getCount()<1) // UserName Not Exist
        {
            cursor.close();
            return "NOT EXIST";
        }
        cursor.moveToFirst();
        String password= cursor.getString(cursor.getColumnIndex(KEY_PIN));
        cursor.close();
        return password;                
    }



    // Getting All Contacts
    public ArrayList<Register_BeanClass> Get_Contacts() 
    {

        try 
        {

        register_list.clear();

        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_REGISTER;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) 
        {
        do 
           {

            Register_BeanClass contact = new Register_BeanClass();

            contact.setId(Integer.parseInt(cursor.getString(0)));
            contact.set_name(cursor.getString(1));
            contact.set_email(cursor.getString(2));
            contact.set_pin(cursor.getString(3));

            register_list.add(contact);


        } while (cursor.moveToNext());

     }

        // return contact list
        cursor.close();
        db.close();
        return register_list;
     } 

    catch (Exception e)
    {
        // TODO: handle exception
        Log.e("all_contact", "" + e);
    }

    return register_list;
    }


    // Updating single contact
    public int Update_RegisterField(Register_BeanClass update_Register_BeanClass) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues updateContentvalues = new ContentValues();
    updateContentvalues.put(KEY_NAME, update_Register_BeanClass.get_name());
    updateContentvalues.put(KEY_EMAIL, update_Register_BeanClass.get_email());
    updateContentvalues.put(KEY_PIN, update_Register_BeanClass.get_pin());

    // updating row
    return db.update(TABLE_REGISTER, updateContentvalues, KEY_ID + " = ?",
        new String[] { String.valueOf(update_Register_BeanClass.getId()) });
    }


    // Deleting single contact
    public void Delete_Contact(int id) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_REGISTER, KEY_ID + " = ?",
        new String[] { String.valueOf(id) });
    db.close();
    }



    // Getting contacts Count
    public int Get_Total_Contacts() {
    String countQuery = "SELECT  * FROM " + TABLE_REGISTER;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    cursor.close();

    // return count
    return cursor.getCount();
    }

}



    public class Registration_Activity1 extends Activity
    {
        TextView eText_TotalRecord;
        EditText editTextUserName,editTextEmailId , editTextPin,editTextConfirmPin;
        Button btnCreateAccount;
        Button btnViewDetails;

        DataBaseHandller dbHandller=new DataBaseHandller(this);


        public void onCreate(Bundle savedInstanceState) 
        {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.registration_activity1);


            editTextUserName = (EditText) findViewById(R.id.editText_regName);
            editTextEmailId = (EditText) findViewById(R.id.editText_regEmail);
            editTextPin = (EditText) findViewById(R.id.editText_regApp_Pin);
            editTextConfirmPin = (EditText) findViewById(R.id.editText_regConfirm_App_Pin);

            eText_TotalRecord=(TextView)findViewById(R.id.etext_totalRecord);


            btnCreateAccount=(Button)findViewById(R.id.reg_btnSubmit);
            btnCreateAccount.setOnClickListener(new OnClickListener() {

                @SuppressWarnings("unused")
                @Override
                public void onClick(View arg0) {
                    // TODO Auto-generated method stub

                    String userName=editTextUserName.getText().toString();
                    String userEmail=editTextEmailId.getText().toString();
                    String userPin=editTextPin.getText().toString();
                    String userConfirmPin=editTextConfirmPin.getText().toString();


                    // check if any of the fields are vaccant
                    if(userName.equals("")||userEmail.equals("")||userPin.equals("")||userConfirmPin.equals(""))
                    {
                            Toast.makeText(getApplicationContext(), "Field Vaccant", Toast.LENGTH_LONG).show();
                            return;
                    }
                    // check if both password matches
                    if(!userPin.equals(userConfirmPin))
                    {
                        Toast.makeText(getApplicationContext(), "Password does not match", Toast.LENGTH_LONG).show();
                        return;
                    }
                    else
                    {
                        // Save the Data in Database
                        dbHandller.Add_Contact(new Register_BeanClass(0, userName,userEmail,userPin));

                        Toast.makeText(getApplicationContext(), "Account Successfully Created ", Toast.LENGTH_LONG).show();
                    }
                }
            });


btnLogin=(Button) findViewById(R.id.btn_Login);
        btnLogin.setOnClickListener(new OnClickListener()
        {

            public void onClick(View arg0) {
                // TODO Auto-generated method stub

                str_EmailId=editText_Email.getText().toString().trim();
                str_AppPin=editText_App_Pin.getText().toString().trim();

                // fetch the Password form database for respective user name
                String storedApplication=db.getSinlgeEntry(str_AppPin);


                if(str_AppPin.equals(storedApplication))
                {
                    Toast.makeText(Application_Pin_Activity2.this,"Access Allow", Toast.LENGTH_LONG).show();
                }

                else
                {
                    Toast.makeText(Application_Pin_Activity2.this, "Sorry ! Wrong Pin", Toast.LENGTH_LONG).show();
                }


            }
        });

}

Answer 1

DBhelper dbhlpr;
Context mContext;
SQLiteDatabase db;
ArrayList<String> results;

     results = new ArrayList<String>();
     mContext = getApplicationContext();
     dbhlpr = new DBhelper(mContext);
     db = dbhlpr.getWritableDatabase();

Cursor c = db.rawQuery("SELECT name, phone FROM friends",null);
//Here name and phone are the field name and friends is the table name

if (c != null) 
{

if (c.moveToFirst())

{

 do 
  {
   String name = c.getString(c.getColumnIndex("name"));
   int phone = c.getInt(c.getColumnIndex("phone"));
   results.add("name " + name + ",phone: " + phone);
   //In results we are adding the data from database
   Toast.makeText(Data.this, "Name...."+name+"  Phone...."+phone, 10000).show();
  } while (c.moveToNext());
 }
}

Answer 2

I recommend the use of different clases for different purposes. I think that you poster here the whole class for the sake of simplicity.

You should have a class helper to create/upgrade your database. It is like this:

public class MainDatabaseHelper extends SQLiteOpenHelper {

    private static MainDatabaseHelper mInstance = null;

    private static final String DATABASE_NAME = "persistence.db";
    private static final int DATABASE_VERSION = 1;

    public static MainDatabaseHelper getInstance(Context context) {

        if (mInstance == null) {
            mInstance = new MainDatabaseHelper(context.getApplicationContext());
        }
        return mInstance;
    }

    private MainDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        System.out.println("main database helper created, private constructor");
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        DB_BarTable.onCreate(database);
        DB_FooTable.onCreate(database);

    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        DB_FooTable.onUpgrade(database, oldVersion, newVersion);
        DB_Bar_LINETable.onUpgrade(database, oldVersion, newVersion);

    }
}

After that, wrap every class in a class, like this:

public class DB_FooTable {
    public static final String TABLE_NAME = "foo";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_NAME = "name";

    // TODO foreign keys
    public static final String DATABASE_CREATE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("
            + COLUMN_IDHIDDEN + " integer primary key autoincrement," + COLUMN_ID + " integer," + COLUMN_NAME+ " text);";

    public static void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
        Log.i(Constants.TAG, "Creating database " + TABLE_NAME);
    }

    public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        Log.w(Constants.TAG, "Upgrading database from version " + oldVersion + " to " + newVersion
                + ", which will destroy all old data");
        database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(database);
    }

}

Finally, use a DAO (Data Access Object) to access yo your data in a different tier. An example could be this one:

public class FooDAO {
    public static final int MAX_RESULTS = 30;

    public Future<List<Foo>> getAll(final Context context, final int pagination) {

        Callable<List<Foo>> c = new Callable<List<Foo>>() {

            @Override
            public List<Foo> call() {

                MainDatabaseHelper dbHelper = MainDatabaseHelper.getInstance(context);
                ArrayList<Foo> items = new ArrayList<Foo>();

                SQLiteDatabase db = dbHelper.getWritableDatabase();

                Cursor cursor = null;

                // No parameters, load MAX_RESULTS first items
                if (pagination < 1) {
                    cursor = db.query(DB_FooTable.TABLE_NAME, null, null, null, null, null,
                            null, String.valueOf(MAX_RESULTS));


                while (cursor.moveToNext()) {
                    Foo o = new Foo();
                    o.setId(cursor.getInt(cursor.getColumnIndexOrThrow(DB_FooTable.COLUMN_ID)));
                    o.setName(cursor.getString(cursor.getColumnIndexOrThrow(DB_FooTable.COLUMN_NAME)));

                    items.add(o);
                }
                return items;
            };
        };
        ScheduledExecutorService s = Executors.newSingleThreadScheduledExecutor();
        return s.submit(c);
    }


    public void delete(final Context context, final long idToDelete) {

        Log.i(Constants.TAG, "onDeleteItem single" + idToDelete);
        Thread t = new Thread(new Runnable() {

            @Override
            public void run() {
                long idDeleted = -1;

                MainDatabaseHelper dbHelper = MainDatabaseHelper.getInstance(context);
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                idDeleted = db.delete(DB_FooTable.TABLE_NAME, DB_FooTable.COLUMN_ID + " = " + idToDelete,
                        null);

                Log.i(Constants.TAG, "ITEM DELETED, id " + idDeleted);
            }
        });
        t.start();
    }

    public Future<Long> insert(final Context context, final Foo p) {

        Callable<Long> c = new Callable<Long>() {

            @Override
            public Long call() throws Exception {
                long id = -1;

                ContentValues cv = new ContentValues();
                cv.put(DB_FooTable.COLUMN_ID, p.getId());
                cv.put(DB_FooTable.COLUMN_NAME, p.getName());

                MainDatabaseHelper dbHelper = MainDatabaseHelper.getInstance(context);
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                id = db.insert(DB_FooTable.TABLE_NAME, "<empty>", cv);

                return id;
            };
        };
        ScheduledExecutorService s = Executors.newSingleThreadScheduledExecutor();
        return s.submit(c);
    }

}

Hope it helps!!

Obviously you need a business object to encapsulate you results!(class Foo here)

Answer 3

I recommend you to make the queries outside this class.

DataBaseHandller dbHelper= new DataBaseHandller (this, "DB_NAME", null, 1);
SQLiteDatabase db = dbHelper.getWritableDatabase();

...

And the same code as you pasted there.