0

I make database for signup and login, I authenticate when user is enter email and password in signup form then and then he/she login. Now I want to display userwise data in application means eg. user A and user B, if user A login and enter the data in database then only user A is show his/her entered data not user B. Same way user B is enter data in database then and then only user B show his/her entered data. I have signup,login,account,expense,income tables. In short I want to display user wise data in whole application.

Here is my SQLIteHelper class which I use foreign key but I'm not success to make foreign key.

public class SQLite_Helper extends SQLiteOpenHelper {
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "login.db";

    // Contacts table name
    private static final String TABLE_SIGNIN = "user_login";
    private static final String TABLE_EXPENSE = "expense";
    private static final String SIGNUP_TABLE = "signup";
    private static final String TABLE_ACCOUNT = "account";
    private static final String TABLE_INCOME = "Income";
    private static final String TABLE_TRANSACTION = "tran";
    // Contacts Table Columns names
    @SuppressWarnings("unused")
    private static final String ACCT_ID = "actid";
    private static final String KEY_ID = "id";
    private static final String KEY_PWD = "pwd";

    private static final String ACCT_NAME = "acctname";
    private static final String ACCT_HEAD_NAME = "acctheadname";
    private static final String OPEN_BAL = "openbal";

    private static final String FIRST_NAME = "fname";
    private static final String LAST_NAME = "lname";
    private static final String EMAIL = "email";
    public static final String PASSWORD = "pwd";
    private static final String PAYEE = "pname";
    private static final String CATEGORY = "category";
    private static final String AMOUNT = "amnt";
    private static final String DUEDATE = "dd";
    private static final String PAYFROM = "payfrom";
    private static final String NOTES = "notes";
    private static final String ENOTES = "notes";
    private static final String EPAYEE = "pname";
    private static final String ECATEGORY = "category";
    private static final String EAMOUNT = "amnt";
    private static final String EDUEDATE = "dd";
    private static final String EPAYFROM = "payfrom";
    private static final String TID = "tid";
    private static final String TAMT = "tamt";
    private static final String TDATE = "tdate";
    private static final String EXPENSE_ID = "eid";
    private static final String INCOME_ID = "eid";
    private static final String REPEAT_EXPENSE = "erepeat";
    private static final String REPEAT_INCOME = "irepeat";
    private static final String SIGNIN_ID = "uid";
    private static final String SIGNUP_ID = "sid";

//  Signup Table
    String CREATE_SIGNUP_TABLE = "CREATE TABLE " + SIGNUP_TABLE + "("
            + SIGNUP_ID + "  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + FIRST_NAME + " TEXT not null unique," + LAST_NAME + " TEXT not null unique," + EMAIL + " TEXT not null unique,"
            + PASSWORD + " TEXT not null unique" + ")";

//  User_Login Table
    String CREATE_SIGNIN_TABLE = "CREATE TABLE " + TABLE_SIGNIN + "("
            + SIGNIN_ID + "  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+ KEY_ID + " TEXT not null unique," + KEY_PWD + " INTEGER not null unique," + " FOREIGN KEY ("+SIGNIN_ID+") REFERENCES "+SIGNUP_TABLE+" ("+SIGNUP_ID+"));";

//  Transaction Table   
    private static final String CREATE_TABLE_TRANSACTION = "CREATE TABLE " + TABLE_TRANSACTION
                + "(" + TID + " INTEGER PRIMARY KEY," + TAMT + " TEXT,"
                + TDATE + " TEXT" + ")";

//   Expense Table
    static String CREATE_TABLE_EXPENSE = "CREATE TABLE " + TABLE_EXPENSE + "("
                + EXPENSE_ID + " INTEGER PRIMARY KEY NOT NULL," + PAYEE + " TEXT," + CATEGORY + " TEXT," + AMOUNT + " NUMERIC,"
                + DUEDATE + " TEXT," + PAYFROM + " TEXT," + NOTES + " TEXT,"  + REPEAT_EXPENSE + " TEXT," + " FOREIGN KEY ("+EXPENSE_ID+") REFERENCES "+TABLE_SIGNIN+" ("+SIGNIN_ID+"));";

//  Income Table
    static String CREATE_TABLE_INCOME = "CREATE TABLE " + TABLE_INCOME + "("
                + INCOME_ID + " INTEGER PRIMARY KEY NOT NULL,"+ EPAYEE + " TEXT," + ECATEGORY + " TEXT," + EAMOUNT + " NUMERIC,"
                + EDUEDATE + " TEXT," + EPAYFROM + " TEXT," + ENOTES + " TEXT,"  + REPEAT_INCOME  + " TEXT," + " FOREIGN KEY ("+INCOME_ID+") REFERENCES "+TABLE_SIGNIN+" ("+SIGNIN_ID+"));";

//  Account Table
    static String CREATE_TABLE_ACCOUNT = "CREATE TABLE " + TABLE_ACCOUNT + "("
            + ACCT_ID + " INTEGER PRIMARY KEY NOT NULL," + ACCT_NAME + " TEXT,"
            + ACCT_HEAD_NAME + " TEXT," + OPEN_BAL + " TEXT," + " FOREIGN KEY ("+ACCT_ID+") REFERENCES "+TABLE_SIGNIN+" ("+SIGNIN_ID+"));";



    public SQLite_Helper(Context context) {
        super(context, "login.db", null, 1);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(CREATE_SIGNIN_TABLE);
        db.execSQL(CREATE_SIGNUP_TABLE);
        db.execSQL(CREATE_TABLE_ACCOUNT);
        db.execSQL(CREATE_TABLE_EXPENSE);
        db.execSQL(CREATE_TABLE_INCOME); 
        db.execSQL(CREATE_TABLE_TRANSACTION); 
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SIGNIN);
        db.execSQL("DROP TABLE IF EXISTS " + SIGNUP_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACCOUNT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EXPENSE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_INCOME);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TRANSACTION);
        // Create tables again
        onCreate(db);
    }

    // Adding new user
    void addContact(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        //values.put(SIGNIN_ID, user.getuId());
        values.put(KEY_ID, user.getId()); // Contact Name
        values.put(KEY_PWD, user.getPwd()); // Contact Phone

        // Inserting Row
        db.insert(TABLE_SIGNIN, null, values);
        db.close(); // Closing database connection
    }

    void addExpense(ExpenseRecord expenserec) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(EPAYEE, expenserec.getPayee()); // payee name
        values.put(ECATEGORY, expenserec.getCategory()); // Contact Phone
        values.put(EAMOUNT, expenserec.getAmount());
        values.put(EDUEDATE, expenserec.getDueDate());
        values.put(PAYFROM, expenserec.getPayfrom());
        values.put(ENOTES, expenserec.getNotes());

        values.put(REPEAT_EXPENSE,expenserec.getRepeat() );
        // Inserting Row
        db.insert(TABLE_EXPENSE, null, values);
        db.close(); // Closing database connection
    }

    void addIncome(Incomerecord incomerec) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(PAYEE, incomerec.getPayee()); // payee name
        values.put(CATEGORY, incomerec.getCategory()); // Contact Phone
        values.put(AMOUNT, incomerec.getAmount());
        values.put(DUEDATE, incomerec.getDueDate());
        values.put(PAYFROM, incomerec.getPayfrom());
        values.put(NOTES, incomerec.getNotes());
        values.put(REPEAT_INCOME, incomerec.getRepeat());
        // Inserting Row
        db.insert(TABLE_INCOME, null, values);
        db.close(); // Closing database connection
    }

    // Adding Signup field

    void addsignupfield(SignUp signup) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues cv = new ContentValues();
        cv.put(FIRST_NAME, signup.getFname());
        cv.put(LAST_NAME, signup.getLname());
        cv.put(EMAIL, signup.getEmail());
        cv.put(PASSWORD, signup.getPwd());

        db.insert(SIGNUP_TABLE, null, cv);
        db.close();
    }

    void addAcctfield(Account account) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues cv = new ContentValues();
        cv.put(ACCT_NAME, account.getAcctname());
        cv.put(ACCT_HEAD_NAME, account.getAcctheadname());
        cv.put(OPEN_BAL, account.getOpenbal());

        db.insert(TABLE_ACCOUNT, null, cv);
        db.close();
    }

    // Getting single user
    User getUser(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_SIGNIN,
                new String[] { KEY_ID, KEY_PWD }, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        // User user = new User(Integer.parseInt(cursor.getString(0)),
        // cursor.getString(1));
        User user = new User((cursor.getString(1)),(cursor.getString(2)));
        // Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
        // cursor.getString(1), cursor.getString(2));
        // return contact
        return user;
    }

    // Getting Single Signup

    SignUp getSignup(String email) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(SIGNUP_TABLE, new String[] { FIRST_NAME,
                LAST_NAME, EMAIL, PASSWORD }, EMAIL + "=?",
                new String[] { String.valueOf(email) }, null, null, null, null);

        if (cursor != null)
            cursor.moveToFirst();

        SignUp signup = new SignUp(cursor.getString(0), cursor.getString(1),
                cursor.getString(2), cursor.getString(3));

        return signup;
    }

    ExpenseRecord getExpense(String Payfrom) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_EXPENSE, new String[] { PAYEE, AMOUNT,
                CATEGORY, PAYFROM, NOTES,REPEAT_EXPENSE}, PAYFROM + "=?",
                new String[] { String.valueOf(Payfrom) }, null, null, null,
                null);

        if (cursor != null)
            cursor.moveToFirst();

        ExpenseRecord expenserec = new ExpenseRecord(cursor.getString(0),
                cursor.getString(1), Integer.parseInt(AMOUNT),
                cursor.getString(3), cursor.getString(4),cursor.getString(5),cursor.getString(6));

        return expenserec;
    }
    Incomerecord getIncome(String Payfrom) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_INCOME, new String[] { PAYEE, AMOUNT,
                CATEGORY, PAYFROM, NOTES,REPEAT_INCOME }, PAYFROM + "=?",
                new String[] { String.valueOf(Payfrom) }, null, null, null,
                null);

        if (cursor != null)
            cursor.moveToFirst();

        Incomerecord incomerec = new Incomerecord(cursor.getString(0),
                cursor.getString(1), Integer.parseInt(AMOUNT),
                cursor.getString(3), cursor.getString(4),cursor.getString(5),cursor.getString(6));

        return incomerec;
    }


    Account getAccount(String name) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_ACCOUNT, new String[] { ACCT_NAME,
                ACCT_HEAD_NAME, OPEN_BAL }, ACCT_NAME + "=?",
                new String[] { String.valueOf(ACCT_NAME) }, null, null, null,
                null);

        if (cursor != null)
            cursor.moveToFirst();

        // User user = new User(Integer.parseInt(cursor.getString(0)),
        // cursor.getString(1));
        Account account = new Account(cursor.getString(0),
                (cursor.getString(1)), cursor.getString(2));
        // Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
        // cursor.getString(1), cursor.getString(2));
        // return contact
        return account;
    }

    // Getting All Contacts
    public List<User> getAllContacts() {
        List<User> userlist = new ArrayList<User>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_SIGNIN;

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

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                // Contact contact = new Contact();
                User user = new User();
                // user.setId(Integer.parseInt(cursor.getString(0)));
                // user.setPwd(cursor.getString(1));
                //user.setuId(Integer.parseInt(cursor.getString(0)));
                user.setId(cursor.getString(1));
                user.setPwd((cursor.getString(2)));

                // Adding contact to list
                userlist.add(user);
            } while (cursor.moveToNext());
        }
        cursor.close();
        // return contact list
        return userlist;
    }

    public List<ExpenseRecord> getAllExpense() {
        List<ExpenseRecord> userlist1 = new ArrayList<ExpenseRecord>();
        // Select All Query

        Log.d("Reading : ", "Reading all Expense..");
        String selectQuery = "SELECT * FROM " + TABLE_EXPENSE;
        Log.e("all data", "-->" + selectQuery);
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToPosition(0)) {
            do {
                ExpenseRecord exp = new ExpenseRecord();
                exp.setPayee(cursor.getString(0));
                exp.setCategory(cursor.getString(1));
                exp.setAmount(cursor.getInt(2));
                exp.setDueDate(cursor.getString(3));
                exp.setPayfrom(cursor.getString(4));
                exp.setNotes(cursor.getString(5));

                userlist1.add(exp);

            } while (cursor.moveToNext());
        }
        cursor.close();
        // return contact list
        return userlist1;
    }
    public List<Incomerecord> getAllIncome() {
        List<Incomerecord> userlist2 = new ArrayList<Incomerecord>();
        // Select All Query

        Log.d("Reading : ", "Reading all Income..");
        String selectQuery = "SELECT * FROM " + TABLE_INCOME;
        Log.e("all data", "-->" + selectQuery);
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToPosition(0)) {
            do {
                Incomerecord ixp = new Incomerecord();
                ixp.setPayee(cursor.getString(0));
                ixp.setCategory(cursor.getString(1));
                ixp.setAmount(cursor.getInt(2));
                ixp.setDueDate(cursor.getString(3));
                ixp.setPayfrom(cursor.getString(4));
                ixp.setNotes(cursor.getString(5));
                ixp.setRepeat(cursor.getString(6));
                userlist2.add(ixp);

            } while (cursor.moveToNext());
        }
        cursor.close();
        // return contact list
        return userlist2;
    }

    public String checkLogin(String pass) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(SIGNUP_TABLE, null, "pwd" + "='" + pass.trim()
                + "'", null, null, null, null);

        if (cursor == null || cursor.getCount() == 0)
            return "";
        cursor.moveToFirst();
        String password = cursor.getString(cursor.getColumnIndex("pwd"));
        return password;

    }
    public String checkLogin2(String email) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(SIGNUP_TABLE, null, "email" + "='" + email.trim()
                + "'", null, null, null, null);

        if (cursor == null || cursor.getCount() == 0)
            return "";
        cursor.moveToFirst();
        String emailid = cursor.getString(cursor.getColumnIndex("email"));
        return emailid;

    }
    public String checkLogin3(String signupid) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(SIGNUP_TABLE, null, "sid" + "='" + signupid.trim()
                + "'", null, null, null, null);

        if (cursor == null || cursor.getCount() == 0)
            return "";
        cursor.moveToFirst();
        String emailid = cursor.getString(cursor.getColumnIndex("sid"));
        return emailid;

    }



    // Getting All Signup User

    public List<SignUp> getAllSignup() {
        List<SignUp> signuplist = new ArrayList<SignUp>();

        String select_statement = "SELECT * FROM " + SIGNUP_TABLE;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(select_statement, null);

        if (c.moveToPosition(0)) {
            do {
                SignUp signup = new SignUp();
                signup.setFname(c.getString(0));
                signup.setLname(c.getString(1));
                signup.setEmail(c.getString(2));
                signup.setPwd(c.getString(3));

                signuplist.add(signup);

            } while (c.moveToNext());
        }
        c.close();
        return signuplist;
    }

    public List<Account> getAllAccounts() {
        List<Account> accountlist = new ArrayList<Account>();

        String select_statement = "SELECT * FROM " + TABLE_ACCOUNT;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(select_statement, null);

        if (c.moveToPosition(0)) {
            do {
                Account account = new Account();
                account.setAcctname(c.getString(0));
                account.setAcctheadname((c.getString(1)));
                account.setopenbal(((c.getString(2))));

                accountlist.add(account);

            } while (c.moveToNext());
        }

        c.close();
        return accountlist;

    }

    // Updating single contact
    public int updateContact(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ID, user.getId());
        values.put(KEY_PWD, user.getPwd());

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

    // Deleting single contact
    public void deleteContact(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_SIGNIN, KEY_ID + " = ?",
                new String[] { String.valueOf(user.getId()) });
        db.close();
    }

    // Deleting single Signup
    public void deleteSignup(SignUp signup) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(SIGNUP_TABLE + " = ?", null,
                new String[] { String.valueOf(signup.getEmail()) });
        db.close();
    }

    public int updateAccount(Account account) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(OPEN_BAL, account.open_bal);
        Log.i("Result for Inside updateaccount", "" + values);

        // return db.update(TABLE_ACCOUNT, values, "openbal"+"="+5000, null);

        // updating row

        final String[] whereArgs = { account.Acct_id.toString() };
        return db.update(TABLE_ACCOUNT, values, ACCT_ID + " = "
                + account.Acct_id.toString(), null);
        // new String[] { String.valueOf(ACCT_ID) });

    }

    // Getting contacts Count

    public int getContactsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_SIGNIN;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

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

}
Kara
  • 6,115
  • 16
  • 50
  • 57
jack
  • 338
  • 1
  • 3
  • 29
  • This place is not a place for spoon feeding codes.You must want to show your efforts.What you tried by now.Just post it. – ShihabSoft Apr 17 '14 at 06:36
  • So, your userId is the centre of your application. You simply need to provide UserId as foreign key to respective tables and just query the tables with userId which is logged in. – Chintan Soni Apr 17 '14 at 06:45
  • Also, your question is too abstract. SO is not a code generator where you input your concept or idea behind the application, and you will get the output. Here, we help users where they get stuck somewhere, and users stuck when they try something. So, please show what have you tried so far. – Chintan Soni Apr 17 '14 at 06:48
  • ok. have a look at this answer, I am sure you will solve your problem of Foreign key: http://stackoverflow.com/a/5289976/1739882 – Chintan Soni Apr 17 '14 at 07:21
  • i already try that post but i m not success. – jack Apr 17 '14 at 07:24
  • Ok... can you post your full code of database helper class ? – Chintan Soni Apr 17 '14 at 07:25
  • i already post the code for whole class of sqlitehelper, now tell me about foreignkey – jack Apr 17 '14 at 07:51

1 Answers1

1

After login is authorized, you can save the user id to SharedPreferences, and then call your queries by that id which is saved in SharedPreferences till the user is logged out.

This is your SharedPreferences class, which is static so you can reach from every activity.

public class SharedPref {

    public static void setDefaults(String key, String value, Context context) {
        SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(context);
        SharedPreferences.Editor editor = prefs.edit();
        editor.putString(key, value);
        editor.commit();
    }

    public static String getDefaults(String key, Context context) {
        SharedPreferences preferences = PreferenceManager.getDefaultSharedPreferences(context);
        return preferences.getString(key, null);
    }

}

This is how you are going to set and get your user id information.

Save to SharedPreferences

   // after user is logged in successfully, save the user id to sharedpref
    SharedPref.setDefaults("user_id", "user id is written here", context);

Get from SharedPreferences

SharedPref.getDefaults("user_id", getApplicationContext())
canova
  • 3,965
  • 2
  • 22
  • 39